Home > Sample chapters

Securing and Sharing Information in Microsoft Office Access 2007

In this chapter from Microsoft Office Access 2007 Step by Step, you will explore ways to protect data from accidental or intentional corruption, and ways to make it difficult for unauthorized people to gain access to private information. Then you will learn about ways of sharing databases among team members and backing up a shared database.

The need for database security is an unfortunate fact of life. As with your house, car, office, or briefcase, the level of security required for your database depends on the value of what you have and whether you are trying to protect it from curious eyes, accidental damage, malicious destruction, or theft.

The security of a company’s business information can be critical to its survival. For example, you might not be too concerned if a person gained unauthorized access to your products list, but you would be very concerned if a competitor managed to see—or worse, steal—your customer list. And it would be a disaster if someone destroyed your critical order information.

Your goal as a database developer is to provide adequate protection without imposing unnecessary restrictions on the people who should have access to your database. The type of security required to protect a database depends to a large extent on how many people are using it and where it is stored. If your database will never be opened by more than one person at a time, you don’t have to worry about the potential for corruption caused by several people trying to update the same information at the same time. If your database is sold outside of your organization as part of an application, you will want to take steps to prevent it from being misused in any way.

Another way to protect a database is by securing the distribution channel; for example, by making it available from a password-protected Web site.

In this chapter, you will explore ways to protect data from accidental or intentional corruption, and ways to make it difficult for unauthorized people to gain access to private information. Then you will learn about ways of sharing databases among team members and backing up a shared database.

Assigning a Password to a Database

You can prevent unauthorized users from opening a database by assigning it a password. Access will prompt anyone attempting to open the database to enter the password. The database will open only if the correct password is entered.

A secondary benefit of assigning a password is that your database will automatically be encrypted each time you close it, and decrypted when you open it and provide the correct password.

It is easy to assign a database password, and certainly better than providing no protection at all, in that it keeps most honest people out of the database. However, many inexpensive password recovery utilities are available, theoretically to help people recover a lost password. Anyone can buy one of these utilities and “recover” the password to your database. Also, because the same password works for all users (and nothing prevents one person from giving the password to many other people), simple password protection is most appropriate for a single-user database.

To assign a password to or remove a password from a database, you must first open the database for exclusive use, meaning that no one else can have the database open. This will not be a problem for the database used in the following exercise, but if you want to set or remove a password for a real database that is located on a network share, you will need to make sure nobody else is using it.

In this exercise, you will assign a password to a database.

  1. Click the Microsoft Office Button, and then on the menu, click Open.

    httpatomoreillycomsourcemspimages1121720.jpg

    Microsoft Office Button

  2. In the Open dialog box, navigate to the Documents\Microsoft Press\Access2007SBS\Reports folder, and click (don’t double-click) the Password database. Then click the Open arrow, and in the list, click Open Exclusive.

    httpatomoreillycomsourcemspimages1122342.png

    Access opens the database for your exclusive use—no one else can open the database until you close it.

  3. On the Database Tools tab, in the Database Tools group, click the Encrypt with Password button.

    The Set Database Password dialog box opens.

  4. In the Password box, type 2007!SbS, and then press the httpatomoreillycomsourcemspimages1121704.jpg key.

    httpatomoreillycomsourcemspimages1122344.png

    Access disguises the characters of the password as asterisks as you type them, to protect against other people seeing your password.

  5. In the Verify box, type 2007!SbS. Then click OK.

  6. Close and reopen the database.

    The Password Required dialog box opens.

    httpatomoreillycomsourcemspimages1122346.png
  7. In the Enter database password box, type 2007_SBS, and then click OK.

    Access warns you that the password is not valid.

  8. In the Microsoft Office Access message box warning you that the password you entered is not valid, click OK.

  9. In the Password Required dialog box, type the correct password (2007!SbS), and then click OK.

    The database opens.