Setting Up Microsoft Access Security

Articles Index

Course Listing

Free Catalog

Request Information

What's New

 

About

Like many databases Microsoft Access allows you to create users and groups. It also allows you to control access to the objects in your database. Once you start to deploy your database to several users you may want to consider implementing security. Here's a short step-by-step guide. We will be using the User Level Security Wizard in Access which will backup your exisiting database and create a new, secured one. Along the way the Wizard will create or modify a Workgroup Information File with your user security settings. Access permissions to the various database objects will be kept with the actual database itself.

Backup your original Workgroup Information File (WIF).

Step 1: Make a copy of the system.mdw file found in the Microsoft Office folder tree. Call the copy something like systems.mdw-original. The system.mdw file contains all the user security settings. When you modify the settings from the out-of-the box defaults Access may modify this file. If, like me, you can be a little fumble fingered, you will be thankful you have this copy.

Step 2. Check you have this copy. If there are problems you can get locked out of your own database!

Step 3. Double check you have the file. You may need it to restore security back to the default.

Launch the User Level Security Wizard.

Step 4. Open the database.

Step 5. Under Tools | Security launch the User Level Security Wizard. I am not a real big fan of using the wizards as a crutch but in this case that's what we want.

We will cover the other choices in later articles.

Step 6. You will be aked if you want to create a new Workgroup Information File (WIF). Remember the system.mdw file? If we select no here, than that file will be overwritten. Pick yes to create a new file, just as the Wizard suggests. Click next.

Step 7. The Wizard asks how you want to identify the WIF and where you want to put it. For simplicity you can use the name of the database for the WID and the new WIF file itself (although with extension .mdw, of course). Put the file in the same folder as the database. One more question - the Wizard asks if you want to make this you default WIF. If you answer yes to this question you haven't been paying attention! Pick create a shortcut as the Wizard suggests and click next.

Step 8. The Wizard asks what object you want to secure. By default it secures everything. Accept this default and click next.

Set up the groups.

Step 9. Access has two built in groups - admins and users. This screen asks if you would like some pre-defined groups setup. You will probably define some groups later youself, but go ahead and select here those groups that match your needs. Why not save yourself a little work? Check the ones you want and click next.

Step 10. The wizard asks what you want to do about the users built in group. By default they can't do anything and this is probably what you want since all users are automatically a member of users. You don't want those two new users picking up a default read permisssion to read the salary table! So, generally you would accept the Wizard's suggestion not to assign permissions to users and click next.

Set up the users.

Step 11. The Wizard now ask what users you want to setup. You will probably add most of the users later after security is up and running but you may want to define a few now. You should at least create an account or two for people who will be administering the database. Once finished adding users here click next.

Step 12. Assign each user to one or more groups. They can belong to more than one group and their effective permissions will be the sum of the individual permissions. Or do it the other way around and select the group and assign the users that should belong to it. Then click next.

Create secured database.

Step 13. Prior to creating the secured database the Wizard asks for the name of the unsecured backup it will create. Use the default provided or make up your won name. Then click finish. That's it! Access will create an unsecured backup and a new secured database. It will also create a summary report which you should print and file.

Dan D'Urso
Laguna Niguel, CA 92677
Updated July 2012
articles at dhdurso dot org


Please send comments and suggestions to WebMaster at dhdurso.org

Copyright(2001-2012) D.H.D'Urso & Associates

 Articles Index | Back | Top