Access Security Trouble - Users not getting prompt

[presslc]

Hi!

I have an access database that is in a network directory.

Here are the steps I've taken so far to secure the db.
1)Created a workgroup file specifically for the database and saved it in the same directory.
2)Created myself as a user and set admin password.
3)Removed admin from Admins group and created my own Admins2 group.
4)Removed all access from Users group and Admins group.
5)Created new database and imported objects.
I think that's about it.

Here's my problem. My users cannot get into the database. The message says they do not have permission, which is a good thing because that means it's protected (for the most part). But they are also NOT getting the prompt. On the other hand I'm getting prompted for every database I try to open. it's like I'm making changes to the system, not the security file for the database. Please HELP! I've been working on this for days and I can't figure out whats going on.

THANK YOU!!!!!

 

[LarryDeLaruelle]

Presslc:

One. All Access applications use a Workgroup Information File. In unsecured databases that is the Default.mdw.

Two. When you create a new Workgroup file, you are automatically joined to it and it becomes the default WIF. Thus, each database that you attempt will try to use the new WIF you created. Run the wrkgadm.exe to rejoin the default WIF for access to the unsecured databases.

Three. Your users are not being prompted with the logon because they are probably still using the Default.mdw. Run the wrkgadm.exe (on each of their systems) to join them to your new WIF.

Four. Doing number three will result in your users experiencing the same problem described in number 2.

Five. The fix. Leave your users joined to the default.mdw for all unsecured applicatons. For each secured database, create a shortcut on the users desktop pointing to that database. Edit the target string so it looks like this (change drive/directory names as appropriate):

"C:\Program Files\Microsoft Office\Office\MSAccess.exe" "G:\Database Folder\Application Folder\Application.mdb" /wrkgrp "G:\Database Folder\Applicaton Folder\Application.mdw"

The target string is composed of three elements: path to the executable (MSAccess.exe); path to the database and path to the WIF. (NOTE: quotes are only needed where the path contains spaces in any of the names.) The /wrkgrp is a switch that tells Access to open the database using the identified WIF.

Hope this helps.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[presslc]

LarryDeLaruelle -

You rock!
That's exactly what I've been looking for.
Thanks!

 

[RickSpr]

Presslc,

Frankly, I'm amazed that you got all the steps right without finding out about the Workgroup Administrators program, or the connection between setting Admin's password and getting the logon prompt.

Did you perhaps find a procedure somewhere that laid out the steps? If so, I'd urge you to read the help file topics on user-level security, for background. I'm a little concerned that you might have left a hole that you just haven't discovered yet.

BTW, you didn't mention encrypting your database. If it contains sensitive data, such as passwords, and you haven't encrypted it, then you could open it with Notepad and read the passwords.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[marksnow]

and if you have smart users they can modify the shortcut and bypass the WIF and hence login on the default WIF and have full access/control.

 

[RickSpr]

Marksnow,

Actually, no. Presslc said that users are not able to get in with the default WIF: "The message says they do not have permission...".

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[marksnow]

But couldn't they create their own WIF and swap over to that one??? sorry still learning on security and that was my understanding.

 

[RickSpr]

They can certainly create their own WIF, or even use the default WIF, but unless it contains a user account identical (including the Personal ID) to one that Presslc created in his/her WIF, none of the accounts they have will have permission to open the secured database.

A little explanation might help make this more understandable. The WIF contains user and group accounts only--no permissions. Each user and group account was created with a Personal ID (PID) that is supposed to be kept secret. Within the WIF, a Security ID (SID) is generated by an algorithm whose inputs are the account (user or group) name and PID. It is extremely unlikely that you could create an account with the same SID unless you use the same account name and PID. (That's why the PID should be kept secret.)

Permissions are stored in the secured database, associated with the SID of the account the permissions are granted to. When Jet checks permissions, it looks them up not by user or group name, but by SID, using the SIDs of the currently logged on user and any groups the user belongs to.

So you see, in order to hijack somebody's permissions, you'd have to create a user account with an identical SID, without knowing the user's PID. That's virtually impossible by trial and error, especially since you can't even find out what the SID value is (Jet makes the column it's stored in unavailable).

The problem that usually occurs, when people get their security setup wrong, is that they didn't create a unique WIF. Sometimes they just make a copy of system.mdw. By doing that, they wind up with an Admins group that's a duplicate (including the SID) of the one in system.mdw. Given that, anybody with a default system.mdw can log on as Admin and automatically become a member of the Admins group--which has the same SID as the Admins group of the faulty "secure" WIF. To Jet, that makes them an administrator of the "secure" database, and they can them give themselves all the permissions they want.

To create a unique WIF, you have to create it with a unique Workgroup ID (WID). The Admins group account is special, in that the WID is included as an input to the algorithm that generates its SID. Thus, if you have a unique WID, you get a unique SID for the Admins group, and the SID of the Admins group in a default WIF won't have administrative permissions in the unique WIF.

(Talk about your alphabet soup! )

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[presslc]

Hi!
Thanks for your replies! I think I got confused about the shortcut. I used the help file to get me as far as I got, but I didn't read far down enough to see the section about having secure and non-secure databases, which is the only place I found that addressed \WRKGRP.

In fact, I was kind of groping in the dark. I'm not typically an Access developer and am new to using Access security. I'm so thankful someone replied )

I'm not too worried about someone getting in a back way to read the data. My app owner's concern is more for the other users that can modify the data, and what they have access to modify.

Just in case, I also added an access group to the file directory where the short cut is kept. Only the appropriate users have access to that directory.

Hope it works.

Thanks again!!!!

 

[RickSpr]

Glad I could help. There are a number of proponents of VBA-based security in this forum. There are places where that's sufficient, but it's really very weak security, protecting against only the innocent. I always worry that novices will be misled into thinking they can avoid the complexity of user-level security by using VBA when it's really not sufficient. So it's gratifying that you tackled the better way, and that we now have another user-level security proponent here to help us guide them.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein