Many people have said to us that they find Access Workgroup security impossibly difficult, and the more they read, the worse it gets! We have adopted a simple and yet effective approach, for which we are indebted to our colleague David Hay.

Although you can, if you wish, employ the full capabilities of the security mechanism which Microsoft supply (groups of users with allocated rights, and/or specific rights for individuals), we feel that this is not only a high-maintenance approach (you need to keep editing the settings as people join, move around and leave), but it also fails to encompass some of the features which we regularly need, such as reporting structure and telephone extension, so we prefer to include a users table in the database (we normally call this USysUsers, by the way, so that it is normally hidden in the database window. If you haven’t discovered the “USys” prefix, it is a very useful trick for all sorts of cases!).

Some observations about the non-secured Access installation are as follows:

• The default creator, and hence owner, of ALL databases and contained objects in the environment initially will be “Admin”.
• There is an Admin user on all installations of Access, and security is in place, but because there is no password for admin, no login screen appears.
• You can change the ownership of any object except the database itself – this is a common cause of frustration!
• The workgroup file includes by default two “Groups”: Admins and Users. The Admin user is a member of both; there must be at least one user in Admins (otherwise it would be impossible to administer databases…).
• Permissions (rights) are allocated for both of these groups in the default file. The permissions which apply are the least restrictive set available to a user. So, although they may be restricted as an individual, they may gain extra privileges through membership of groups. The Admin user has full rights to all non-Microsoft objects.
• Members of the Admins group, plus the owner of an object, can change the permissions on an object.

Our approach uses a new Workgroup (MDW) file in which the Admin user is joined by one other “Super-user” to whom all the powers are given. Poor old Admin is demoted to have only the permissions which this upstart decrees. The new workgroup file is used when you are creating and administering databases; but (and this is the cunning part) you can give your MDB files to anyone else in the world to use, and, because they don’t have a “Super-user” in the System.mdw which was created when Access installed on their machine, they are stuck with the settings you have dictated.

Step-by-step

Use the Workgroup Administrator tool to create a brand new Workgroup file. Ensure that you enter a unique and non-guessable “WorkgroupID” (like an extended PIN number) and give the file a recognisable name. A good tip is to take a screen dump of the confirmation screen which appears towards the end of the process; paste the picture into Word and print it out for retention in a safe place: you’ll need the information should you ever need to re-build the workgroup file.

• “Join” the new workgroup file (ultimately it is a good idea to have two desktop icons to launch Access; one has the standard command line, and the other includes the “/Wrkgrp xxx.MDW” flag to join your new group for the session of using Access: you naturally employ this icon when you are going to do some development).
• Start Access and, via Tools/Security/User and Group Accounts, set a password for Admin.
• Also create a new user: we’ll call it SuperUser here, but please choose something for yourself! Again, ensure that you note the PIN Number which you chose for this user. SuperUser may have a password, but does not actually need to.
• Add the new user to the Admins Group, then close Access.
• Start the program again, this time logging-in as SuperUser.
• Remove the Admin user from the Admins group – vital!

You are now ready to begin creating secure databases! Just remember to log-in as SuperUser when you start a new project or whenever you are working on the database structure (rather than the data).

• Under Tools/Security/user and Group Permissions, we recommend that you remove ALL permissions from both Groups (i.e. no-one gets any spurious permissions just because they happen to belong to a specific group).

It is easiest to set the default permissions for users before you create any objects within the database; for each type of object, simply select the “” entry in the list and reduce the permissions as you feel appropriate. Then you can forget the whole issue!

Retro-fitting security to a database

If you need to apply restrictions to an existing database, the first thing to remember is that you cannot change the ownership of the database. So, you need to create a new database and import into it all of the objects from the original file. Change their owner to be SuperUser, and then adjust the permissions of all of the objects as above (it is easy to select all of the members of the lists, but don’t try to change the ownership or permissions on Microsoft’s MSys objects!).

The User-level Security Wizard will do all of this for you, but in Access 97 do remember that you must be logged-in as your SuperUser, otherwise you’ll see no effect! Access 2000 automatically prevents this possibility.

One other tip is that, if you use the common “two-database approach” with a data file and a “front-end” file, you’ll need to grant Admin permissions on the tables in the front-end file if there will be the need to change the connection string (i.e. if the back-end file may move or be mapped to a different drive on users’ machines).

The other, vital tip is to remember to take with you a copy of your developer’s MDW file when you go on-site – otherwise you won’t be able to make any changes to the database structure!

Comments are closed.