Appendix C - Security

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.

Hiding Database Objects

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.

Figure C-1. Hide a table if you don’t want users to see it.

caution


You might think that you can hide a database object by selecting it in the database window and then selecting Hide from the Window menu. However, this action hides the entire database window, not just the selected object.

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.

Encryption

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.

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.

Password-Protecting VBA Code

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:

  1. Switch to the VBE window by pressing Alt+F11 or choosing Tools, Macro, Visual Basic Editor.
  2. Choose Tools, Databasename Properties to open the Project Properties dialog box.
  3. Click the Protection tab, select the Lock Project For Viewing check box, and then enter a password in both text boxes, as shown in Figure C-3.

    figure c-3. set a password to protect a database’s vba code.

    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.

Saving as an MDE File

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.

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:

  • Doesn’t allow viewing or modification of forms, reports, or modules in Design view
  • Doesn’t allow adding, deleting, or changing references to object libraries or databases
  • Doesn’t allow changing of code (MDE files don’t contain any code.)
  • Doesn’t allow importing or exporting of forms, reports, or modules

caution


Converting an MDA file to an MDE file is a one-way trip. If you lose the original MDA file, you won’t be able to recreate it from the MDE file.

Startup Options

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.

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.

Database Password

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:

  1. Choose File, Open.
  2. In the Open dialog box, select the database for which you want to set a password, click the down arrow next to the Open button, and select the Open Exclusive option, as shown in Figure C-6.

    figure c-6.open a database in exclusive mode.

    Figure C-6. Open a database in exclusive mode.

  3. Choose Tools, Security, Set Database Password.
  4. The Set Database Password dialog box opens, as shown in Figure C-7. Enter the password twice and click OK.

    figure c-7.enter the password twice.

    Figure C-7. Enter the password twice.

After you close and reopen the database, the Password Required dialog box appears.

tip


To remove a database password, first open the database in exclusive mode. Then choose Tools, Security, Unset Database Password.

User-Level Security

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:

  1. Open the database you want to secure in shared mode (this is the default mode).
  2. Choose Tools, Security, User-Level Security Wizard.

    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.

    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.

    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.

    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.

    Figure C-11. The first screen of the User-Level Security Wizard looks like this.

    tip


    For more information on user-level security, click Help on the wizard screen.

  3. Click Next to create a new workgroup information file.
  4. Specify the name and location of the new workgroup, its ID (WID), and your name and company. The last two fields are optional. (See Figure C-12.) In the option group at the bottom of the screen, choose between using the new workgroup as the default one or making a shortcut to open the secured database. (The latter option is recommended.) Click Next.

    figure c-12.specify various settings for the new workgroup information file.

    Figure C-12. Specify various settings for the new workgroup information file.

  5. Specify which objects to secure. (See Figure C-13.) By default, all database objects are selected, which is generally desirable. Click Next.

    figure c-13.select the database objects you want to secure.

    Figure C-13. Select the database objects you want to secure.

    caution


    Ifyou ever worked with user-level security in Access 97 or earlier, you might remember that modules could be secured. Since Access 2000, modules can’t be secured with user-level security; you must either password-protect your VBA code or save the database as an MDE file.

  6. As shown in Figure C-14, you can optionally create a variety of different user groups with preset permissions. Click Next.

    figure c-14.select standard user groups for the workgroup information file.

    Figure C-14. Select standard user groups for the workgroup information file.

  7. You now can assign permissions to the Users group, as shown in Figure C-15. (All users belong to this group.) However, it is generally best to leave this option at the default (No, The Users Group Should Not Have Any Permissions) to ensure that permissions are acquired only through specific group membership. Click Next.

    figure c-15.set permissions for the users group.

    Figure C-15. Set permissions for the Users group.

  8. Add users to the workgroup file, as shown in Figure C-16. (You can add more users later on from the Security submenu.) Click Next.

    figure c-16.add a user to the workgroup information file.

    Figure C-16. Add a user to the workgroup information file.

  9. Assign users to groups or select a group and assign users to it, as shown in Figure C-17. Click Next.

    figure c-17.assign users to groups.

    Figure C-17. Assign users to groups.

  10. Specify the name of the backup (unsecured) database copy. You can choose to display Help on customizing security. Click Finish.

    figure c-18.save the backup (unsecured) database.

    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.

    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.

Figure C-20. The Logon dialog box for a secured database looks like this.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net