Setting Database Permissions

It is important to realize when allocating and removing permissions that the database itself is classified as an object and has its own very important permissions. The database object name is MSysDb , and this section describes the outcomes from removing those permissions.

If you go to the User and Group Permissions dialog and select the Object Type drop-down list, you will find that the first option in the list is Database (as shown in Figure 8-18). Intentionally or not, the list box almost conceals the word Database above Tables, the second item in the list. Now we will see what happens when you remove each of the permissions in this dialog.

Figure 8-18: The User and Group Permissions dialog.

Clearing Administer Permission

When you clear the Administer check box for a user account (Admin) or a group (Users), that account will not be allowed to

• Change any permission for the Database object, as shown in Figure 8-18.

• Change the database startup options as shown in Figure 8-19 and discussed in detail in the section "Preventing End Users from Changing the Startup Properties" in Chapter 2.

  
  Figure 8-19: Database Administer permission stops users from changing the startup options on this dialog.

• Add or modify the database password (see Chapter 9).

• Convert the database into a replicated design master. Links to further resources on this topic can be found in the section "Further Reading" later in this chapter.

More on Securing the Startup Options

If workgroup security has been defined for your database, you can prevent users from changing the settings in the Startup dialog by making sure they haven't been granted Administer permission for the database. When you test these changes with an account that has the Administer permission removed, the startup properties will seem to change. When you return to view the startup properties, however, you will find that the changes that you made were not accepted.

It is important to be aware that all users can change the startup options if a special setting for a startup option called the Data Definition Language is set to False. This setting is explained in detail in the section "Preventing End Users from Changing the Startup Properties" in Chapter 2. In addition, bear in mind that the startup options are not transferred when you import all or any of the objects from one database into another. For more information on these startup options, see Chapter 2.

Clearing Open Exclusive Permission

If you remove open exclusive permission for an account, that account will not be allowed to open the database in exclusive mode. In Access 97, if the account tries to open the database in exclusive mode, the database will not open.

In Access 2000 and later, no error message is issued and the database opens in shared mode instead. Then comes a wonderful protection measure ”the account user cannot save any form, report, macro, or module opened in design view back to the database, which means that users who belong to groups (such as the Users group) who do not have Open Exclusive permission for a database cannot alter any of those objects in the database. Removing this permission resolves two great headaches for the DBA: the software cannot be changed and the database cannot be opened by someone in exclusive mode, thereby stopping all other people from opening the database at all.

Clearing Open/Run Permission

Clearing the Open/Run permission for the Users group (and Admin user) results in a very secure database. No one who uses the anonymous Admin user account will be able to open the database, link to the tables in the database, or import any objects from the database, which means that you will need a workgroup file with an account that has Open/Run permission for the database. We will employ this option in Chapter 10, but for the moment, our whole strategy is to allow the anonymous (Admin) account to access the database, so this permission is not very relevant.

Testing the Database Permissions

To test your database permissions against the anonymous Admin account, you generally can use any workgroup file other than your developer workgroup and log on as the Admin user. Sometimes, though, you may have made alterations to the groups of which the Admin user is a member. To be perfectly sure, you can do the following test:

1. Create a brand-new workgroup by using the steps in the section "Preparing Your Developer Workgroup File." Call it something like AdminTest.mdw .

2. Join that workgroup file.

3. Open the database that you want to test. As long as no user name dialog appears, you will be using the anonymous Admin account.

4. Test the Administer, Open Exclusive, and Open/Run permissions for the database.

5. Rejoin your normal workgroup file.

Removing all Permissions From the Admin User

When you transfer ownership by importing objects into a database by using your Developer user account, the anonymous Admin user is unlikely to have any individual user permissions for objects in the database. Anyway, it is important that you check for and remove all the permissions now so that you don't get caught out at a later date. To do check for and remove these permissions:

1. Log on to your Developer workgroup (as Developer).

2. Choose Tools ˜ Security ˜ User and Group Permissions.

3. Make sure that you select Users for the List option box and Admin in the User/Group Name list (shown in Figure 8-20).

   
   Figure 8-20: Clearing all permissions for all objects for the Admin user.

4. Manually work through all the object types (by using the Object Type drop-down list) and remove all permissions from the Admin account. To do this step quickly for forms, you will need to select <New Forms> in the Object Name box and then press SHIFT while you select the last item in the list.

5. Clear all the check boxes under Permissions and then click Apply. It is important to note that by selecting <New Objects> at the top of each list, you are stopping the Admin user from doing things with new objects that you create with the Developer account.

Tip  

If you use the AutoExec macro to start your database, it will be important to secure it. This rule particularly applies if you are using user surveillance techniques such as those described in Chapter 6.

Using Permissions to Tame the Users Group

Earlier in the chapter when I covered how to make a developer workgroup file, I showed how the Admin user belonged to the Users group on every computer. As this is exactly the setup that we are catering to in the developer workgroup strategy, you need to remove permissions from the Users group. The permissions that need to be changed vary between object types, as shown in this list:

• For forms, reports , and macros, clear the Read Design, Modify Design, and Administer permissions check boxes (shown in Figure 8-21).

  
  Figure 8-21: Removing permissions for objects owned by the Users group.

• For tables and queries, clear the Modify Design and Administer permissions check boxes.

• For modules in Access 97, clear all Permissions check boxes. Unfortunately, this security was removed in Access 2000 and was replaced by VBA passwords (see Chapter 9). Thankfully, you can simulate this effect by using VBA code, which I will demonstrate later in the section "Module Permissions Are Back."

Tip  

When changing permissions on an object, it is easier to select the object in the Database window and then switch to the User and Group Permissions dialog.

More on Tables and Query Permissions

When securing tables and queries for the anonymous Admin account as a member of the anonymous Users group, the Modify Design and Administer permissions check boxes should be cleared (as shown in Figure 8-22). The exception to this rule is for linked tables in the front end of split databases, where it is best that you leave full permissions on the linked table because the linked table inherits the permissions from the back-end database. Therefore, you may as well manage the permissions in the back-end database.

Figure 8-22: The minimum permissions that should be applied to the Users group.

Conceptually, the Read Design permission is a bit of a problem because it allows users and groups with that permission to view the design of either the table or the query. This capability is not quite as big a problem as it appears at first glance, however, because

• That user cannot actually change the design of those objects.

• Tables and queries can be well protected through the startup properties discussed in Chapter 2, such as database window, special keys, and allow bypass. You can further protect these startup properties by using workgroup security to remove the Database Administer property (discussed in this chapter).

• You can use the With Owner permission in queries (as discussed in Chapter 11) to secure your query and table designs.

The problem that I have with the Read Design permission is that the data and queries are still vulnerable to importing the data into another database. You may consider using additional workgroup security, as discussed in Chapter 10, to secure your data. In Chapter 11, I show you how you can use With Owner permission in queries to secure your query and table design.

Module Permissions are Back

It was unfortunate in Access 2000 that the changes to the Visual Basic project meant that the User and Group Permissions dialog no longer allowed you to remove module permissions from the Database window. In Access 97, you could set these permissions as shown in Figure 8-23, and it was a protection measure that I had used since Access 2. Admittedly, it isn't foolproof, but it does deter the ordinary programming snoop.

Figure 8-23: Removing design permission from a module in Access 97.

One day when I was upgrading my Graf-FX shareware program from Access 97 to Access 2000, I noticed that the permissions that were supposed to have been abolished by the latest version were actually still prohibiting the anonymous Admin user from opening the modules. After some investigation into DAO permissions, I found that backward compatibility meant that you could still protect your modules from being opened in design mode from the Database window (see Figure 8-24).

Figure 8-24: The user account that does not have the permissions will receive this error message.

The following steps show how the VBA code in the frmProtectModule download form will protect a module:

1. Make sure that you are joined to the Developer workgroup and logged on as the Developer account.

2. Import the frmProtectModule form into your database.

3. Create and save a module called basHelloWorld .

4. Click the only button on the frmProtectModule form.

5. Close Access.

6. Open the database and log on as the anonymous Admin user.

7. Try to open the basHelloWorld module; you should receive an error like that shown in Figure 8-24.

The VBA code that will protect a module follows . Note that I have set up the code to remove all permissions on the object for both the anonymous Admin user and the Users group.

 Private Sub cmdProtectModules_Click() ' Protect a module in this database from the Admin user ' or the Users group from opening it it in design view. ' This subroutine requires a reference to ' Microsoft DAO 3.6 or 3.51 library. Const MYMODULE = "basHelloWorld" On Error GoTo err_cmdProtectModules Dim dbs As DAO.Database, cnt As DAO.Container, doc As DAO.Document Set dbs = CurrentDb Set doc = dbs.Containers("modules").Documents(MYMODULE) doc.UserName = "Admin" ' Remove all design permissions from module for the Admin account. ' You can restore it later by using "= dbSecFullAccess." doc.Permissions = dbSecNoAccess ' Remove all design permissions from module for the Users account. doc.UserName = "Users" doc.Permissions = dbSecNoAccess MsgBox "Permissions for " & MYMODULE & " and Account/Group " & _        doc.UserName & " are = " & doc.Permissions cmdProtectModules_exit:    Set doc = Nothing    Set dbs = Nothing    Exit Sub err_cmdProtectModules:    GoTo cmdProtectModules_exit End Sub 

Naturally, there is a caveat with this approach that says that if the user can open the Visual Basic project, the user can view this code by using the VB Project Explorer. Though that is true, removing this permission stops anyone from opening the module directly from the Database window in the first place, and that will be a major deterrent to alteration and code theft. It will also stop people from importing the module into another database, which is also a useful deterrent.