The Security Wizard in MS Access is a very useful tool. But there is more to security than just running the wizard. These 12 steps will describe how to secure a database from start to finish. After which, each step is described in further detail below.

1. You may secure any database that has been created while joined to the System.Mdw.
2. Create a new workgroup file(*. MDW)
3. Open the unsecured database and create a password for the Admin user.
4. Create a new user account that will be the new workgroup administrator, like "PowerAdmin"
5. Modify the Admins group by adding the new administrator and removing the original Admin account.
6. Re-log into Access as the new workgroup administrator that you created previously.
7. Set up a password for the new workgroup administrator.
8. Run the Security wizard under Tools | Security | User - Level Security.
9. Create any additional group accounts.
10. Create any additional user accounts.
11. Set up the database object's permissions.
12. Any new databases that you create will already be secure.

Before we can get into the details of each step, we first need to define some terms.

A Workgroup Information File (*.MDW) stores information to authenticate a user. It stores the user names, group names, and passwords. It does not store any permission or rights to any database. Its main purpose is to verify that a user is really who they say they are. The permissions of the database objects, tables, queries, forms, etc., are stored in each MDB file. The System.mdw is the default workgroup filename created when you install MS Access.

When you create a new User, you will be prompted for a User Name, Password, and a PID or Personal Identification number. A PID can be any text or numbers up to 20 characters long. All three values uniquely identify each user.

Every time a user opens the MS Access program, MS Access attempts to login the Admin user with a blank password. If the log in is successful, MS Access continues loading and the user never realizes that they were logged in as Admin. However, if the login is unsuccessful, say for example the Admin user does not have a blank password, then a login dialog box pops up asking the user to specify a username and password.

MS Access verifies that the user name and password exist in the Workgroup Information File. After the user has been verified, the workgroup information file’s job is done. The MDB itself stores security rights and privileges for each user and for each database object

The MDB will have a list of user id’s and the privileges that each user may have. One user may have the rights to open the table, but not delete any records, or change the design of the table. Another user may not have any restrictions at all. The MDB file knows each user’s privileges. The distinction between the workgroup information file and the MDB file is one that confuses a lot of developers.

Now we are ready to explore the steps above in more detail.

1. All the databases that you have been creating up to this time have been created by the Admin user of the default System.Mdw. The System.Mdw that comes with Access is a generic workgroup file. All System.Mdw on everyone’s computers has the exact same Admin account with no password and with a PID of Null, making the Admin user account not very secure to continue to use.

The database does not have to be completely developed to continue setting up security. You may continue to work on it after it is secured.

You can secure a database that has already been create with a different workgroup file, if you can log into the database with the account that created the database and if that account still has full rights to all the database objects.

2. Create a new workgroup file(*. MDW)

Use the MS Access Workgroup Administrator program. The WRKGADM.EXE file starts this program. Do not leave the Workgroup ID blank or anyone will be able to create a workgroup file similar to yours. You can name the workgroup file anything you'd like. It does not have to be named System.MDW. In fact it should have a different name to avoid confusion.

It’s a good idea to keep a back up copy of any workgroup file you create file offsite and in a secure location should anything happen to the original.

3. Open the unsecured database and create a password for the Admin user.

By establishing a password for the Admin user, Access will ask for a user name and password every time it Access is opened.

4. Create a new user account that will be the new workgroup administrator.

Instead of continuing to use the Admin user, we will disable the Admin user's abilities. The reason for this is that every workgroup file has an Admin user, so using it will make our database less secure. I like to name my new workgroup administrator something like PowerAdmin. Write down the new name and PID. Store this information offsite in a secure location.

5. Modify the Admins group by adding the new administrator and removing the original Admin account.

Any user in the Admins group will have full control of all the database's objects. They can also control other user's access to the database's objects.

By removing the Admin account from the Admins groups, the Admin becomes virtually useless. After our database is secure, if any one successfully logs in as the Admin user, they find that they don't have very many rights. If anyone needs to log in as a user with full rights, they should use the new workgroup administrator that we created in the previous steps.

6. Re-Log into Access the new workgroup administrator that you created previously.

You'll need to shut down and restart Access to be able to log in.

7. Set up a password for the new workgroup administrator.

Up to this point, the new workgroup administrator doesn't have a password. You can set up a password for this account under Tools | Security | User and Group Accounts | Change Log On Password.

8. Run the Security wizard under Tools | Security | User - Level Security.

This step creates a new database and copies all the current database's objects into it. The Security Wizard does 4 things for us.

The Wizard sets the owner of the database from Admin to the new workgroup administrator. This is done by creating a new database while logged in as the new workgroup administrator. If we had left the database owner as the Admin user, then the admin user would still be able to administer certain permissions of the database objects.

The Security Wizard changes the owner of each object in the database from Admin to the new workgroup administrator. The owner of any object has special administrator privileges that we don’t want any one to have except the new workgroup administrator. This step can be done manually, but the Security Wizard automates this for us.

The Security Wizard removes all permissions from the Admin user. It also removes all permissions from the Users group account, except for the Database Open/Run permission which allows a user to open the database. Every user account that is created is automatically added to the User group. Before we ran the Security Wizard the User group had all permissions to all database objects.

9. Create any group accounts for this workgroup.

For example, you may want to create a group named PowerUsers for users that may need lots of database permissions. You may also create a Personnel group account for those user that only need limited rights.

10. Create user accounts and assign them to the appropriate groups.

Remember that if is a user belongs to more than one group, the user receives the sum or combination of the permission given to each group.

11. Change the database object's permissions for each group account.

To make permissions easier for you to manage, try and avoid setting permissions for individual user.

Remember that there are several database object types for which you may need to set permissions.

Database object

Table objects

Query objects

Form objects

Report objects

Macro objects

Module objects

12. Any new databases that you create will already be secure as long as you are still joined to the workgroup file that you created in step #2. Just repeat step #11 to grant permissions to the object in your new database.

Network Notes: