HOW TO SECURE AN MS ACCESS 2003 DATABASE

 

This guide is a step-by-step example of how to secure an MS Access 2003 database application. The method for other versions will not be a million miles from this.

 

Beware:  This guide is not a substitute for the MS Access Security FAQs - read and understand these first!  Work on a copy of your files - don't come crying to me if you lock yourself out of your only copy!  Make sure you understand the difference between users and groups before continuing!

 

The Default

 

By default, all MS Access databases are ‘secure’; they just don’t appear to be. A security (or ‘Workgroup’) file (essentially a ‘hidden’ database) is required for any Access database to start up. The default is "System.mdw" usually located in the user profile folder somewhere. So if you create a new ‘unsecured’ database, it will use System.mdw as its security file. The owner of all database objects (tables, queries, forms etc) will be the ‘Admins’ group, and the ‘Users’ group will have full access to everything. That way it doesn’t appear to be secured.

 

To Secure an Existing Database

 

To secure an existing database you are required to:

Create and switch to your own Workgroup (*.mdw) file

 

Launch Access, create a new, blank database and save it as "db1.mdb". Launch the MS Access Workgroup Administrator (Tools, Security, Workgroup Administrator ...). Unless someone has changed it, the dialog box will tell you that you’re looking at the default c:\Documents and Settings\<profile name>\Application Data\Microsoft\Access\System.mdw. Click ‘Create’ and fill in the Name and ID fields (it's a good idea to jot these down and keep them safe in case you ever need to re-create the workgroup).

 

Click OK and then define the path where you want to store the file (usually the same place as your database). After a confirmation dialog this action will create a file called Name.mdw, where Name is whatever you typed in the name field. I’ve assumed a name of “MyWIF.mdw”. After yet another confirmation, the computer will switch to the file you just created. All Access databases created on your PC from this point will be affiliated with MyWIF.mdw until you use the Workgroup Administrator to change it to something else.

 

Open a new blank database using your Workgroup file and take ownership of it

 

Go to Tools … Security … User and group accounts. Select the ‘Users’ tab and click ‘New’. You’re about to create your admin user account and give it a name (other than ‘admin’ which is a reserved account name). Enter a name (eg MyAdmin) and an ID. You’ll never see the ID again so make a note of it - you’ll need it if you ever accidentally delete an account and need to re-create it.

 

Note: you could use ‘Admin’ but with obvious security implications. Make sure that ‘Admins’ appears in the ‘Members of’ box (click ‘Add’ if necessary) and click OK. MyAdmin is now a member of the ‘Admins’ group in workgroup MyWIF.mdw. You now need to log on as MyAdmin and make MyAdmin the database’s owner, for which you will need a log on dialog.

 

To invoke a log on dialog, give the Admin account a password (tools, security, user groups and accounts, change password tab), then close Access.  Open db1.mdb again and enter MyAdmin in the log on name field and leave the password field blank. Click OK. You’re now logged on to Access as MyAdmin and now need to create another database which MyAdmin will then own.

 

Keeping Access open, close db1.mdb, you don’t need it anymore. Open a new blank database, call it Name.mdb where "Name" is the name of your choice.  Check that MyAdmin is the owner of the database by going to tools, security, user groups and permissions, change owner tab. From the ‘object type’ drop down box, scroll up and select ‘database’. The top box should say object - current database, owner - MyAdmin.  If it doesn’t, see me after class.

 

IMPORTANT - You must now remove the ‘Admin’ account from the ‘Admins’ group - the only member of ‘Admins’ should be MyAdmin.  Give MyAdmin a password in the same way you assigned one to ‘Admin’. Make a note of it and keep it safe!!  If you lose it you’re stuffed.  Close the dialog box. You’re now ready to …

 

Import the objects and data from the ‘unsecured’ database

 

In the database window, got to the ‘tables’ tab. Right click and select ‘import’. Browse to your source database and click OK. Select the objects you want to import (you can do them all at once) and click OK.  Use security menu to check that IPGAdmin is the owner of the imported objects.

 

IMPORTANT - Access defaults to allowing full admin access to the ‘Users’ group, of which account ‘Admin’ is a member. You must deny access to the ‘Users’ group otherwise security is useless. Denying access to the database object alone should do the trick but it's good practice to deny access to all database objects.  Check that it has worked by trying to log on as Admin with the password you gave the account earlier.  You should also check that you can't open the database from an explorer window.

 

Design and implement your security model

 

You’re now ready to create your user groups and accounts. A simple model is to have one group for access to all areas and another for read-only access. I don’t recommend assigning permissions at user account level, it's much more logical to assign permissions to groups and then making users members of those groups.

 

Create a read-only group for your read-only account to be a member of (eg ‘Guests’).  Tools, security, user groups and accounts, groups tab, click ‘New’ and type ‘Guests’ in the name field. Give it an ID (see above) and click OK.  Click the ‘users’ tab and create an account called ‘guest’. Make it a member of ‘Guests’ but not Admin.

 

Assign appropriate permissions to the ‘Guests’ group using Tools, security, user and group permissions. Tip:  make *ALL* users members of the "Guests" group - that way you only need assign open permissions to the database object, forms and reports, and read permissions to queries, once - to the "Guests" group.  Those permissions will be inherited by all users.

 

Your users will need to use desktop shortcuts to switch their PCs to your workgroup and release it afterwards:
"C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE" "x:\Path\MyDatabase.mdb" /wrkgrp "x:\Path\MyWIF.mdw"

 

IMPORTANT - use the workgroup administrator to change the default workgroup back to the "System.mdw" group that your PC was joined to at the start of this exercise, otherwise you will be prompted for a logon ID every time you open Access.  Use shortcuts like the one above to open your apps.

 

Final Thoughts

 

This guide just scratches the surface of secured databases, and there are lots of other administrative whistles and bells you can add.  For example, you can set startup options in Tools, Startup - you can choose to hide the database window and built-in menus, and also to display a form of your choice when the file opens.

 

Startup options can be bypassed by holding down the shift key as you open the file.  You can disable this by running some code to create and set the AllowBypassKey property.  See this article for more information.

 

I also strongly recommend that you use the linked tables utility to split your database into front and back ends, whereby the data tables (back end) are in one location and all other objects (front end) in another.  Using this method you can have a working copy front end in one location for development purposes, and a user front end in another location for every day use by your users. Only minor drawback is that the developer uses the same live data as the users.