writing

One of my clients needed to add some simple user-level security to an MS Access 2003 database.  At the time of this writing, I found no concise, start-to-finish howto guides for applying and using this scheme.  In hopes this information will be useful to others, here’s a writeup based on how we handled it.

The Problem
You want to share an Access database with others.  You want some users to be able to read all the data, but not make changes.  You want other users to have full control over the database.

The Result
You will end up with a single, secured Access database.  Read-only users will access it by opening the database directly.  They will not be prompted for a login.  Administrative users will access the database through a special shortcut, and will be prompted for a login.

Adding User-Level Security to the Database
a) Open your database in Access.
b) Run the User-Level Security Wizard (Tools - Security - User-Level Security Wizard).
c) Accept default on the first page ("new workgroup information file") and continue.
d) Accept defaults on the workgroup config page and continue.
e) Accept defaults (all selected) on the object selection page and continue.
f) Accept defaults (no groups checked) on the group selection page and continue.
g) Set permissions on the Users group permissions page (select “yes”, and set all tabs to “open/run” or “read data") and continue.  This allows anyone to open and read the database without a user account.
h) Add administrative user accounts and passwords on the user list page (e.g., “Administrator”, “Albert Thomas”, “Benito”, etc) and continue.
i) Check each user and ensure they are assigned to the “admins” group on the user/group management page.
j) Click “finish” and then save or print the “one-step security wizard report” which is displayed.  This contains info critical to rebuilding the permissions file if it becomes corrupt.

Using the Database
You now have a few files at your disposal:

• Database.mdb (the newly-secured database)
• Database.bak (an original, unsecured copy of your database)
• Security.mdw (the workgroup security file which contains the users and permissions you defined)
• A shortcut to your database, probably located on your desktop and also called Database.mdb

You can open the database file directly, which will give you read-only access to the database and won’t require a login.  You can use the new shortcut, which will use the new security file and *will* require a login.  Note: The shortcut contains full paths to Access, the database, and the workgroup file.  If you move any of those things be sure to update the shortcut.

Managing Users

To add another user account:

a) Log in to the database as an administrator (so you’re connected to the workgroup file).
b) Add a user via the User and Group Accounts menu (Tools - Security - User and Group Permissions).
c) Close the database and log in again as the new user, and set a password (the password is blank by default, and can’t be set through the menu).

To delete a user account:

a) Log in to the database as an administrator (so you’re connected to the workgroup file).
b) Remove the user via the User and Group Accounts menu.

To change your own password:

a) Log in to the database as an administrator (so you’re connected to the workgroup file).
b) Select the “change login password” tab and change your password there.

To reset another user’s password:

a) Log in to the database as an administrator (so you’re connected to the workgroup file).
b) Select the user record in the User and Group Accounts menu.
c) Click the “clear password” button.
d) Close the database and log in as the new user.
e) Go to the “change login password” tab in the User and Group Accounts menu and set a new password.

###