Need 'bulletproof' security for tables in network environment for a single db

I've got a problem, and I couldn't find a good answer by searching this forum, so...

Up until now, I've been able to avoid using Access security on our network databases, because all users of a given db needed similar privileges, so I have addressed security so far by careful network directory permissions assigments.

However, I now have an existing database, on a network, where the data entries of the other users must be 'approved' by a supervisor. This approval must NOT be changeable by the other users (other than admin), no matter how they start up the db. I am aware that I can enact single-db security by setting up the appropriate shortcut, but my solution should work even if a user starts Access by other means.

There are many other databases in use on the network where I do not want Access security enacted, so I would prefer not to require username/passwords for all users on every db.

I need to make the solution somewhat 'hack'-proof. It's not that I mistrust the users, but we have a new 'business rule' that dictates that users must not be able to modify the approval once made, under any circumstances (it involves cash transactions).

I am still developing the db, so I haven't yet set up a fe/be structure, but I plan to after I sort this out.

We are using Access XP on Windows 2000 Server with some users' workstations still using Windows NT and some using XP.

Anyone have a good way out of this?

What I've done for some of mine is create a table that contains the Windows login IDs for each person that will be using it. Then on opening of the db I run a function that determines what the ID is that lookups the group on the table and will modify what is available to the user based on that. The user doesn't have to enter anything. Is this kind of what you're looking for?

Just a thought...

You can not keep out the riffraff unless you are using Access security with workgroups and permission's. Anybody with above novice Access experience can easily import/export your db objects (tables, forms, etc.) into a new database. They can just as easily link to your db and modify the table data from another db. Not to mention that they can alter the design view of your objects (tables, forms, modules, etc.) if the db is not secured. Creating a MDE version of your db will only protect the design of the forms, reports and modules but it will not protect the design of the tables, queries nor will it protect the data in the tables.

HTH

What you are describing is not trivial within the boundaries of Access security. The specific part that is going to be tough is the part where "once a record in table X is approved it cannot be modified."

The approach I might take is that the user doesn't actually enter data to table X. Instead, enter to table X_PreApproval. Then, when the supervisor approves it, manipulate the record using the supervisor's rights. Copy the record to table X and remove it from X_PreApproval. This probably means some canned procedures, either macros or VBA code, behind an "Approval" pushbutton on a form perhaps.

Then your folks could have read-only access (or no access at all, if that was what you wanted) to the X table. Of course, you still have to trust your supervisors with this scheme.

As to "bullet-proof" security, there are a few tricks to remember, and your desire to avoid passwords for other DBs is not going to be possible if you use Access security. The key to understanding this is that Access uses Windows Workgroup security. The workgroup file that contains users and passwords is not necessarily specific to the database. It is specific to the workstation.

When you join a workgroup, it is a registry entry, not a database entry. So if you exit the secured database and try to open another database, your registry still has you as requiring a password even for a non-secured database. It gets worse, of course, because in the non-secured database, you are neither the owner of the files nor listed in the default user list. So you have no rights. The way around this is to remember to join your default workgroup before entering any non-secured database. No joy there, I'm sure, but I won't sugar-coat it for you.

Now, as to the part about blocking unauthorized accesses, you need to read the help files about securing a database by blocking off the shift-key entry method and the function key methods to bypass system startup functions. They will all suggest that you have an opening switchboard form as a "startup form" (Help file hint) to drive your database actions. In the Form_Load routine, you can take action to enable or disable the special functions if you are an administrator account. But there are a couple of things you have to do to make sure you don't get blind-sided by someone coming in the wrong way.

The thing to protect against is the person who comes in while in the default workgroup that is created when MS Office is first installed. This workgroup thinks that ALL users are ADMIN and therefore are members of the ADMINS (note the "S") group. The solution is that you must completely avoid any reliance on group USERS or account ADMIN (no "S").

Make new group names for your levels of access rights. Add all your users to one of the new group names. Assure that they have adequate rights SOLELY through the new groups. Now go back to group USERS and remove ALL access rights from ALL objects in your tables. Go to user account ADMIN and remove it from the ADMINS group, but leave it as a USERS group member. (You cannot do otherwise, for ALL users are in group USERS, and this is hard-coded into the security manager.) You can make yourself a member of group ADMINS. Make absolutely sure that you do this BEFORE you remove ADMINS group from user ADMIN, because otherwise you have no administrators for the DB.

OK, now what will happen is that if the forgetful user comes in without joining the workgroup, they come in as user ADMIN - but that user has NO rights whatsoever. Your users who come in under their assigned usernames have rights associated with the groups you created. And while this is not bullet-proof, you can at least get MOST of what you want out of it.

Now, the final part has to do with how you let yourself into the system as an administrator. In the startup form, you can determine your current username. Using VBA, you can examine the Workspace(0).Users.User("your-name").Groups collection to see if the .Name of one of the groups is "Admins". ( ....Groups.Group.Name = "Admins") The number of groups to be examined is ....Groups.Count, so you can do a simple search loop to verify the presence of the group.

Then you can do things to set properties of the CurrentDB in order to allow you to close the switchboard form without exiting the database automatically. These things are referenced in the Help Files. Remember, first verify that you are in the Admins group BEFORE you attempt to modify the CurrentDB properties, because otherwise you will trigger error traps.

Thanks for your replies, everyone (particularly The_Doc_Man).

I'll have a go at this, but I'm sure to be back with a few specific questions.

By the way, is there a way to use the fact that all of my Access users have previously logged into the Windows 2000 network domain to avoid/replace the need to "re-log" into Access each time? Life would be good if something could be stored in each user's network profile once and for all.

The_doc_man said:
"The way around this is to remember to join your default workgroup before entering any non-secured database."

Assume I enact Access security on this db with all of the steps The_Doc_Man described.

Can I then use startup shortcuts that specify the network location of a copy of the default workgroup file when loading each of my unsecured db's to eliminate the login step?

I advise against 'joining' a PC to a specific security workgroup. That unnecessarily forces a user to log into the secured workgroup just to open any Access db's (new or old).

You should use a custom shortcut to open a secured db. The Target: field of the shortcut should look something like this to pass on the security info...
"C:\Program Files\Microsoft Office\Office\msaccess.exe" /wrkgrp "X:\Testing\YourSecurityWorkgroupFile.mdw" "X:\Testing\DB.mdb" /user TestUser

HTH

ghudson:

Thanks for your reply, but...

I don't think I can use your approach for the reasons outlined in my original post on this thread.

My previous post was an attempt to avoid the security login headaches on Access files that do not require it, in a network environment where at least one db DOES need security.

ghudson:

I re-read your post in light of what The_Doc_Man said: "OK, now what will happen is that if the forgetful user comes in without joining the workgroup, they come in as user ADMIN - but that user has NO rights whatsoever. "

Now that I understand what you said, I apologize for my statement that it would not work. It should work, but I'm unclear about the login issues with those users who must use the secured db and one or more unsecured db's.

Does the use of the "C:\Program Files\Microsoft Office\Office\msaccess.exe" /wrkgrp "X:\Testing\YourSecurityWorkgroupFile.mdw" X:\Testing\DB.mdb" /user TestUser " technique change their .mdw default file permanently?

The_doc_man said "your desire to avoid passwords for other DBs is not going to be possible if you use Access security". This is not quite true. As he later pointed out, when you join a work group that updates the registry thereby making it look like ALL db's are secured. Well as it happens, whether you realize it or not ALL db's actually ARE secured. The default workgroup is system.mdw. This is the workgroup name that is placed in the registry when Access is installed. It is IMPERATIVE that you NEVER modify system.mdw. However, if you use a shortcut with the workgrp switch to open your db, no modification is made to your registry. Therefore, there is no impact to other db's that you may use. So, let me summarize:

1. NEVER, NEVER, NEVER modify system.mdw. Always create a new workgroup or join an existing one if you want to secure a database. It is possible (and many companies do) to use the same workgroup to secure a group of related db's. This minimizes maintenance to some extent.

2. ALWAYS, ALWAYS, ALWAYS, open your secured db's with a shortcut so as to not cause the registry to be modified. If the db is properly secured and the user tries to open the db by double clicking on it, he will not be able to open it no matter what he does since Access will assume that the workgroup is system.mdw and although system.mdw has admin and users that are unsecured, none of the objects in your secured db can be accessed by the default admin and users groups.

You know what? I've never looked....

Tell you how you find out.

Join your default workgroup. It is SYSTEM.MDW in the /SYSTEM32 folder of your WINDOWS directory (unless it is in the /MyPrograms directory because of where Office put things.) You can do a FindFiles on it from the Start menu. The name is absolutely right as I have spelled it.

You have to also have a different workgroup set up. Let this workgroup have a name other thatn SYSTEM.MDW and let it reside anywhere but definitely NOT in the same place that SYSTEM.MDW resides.

OK, join the default workgroup. Now create the shortcut that includes the /wrkgrp option. Launch it. Exit from the database.

Now from the Start>>Run option, run REGEDIT. WHATEVER ELSE YOU DO, BE EXTRA SUPREMELY CAREFUL TO NOT TRY TO EDIT ANYTHING WHILE IN THIS MODE. (Caps for extreme emphasis.)

Search for ".MDW" - you will find it in the section that binds applications to types, so do a FindNext. Eventually you will find a reference to a file of type .MDW and it will tell you which workgroup it thinks you have joined.

Now very carefully EXIT from the REGEDIT program.

OK, you can breathe easier now and you have your answer to whether the /wrkgrp switch changes the stored workgroup.

Never mind, Pat already has the answer. Thanks, Pat. It just never came up for me to look.

Thanks all for your time on this.

So to summarize:

I should follow The_Doc_Man's plan from his first post on this thread to secure my database.

I should ensure that ALL users of this or any other secured database ALWAYS use a shortcut with the /wrkgrp switch set to a .mdw file that should exist somewhere on my network accessable to all (such as the directory containing the secured db) with appropriate network rights.

I can use the same .mdw file for several db's if my security issues on each are similar.

My users can still use their unsecured db's as before without any special issues.


Is that about right?