Securing an Access Database [AVDF Access Article]

Image of Navigational Map linked to Home / Contents / Search

Securing an Access Database

Rafael Merino - GUI Computing

Step 1) Copy and Rename the SYSTEM.MDA file that is installed with Microsoft Access, into the directory which contains the MDB which you wish to secure.
ie.: If I had a MDB called C:\SOURCE\TEST.MDB that I wanted to secure, then I would copy my C:\MSOFFICE\SYSTEM.MDB file to C:\SOURCE\TEST.MDA.
The MDA file will contain all of the security settings for your Access database.

Step 2) Run the WorkGroup Administrator applicaton to point to the new TEST.MDA file. ie.: Double click the C:\MSOFFICE\WRKGADM.EXE file, a dialogue will appear. Click the JOIN button and Browse the C:\SOURCE\TEST.MDA file and click OK. A message should appear saying "You have successfully joined the workgroup defined by the system database C:\SOURCE\TEST.MDA". Click OK and exit the WorkGroup Administrator.

Step 3) Create a new account to Administer the WorkGroup and Own the Database. This account will replace the predefined Access Admin user account, which is the default administrator and owner account. To do this:

Open the C:\SOURCE\TEST.MDB using Access. Select Security, then Users, from the drop down menu. A Users Dialogue will appear - click the New button to create a new user. Call the new user CLIENT and enter a PIN number, then click OK. This has created a new user. The users dialogue will display the new user and indicate which groups the CLIENT is a part of, make sure the CLIENT is a member of the users group. Close the users dialogue.
Put a password on the Admin account. Select Security then Change Password from the drop down menu. The Change Password dialogue will appear, type in a new password and click OK.
Close down Access and restart it. The Logon dialogue will appear. Type CLIENT as the name and click OK. You are now logged on as CLIENT.
While you are logged in as CLIENT, create a new database called C:\SOURCE\TEST2.MDB and import all the objects from the C:\SOURCE\TEST.MDB. This will make CLIENT the owner of all the objects within the new database.

Step 4) Set the appropriate Permissions and Passwords on each account.

That's it. You now have a secure Access MDB...

One tip is that the default login is the Admin account. So if you clear the Password from this you will deactivate the login dialogue. That way, when the user opens the database (assuming they have permissions to do so) they don't have to type in a login name and password. While still allowing the CLIENT user to specify their Name and Password through the command line or in the application source code.

Another tip is to download the Access Security Wizard from the Microsoft Web Site. This Access add-in basically completes Steps 1, 2 and 3 for you using a series of easy-to-use dialogues.

Written by: Rafael Merino
November '96

[HOME] [TABLE OF CONTENTS] [SEARCH]

Step 1)	Copy and Rename the SYSTEM.MDA file that is installed with Microsoft Access, into the directory which contains the MDB which you wish to secure. ie.: If I had a MDB called C:\SOURCE\TEST.MDB that I wanted to secure, then I would copy my C:\MSOFFICE\SYSTEM.MDB file to C:\SOURCE\TEST.MDA. The MDA file will contain all of the security settings for your Access database.
Step 2)	Run the WorkGroup Administrator applicaton to point to the new TEST.MDA file. ie.: Double click the C:\MSOFFICE\WRKGADM.EXE file, a dialogue will appear. Click the JOIN button and Browse the C:\SOURCE\TEST.MDA file and click OK. A message should appear saying "You have successfully joined the workgroup defined by the system database C:\SOURCE\TEST.MDA". Click OK and exit the WorkGroup Administrator.
Step 3)	Create a new account to Administer the WorkGroup and Own the Database. This account will replace the predefined Access Admin user account, which is the default administrator and owner account. To do this: Open the C:\SOURCE\TEST.MDB using Access. Select Security, then Users, from the drop down menu. A Users Dialogue will appear - click the New button to create a new user. Call the new user CLIENT and enter a PIN number, then click OK. This has created a new user. The users dialogue will display the new user and indicate which groups the CLIENT is a part of, make sure the CLIENT is a member of the users group. Close the users dialogue. Put a password on the Admin account. Select Security then Change Password from the drop down menu. The Change Password dialogue will appear, type in a new password and click OK. Close down Access and restart it. The Logon dialogue will appear. Type CLIENT as the name and click OK. You are now logged on as CLIENT. While you are logged in as CLIENT, create a new database called C:\SOURCE\TEST2.MDB and import all the objects from the C:\SOURCE\TEST.MDB. This will make CLIENT the owner of all the objects within the new database.
Step 4)	Set the appropriate Permissions and Passwords on each account.