Access Security Mess 1

[autosol]

I have a split database, which was working fine on the first two machines it was implemented on. Development is ongoing on one with a test database, which is just a copy of the live database (I guess you'd call it the back end database) on machine two. Occasionally, data operations were performed on the back end from the development machine by simply using the Link Table Manager from the development machine. No problems. Oh, I didn't mention yet that security had been implemented months ago with a custom workgroup info file. Anyway, about two weeks ago, following installation of Microsoft Service Pack 1a on the back end machine, it became impossible to link the development machine app database to the back end db. I don't believe anything else significant changed. I have read tons of stuff on the subject of securing a database but can't come up with an answer. I understand this is complicated. Is it possible that something in updating XP has caused this or is it a coincidence? The back end files will work on either machine if both db files are used locally. The same mdw files exist on both machines. Whatever direction anyone out there might be able to give would be greatly appreciated. Thanks, Don

 

[psemianonymous]

I don't know specifically what happened.

It's possible that the folder permissions changed. It's possible that the directory structure changed.


You didn't mention why this was an Access security issue. Incidentally, the client machine does all the security checks, so the file server just ... serves files to the clients. So a service pack upgrade doesn't fundamentally change the way Access security works--the file server still just sends the backend/workgroup file over to the client machine, and the client machine/Access decides whether the user has the permissions to ... do whatever it is you're doing.


So check the folder permissions, check the table links (Tools->Add-Ins->Linked Table Manager), and check the shortcut you're using to get into the secured database.

 

[autosol]

Thanks for the reply.

The reason I posted this as a security issue is that, after things were working for months as described, I received a "You don't have the necessary permissions....." message when trying to Link to the backend data files using the Link Table Manager from the dev machine. This is a procedure I had been doing for months without problem and, when I posted my dilemma, the only thing I could think of that had changed is that I performed a Win XP update of Service Pack 1a on the backend machine. However, I did do something else. The dev machine had been setup with only the default Win Admin user when the backend db was setup with Jim as the owner. Recently I added Don as an Admin Win user and it as Don that I normally login to Win. However, logging as Jim yielded the same Access error when trying to link to the backend db. Is this significant?

Anyway, yesterday I changed the ownership of the backend db to Don by importing it into a new db while Don was logged into Access. This allowed me to link from the dev machine to the backend again. I don't understand what was going on but it's now working.

Now I want to get the app db under Don's ownership. I tried importing it to a new db but have received errors opening the new db such as "Error accessing file, network connection may have been lost." and "The expression you have entered has a function name that Microsoft Access can't find.". This is the same computer and installation of Access 2000 that was used to create the original dev db.

One other strange thing I have observed that seems to be related to all of this. I have noticed that even though permissions have been given to particular groups, let's say the Admins group, users that have been put into the Admins group are not getting those permissions. I have tried removing users from the group and adding them back in but that still did not do the trick.

Any thoughts? Your attention to this is greatly appreciated.

Don

 

[psemianonymous]

You've got to be absolutely sure as to what security workgroup file you're using. It's entirely possible to create a 'Jim' user on one workgroup and have an entirely different 'Jim' user on another workgroup.

So go to the Workgroup Administrator (for new versions of Access, it's in the Security menu, for older versions, hunt for WRKGADM.EXE), and check what workgroup you're 'joined to'. Or, check your shortcut (.LNK) file that you use to open the database. These are the only two ways to connect to a 'custom' workgroup file, and if you're not entirely sure which workgroup file you're connecting to/using, you need to get that straight.

Jeremy Wallace (JeremyNYC) has an excellent page on Access security:

http://www.abcdataworks.com/security.htm

A few things before you hop over there:

1. You have the change-of-ownership process down proper. Now just be sure which workgroup you're using when you try to change it to the 'new' owner.

2. All objects lose their special security permissions when they are transferred to a new database.

3. Be sure to make backups before you do anything drastic, this is just a tip.