how-to #3a: create a secure Jet (MS Access) database
How to secure an Access database with user-level security
This is explained in the help file for MS Access, but that involves jumping to different sections of the file. This page aims to present this technique in a single document. It assumes you already have a database you want to secure and that you are using Access 97, which must be installed on your machine. This document is probably applicable – possibly with minor variations – to Access 95 and Access 2000 as well, but I haven’t tested it with these versions.
Important – this is a long drawn out technique but it’s very straightforward. Once you have done it once or twice, you start to understand the basics of Access security, and it is worth working through the exercise even if you don’t want to secure a particular database right now. Screenshots are provided at important stages in the process.
1. create a workgroup file
First you need to create a workgroup file which will contain details of all the users who are allowed to access this database. You (the administrator of this database) will then be able to add or remove users from this workgroup.
Workgroup files are sometimes called ‘system databases’ in Access, and have the extension .MDW (in Access 97). A default file, system.mdw, is created by Access on installation, but you shouldn’t use this file. Create a new file for each workgroup.
To create a new workgroup file:
- exit Access
- run the Workgroup Administrator, which is the file wrkgadm.exe and
which is in your windows system directory (\windows\system on Windows
9x, or winnt\system32 on Windows NT or 2000):
- click Create… then follow the instructions on the dialog box:
- make sure you enter a workgroup ID, and write the name, organisation, and workgroup ID down in case you need it again (you would need this if you ever needed to recreate the workgroup file, without which you might not be able to access your database)
- you can save the new workgroup file wherever you like.
2. join the workgroup
Now run Access. You will automatically be a member of the workgroup you just created, but this has only one member – Admin – and is not secure because it doesn’t require a password to log on. At the moment therefore, you won’t see any difference than before joining a workgroup.
So you need to make Access require a password to log on:
- open the database you want to secure
- choose Security/User and Group Accounts from the Tools menu. The only user in this workgroup at present is the default Admin user which Access automatically creates for you
- on the Users tab of the dialog box, ensure that Admin is highlighted
in the Name box:
- click Change Logon Password, then type a password into the New password
and Verify boxes. Don’t type anything in the Old password box
(you don’t have an old password yet). Make a note of the password
you chose:
- click OK
- exit Access, then run it again to log on as the new Admin account user. Make sure that the Name box reads ‘Admin’ (it may read ‘Administrator’) then enter the password you chose and hit Return
- open the database to be secured.
3. create a new adminstrator account
Now you have to create a new administrator account for yourself. To do this:
- open the User and Group Accounts box again (Tools->Security menus)
- on the Users tab, click New… then enter your name and a personal
identifier – you will need this to log on. (The Access help file
tells you about limitations on the size and content of the personal
identifier):
- click OK
If you look in the User and Group Accounts box, you will see that the new user is only a member of the Users group. You want to be a member of the Admins group, so:
- in the Available groups list, select Admins then click the Add >> button; your new account is now an administrator
- click OK
- exit Access
- now run it again, this time entering the name of your new account (not Admin) when you log on. You don’t have to enter a password because you haven’t entered one for the new account yet
- so, run Access again and create a password for the new account, just as in step 2
4. remove the default Admin account from the Admins group
Last step but one. You need to stop the default Admin account being an administrator. Bring up the User and Group Accounts box again. It looks as if you could ought to be able to select the Admin account in the Name list, then click Delete – but if you do this, you will get an unhelpful message saying that the operation could not be completed. This is because the default Admin account cannot be deleted. What you can do is remove it from the Admins group. To do this, select Admin from the Name list, then select Admins in Member of list, then click the << Remove button. Click OK.
5. secure the database with the User-level Security Wizard
Finally you get to secure the database! Ensure that the database you want to secure is open. Go to Tools->Security, then click User-level Security Wizard (note: this won’t have been installed by a default Access installation. If it isn’t installed, run Access or Office setup again, and ensure that Advanced Wizards are selected in the setup configuration).
The wizard copies the database, encrypts it, and (if you accept the default options) removes all permissions from members of the Users group, if there were any. You need to add these again as the administrator if you want any other users to be able to open and manipulate the database.
And that’s it! Long-winded but not difficult. Of course, only step 5 needs to be repeated for another database if you use the same workgroup. If you want to create a new workgroup, you have to repeat steps 1-4 again first.
To revert to the default Access configuration, where you don’t have to enter a password to log on, exit Access and rerun the Workgroup Administrator and this time click the Join… button. Navigate to the default workgroup file system.mdw, and click OK. You won’t be able to access your secure database again (although you can apparently open it you won’t be able to read or alter any of the contents) until you rejoin the secure workgroup you created in step 1 and log on with a valid user ID and password.