Agee Software, Inc.

User Level Security

Microsoft User Level Security for Access lets you set permissions for each object in the database.

Here are the permissions for each type of object:

Database	Open/Run, OpenExclusive, Administer
Table or Query	ReadDesign, ModifyDesign, Administer, ReadData, UpdateData, InsertData, DeleteData
Form, Report or Macro	Open/Run, ReadDesign, ModifyDesign, Administer

So you can set the rights to UserID Bob on the form frmSetup to Open/Run and ReadDesign. He would be able to open the form in design mode to view its properties, but he would not be able to change them, because he does not have ModifyDesign rights. He would not be able to change the security on frmSetup, because he does not have Administer rights.

You could give Bob the Administer right to frmContact which would also give him Open/Run, ReadDesign and ModifyDesign rights.

To simplify administration, we can assign users like Bob to groups like SuperUsers. In an unsecured database, there is one user: Admin. There are two groups: Users and Admins. We can't remove this user or these groups, but we can remove Admin's rights in our secured database and remove Admin from the Users and Admins groups. Before we remove these rights, we must add a user who has administrator rights to each object.

Access stores encrypted user accounts in a workgroup file with the extension of .MDW. To use this file, we must log on using a desktop shortcut. The shortcut stores the path to MSAccess.exe, our database, our MDW file, the default userid and other parameters. To secure the database, we add each user to the MDW file and give them permissions for each object in the database.

After setting the permissions, we must also set some database properties: Show or hide Special Keys, Built in Toolbars, Full Menus, Break Into Code, Bypass Key and Database Window on Startup. We can also encrypt the database so it can't be viewed in a text editor.

The User form makes adding new users and granting permissions more convenient for users.

Once we have secured the database, in addition to preventing users from directly editing or deleteing records, we can add code to timestamp each record in each table when it is changed, show or hide the Delete or New buttons, show forms with read-only or hidden fields for some users or show or hide sensitive reports. Since we control who can change the database objects, unauthorized users can't make program changes that would be very costly and time consuming to find and fix. If we need to transmit the data tables via an insecure method like email, security makes it very difficult for an unauthorized user to see.

No security system is absolute. User Level Security should be combined with network security, a good backup strategy, trusted employees and physical security.