Securing and Administering Access

This chapter addresses the issue of database privacy, integrity, and security. The main purpose for database security is to prevent both inadvertent and intentional damage to the data and the database objects. Proper security measures prevent anyone who might view or edit the information from gaining unauthorized access. They also prevent anyone from making design changes without express permission to do so. You can protect both the data and the design elements with a variety of approaches. This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293).

Pick Up a Copy Today!Establish User-Level Security

In a multi-user environment, security becomes both more essential and more complicated. Not all users need to have access to all the data or all the design elements in the database. One way to create security is to organize users in groups, each of which has specific responsibilities. This information is stored in the Workgroup Information file (WIF).

Understand the User-Level Security Model

The user-level security model is based on the idea of workgroups whose members share the data and privileges. The group and user accounts list the members of the workgroup. A group account is a collection of user accounts. Each member of the group is permitted some degree of freedom in dealing with data and objects. A user account belongs to a single user and includes the user name and personal ID (PID).

The four pieces of a user-level security model are:

  • A user is a person who uses the database.

  • A group is a set of users, all of whom operate at the same security level and need access to the same parts of the database.

  • A permission gives a user or group the right to carry out a specific action. For example, Read Data permission allows opening a table or query for viewing but not for entering new data or editing existing data.

  • An object refers to any of the Access tables, queries, forms, reports, macros, or modules—as well as to the database itself.

QUICKSTEPS: Deciding Who Needs What Permisions

Depending on what the user needs to do with the database, you can assign her to any of the groups provided by the User-Level Security Wizard. To give a user more permissions than one group has, you can assign that user to more than one group.

  • Backup Operators can open the database exclusively for backup and compacting but are not permitted to see any of the database objects.

  • Full Data Users have full permission to edit data but are not allowed to make any design changes.

  • Full Permissions have full permissions on all database objects but are not allowed to assign permissions to others.

  • New Data Users can read and insert data but are not allowed to delete or update existing data. They are also not allowed to alter any object designs.

  • Project Designers have full permission to edit data and all objects but are not allowed to alter any tables or relationships.

  • Read-Only Users can read all the data but are not allowed to change data or any design object.

  • Update Data Users can read and update all data but are not allowed to insert or delete data. They are also not allowed to make any design changes.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Secure the Database with the User-Level Security Wizard}

The User-Level Security Wizard can secure any or all of the objects in your database. All the relationships and linked tables are kept intact when you secure the database with the Security Wizard. It also makes a backup copy of the original database in case you have forgotten to do so. The backup file has the same name with the .bak fi le extension.

With the Security Wizard, you can be specific about which users enjoy which permissions. You can also decide who belongs to which groups and edit user passwords and personal IDs (PIDs) as well. A PID is similar to a password and is used in combination with the user name to identify an account.

Start the User-Level Security Wizard
The database must be open before you can work with the User-Level Security Wizard.

  1. Open the database whose objects you want to secure.

  2. Click Tools | Security | User-Level Security Wizard.

    Securing the  Access Database

  3. In the first dialog box, as shown in Figure 9-1, either:

    • Select Create A New Workgroup Information File if one doesn’t exist.

      –Or–

    • Select Modify My Current Workgroup Information File to make changes in an existing WIF

      Figure 9-1: You can choose to create a new workgroup information fi le or edit an existing one, if available.
      Securing the Access Database


  4. Click Next.

  5. In the next dialog box, as shown in Figure 9-2, accept the workgroup ID (WID) the Security Wizard offers or enter a unique string in the WID box. The WID is a case sensitive string of between 4 and 20 alphabetic and numeric characters.

    Figure 9-2: The Security Wizard lets you choose where  to locate and what to name your Workgroup ID.
    Securing the Access Database

  6. Select I Want To Create A Shortcut To Open My Security-Enhanced Database.

  7. Click Next.

CAUTION  If you choose to make the WIF identifi ed in Figure 9-2 the default, every Access database you open will use it unless you specify that it be opened with a different WIF.

Secure Individual Objects
The third Security Wizard dialog box, shown in Figure 9-3, shows tabs for each of the objects in the current database. By default, all objects are secured by the wizard, but you can exclude some and keep the existing security measures in the others.

Figure 9-3: You can remove security from
objects that don’t need it.
Securing the Access Database

  1. Deselect the check box next to the object you want to leave as it is now.

  2. Click the tab (such as Queries, Forms, or Reports) for any other object whose security selections you want to change.

  3. When finished, click Next.

  4. If you have secured your Visual Basic code with a password, the Security Wizard asks for the password in the next dialog box. If not, you move on to setting up group accounts.

TIP  Prior to using the Security Wizard, be sure to open the database in shared mode. If you open it in Exclusive mode, the Security Wizard will offer to reopen it in shared mode. Also, be sure to close any unsaved objects you have open.

Securing the Access Database

NOTE  The Other tab in the third dialog box of the Security Wizard contains a list of new objects. You can choose to keep or to remove all new tables/queries, forms, reports, and macros from the Security Wizard’s security program.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Groups}

Set Up Group Accounts
Setting up group accounts involves choosing the appropriate groups for your application based on the tasks assigned to the users. In the next dialog box, shown in Figure 9-4, you can view which permissions are assigned to specific security groups, and then decide how to assign the users to the different groups. A unique group ID is assigned to each group by the Security Wizard.

Figure 9-4: The security group accounts provide specific
permissions to their members.
Securing the Access Database

CAUTION  The default Users group provides access to all objects in a database. Everyone who has a copy of Access is automatically a member of the Users group and will have the same permissions applying to all open databases.

  1. Select a group in the group list to read the description of the permissions in the Group Permissions area. For example, Figure 9-4 shows the permissions granted to the New Data Users group.

  2. Select the check boxes for the groups you want to include in the security model.

  3. Click Next.

  4. The next Security Wizard dialog box, shown in Figure 9-5, allows you to assign some permissions to the Users group but strongly recommends against that. Choose No, The Users Group Should Not Have Any Permissions.

    Figure 9-5: The Security Wizard recommends not giving the Users group any permissions.
    Securing the Access Database

5. Click Next.

{mospagebreak title=Add, Edit, and Remove Users}

In the next Security Wizard dialog box, shown in Figure 9-6, you can get specific about which users to add to the workgroup. You can also delete a user from the workgroup and edit a user’s password or PID.

Figure 9-6: The Security Wizard allows you to add users to and delete users from the workgroup.

Securing the Access Database

To add a new user:

  1. Click Add New User in the left pane.

  2. Type a name in the User Name text box.

  3. Type a password in the Password text box.

  4. The user’s PID is automatically entered, but you can change it if you want to.

  5. Click Add This User To The List.

  6. Repeat Steps 1 through 5 to add other users to the workgroup.

To remove a user from the workgroup, select the name and click Delete User From The List. To edit user information, select the name and change the password or PID. Click Next when fi nished with the users in the workgroup.

Assign Users to Groups
The final major step in defining user-level security is to assign each user to one of the groups that you have selected. You have two ways to carry this out in the next Security Wizard dialog box, as shown in Figure 9-7. You may start with a user and add the user to groups, or start with a group and assign users to it.

Figure 9-7: You can assign users to groups or groups to users.
Securing the Access Database

  1. In the next dialog box, click Select A User And Assign The User To Groups. The user names you added to the workgroup appear in the Group Or User Name drop-down list. The group names you included appear in the pane below the list.

  2. Click the Group Or User Name down arrow and select a user name from the list.

  3. In the lower pane, select each group name that you want the user to be a member of.

  4. Repeat Steps 2 and 3 to assign other users to groups. Then click Next.

  5. In the final Security Wizard dialog box, enter a name and path for the unsecured backup database, and click Finish.

NOTE  You can assign a user to more than one group. The user then has all the permissions associated with all the groups she belongs to.

NOTE  When you assign users to groups in the Security Wizard, if you choose Select A Group And Assign Users To The Group, you select the group name in the Group Or User Name drop-down list and select the user names from the list below it.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Save or Print the Security Report}

It is extremely important to document and save all the security provisions you have set. When the Security Wizard is finished, a security report is displayed in Print Preview as shown in Figure 9-8. You will need all this information if you need to rebuild the WIF.

Figure 9-8: All the security provisions are
documented in the security report.
Securing the Access Database

You have three ways to save the document:

  • Click Print on the Print Preview toolbar to print a hardcopy of the report. Store the printout in a safe place.

  • Choose Tools | Offi ce Links | Publish With Microsoft Office Word to save it as a Word document. Print the document or store the fi le in a safe place.

    Securing the Access Database

  • Choose File | Export to open the Export Report Reportname As dialog box. In the Save As Type drop-down box, choose Snapshot Format (*.snp), and type a name for the file in the File Name box. Click Export.

    Securing the Access Database

QUICKSTEPS: Keeping Data Safe

Prevent User Access to Data in a Form
If you don’t want the user to be able to get to a text box control in a form by pressing TAB:

  1. Open the form in Design view.

  2. Double-click the text box control you want to protect.

  3. In the property sheet, click the Other tab.

  4. Select the Tab Stop property, and choose No from the drop-down list. Then save the form design.

The user will still be able to click in the control and edit the contents.

Securing the Access Database

Limit To Values in a Lookup Field
A lookup field can appear in a form as a combo box or as a list box from which the user chooses a value. She may also enter a value not already on the list. If you don’t want other values in the field:

  1. Open the table in Design view.

  2. Select the lookup fi eld in the upper pane.

  3. Click the Lookup tab in the lower pane, and change the Limit To List property to Yes.

    Securing the Access Database

  4. Save the table design.

Require Valid Data
To make sure newly entered data is correct, include data validation rules in a table or form design. To do this:

  1. Open the table in Design view and select the field name in the upper pane.

  2. Select the Validation Rule property in the Field Properties pane, and enter the expression that the field value must comply with.

  3. Select the Validation Text property text box, and enter a message to display when the rule is broken.

  4. Save the table design.

Text box controls in forms also have the Validation Rule and Text properties. See Chapter 3 for more information on using data validation.

Prevent Data Changes in a Form


To keep a user from entering, deleting, or editing data in a form:

  1. Open the form in Design view, and double-click the form selector.

  2. In the property sheet, click the Data tab.

  3. Select the Allow Edits property text box, and choose No from the drop-down list.

    Securing the Access Database

  4. Repeat Step 3 to set the Allow Deletions and Allow Additions properties.

  5. Save the form design.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Apply Global Database Protection to a Database}

Access offers several less comprehensive methods of securing a database and its objects. For example, you can require a password to open the database, and you can hide specifi c objects from user view. In addition, there are methods to create backup copies of the database and to improve performance by optimizing disk space usage.


Use a Database Password

A database password only protects a database from being opened by someone who doesn’t know the password. Anyone who knows the password can open the database. Once the database is open, the user can do anything with it.

Assign a Password
You must have exclusive use of the database to assign a password.

To add a password, make sure all users have closed the database, then:

  1. Click File | Open. In the Open dialog box, select the database, click the Open down arrow, and choose Open Exclusive.

  2. Click Tools | Security | Set Database Password.

    Securing the Access Database

     
  3. Type the password in the Password text box.

  4. Repeat the password in the Verify text box, and then click OK. (If the entries don’t match, Access will ask you to reenter the password in the Verify box. Do so, and click OK.)

The next time you try to open the database, you will be asked for the password.

Securing the Access Database

Some basic guidelines for creating a password include:

  • Create a password that combines uppercase and lowercase letters with numbers and symbols. This builds a “strong” password. For example, “67TrCg!89sdJ” is a strong password, while “MyFavoriteCat” is not.

  • Never use a word that appears in the dictionary.

  • Do not use the special characters that have a special meaning in Access: “ [ ] : | < > + = ; , . ? *.

CAUTION  Don’t forget your password. You won’t be able to open the database without it. To be safe, write it down and store it in a safe place.

Remove a Password


To remove the requirement for a password:

  1. Open the database in Exclusive mode.

  2. Choose Tools | Security | Unset Database Password.

  3. Enter the password and click OK.

Encode/Decode a Database


An encoded database is compacted and unreadable by any word processor or other utility program. You still have access to an encoded database and can work with it as usual. When you decode an encoded database, it returns to its original form.

The database must be closed before you can encode it. You must be the owner of the Workgroup Information File and be able to open it in Exclusive mode. In addition, your disk must have enough free space for both the original database and the encoded copy.

TIP  Always make a backup of your database before adding a password. Store the backup in a secure place.

To encode a database:

  1. In the empty Database window, click Tools | Security | Encode/Decode Database.

  2. Select the database in the Encode/Decode Database dialog box, as shown in Figure 9-9, and click OK.

    Figure 9-9: You can encode a database to make it
    unreadable by programs other than Access.
    Securing the Access Database


  3. In the Encode Database As dialog box, specify the location where you want to save the encoded database, and click Save.

To decode the encoded database, repeat steps 1 and 2 above, and then specify the name and location in the Decode Database As dialog box. Click OK.

CAUTION  You can have problems if you link a table in a password protected database to an unprotected database. The password for the protected database is stored with the linking information passed to the second database. Any user who can open the unprotected database also has access to the linked table in the protected database.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Remove Database Objects from View}

Hiding database objects does not really tighten security; it just keeps certain objects from appearing in the Database window.

Hide Database Objects


To hide an object:

  1. Select the object in the Database window, and click Properties on the Database toolbar.

  2. Check Hidden at the bottom of the General tab in the Properties dialog box, as shown in Figure 9-10.

  3. Click OK.

NOTE  You can save the encoded version with the same name as the original, but you will be asked to confirm this. If the encode process fails, the original database is not deleted.

View Hidden Database Objects

Normally, the hidden object is no longer listed in the Database window; however, you can choose to see the names of the hidden objects.

  1. Choose Tools | Options and click the View tab.

  2. In the Show area, select Hidden Objects.

  3. Click OK.

Although the hidden objects appear dimmed in the Database window, they can still be opened. To remove the Hidden property, open the Properties dialog box (see the previous section, “Hide Database Objects”) and deselect the Hidden option.

Figure 9-10: Use the object’s Properties dialog box to hide the object.

Securing the Access Database


This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Back Up and Restore a Database}

When working with an important database, it is a good idea to have a backup copy on hand. Creating a backup database on a regular basis can help reduce the risk of losing important data.

Before making a backup copy, make sure all users have closed their databases so all changes in the data have been saved.

You can make a backup copy from either outside or inside of Access. To backup without using Access, do one of the following:

  • Right-click the file name in the Windows Explorer window, and point to Send To in the context menu, as shown in Figure 9-11. Click the drive where you want to save the copy.

    Figure 9-11: You can create a backup copy using Send To
    in Widows Explorer.

    Securing the Access Database

  • After locating the file name in the Windows Explorer window, drag it to another disk in the Folders pane.

  • Use Microsoft Windows Backup and Recovery tools. For example, in Windows XP you can use the Automated System Recovery (ASR) tool (Click Start | All Programs | Accessories | System Tools | Backup | Advanced Mode). You can also use the MSDOS Copy command or a third-party backup utility. (You may also be able to compress the file at the same time.)

Back Up a Database From Access

You can use Access to create a regular copy to keep as a backup copy. No compression or other reformatting takes place—you just create a regular database file.

TIP  By default, the backup fi le keeps the same name with the current date added to it.

  1. Click File | Backup Database.

  2. Choose the location for the copy, and type a name for it in the Save Backup As dialog box, as shown in Figure 9-12.

    Figure 9-12: You can also create a backup copy with Access.

    Securing the Access Database

  3. Click Save.

TIP  If yours is a multi-user environment, be sure to backup the Workgroup Information File, too.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Back Up Individual Database Objects}

If you want to back up only a few objects instead of the whole database:

  1. Create a new empty database.

  2. Click File | Get External Data | Import.

    Securing the Access Database

  3. Locate and select the database in the Import dialog box. Click Import.

  4. Click each tab in the Import Objects dialog box, as shown in Figure 9-13, and select the objects you want. See the section, “Import Data from Outside Sources,” in Chapter 4 for more information about importing.

    Figure 9-13: You can import objects into the backup database.

    Securing the Access Database

  5. Click OK to import the objects.

  6. Save the new database.

NOTE  By default, the new database assumes the Access 2000 format, unless you’ve changed the default settings. See Chapter 1 for more information about changing default settings.

TIP  Some defi nitions can cover many pages. Be sure to check how many before you start to print.

Restore a Database

When you need to restore the database from the backup copy, use the recovery tool for the method you used to create the backup. For example, in Windows Explorer you may simply drag the fi le name back to the original folder on the hard drive.

NOTE  You can save the Documenter’s report as a Report Snapshot; an Excel worksheet; or in another format, such as HTML or RTF. (See Chapter 7 for information on creating a Report Snapshot.)

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Database Administration}

Access provides tools to assist you in managing the size of your database, as well as to repair a database that may have become corrupted. You can also create a printout of your database relationships, database properties, and definitions of your database objects.

Document a Database

If you are working alone on your own database, you probably don’t need extensive documentation of the database objects. In a corporation where there is a large information management team, documentation is extremely important. With up-to-date object definitions, errors can be quickly isolated and fixed.

The documentation can include all or a select group of objects in the database.

  1. Choose Tools | Analyze | Documenter, as shown in Figure 9-14.

    Figure 9-14: You can select just those objects
    you want documented.
    Securing the Access Database

  2. Either:

    • Select each object tab and select the objects you want documented, or click Select All.

      –Or–

    • Select the All Objects Types tab, and click Select All. This includes relationships and the database properties as well as the definitions of all the database objects.

  3. If you don’t need all the information about an object, you can click Options and choose how much you want to see. Figure 9-15 shows the choices you have with table documentation.

    Figure 9-15: Choose the table defi nition items to
    include in the documentation.

    Securing the Access Database

  4. Click OK twice when you have finished making your selections.

  5. When the Documenter is finished, the results appear in Print Preview.

  6. Click Print to print the entire document, or press CTRL+P and use the Print dialog box to print selected pages. See Chapter 8 for more information on printing database objects.

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

{mospagebreak title=Compact and Repair a Database}

As you improve and modify your database, the file can become scattered about on your disk with empty blocks of space between. The Compact and Repair Database utility removes the empty spaces and rearranges the file more efficiently to improve performance. If there has been some damage, this utility can find the problems and offer to repair them at once.

You can start the compact and repair process with the database open or closed. With the database open, simply click Tools | Database Utilities | Compact And Repair Database.

TIP  One of the options on the General tab of the Tools | Options dialog allows you to automatically compact a database when you close it if the reduction in size would be greater than 256K. To do this, select the Compact On Close option.

If the database is closed, you can compact and repair it to the same fi le or with a different name in another location.

Figure 9-16: Documenter shows you the documentation
report before you print.
Securing the Access Database

If the compact and repair utility doesn’t work, one of the following problems may exist:

  • The database may be open by another user. Wait for the other user to close the database and try again.

  • There is not enough free space for both the original and the repaired database on the disk. Go back and delete unnecessary files, and try again.

  • You may not have the required Open/Run and Open Exclusive permissions.

  • The name of the database from an earlier version of Access may include a character that is no longer permitted, such as the grave accent (`). Return to the earlier version of Access, change the name, and then try again.
  • The database file may be set to Read Only.

    Securing the Access Database

This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.

Buy this book now.

2 thoughts on “Securing and Administering Access

  1. I\’m not sure about Access2003… but every other version of access that uses the workgroup files starts out with a flaw that must be fixed. The Admin user has administration rights on everything, and if the workgroup file is moved or inaccessable, a user is automatically logged in as Admin… even if you gave the Admin user a password (as you have to to get the password prompt at all for other users in some versions) it will not require one because the password is found in the workgourp file! So make sure you give admin rights to someone else (i.e. your user name) kill the rights for the Admin user, and change the owner of all the database objects to someone else as well.

    Warn the people! 😮

    mwalts

    p.s. the ASP Free forum for Access help is very active, stop by for quick help with your problems and questions

[gp-comments width="770" linklove="off" ]