Security Wizards

If you want to secure your database and data with workgroup security, there is no better way to make wholesale changes than with the User -Level Security wizard in Access 2000 or later. This very well-crafted wizard will undertake a lot of complex tasks for you. If you are using Access 97, the wizard does not set up any particular Group accounts and requires more setup time and testing after it finishes. Nevertheless, when running either of the wizards, I suggest that you wait until a good portion of the application is completed before implementing detailed workgroup security.

Access 2000 (or Later) User-Level Security Wizard

This wizard adds Users and Groups to the workgroup file and then offers you a number of permission schemes that you can apply to the database that you are going to protect. To demonstrate how the security wizard works, I will show you how to secure data in a back-end version of the Northwind database. The purpose of this demonstration will be to:

• Secure the tables in that database so that members of the ubiquitous Users group (Admin user) can have read-only access to all the tables in the database.

• Add a new Group account that will allow permission to insert and change data but will not allow changes to any objects in the database.

To do this demonstration, I am going to use a copy of the Northwind database that has been split into a back end that contains only tables and an existing developer workgroup file as described in Chapter 8. This workgroup file should already have the Developer and Admin accounts in it, and, if you have the Editor account (as described earlier), don't worry ”the steps are almost identical. If you have never run the security wizard before, it is always a good idea to test the wizard on the copy of your database prior to running it on a live database. The steps to protecting data with this User-Level Security Wizard follow:

1. Join the developer workgroup.

2. Open the database that you want to secure (probably called Northwind_BE.mdb ).

3. Open the User-Level Security wizard by choosing Tools ˜ Security ˜ User-Level Security Wizard.

4. On the first page of the wizard, you will be asked whether you want to modify an existing workgroup file or create a new one (as shown in Figure 10-17). In this case, select Modify my current workgroup information file. You are also informed that a backup will be made of your database before the permissions are changed.

   
   Figure 10-17: Page one of the security wizard, where you can select an existing workgroup file.

5. On the second page of the wizard, a multitab form allows you to select the objects that you want to secure. In most cases, you will be securing all the objects in the database, which is the wizard's default selection. For this example, the wizard selects all the tables, including four New object properties (as shown in Figure 10-18). The permissions on these new objects ensure that you will be covered by the same permission schemes when you create new objects in the database after the wizard is run.

   
   Figure 10-18: Page two of the security wizard, showing the optional selection of objects.

6. The third page (in Figure 10-19) shows this wizard's sophistication. On this page, you can select from a number of very useful permissions schemes to apply to your database. As you select each option, you will see a description of what each scheme does. If you decide to adopt one of the schemes, choose a meaningful group ID (called a PID in the rest of the Access help guide), because you will use this PID to re-create the Group account later. For this exercise, we are interested in allowing a new class of users to change any data in the database, so select the Full Data Users Group.

   
   Figure 10-19: Page three of the wizard, which allows you to choose the different permission schemes.

   Caution  

   If you use the User-Level Security wizard in Access 2000 or later, you have to be aware that the wizard does not allow you to use different names for the Group accounts. Therefore, if you have two different databases with different group account PIDs, you should set up one of them manually, or you will be forever pulling your hair out.

7. The fourth page of the wizard allows you to grant permissions to the ubiquitous Users group (as shown in Figure 10-20). As I explained in Chapter 8, it is possible to have protected Access systems where the users do not have to log on to a workgroup but instead can use the anonymous Admin account to use the database. Of course, if you want protection against the ubiquitous Users group, you should select No, the Users Group Should Not Have Any Permissions. This option locks out all workgroup users who are not a member of a group defined on the third page of this wizard. Also on this page of the wizard, select the Database tab and the Open/Run permission to allow the Users group to open the database.

   
   Figure 10-20: Page four of the wizard, which allows you to assign permissions to the Users group.

8. The fifth page of the wizard (in Figure 10-21) allows you to add user accounts to the workgroup file. When you have typed in the new user name , password, and PID, click the "Add This User to the List" button. In this case, I am going to add the identical Editor user that I established earlier in this chapter.

   
   Figure 10-21: Page five of the wizard, which allows you to add additional user accounts, passwords, and PIDs.

9. The sixth page of the wizard (as shown in Figure 10-22) allows you to allocate Groups to Users or, conversely, Users to Groups. You need to take care with this page to ensure that you set up the associations correctly. Remember that you need to use the Group or User Name drop-down list in the center of the form to change your user selections. I recommend, when you have finished defining who belongs where, that you switch to the alternative view of users or groups to verify your selections.

   
   Figure 10-22: Page six of the wizard, which allows you to allocate users to groups.

10. The final page of the wizard allows you to select the location to which the current unsecured database will be copied (as shown in Figure 10-23). When you complete the wizard, you need to be careful about where you leave the (current) unsecured copy of the database. On this page, you should also select the Display Help on Customizing Security check box, which will open the Access help as a good point of reference.

    
    Figure 10-23: Saving the unsecured database in the final page of the wizard.

11. When you click the Finish button on that last page, the wizard will first rename your current database to the backup name and then will create a new encrypted database and import all the objects into it by using your secure User account. When the objects are imported into the new database, the wizard will allocate all the permissions on the objects according to the permissions schemes that you selected. When this task is complete, an Access report appears that details all the changes made to the database. This report (a portion of which is shown in Figure 10-24) will include the workgroup IDs if this is a new workgroup file plus personal IDs for each User and Group account. When this report appears, you should print it or even save it in Microsoft Word format as a password-protected document. Once you have printed it, you will be asked to save the report as an Access snapshot.

    
    Figure 10-24: A report showing all the security settings and passwords.

Checking Out the Results of the Wizard

Once the wizard has completed its tasks, what has actually happened to the database? The first thing that has happened is that the database is now in an encrypted format. If you are worried about the small items of information in your database, then this may be important to you. Alternatively, if you are confident that your database doesn't offer any little gems to a hacker manned with a Hex editor, then you should contemplate returning the database to an unencrypted format. I explained the vagaries of this database format in more detail in Chapter 9.

To verify what has happened to the workgroup file that you altered , join the workgroup file and then open any database. I know you may have been tempted to say that we should open the secured database, but I want to make a point that the changes that you made to either User or Group accounts only apply to the work-group file, not to the database itself. Now choose Tools ˜ Security ˜ Users and Group Accounts, and on the dialog that appears, click the Print Users and Groups button. A report of the workgroup file, as shown in Figure 10-25, will print on your default printer.

Figure 10-25: The report of the workgroup file.

Tip  

To keep a copy of the Users and Group report, you need to set up a PDF printer driver such as the one that comes with Acrobat Distiller. Make that driver your default printer by using your Windows configuration dialogs, then you can capture the output of the printout to a file.

Now you can open the database that you secured with the wizard and verify the object permissions. First choose Tools ˜ Security ˜ User and Group Permissions, and then look at the permissions for a random selection of objects in the database. If the wizard has done its job properly, all the user accounts, apart from your Developer account, should have no permissions for any objects. Figure 10-26 shows that the new Editor account has no permissions for the Orders table.

Figure 10-26: The Editor should have no permissions for any table.

Finally, we need to review the permissions for the Groups that the wizard has established. To do this, select the Groups option and select Full Data Users, as shown in Figure 10-27. You will see that anyone who is a member of this group will have the ability to change or add any data in this table. This result, of course, is the same that we achieved manually earlier in the chapter (as shown in Figure 10-12), and it is exactly the result we want.

Figure 10-27: The Full Data Users group permissions on tables.

Tip  

As a test of your documentation, give the workgroup IDs and PIDs to someone else, and get him or her to rebuild the workgroup file from scratch.

The Access 97 User-Level Security Wizard

Unlike its newer cousins, the Access 97 User-Level Security wizard is a very Spartan affair. In fact, the whole wizard fits on just one page. Used correctly and at the right time in your project, it will accomplish the following:

• Create a new database and export all the objects into it.

• Transfer ownership of all the objects in the new database to the User account that is running the wizard.

• Remove all the permissions from the object types selected on the first page of the wizard (as shown in Figure 10-28) for all but the Admins group (administrators) and the User account that is running the wizard.

  
  Figure 10-28: The Access 97 User-Level Security wizard.

• Grant full permissions for all the object types selected on the first page of the wizard to the Admins group and the User account running the wizard.

• Encrypt the database.

How Not to Run the Access 97 Wizard

When you start the Access 97 User-Level Security wizard, you first need to make sure at the bottom of the page that you are not logged on as the anonymous Admin account or joined to the default workgroup file. In Figure 10-29, I show you how the security wizard should not look when you start it up. In this case, the default Admin account is using the default workgroup file. If you continue past this form by using the Admin account, the wizard will tell you that the database and all its objects will be unsecured because the Admin user will own all the objects in the new database and have full permissions for all object types selected in the wizard. Therefore, if the current user is Admin or the current workgroup file is in Windows' System32 subdirectory, do not bother going any further with this wizard.

Figure 10-29: The Access 97 Users-Level Security wizard should not look like this.

Running the Access 97 Wizard

To run the User-Level Security wizard in Access 97, you need to have set up a developer workgroup file and have an account that belongs to the Admins group, such as the Developer account described in Chapter 8. Now you can follow these steps to secure your database from the ubiquitous Users group and Admin user.

Caution  

The Access 97 User-Level Security wizard will remove all permissions granted previously to any other User or Group account. Therefore, this wizard should not be used on any database to which you have already added significant security.

This wizard is good to use when you haven't added any security to the database and want to start the database in a secure state. Once you have run the wizard, you will need to resort to the User and Group Permissions or User and Group Accounts menus to add the rest of your security. That said, the wizard is useful for taking the database from an unsecured state to one in which it is ready for you to add User and Group accounts and to establish permissions for groups of users.

Testing for Flaws after Running the Wizard

Despite this wizard's sophistication and power, a lot happens behind the scenes that really does need testing. As an example, both the Access 97 and 2000 User-Level Security wizards sometimes do not remove the open/run permissions on the Database object. In all cases, you need to test your database by attempting to open the database by using a default workgroup file and the anonymous Admin account. Irrespective of which version of Access you are using, testing the permissions is as important as creating them.

Now we come to the all-important part of the chapter where I tell you how your Access database can be secured rather than just protected for multiple users. To secure it, you must adopt one of the techniques explained in the next section.