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
- You may secure any database that has been created while joined to the System.Mdw.
- Create a new workgroup file(*. MDW)
- Open the unsecured database and create a password for the Admin user.
- Create a new user account that will be the new workgroup administrator, like
- Modify the Admins group by adding the new administrator and removing the original Admin
- Re-log into Access as the new workgroup administrator that you created previously.
- Set up a password for the new workgroup administrator.
- Run the Security wizard under Tools | Security | User - Level Security.
- Create any additional group accounts.
- Create any additional user accounts.
- Set up the database object's permissions.
- 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 files job is done.
The MDB itself stores security rights and privileges for each user and for each database
The MDB will have a list of user ids 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 users 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 everyones 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.
Its 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
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
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 dont 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
Remember that there are several database object types for which you may need to set
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.
If you are creating a database for several users to user on a network, you will want to
put both the MDW and the MDB file on the network for all persons to use. Everyone must
join the new MDW to be authenticated properly. Otherwise, the MDB file will not allow them
to access any objects.