How to Secure an Access Database

Derived from Microsoft Security FAQ

1. Use the Workgroup Administrator program (Wrkgadm.exe) to create a new workgroup information file. Write down the Name, Organization, and WorkGroup ID strings that you will be prompted for when you create your new workgroup information file and store them in a safe place. If your workgroup information file ever becomes lost or corrupted, you can reconstruct it by using these identical strings, which are then encrypted to create a unique token. Without a valid workgroup information file, you could conceivably be locked out of your database forever. Another reason to save this information is for upgrading a secured Access database to a newer version of Access. The recommended path for upgrading databases is to re-create the workgroup file in the new version of Access before upgrading the database itself.
   
2. The Workgroup Administrator automatically switches you to the new workgroup information file. Start Access, and open any database.
   
3. You will be logged on as a user named Admin. Use the Security menu options to add a password for the Admin user. The Admin user is the default account, and setting its password is what causes Access to prompt for a logon Name and Password the next time that you start Access.
   
4. Create a new user, which is the account you will use to secure the database. Add this new user to the Admins group. Write down the strings that you use for the name and PID in case you ever need to re-create your workgroup information file. The PID is not the password-the string used for PID is encrypted, along with the string used for the Name, to create a unique token (SID, or system identifier) identifying the user.
   
5. Quit Microsoft Access and log back on as the new user account that you created in step 4. You will not have a password for this account yet, (the PID you typed with the name in step 4 is not the password), so now is a good time to set one.
   
6. Remove the Admin user from the Admins group so that Admin is a member only of the Users group. The Admin user account has no administrative powers built into it; they are derived from membership in the Admins group, which does. Although you cannot delete any of the built-in users or groups (Admin, Admins, and Users), you can move users to and from the Admins group and restrict permissions to the Users group.
   
7. Open the database that you want to secure and run the Security Wizard. Select the objects that you want to secure (it makes sense to secure them all). The wizard will then create a new database owned by your new user, and will import all of the objects and relationships into it. It will also remove all permissions from the Admin user and the Users group and encrypt the new database. The original database will not be altered. Note that the Access 2000 security wizard does not create a new database-it simply creates a backup copy of the original. One flaw with this arrangement is that not all permissions to open the database are removed from the Admin user and Users group to open the database, even though they appear to have been removed.
   
8. Open the new database. Because the Security Wizard removed all permissions from the Users group for the secured objects, you need to create your own custom groups and assign the level of permissions needed to these groups. Every user is required to be a member of the Users group (otherwise, a user would not be able to start Microsoft Access), so only grant permissions to Users that you want everyone to have. Members of the Admins group have irrevocable power to administer database objects, so make sure to limit membership in the Admins group to only those users who are administrators.
   
9. Create your own users and assign them to the groups that reflect the level of permissions that you want them to have. Do not assign permissions directly to users because that is extremely hard to administer. Users inherit permissions from the groups they are members of, and keeping track of the permissions assigned to a group is much easier than keeping track of the separate permissions of individuals. If a user is a member of multiple groups, then that user will have all of the permissions granted to any of those groups plus any permissions assigned specifically to the user (this is known as the "least restrictive" rule). There is no way to deny permissions to a user if that user is a member of a group that has been granted those permissions. If you need to create specific permissions for only a single user, create a group for that user and assign the permissions to the group; then, add the user to the group. The reason for this becomes clear when you consider that the user may quit, and you may have to set up permissions for the replacement on short notice.
   
10. Additionally, you may need manually to remove the Open/Run permission from the database container for the Users group through the security menus or through code. This will prevent someone from opening the database by using another workgroup information file or the default System.mda/mdw. In Microsoft Access 97, the User Level Security Wizard is supposed to remove the Open/Run database permissions for the Users group, but fails to do so. The Access 2000 Security Wizard removes permissions to the point where they are not visible on the security menus, but testing has revealed that in Access 2000 it is possible to open a database by using the default workgroup information file regardless of the menu settings. The cure for both versions of Access is to create a new, empty database while logged on as a member of the Admins group and import all of the objects from the secured database. You should take this step before spending too much time securing objects because Access considers imported objects to be "new" and loses the permission information that was stored in the source database.
    

1. The following table lists the default names and locations of the workgroup file and the Workgroup Administrator program.