1. Create a new workgroup file to control access to the database
Open Microsoft Access. Click on Tools->Security->Workgroup administrator
Click on the ‘Create’ button to create a new .mdw (Microsoft Database Workgroup) file
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)
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.
Then, make the newly added user a member of the ‘Admins’ group
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’
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.
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)
(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
Create your new group and hit the OK button:
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
Click on ‘Modify my current workgroup information file’ and hit the ‘Next’ button
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
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
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:
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!
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)
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.
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.
