IMPLEMENTING MICROSOFT ACCESS SECURITY
ON AN EXISTING SYSTEM
 



This page describes the process of implementing Access security on an existing system that isn't secured.

Check out the Headway Shortcut Builder. This is the fastest way to create shortcuts for your secured Access applications.





IMPLEMENTING ACCESS SECURITY

Getting Ready

Let's assume that you're starting with an unsecured Access system. First of all, consider who will have what type of privileges. We usually administer permissions by Groups, not Users. These security groups are typically required:

  1. Administrators - full permissions on data and application objects;
  2. Editors – all data permissions;
  3. Readers - read only permissions on data.

Backup your application and data files and let's go...

The Steps

  1. Create a new custom workgroup, or mdw file. To do this, use the Workgroup Administrator, wrkgadm.exe. If the MS Office setup didn't create a shortcut for it, you will have to search for it on your computer and make a shortcut for yourself. (In Access XP, the Workgroup Administrator is under Tools | Security | Workgroup Administrator.) Before you create the new Workgroup, make a note of which one is in use first.

  2. Now join back to the original system.mdw using the Workgroup Administrator again. Each version of Access stores the system.mdw in a different folder. Access 97 stores it in the \Windows\System folder. Access 2000 stores it in the \Program Files\Office 2000\Office folder. Access XP puts it in \Program Files\Common Files\SYSTEM\.

  3. Using the Headway Shortcut Builder we will create a shortcut to point to the existing unsecured application file and the new mdw file. The shortcut needs three pieces of information to fully define the target: (1) the location for MSAccess.exe; (2) the location of the application mdb; and (3) the location of the mdw. Create the shortcut using the new mdw to start the application, the new mdw filename goes after the "/wrkgrp" switch.

    The shortcut target string will look similar to:

    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "D:\Roy\Projects\JobTask\DEVEL\JobAnalysis.mdb" /wrkgrp "D:\Roy\Projects\JobTask\DEVEL\JobAnalysis.mdw"

  4. Start the unsecured application with this new shortcut. It should just open up without asking for a Name or Password.

  5. At this point you have the application file opened with the user "Admin". Until you activate the Admin password, Access will not ask for a User ID and password. So change the password for Admin using Tools | Security | User and Group Accounts.

  6. Create a new account using Tools | Security | User and Group Accounts | New. This will be the primary administrator. Use your name, or for example, "Roy Scarisbrick".  Make sure to add this new user to the "Admins" group. Either record the Personal ID that you use or keep several backups of your custom mdw file.

  7. Remove "Admins" membership for the Admin user. The Admin user won't be used in the future. See the section, "Security Loopholes" for the reason for this.

  8. Close and reopen the application, logging on as this new administrator. There is no password for "Roy Scarisbrick" at this point. So after the application file has opened, change the logon password for "Roy Scarisbrick" to one that is not null.

  9. Now close the application file, but not the Access window. Create a new mdb. Creating a new mdb at this point will ensure that "Roy Scarisbrick" is the owner of it.

  10. Now close this new mdb and modify the shortcut created earlier to open this new mdb, or you can rename the new mdb to the name of the old one.
     

  11. Open the new (empty) mdb with the shortcut, logging on as the administrator. Verify the database owner is the administrator you created using Tools | Security | User and Group Permissions | Change Owner. Select "Object Type" as database to view the owner's name.

  12. Create the Group accounts using Tools | Security | User and Group Accounts | Groups. The Personal ID string is rarely needed again if you keep backups of the mdw in various places. Personal ID strings can be random strings if you like.

    Note that because of a loophole discussed in Security Loopholes later in this article, you should create a group to use instead of the built-in group, "Users".

    These might be the groups you create. In the order they are listed here, they have successively increasing permissions. The ReadersGroup will have the minimal permissions required to run the system.

    Name Personal ID Permissions
    ReadersGroup random string read data permissions
    UsersGroup random string base data permissions
    EditorsGroup random string enhanced data permissions
    Admins built-in full administration permissions

  13. Create the user accounts using Tools | Security | User and Group Accounts | Users. All users will need to be members of the ReadersGroup. Add each user to the group he needs permissions for.

    Name Personal ID Membership
    Joe Editor random string ReadersGroup, UsersGroup, EditorsGroup
    Joe User random string ReadersGroup, UsersGroup
    Joe Reader random string ReadersGroup

    At this point each new ID has a Null password. When your users start the system you may want to have your startup programming check for this situation and present the user with a dialog that prompts them for a non-null password. You might also allow this dialog to be called up anytime a use wants to change his password. A good place for this might be from the custom menubar of the startup form.

  14. Set the default permissions of each group using Tools | Security | User and Group Permissions | Permissions. (We normally set permissions by Group, not by User.) Select the "Groups" radio button and set the object type to Database, Table, or Query, etc. Set the permissions for each group for each object type type. While you're doing this you should remove all permissions for the Users group. The reason for this is explained in Security Loopholes.

  15. The file you've been working with can be the application file. Exit the database and create a copy of it for the database file. This assumes that you want separate mdb's for the application and database. This step is just to save some time: you won't have to set default permissions all over again.

  16. Open the application file, logging on as your administrator and Import all objects from the original database except the tables if you're using a split front-end, back-end design. These imported objects will pick up the default permissions you assigned. Don’t forget to turn on the option to import any custom Menus and Toolbars and Import / Export Specs. Check that the References are correct and that project will compile, then set the startup properties. They don't get imported.

  17. Open the database mdb, logging on as your administrator, and import all the data tables from the original mdb. Even if there are no modules, check that the references are what you want. They can be required if there are any functions on field default values and such. You will need to rework the layout of the Relationships window.

  18. Rename the application and data mdb’s and update the shortcuts for the new names.

  19. Link the database tables to the application file.

  20. Test with a user ID from each Group. Assign extraordinary permission as they are required and keep a record in your systems docs what they are. If your system is complex with a lot of extraordinary permissions, you should develop and maintain a test script.


Extraordinary Permissions:

If you have any tables that record system events or errors, users who normally don't have Insert permissions will need them for these tables. Set these permissions for the groups, not the users. You will need to do this in both the application and data mdb’s if the tables are linked.
 


Special Functions:

Note that to generate an mde or create new users a user needs to be a member of the built-in Admin group, regardless of what permissions you have assigned to your own administration group.

To upgrade a file to a later Access version you need to be signed on as the database owner.

Note that nowhere in this article do we work with the "Database Password".
 


Twists and Kinks

Okay, we're getting the hang of Access security. Maybe we're not quite experts yet though. There are always a few kinks:

Never, Never...

  • ...change the password for the default user, Admin, or add new Users or Groups while using the default system.mdw. You'll be affecting security for every Access system you work with. You should create a custom mdw file for each project you work with.

  • ...try to implement security on an Access system using network security. Access wants to have even read-only users create or edit the ldb file that will be in the same folder. Furthermore, what are you going to do when your read-only users need to add a record to a error-logging table or write to a temp table? Trying to use network security instead of Access security isn't a good idea.

Table Permissions in a Split Application / Database Setup

Splitting the application objects from the database tables has several advantages. One of them is that a new application file can be sent to a client site with a routine to automatically link the tables. Often there will be special permissions for system tables and such. For example all users will need Insert permission for an error logging table.

For this split-file configuration the best plan is to set the table permissions in the database file exactly as required, but to set the default Table data permissions for the new tables in the application file wide open. When the tables are re-linked the tighter permissions from the database file will be the effective ones.
 

Repairing A Damaged Workgroup File:

It is possible for the System Workgroup file to become excessively "bloated" and quit functioning properly.
Symptoms of this can be:

  1. any changed behaviour in user permissions, including the inability to logon, or
  2. the increased size of the file over normal.

A workgroup file is normally about 150 KB in size.

If it’s suspected that the Workgroup file has become bloated and isn’t being read properly, it is easiest to replace it with a backup. The disadvantage of this would be in possible losing any new ID's or passwords since the backup was made. The alternative is to copy the file, start the system, compact the copy, then rename it. These are the steps to do this:

1. In a Windows Explorer window, locate the mdw file and copy and paste it to the same folder.

2. Start the Access application file with the normal shortcut. When you have entered your Name and Password, hold down the Shift key when you click OK or press enter. This will stop the startup procedures from running and give you access to the full set of Access menubar options.

3. When the application mdb has opened, select File | Close.

4. Then select Tools | Database Utilities | Compact and Repair Database.

5. In the "Database to Compact From" locate the copy of the mdw file. (If you didn’t change the name it will be called "Copy of AppWorkGroup.mdw".

6. In the "Compact Database Into" dialog select the same file. You will have to change the "Save as type" field at the bottom of the dialog to the mdw type. This will quickly compact the copied mdw file into itself.

7. Close Access entirely.

8. Remove the original mdw file to a backup folder.

9. Rename the copy of the mdw file to the original name.

In the future, practice preventative maintenance, and compact the mdw periodically.


Security Loopholes:

The "Admin" account is available on every mdw. It has the same Personal ID for every mdw. If your application allows the Admin user to have any permissions - especially Admins group permissions, then any user could create a new mdw where Admin would have full permissions and log onto the mdb and do whatever they want. For this reason, we should remove all the permissions for the Admin user and to make the intent even more clear, Admin should be removed from the Admins group.

By default, all users are members of the built-in group, "Users". Unless this works out conveniently for you, you may find it best to remove all permissions for the Users group and create your own security groups.

Certainly if you choose to use the Users group, it should have very basic permissions: Admin will be a member of it, and he is available through any mdw.

In summary, use the built-in Admins group, remove permissions for the Admin user, and don't use the built-in Users group.
 

Getting Data From a Secured Database to an Unsecured One:
 


Let's say you'd like your users to be able to merge unsecured and secured data in a controlled way. One approach is to open a form like this and let the user supply his ID and password. A little programming and voila, he's got his data!


Check out the Headway Shortcut Builder
 


Using this tool is the fastest way to create a shortcut for your secured Access system. Right-click on the application file and your shortcut is one more click away.

Download the exe.

THAT'S IT - You're an expert with Access security!  Any comments?
Any feedback about HeadwaySystems.com?