Securing an MS Access database with User level security

1. Create a new workgroup file to control access to the database

Open Microsoft Access. Click on Tools->Security->Workgroup administrator

image

Click on the ‘Create’ button to create a new .mdw (Microsoft Database Workgroup) file

image image

Then, select the location where you want the security file (.mdw) stored. Note that once you have created this file, the workgroup administrator automatically links to the newly created workgroup file. Hit the ok button to quit the workgroup manager.

2. Securing the ‘Admin’ user and creating a new administrator account

By default, access logs people into databases as ‘Admin’. Once a password is assigned to this account, the user is prompted with a username and password dialog box.

Click on the ‘Change Logon Password’ tab and enter your new password (and verify)

image

When we are done with the whole process, we will remove the user ‘Admin’ from the ‘Admins’ group. So, go ahead and create an administrator account for your database.

Click on ‘Tools’ ->’Security’->’User and group accounts’. Clicking on the  ‘New’ button will allow you to enter a new username.

 image image

Then, make the newly added user a member of the ‘Admins’ group

image

There.. this step completes the ‘preparatory’ activities for establishing workgroup security on your database.

3. Starting the securing process with a clean slate

Now, we will create a brand new database, import ALL objects from the database that we wish to protect and assign our privileges. Note that all the time we are ‘connected’ to the newly created mdw file from step 1.

The easiest way to create a new MS Access database is to rightclick on the target folder and select ‘New’=>’Microsoft Access Application’

image

When you doubleclick on the newly created mdb file, you will be prompted for a username and password. Now, login using the administrator credentials that you just created (‘phhpadmin’ in this case). Note that the default password is blank. After you login, click on the ‘Tools’->’User and Group Accounts’ option. You will now be able to change the administrator password from blank to something more secure.

image

4. Importing ALL objects to the new database

Double click on the new database and login as the administrator (phhpadmin in my case). Right click on the database window and select ‘Import’ to bring up the import wizard to help copy over all your objects from the database you wish to protect. Note that after the securing process is complete, you will be using the newly created database (and abandoning the unsecured access file)

image

image

(Note that sometimes the import process just hangs.. do and end-task and reopen the db if it happens.. The objects normally get imported)

The next step is to create the groups that we require. It is always a good idea to assign privileges to groups rather than users. In our case, we will create a new group called ‘advisors’.

Click on ‘Tools’->’security’->’user and group accounts’. Click on the ‘Groups’ tab and click on the ‘New’ button

image

Create your new group and hit the OK button:

image

5. Running the MS Access user level security wizard. Although it is possible to do the entire configuration by hand, this wizard makes it so much more easier and streamlined.

Login to the newly created database as administrator (phhpadmin). Click on tools->security->user-level security wizard

image

 

Click on ‘Modify my current workgroup information file’ and hit the ‘Next’ button

image

On the following screen, All database objects are selected by default. Verify and hit the Next Button.

The next step involves selecting groups for inclusion in the workgroup file. MS Access provides 7 default groups. But we are only interested in the group that we created (advisors). Select it and hit the ‘Next’ button

image

The next step of the wizard asks whether the ‘Users’ group should be assigned any permissions. Make sure that ‘No’ is selected on this page.  All users belong to the users group by default so it is best to deny all privileges first then assign as required

image

Add the new users that you would like to have access to the database in the next step. Note that entering a password is not mandatory

In the next step, click on ‘Select a group and assign users to the group’. This way, you can assign all the newly created users to the ‘advisors’ group. This is shown below:

image

Access will create a backup of the database (.bak). This is for archival purposes only. you will not be using this file. Close MS Access

6. Granting rights to the newly created group (advisors)

Open the secure database file and login as administrator. Click on ‘Tools’-‘security’=>’user and group permissions’

Click on the ‘groups’ radio option and select ‘advisors’ group. From the Object type drop down list, select ‘Database’. Then, check the ‘Open/Run’ permission. Note that without doing this, your newly created users will not be able to open the database!

 

image

Next, select ‘Table’ from the object type drop down, Select all the tables in the ‘Object name’ list box and check the ‘Read’, ‘update’,’insert’ and ‘delete’ (since I want all advisors to be able to read, update, insert and delete data to/from all tables)

image

You may want to do the same for ‘Query’.

For Forms, Reports and Macros, I like to restrict the users to just ‘Open/Run’ privileges (so they wont be able to modify design)

When you are done tweaking your database permissions, close out the database. Now, try logging in as one of your users (note that the password defaults to blank) and testing the newly assigned permissions.

Your database is secure at this point. However, one crucial step remains – Forcing the user to open the database using the workgroup file that you created (secure.mdw)

7. Creating a shortcut that incorporates the workgroup file

As creator of the workgroup file, I remain connected to it until I connect back to the default system.mdw workgroup. Let us do that and see what happens. Open MS access, click on Tools->security->workgroup administrator

Click on the join button and join the default workgroup file at:

C:\Documents and Settings\<username>\Application Data\Microsoft\Access\System.mdw

Now try opening our newly created database.

image

So, what just happened? The users and groups that were granted access to the database objects are not present in system.mdw!

The fix is easy. We need to create a shortcut that links the mdw file to the mdb file.

Right click the secure mdb file and select ‘Create shortcut’.
This creates a shortcut to the database in the same directory. right click on the shortcut file and select ‘Properties’

 

Change the "Target" field so it looks like:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"  "C:\Secure_Advisors.mdb" /wrkgrp "C:\Secure.mdw"

Note the ‘/wrkgrp’ switch that is used to reference a workgroup file.

You can now distribute the shortcut file to the users of your database and they should be able to login using their credentials.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s