3 4
If you are creating databases for your own use, or you don’t need to restrict access to your databases in any way, you might not need this chapter. However, when you develop databases for use by others (particularly in a corporate environment), you often need to limit the access of certain users to an application, or to specific application components. For example, you might need to restrict access to sensitive employee data (like salaries) to employees in the Payroll department, or limit access to forms and reports in Design view to users in the MIS department.
Microsoft Access offers a variety of ways to restrict user access to database components. Some of these methods are quick and easy to apply; others are complex and the steps to implement those methods lengthy. This chapter describes some of the ways you can protect an Access database (or specific database components) against unwanted inspection or modification.
One of the simplest ways to limit access to certain database objects is to hide them. For example, to hide a look-up table so that users can’t see it (and therefore, can’t modify it), right-click the table in the database window and then select Properties. Select the Hidden check box in the properties sheet to hide the table, as shown in Figure C-1.
Figure C-1. Hide a table if you don’t want users to see it.
caution
It is easy to hide a database object so that users can’t see it. (The database object will remain available for use by other database components, and in VBA code.) However, a knowledgeable user won’t have much trouble finding that object. All the user needs to do is select the Hidden Objects check box on the View page of the Options dialog box, rendering hidden objects visible in the database window (though their icons are dimmed), and then open the hidden object’s properties sheet and clear the Hidden check box.
Because of the ease with which users can uncover hidden objects, this method does not offer any real security against users viewing or modifying objects. Use this technique primarily when you want to conceal system tables, forms under development, or other database objects that might be confusing to users or just clutter up the database window.
Encrypting a database offers another kind of protection: The database will be unintelligible when viewed by using a utility program or a word processor.
To encrypt a database, you must be either the database’s owner or (if the database is secured) a member of the Admins group of the workgroup information file used to secure the database. You must also be able to open the database in exclusive mode. If you can meet these conditions, choose Tools, Security, Encrypt/Decrypt Database to encrypt the database. (See Figure C-2.)
An Encrypt Database As window appears, in which you can specify the name and path of the encrypted database.
After you click Save, a progress bar in the status pane shows the progress of creating the encrypted database. (The original, unencrypted database remains unaffected.)
Figure C-2. Encrypting a database provides more security than simply hiding it.
You can view any database object in an encrypted database, and you can also switch to Design view and look at the code. Thus encrypting a database won’t protect database objects against viewing or modification. However, encryption does prevent examination of the database by someone using a utility program or word processor, and thus it can provide protection when transmitting a database electronically or storing it on various media.
If you are primarily interested in protecting your VBA code from viewing or modification (either because you don’t want the database’s functionality to be modified or destroyed, or it is a commercial product that you are selling and you don’t want to give away the code that makes it work), you can use a password to secure only the code.
To set a VBA code password, follow these steps:
Figure C-3. Set a password to protect a database’s VBA code.
After you close and reopen the database, the Databasename Password dialog box will appear when you attempt to open a module. You must enter the password to view or modify the VBA code in the database.
An even more secure method of preventing user access to VBA code is to save a database as an MDE file. This technique compiles all modules, removes all editable code, and compacts the database into an MDE file. To save a database as an MDE file, choose Tools, Database Utilities, Make MDE File, as shown in Figure C-4.
Figure C-4. Save a database as an MDE file for an even more secure method of protecting your VBA code.
A Save MDE As dialog box appears, in which you can specify the path and name of the MDE file.
An MDE file is smaller than an MDA file and might have better performance. Compared to an MDA file, an MDE file has the following characteristics:
caution
The Startup dialog box (opened by choosing Tools, Startup) offers several options for restricting user access to various parts of a database, in particular the Display Database Window, Allow Full Menus, and Use Access Special Keys check boxes. If you clear all three of these check boxes, (see Figure C-5) users won’t be able to see the database window and won’t be able to restore it by using the F11 hot key or the Unhide command on the Window menu. Of course, you need to have a main menu form set as the Display Form/Page or the database will open to a blank window.
Figure C-5. You can turn off access to the database window in the Startup dialog box.
This option is a good way to restrict users to working with a database through the menus and other interface elements you have designed. However, it does not totally prevent access to the database window, as users can still bypass the startup settings by holding down the Shift key while opening the database.
Suppose you want to restrict access to a database to certain individuals (say, coworkers, and not casual passers-by). You can set a database password to restrict access to the database to people who know the password. After you set a database password, users who try to open the database will be asked to enter a password into a Password Required dialog box.
Only users who have the password can open the database. After the user enters the password and opens the database, no further restrictions are imposed on access to database objects, so this method is primarily useful for preventing casual access to databases by anyone with access to a computer. (Unless you keep the password on a sticky note attached to the monitor.)
To set a database password, follow these steps:
Figure C-6. Open a database in exclusive mode.
Figure C-7. Enter the password twice.
After you close and reopen the database, the Password Required dialog box appears.
tip
The most sophisticated type of security (and not surprisingly, the most complicated and difficult to implement) is user-level security, which is implemented by means of a workgroup (a group of users who need to share data). User-level security allows you to grant different levels of access to specific database objects to different groups of users, a more sophisticated and flexible method of restricting access to database components than a database password’s all-or-nothing approach.
User-level security is complex, but the User-Level Security Wizard walks you through the process of implementing a standard user-level security scheme. (If you need a customized scheme, you will still have to work through the selections on the Security submenu.)
To set up security with the User-Level Security Wizard, follow these steps:
Joining the Admin Group
If you get the message shown in Figure C-8, you have to do some preparatory work in the Security submenu before running the wizard.
Figure C-8. If this error message appears when you first run the User-Level Security Wizard, you need to do a little extra work first.
Normally, all you need to do is choose Tools, Security, Workgroup Administrator, and then click Join in the Workgroup Administrator dialog box, as shown in Figure C-9.
Figure C-9. Join the default workgroup.
The Workgroup Information File dialog box appears next, as shown in Figure C-10. Click OK to join the default workgroup, or browse for another workgroup.
Figure C-10. Select the default workgroup to join.
Click OK in the success message box, and then click OK again in the Workgroup Administrator dialog box.
Now you’re ready to use the User-Level Security Wizard.
The first screen of the wizard appears, as shown in Figure C-11.
Figure C-11. The first screen of the User-Level Security Wizard looks like this.
tip
Figure C-12. Specify various settings for the new workgroup information file.
Figure C-13. Select the database objects you want to secure.
caution
Figure C-14. Select standard user groups for the workgroup information file.
Figure C-15. Set permissions for the Users group.
Figure C-16. Add a user to the workgroup information file.
Figure C-17. Assign users to groups.
Figure C-18. Save the backup (unsecured) database.
You’ll see a progress bar indicating that security is being applied to the database objects. When the wizard finishes, the Security Wizard report appears in print preview. Figure C-19 shows a portion of this report.
Figure C-19. The One-Step Security Wizard report looks like this.
As noted in the report header, you should print the report or export it to a file (by clicking Publish It To Microsoft Word on the toolbar) so that you will have a record of the settings used to create the workgroup information file and the secured database. Whether or not you publish the report to Word, on closing the report you will get a message asking if you want to save the report as a snapshot. If you click Yes in this dialog box, the report will be saved as a snapshot (.snp) file.
After you close the snapshot file, you will get a message that the Security Wizard has encrypted your database, and that you must close Access and then reopen it to use the secured database. The shortcut requested in step 4 of the wizard is located on your desktop. If you open the shortcut’s properties sheet, you will see its Target property is set as follows (the exact paths will depend on the location of Office XP, the database, and the workgroup file):
"F:\Microsoft Office XP\Office10\MSACCESS.EXE" "D:\Documents\Writing\Microsoft Press\Access Inside Out\ Test Case 2002.mdb" /WRKGRP "D:\Documents\Writing\Microsoft Press\Access Inside Out\Secured.mdw"
This lengthy target expression specifies the Access executable, the secured database, and the workgroup information file to use with the database. When the database is opened by using this shortcut, the Logon dialog box appears. (See Figure C-20.) After someoneenters a user name and that user’s password, the secured database opens, with access to the database objects set according to the group (or groups) to which that user belongs.
Figure C-20. The Logon dialog box for a secured database looks like this.