Implementing User/Group security in a distributed split DB

[ecline]

Hi all,

A quick bit of background... Network Engineer/Admin with 13 years experience in network design, implementation, troubleshooting and admin. However, a VERY fledgeling Access programmer.

Employer has placed me in the position of putting together a client/server Access app for use on the Corporate WAN. I have the app together and working across the WAN (in beta test). Front-end DB on userPCs, Back-end on centralized server. However, I am unsure how to make the User/Group security that I've setup work on user's PCs. It works beautifully on my PC, but my test users are able to open the database with no prompt for UID and P/W.

How does one incorporate the security settings for distribution? Is it possible to store this information in the Back-end?

Any and all help is greatly sought and appreciated!

Regards,
Ed Cline

 

[THoey]

I haven't tried this yet myself, but I know that there are three different FAQ's that go into great detail on Security. Did you check those? If you still need more info, I am sure someone here can help you...
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ghubbell]

Hi Ed,
If you've distributed using the Package & Deployment Wizard, you are offered during one of its steps, to "pack along" additional files. You could/should be sending along your Mdw with the db via this method, and it should usually reside in the same folder as your Db for "simplicitys" sake.
If you're simply moving your db over the LAN, you'll have to disable each users default workgroup, "System.Mdw" using the Workgroup Administrator...have each workstation join your workgroup. In either case, something is not right with the security if someone can "walk in" using the default "system mdw"... Gord
ghubbell@total.net

 

[ecline]

Hi Gord,

Actually, I had split the DB using the Database Splitter and simply sent the front-end along. I will try the Package & Deployment Wizard out and see if this takes care of the issue.

Just for the sake of my curiosity... If I were to use the Work GroupAdmin program to create a WIF file for the database and sent that along to go in the same directory as the front-end, would that work as well? Just curious...

Thanks for the info, Gord!

Ed

 

[ghubbell]

A pleasure Ed, The Mdw file? Why sure, although your maintenance chores would increase if you need to change a security setting...but even that, no big deal really as you could paste over a new one if you have access to the other computers. Good luck Ed, Gord
ghubbell@total.net

 

[ecline]

Gord,

Where does one locate the Package & Deployment Wizard in Access 2000? Or is this some sort of add-on/plug-in? Please help... My deadline is quickly fading into the distance!

Thanks!
Ed Cline

 

[ghubbell]

Ed, I hope you are working with a developers edition? Reply while I dig out the steps to get there if you do... Gord
ghubbell@total.net

 

[ecline]

Ummmmmm... *looking EXTREMELY sheepish* Developer's Edition?

 

[Quehay]

ecline:

I'm working in a corporate environment with just the garden variety Access, and I don't miss the developer edition, althought source code control would be nice (of course Gord will likely respond with copious help shortly). The security warrants serious investigation and understanding before you get into using it--it can give you some serious headaches if you launch headlong into it (do a keyword search for 'security' and see the results...). There are other ways to protect the integrity of the system if that's what you're seeking (.mde & startup options), although protecting sensitive data requires security settings.

Another issue: Access is great but it is an albatross in the WAN context. I'd check with your admin (OK you said your strength is network admin) about bandwidth issues before getting too involved. Because of its file-server nature Access is a network hog. Maybe replication is the ticket for deployed and synchronized db's in remote locations (and I said security was a headache...).

 

[RickSpr]

"It works beautifully on my PC, but my test users are able to open the database with no prompt for UID and P/W."

What this tells me is that you didn't realize you have to create a secure workgroup file in order to truly limit access to your secured database. This is a very common mistake made by people using Access security. Some folks don't even realize the huge hole they have in their security.

The Access security model is always "turned on". But since it's a consumer product, Microsoft didn't want the security to be obtrusive, so what they did was provide a default "open" workgroup file as part of every installation of Access. If you use that workgroup file to implement security, you can get it to work fine--but anybody who has a copy of the original, unmodified workgroup (set up with the same workgroup id and organization name) has "open" access to your database.

To block this, you have to create a secured workgroup file, one that contains a different, secret workgroup id. When you do that, and then set up your users and groups and permissions in the database, the user permissions in the database will be associated with the secured workgroup. They won't work with the default workgroup any more.

In your case, you already have permissions in the database for the default workgroup, so first you have to remove all the permissions you put in with it. After you've done that, I recommend using the User-Level Security Wizard. If you're using Access 97, read up on the security features in the help file, and follow the instructions in the wizard very carefully. The Access 2000 wizard is much more bulletproof.

Note (not something you'll find in the help file, unless you're very good at reading between the lines): When you're in the database looking at permissions, all you see is User and Group names. In fact, though, the permissions are associated with a security identifier which is generated from the user or group account and the secured workgroup id. Thus, a given user or group can have separate sets of permissions for different workgroup files. That's why you have to remove the permissions that were granted on the default workgroup, before you start adding permissions on the secured workgroup. If you don't, they'll still be able to use the permissions associated with the default workgroup.

Even when you use the wizard, you'll still need to read the docs. Because you're forcing your users to use a secured workgroup, and not the default workgroup installed with Access, one of two things has to happen before they can open your database. Either they have to "join" your secured workgroup (using the Workgroup Administrator), or you have to give them a shortcut that contains the /wrkgrp command line parameter, and they can only use that shortcut to start your database (as opposed to, say, opening Access and then opening your database within it). If they join your workgroup, they'll lose access to other databases until they finish and join the default workgroup again; some users won't like that. If they use your shortcut, they lose alternative methods of getting to your database; other users won't like that. You'll have to weigh the alternatives in your particular environment, pick an alternative, and train the users one way or the other. I usually prefer the desktop shortcut method myself. Rick Sprague