After you design and develop a sophisticated application, you should ensure that the integrity of the application and the data it maintains are not violated. Microsoft Access gives you several options for securing your database. These options range from a very simple method of applying a password to the entire database, to applying varying levels of security to each and every object in the database. The more intricate your security solution, the more difficult it is to implement. Fortunately, you can tailor the complexity of the security you implement to the level of security required by each particular application.
The simplest, yet least sophisticated, method of implementing security is to assign a password to the overall database. This means that every person who wants to gain access to the database must enter the same password. After a user gains access to the database, all the database’s objects are available to that user. This type of security is referred to as share-level security.
Share-level security is the simplest and quickest security to set up. With almost no effort, the database and its objects are secured. This method of security is quite adequate for a small business in which the administrators of the database want to ensure that no unauthorized people can access the data, but that each authorized person has full access to all its objects.
To assign a database password, follow these steps:
Open the database to which you want to assign a password by selecting Open Exclusive from the Open drop-down of the Open dialog.
Choose Tools|Security|Set Database Password. The Set Database Password dialog box appears, as shown in Figure 27.1.
Type and verify the password and click OK. The password is case sensitive.
After you assign a password to a database, users are prompted for a password each time they open the database. The Password Required dialog box appears each time the database is opened, as Figure 27.2 shows.
After users enter a valid password, they gain access to the database and all its objects. In fact, users even can remove the password by choosing Tools|Security|Unset Database Password. The Unset Database Password dialog box only requires that users know the original password. (See Figure 27.3.)
Although these passwords are extremely easy to understand and implement, they also are extremely unsophisticated. As you can see, users either have or do not have access to the database, and it is very easy for any user who has access to the database to modify or unset its password.
Caution
If you forget the password associated with a database, it is not easy to gain access to the database and its objects. It therefore is extremely important that you carefully maintain a list of the passwords associated with each database. On the other hand, it is not impossible to break the security set on an Access database. In fact, there are Web sites that will remove Access database security for a fee! This means that, if security is of the utmost importance to you or your users, Access database security might not be the appropriate solution for you. To ensure that your data is secure, you can store it in a Microsoft SQL Server database. A client/server database such as Microsoft SQL Server offers you a much more robust security model than that available with the MDB file format.
Note
To assign a password to a database, users must be able to open the database exclusively. You can grant or deny users the right to open a database exclusively by using the User and Group Permissions dialog box. Assigning rights that permit or deny users or groups exclusive open rights is covered in the “Step 11: Assigning Rights to Users and Groups” section of this chapter.
Before moving on to the more sophisticated methods of securing a database, it is important that you understand what any method of security does and does not provide for you. No matter how well you learn about and implement the techniques in this chapter, you will not be protected against someone attempting to read the data contained in your database. Even after you secure a database, someone with a disk editor can view the contents of the file. Although the data in the file will not appear in an easy-to-read format, the data is there and available for unauthorized individuals to see.
You might be feeling discouraged and asking yourself, “Why bother with security?” Do not despair! Fortunately, Access enables you to encrypt a database, rendering the data in the database indecipherable in word processors, disk utilities, and other products capable of reading text. When a database is encrypted, it is much more difficult to decipher any of its data.
A database can be encrypted using the standard Access menus or by writing a VBA subroutine. In either case, the database you are encrypting must not be open. To encrypt a database using Access’s standard menus, follow these steps:
Choose Tools|Security|Encrypt/Decrypt Database.
The Encrypt/Decrypt Database dialog appears. Select the file you want to encrypt and click OK.
You are prompted for the name of the encrypted database. If you select the same name as the existing file, Access deletes the original decrypted file after it determines that the encryption process is successful.
Note
You cannot encrypt a database to itself if it is open. You must first close the database and then select Tools|Security|Encrypt/Decrypt Database.
Note
It is always a good idea to back up the original database before you begin the encryption process. This ensures that if something goes awry during the encryption process, you won’t lose your data.
Note
You also can encrypt or decrypt a database file by using code. This is covered in Chapter 28, “Advanced Security Techniques.”
When you encrypt a database, the entire database (not just the data) is encrypted. As you access the data and the objects in the database, Access needs to decrypt the objects so that users can use them. When users are finished accessing the objects, Access then encrypts them again. Regardless of the method of encryption you use, the encrypted database degrades performance by about 15%. Furthermore, encrypted databases usually cannot be compressed by most disk-compression software utilities because compression software usually relies on repeated patterns of data. The encryption process is so effective at removing any patterns that it renders most compression utilities ineffective. You need to decide whether this decrease in performance and the inability to compress the database file is worth the extra security that encryption provides.
For most business environments, share-level security is not sufficient. Therefore, it is necessary to take a more sophisticated approach toward securing the objects in your database. User-level security enables you to grant specific rights to users and groups in a workgroup. This means that each user or group can have different permissions on the same object. With this method of security, each user begins by entering a username and password. The Jet Engine validates the username and password and determines the permissions associated with the user. Each user maintains his or her own password, which is unrelated to the passwords of the other users.
In this method of security, users belong to groups. You can assign rights at the group level, the user level, or both. Users inherit the rights of their least restrictive group. This is highlighted by the fact that security is always on. By default, all users get rights to all objects because every user is a member of the group called Users. By default, this group is given all rights to all objects. If you have not implemented security, all users are logged on as the Admin user, who is a member of the Users group and the all-powerful Admins group. The Jet Engine determines that the Admin user has no password and therefore does not display an opening logon screen. Because members of the Users and Admins groups get rights to all objects by default, it appears as though no security is in place.
With user-level security, you easily can customize and refine the rights to different objects. One set of users might be able to view, modify, add, and remove employee records, for example. Another set of users might be able to only view employee information. The last group of users might be allowed no access to the employee information, or they might be allowed access only to specific fields (such as name and address). The Access security model easily accommodates this type of scenario.
The major steps to implementing user-level security follow (each step is developed in detail later in the chapter):
Use the Workgroup Administrator to establish a new system database.
Start Access and change the Admin user’s password to a non-
Nullpassword.Create a new user who will be the administrator of the database.
Make the user a member of the Admins group.
Exit and restart Access, logging on as the new system administrator.
Remove the Admin user from the Admins group.
Assign a password to the new system administrator.
Open the database you want to secure.
Run the Security Wizard.
Create users and groups consisting of members of the workgroup defined by the system database.
Assign rights to users and groups for individual objects.
Note
Many of the steps previously outlined can be accomplished using the User-Level Security Wizard. Although the Security Wizard is a powerful tool, it does not provide the same level of flexibility afforded to you when you perform the steps yourself. In this chapter, I therefore focus on performing the steps without the Security Wizard and then cover it in detail in the section “Step 9: Running the Security Wizard.” Throughout the chapter, I designate which steps the Security Wizard performs.
The first step to establishing user-level security involves setting up a workgroup. Then you can define groups and users who belong to that workgroup and assign rights to those groups and users. Groups and users are defined only in the context of a specific workgroup. Think of a workgroup as a group of users in a multiuser environment who share data and applications.
When you establish a new workgroup, Access creates a workgroup information file. The workgroup information file contains tables that keep track of
The name of each user and group
The list of users who make up each group
The encrypted logon password for each user who is defined as part of the workgroup
Each user’s and group’s unique security identifiers (SIDs)
A SID is a machine-generated binary string that uniquely identifies each user or group. The system database contains the names and SIDs of the groups and users who are members of that particular workgroup and, therefore, share a system database.
All application databases can share the same workgroup file, or you can maintain separate workgroup files for different application databases.
The default name for the workgroup information file is System.mdw. Each application database is associated with a specific workgroup information file. This combination of the information stored in the workgroup information file and the information stored in the database grants or denies individual users access to the database or to the objects in it. Multiple databases can share the same workgroup information file.
You can create many workgroup information files. The name of the workgroup information file currently being used is stored in the Windows registry. You can view it under HKEY_CURRENT_USER in the key called HKEY_ SoftwareMicrosoftOffice10.0AccessJet4.0Engines. (See Figure 27.4.)
One way to establish a new workgroup is to use the Workgroup Administrator. Prior to Access 2002, the Workgroup Administrator was a separate program that you executed outside Microsoft Access. With Access 2002, the Workgroup Administrator is finally integrated into the product. To launch the Workgroup Administrator, select Tools|Security|Workgroup Administrator.
The Workgroup Administrator dialog box is shown in Figure 27.5.
From the Workgroup Administrator dialog box, you can create a new workgroup or you can join one of the existing workgroups. If you click Create, you see the Workgroup Owner Information dialog box shown in Figure 27.6.
In the Workgroup Owner Information dialog box, you can enter a name, an organization, and a case-sensitive workgroup ID that will uniquely identify the workgroup to the system. If you do not establish a unique workgroup ID, your database is not secure. As you will see, anyone can find out your name and organization. If you do not establish a workgroup ID, anyone can create a new system information file with your name and company, rendering any security that you implement totally futile.
It is important that you record and store all workgroup information in a very safe place so that you can re-create it in an emergency. After entering the workgroup owner information, click OK. The Workgroup Information File dialog box appears, prompting you for the name and location of the workgroup information file, as shown in Figure 27.7.
After you type the name of a new workgroup file and click OK, you are asked to confirm the information, as shown in Figure 27.8. You are given one final opportunity to change any information. Click OK to confirm the information. Next, you are notified that the workgroup has been created successfully. You then can click Exit to close the Workgroup Administrator.
You can use the Security Wizard, covered later in this chapter, to create a new workgroup information file. The Security Wizard prompts you for the information necessary to create the workgroup information file. It doesn’t matter whether you opt to create a workgroup information file using the Workgroup Administrator, or with the Security Wizard, the results will be the same.
If different groups of users in your organization work with entirely different applications, you might find it appropriate to create multiple workgroup information files. In order to access a database that has been secured properly with a specific workgroup information file, the database must be accessed while the user is a member of that workgroup. If the same user requires access to more than one database, each associated with a different workgroup information file, it might be necessary for the user to join a different workgroup. This can be accomplished by using the Workgroup Administrator or by using a desktop shortcut that associates a specific database with a workgroup file. Desktop shortcuts are covered in Chapter 32, “Distributing Your Application.” To join a different workgroup using the Workgroup Administrator, follow these steps:
Launch the Workgroup Administrator.
Click the Join button. The Workgroup Information File dialog box appears.
Locate the name of the workgroup file you want to join. You can click the Browse button to help you locate the workgroup file.
Click OK. You are notified that you successfully joined the workgroup, as Figure 27.9 shows.
Click OK to close the Workgroup Administrator.
After creating a new workgroup, you are ready to change the logon for the workgroup by adding a password for the Admin user. This is necessary so that Access will prompt you with a Logon dialog box when you launch the product. If Admin has no password, the Logon dialog box never appears, and you will never be able to log on as yourself.
To change the password for the Admin user, launch Access and select Tools|Security|User and Group Accounts. The User and Group Accounts dialog appears. It does not matter what database you are in when you do this. In fact, you do not need to have any database open because the password that you are creating applies to the workgroup information file rather than to a database.
The User and Group Accounts dialog box enables you to create and delete users and assign their group memberships. It also enables you to create and delete groups and invoke a logon password for Microsoft Access.
Caution
It is important to understand that, even if you access this dialog box from a specific database, you are setting up users and groups for the entire workgroup. This means that, if you assign a password while you are a member of the standard SYSTEM.MDW workgroup, and others on your network share the same system workgroup file, everyone on your network is prompted with a logon dialog box when they attempt to launch Microsoft Access. If you do not want this to occur, you must create a new system workgroup file before establishing security.
When you are sure that you are a member of the correct workgroup and are viewing the User and Group Accounts dialog box, you are ready to assign a password to the Admin user. Click the Change Logon Password tab of the User and Group Accounts dialog box to select it, as shown in Figure 27.10.
Assign a new password and verify it. (There is no old password unless you think of the old password as blank.) Then click Apply to establish a password for the Admin user. You are now ready to create a new user who will administrate the database.
If you choose to use the Security Wizard to secure your database, it changes the password for the Admin user. This ensures that the Logon dialog appears when the workgroup file created by the Security Wizard is used.
After you assign a password to Admin, you are ready to create a new administrative user. You accomplish this from within the User and Group Accounts dialog. Access comes with two predefined groups: the Admins group and the Users group. The Admins group is the System Administrator’s group account. This group automatically contains a member called Admin. Members of the Admins group have the irrevocable power to modify user and group memberships and clear user passwords, so anyone who is a member of the Admins group is all powerful within your system. The Admins group must contain at least one member at all times.
It is extremely important to create a unique workgroup ID from the Workgroup Administrator. Otherwise, members of other workgroups can create their own workgroup files and grant themselves permissions to your database’s objects. Furthermore, it is important to ensure that the Admin user does not own any objects and is not given any explicit permissions. Because the Admin user is the same across all workgroups, all objects that Admin owns or has permissions to are available to anyone using another copy of Microsoft Access or Visual Basic.
The system also comes with a predefined Users group. This is the default group composed of all user accounts. All users automatically are added to the Users group and cannot be removed from this group. The Users group automatically is given all permissions to all objects. As with the Admin user, the Users group is the same across all workgroups. It therefore is extremely important that you take steps to remove all rights from the Users group, thereby ensuring that the objects in the database are secured properly. Fortunately, the Security Wizard, covered later in this chapter, accomplishes the task of removing all rights from the Users group. Because rights cannot be removed from the Admins group and the Admin user is the same across all workgroups, another user must be created. This new user will be responsible for administrating the database.
To create a new user to administrate the database, click the Users tab of the User and Group Accounts dialog box. If you closed the dialog box after the last step, choose Tools|Security|User and Group Accounts. Just as when you assigned a password for the Admin user, it does not matter which database you are in when you do this; it is only important that you are a member of the proper workgroup. Remember that you are defining a user for the workgroup—not for the database. The Users tab of the User and Group Accounts dialog box is shown in Figure 27.11.
To establish a new administrative user, click New. The New User/Group dialog box appears, as shown in Figure 27.12.
The New User/Group dialog box enables you to enter the username and a unique personal ID. This personal ID is not a password. The username and personal ID combine to become the encrypted security identifier (SID) that uniquely identifies the user to the system. Users create their own passwords when they log on to the system.
The Security Wizard allows you to create one or more administrative users for your database. In fact, the Security Wizard automatically creates a user called Administrator. This user becomes the owner of the database.
The next step is to make the new user a member of the Admins group. To do this, select the Admins group from the Available Groups list box, and then click Add with the new user selected in the Name drop-down list box. The new user should appear as a member of the Admins group, as shown in Figure 27.13.
If you use the Security Wizard, the user called Administrator is automatically added to the Admins group. Of course, you can also add other users to the Admins group.
You now are ready to close the User and Group Accounts dialog box and exit Access. Click OK. Exit Access and attempt to run it again. After attempting to open any database (or if you created a new database), you are prompted with the Access Logon dialog box shown in Figure 27.14.
Log on as the new system administrator. You do not have a password at this point; only the Admin user has a password. It still does not matter which database is open.
Before you continue, you should remove the Admin user from the Admins group. Remember that the Admin user is the same in every workgroup. Because the Admins group has all rights to all objects in the database (including the right to assign permissions to and remove permissions from other users and objects), if you do not remove Admin from the Admins group, your database will not be secure. To remove the Admin user from the Admins group, follow these steps:
Select Tools|Security|User and Group Accounts.
Make sure the Users tab is selected.
Select the Admin user from the Name drop-down list box.
Select Admins from the Member Of list box.
Click Remove. The User and Group Accounts dialog box appears as shown in Figure 27.15.
If you use the Security Wizard to secure your database, the Admin user is automatically removed from the Admins group. In fact, the Security Wizard does not make the Admin user a member of any group besides Users (the group to which all users must be members).
Now that you are logged on as the new Administrator, you should modify your password. If you have closed the User and Group Accounts dialog, choose Tools|Security|User and Group Accounts. Click the Change Logon Password tab. Remember that you can assign a password only for the user whom you are logged on as.
One of the really cool aspects of the Security Wizard is that it allows you to assign passwords for all users who are members of the workgroup. This saves you a lot of time and effort when establishing a large number of users.
After all this work, you finally are ready to actually secure the database. Up to this point, it did not matter which database you had open. Everything you have done so far has applied to the workgroup rather than to a particular database. Open the database you want to secure. At the moment, the Admin user owns the database, and members of the Users group have rights to all objects in the database.
Unless you are creating a brand new database after you perform all the preceding steps, the first thing you should do to secure an existing database is to use the Security Wizard. The Security Wizard allows you to perform the following tasks:
Join an existing workgroup or create a new workgroup information file
Designate the database objects you want to secure
Assign a password for the Visual Basic project
Select from predefined groups that the wizard creates
Assign desired rights to the Users group
Create users
Assign users to groups
Create a backup, unsecured copy of your database
To run the Security Wizard, choose Tools|Security|User Level Security Wizard. The first step of the Security Wizard dialog box appears, as shown in Figure 27.16.
Note
You cannot run the Security Wizard if you have set a Visual Basic Environment (VBE) password for the project. You must unlock the VBE project before running the wizard. VBE passwords are covered in the later section “Securing VBA Code with a Password.”
The first step of the Security Wizard prompts you to select an existing workgroup information file or create a new workgroup information file. Click Next to proceed to the second step of the Security Wizard (pictured in Figure 27.17). The second step prompts you to provide required information about the workgroup information file that you are creating. You are asked to enter a File name, a WID (Workgroup Identifier), your name, and your company name. You can designate the new workgroup file as the default workgroup file on your computer, or you can have Access create a shortcut to the secured database, including the name and path to the workgroup file.
The third step of the Security Wizard, shown in Figure 27.18, allows you to select the objects you want to secure. Notice that you can secure all objects, or you can opt to secure specific tables, queries, forms, reports, or macros. Modules, including the code behind forms and reports, are secured separately.
The fourth step of the Security Wizard, pictured in Figure 27.19, allows you to easily create group accounts. If your security needs match those predefined by one of the default groups, you can save yourself a significant amount of time by allowing the Security Wizard to create the necessary groups for you. An example of a predefined group is read-only users who can read all data but cannot modify data or the design of database objects. Another predefined group is for project designers who can edit all data and the design of application objects but cannot modify the structure of tables or relationships.
In the fifth step of the Security Wizard, shown in Figure 27.20, you designate what permissions, if any, you want to grant to the Users group. It is important to remember that all users are members of the Users group. Therefore, any permissions that you grant to the Users group are granted to all the users of your application. As a general rule, I recommend not granting any rights to the Users group. It is better to assign rights to other groups and then make specific users members of those groups.
The sixth step of the Security Wizard allows you to define the users who will use your database. In this step of the wizard, you supply each user’s name, a password, and a unique Personal ID, or PID, and then click the Add This User to the List button. (See Figure 27.21.) To delete a user, click that user and then click the Delete User from List button. Click Next when you are finished defining all users.
In the next step of the Security Wizard, you assign the users created in step six to the groups designated in step four. To assign a user to a group, click Select a User and Assign the User to Groups. Next select a user from the Group or User Name drop-down. Then click to add the selected user to any of the predefined groups. (See Figure 27.22.)
The final step of the Security Wizard prompts you to enter the name of the backup copy of the unsecured database. After you click Finish, the existing database is secured, and the original unsecured database is given the name designated for the backup.
The owner of a database cannot be changed and always has rights to everything in the database. Because Admin is the owner of the database and is the same in all workgroups, Access must copy all the database objects to a new, secure database owned by the new user. The wizard is intelligent enough to create a new secure database with the original database name and create a backup with the name that you designate. Access in no way modifies the existing, unsecured database. When the process is completed, the security report shown in Figure 27.23 appears.
Upon completion of its steps, the Security Wizard provides you with a report containing detailed information about the workgroup it created, the objects it secured, and the groups and users it created. The new copy of the database is owned by the new system administrator. All rights have been revoked from the Users group.
When you close the report, you are prompted to save it as a snapshot so that you can view it again later. Because the report contains valuable information about the workgroup and the secured database, I strongly suggest that you save the report in a very safe place. Armed with the information contained in the report, a savvy user could violate the security of your database.
Any time after you establish and join a workgroup, you can establish the users and groups who will be members of the workgroup. Users represent individual people who will access your database files. Users are members of groups, which are categories of users who share the same rights. Rights can be assigned at the user level or at the group level. Administratively, it is easier to assign all rights at the group level. However, this involves categorizing access rights into logical groups and then assigning users to those groups.
If groups have been set up properly, the administration of the system is greatly facilitated. If rights of a category of users need to be changed, they can be changed at a group level. If a user is promoted and needs additional rights, you can make that user a member of a new group. This is much easier than trying to maintain separate rights for each user.
You can add, modify, and remove users and groups by using front-end interface tools, as well as through VBA code. This chapter covers how to maintain users and groups using the front-end interface tools. Chapter 28, “Advanced Security Techniques,” covers how to maintain users and groups by using code.
Regardless of how you choose to define groups and users, you generally should create groups and then assign users to the appropriate groups. It is important to evaluate the structure of the organization as well as your application before you begin the mechanical process of adding the groups and users.
To add a new group, follow these steps:
Make sure that you are a member of the correct workgroup. With or without any database open, select Tools|Security|User and Group Accounts.
Click the Groups tab of the User and Group Accounts dialog box.
Click New. The New User/Group dialog box appears.
Type the name of the group and enter a PID that uniquely identifies the group.
Click OK.
Repeat steps 3 through 5 for each group you want to add.
Caution
The personal identification (PID) is a case-sensitive, alphanumeric string that can be from four to 20 characters in length. In combination with the user or group name, the PID uniquely identifies the user or group in a workgroup. Personal identification numbers should be stored in a very safe place. In the hands of the wrong person, access to the PID can lead to a breach of security. On the other hand, if the database is damaged and an important PID is not available, the data and objects in the database will not be accessible, even to the most legitimate users.
To add users through the user interface, follow these steps:
Choose Tools|Security|User and Group Accounts.
Click the Users tab if it is not already selected.
Click New. The New User/Group dialog box appears.
Enter the name of the user and the PID associated with the user. Remember that this is not a password; instead, it combines with the username to create a unique identifier for the user.
Click OK.
Repeat steps 3 through 5 for each user you want to define.
Before you proceed with the final step, assigning rights to users and groups, you should make each user a member of the appropriate group. A user can be a member of as many groups as you choose, but remember that each user gets the rights of his or her most forgiving group. In other words, if a user is a member of both the Admins group and a group with read-only access to objects, the rights of the Admins group prevail. To assign each user to the appropriate groups, follow these steps:
Choose Tools|Security|User and Group Accounts.
Click the Users tab if it is not already selected.
From the Name drop-down list box, select the user for whom you want to create group membership(s).
Double-click the name of the group to which you want to add the user, or single-click the group and click the Add button.
Repeat steps 3 and 4 for each user to whom you want to assign a group membership.
Figure 27.24 shows a user named Dan, who has been added to the Full Permissions group.
So far, you have created groups and users, but you haven’t given any of your groups or users rights to objects in the database. The key is to assign specific rights to each group, and then to make sure that all users are members of the appropriate groups. After that, you can assign each group specific permissions to the objects in your database. User and group information is maintained in the system database; permissions for objects are stored in system tables in the application database (MDB) file. After you establish a workgroup of users and groups, you must assign rights to specific objects in your database by following these steps:
Make sure the database containing the objects you want to secure is open.
Choose Tools|Security|User and Group Permissions. The dialog box shown in Figure 27.25 appears. Notice that as you click on each user in the User/Group Name box, as indicated by the check boxes in the Permissions section of the dialog box, you see that only the Administrator has rights to any objects. The Security Wizard automatically removed all permissions from the user named Admin. If you select the Groups option button, you see that only the Admins group has any rights. (If you have previously run the Security Wizard and added other users and groups they will have rights as well.)
To assign rights to a group, select the Groups option button. All the available groups appear in the User/Group Name box.
From the Object Type drop-down list, select the type of object you want to secure.
From the Object Name list box, select the names of the objects to which you want to assign rights. You can select multiple objects by pressing the Ctrl and Shift keys.
Enable the appropriate Permissions check boxes to select permissions for the objects. The types of available permissions are discussed in the text that follows.
Repeat steps 4 through 6 for all objects to which you want to assign rights.
Note
It is recommended that you assign groups the rights to objects and then simply make users members of the appropriate groups. Notice that you can use the Object Type drop-down list to view the various types of objects that make up your database.
In order to assign permissions appropriately, it is important that you understand the types of permissions available and what each type of permission allows a user to do. Table 27.1 lists the types of permissions available.
Table 27.1. Assigning Permissions
Permission | Allows User To |
|---|---|
Open/Run | Open a database, form, or report, or run a macro. |
Open Exclusive | Open a database with exclusive access. |
Read Design | View tables, queries, forms, reports, macros, and modules in Design view. |
Modify Design | View and change the design of tables, queries, forms, reports, macros, and modules. |
Administer | Set the database password, replicate the database, and change startup properties (when the user has administer permission of a database). Have full access to the object and its data (when the user has administer permission of a database object—such as a table, query, form, report, macro, or module). Assign permissions for that object to other users (when the user has administer permissions for an object). |
Read Data | View the data in a table or query. |
Update Data | View and modify table or query data. Cannot insert and delete records, however. |
Insert Data | Add records to a table or query. |
Delete Data | Delete records in a table or query. |
Some of these permissions implicitly include associated permissions. A user cannot update data in a table if he or she does not have the rights to read the data and the design of the table in which that data is located, for example.
In earlier versions of Access, securing a form or report meant that the code behind it was also secure. With Access 2000 and Access 2002, that is not the case. User-level security does not secure your code. It secures only the forms and reports that refer to the code. This means that denying a user modify design rights to a form prevents them from adding, deleting, or modifying controls on the form, but does not prevent them from modifying the code behind the form.
To secure the code in your application’s form, report, standard, and class modules, you must first activate the VBE. Then select Tools|Properties. Click to activate the Protection tab. If you do not want unauthorized users to view the code behind your forms, reports, modules, and class modules, click to select Lock Project for Viewing. Then enter and confirm a password. After you lock the project for viewing, you are prompted for a password once each session. Developers must supply a valid password to view or modify VBA code for the project.
Access 2000 and Access 2002 offer an additional level of security through the creation of an MDE file. An MDE (compiled database) file is a database file with all editable source code removed. This means that all the source code behind the forms, reports, and modules contained in the database is eliminated. An MDE file offers additional security because the forms, reports, and modules in an MDE file cannot be modified. Other benefits of an MDE file include a reduced size and optimized memory usage. To create an MDE file, follow these steps:
Open the database on which the MDE file will be based.
Choose Tools|Database Utilities|Make MDE File. The Save MDE As dialog box appears.
Select a name for the MDE and click OK.
Before you dive into MDEs, it is important that you are aware of the restrictions they impose. If you plan ahead, these restrictions probably will not cause you too many problems. On the other hand, if you enter the world of MDEs unaware, they can cause you much grief. You should consider these restrictions:
The design of the forms, reports, and modules in an MDE file cannot be viewed or modified. In fact, new forms, reports, and modules cannot be added to an MDE. It therefore is important to keep the original database when you create an MDE file. This is where you will make changes to existing forms, reports, and modules and add new forms, reports, and modules. When you are finished, you simply rebuild the MDE.
Because you must rebuild the MDE every time changes are made to the application, the front-end/back-end approach is best when dealing with MDE files. This means that the tables are contained in a standard Access database, and the other objects are stored in the MDE file. You therefore can rebuild the MDE without worrying about the reconciliation of data.
You cannot import or export forms, reports, or modules to or from an MDE.
You cannot change code by using properties or methods of the Access or VBA object models because MDEs contain no code.
You cannot change the database’s VBA project name.
You cannot convert an MDE to future versions of Access. It is necessary to convert the original database and then rebuild the MDE file with the new version.
You cannot add or remove references to object libraries and databases from an MDE file. Also, you cannot change references to object libraries and databases.
Every library database that an MDE references also must be an MDE. This means that if Database1 references Database2, which references Database3, all three databases must be stored as MDEs. You first must save Database3 as an MDE, reference it from Database2, and then save Database2 as an MDE. You then can reference Database2 from Database1, and finally save Database1 as an MDE.
A replicated database cannot be saved as an MDE. The replication first must be removed from the database. This is accomplished by removing the replication system tables and properties from the database. The database then can be saved as an MDE and the MDE can be replicated and distributed as a replica set. Any time changes must be made to the database, they must be made to the original database, resaved as an MDE file, and then redistributed as a new replica set.
Any security that applies to a database will follow through to an MDE file created from it. To create an MDE from a database that already is secured, you first must join the workgroup information file associated with the database. You must have Open/Run and Open Exclusive permissions to the database. You also must have Modify Design and Administer permissions to all tables in the database, or you must own all tables in the database. Finally, you must have Read Design permissions on all objects contained in the database.
If error handling is not added to code within an MDE, and an error occurs, no error message appears.
If you want to remove security from the database, you must remove the security from the original database and rebuild the MDE.
As long as you are aware of the restrictions associated with MDEs, they can offer many benefits. In addition to the natural security they provide, the size and performance benefits MDEs offer are significant.
In a simple security model, it might be appropriate for all users to have the same rights to all objects in the database. In that scenario it doesn’t make sense to require users to log on. To eliminate the log on dialog while ensuring that specific objects are secure:
Open the database you wish to secure.
Run the Security Wizard. If you wish an object to be available to users, grant the appropriate permissions to the Admin account. Clear the permissions for any objects you don’t want users to have access to.
Make sure that Admin’s password is cleared. This will keep the Login dialog from appearing.
Once Admin’s password is cleared, users are not prompted to log on, and they are automatically logged on as the Admin user. They therefore are granted any rights given to the Admin user and are denied any rights removed from the Admin user.
Although the discussion of security so far has been quite thorough, a couple of issues surrounding the basics of security have not yet been covered. They include additional issues with passwords, understanding how security works with linked tables, understanding and working with object ownership, and printing security information. These topics are covered in this section.
When you create a user, no password is assigned to the user. Passwords can be assigned to a user only when that user has logged on to the system. The System Administrator cannot add or modify a user’s password (the exception to this is when using the Security Wizard). It is important to encourage users to assign themselves a password the first time they log on to the system. Using VBA code, the users can be forced to assign themselves a password. This is covered in Chapter 28. Alternatively, the administrator of the database can log on as each user, assigning each a password.
Although you cannot assign a password to a user or modify the user’s password, you can remove a user’s password. This is necessary when a user forgets his or her password. To clear a user’s password, follow these steps:
When you design your application with two databases (one for tables and the other for the remainder of the application objects), it is necessary for you to secure both databases. Securing only the linked tables is not sufficient!
A potential problem still exists. If a user has access to add, delete, and modify data from your application, that user can open the database containing the data tables from outside your application and modify the data without going through the forms and reports you designed. One solution to this problem is to revoke all rights from the tables. Base all forms and reports on queries that have the Run Permissions property set to Owner’s. This provides users with the least opportunity to modify the data from outside your system. This technique is covered in more detail in Chapter 28.
Remember that the user who creates the database is the database’s owner. This user retains irrevocable rights to the database. You cannot change the owner of a database; you can change only the ownership of objects in the database. There is a workaround that allows you, in effect, to change the owner of the database. If you have rights to the database’s objects, you can create a new database and import all the objects from the other database. You can accomplish this by using the Security Wizard.
By default, the creator of each object in the database is its owner. To change the ownership of an object in the database, follow these steps:
With the appropriate database open, choose Tools|Security|User and Group Permissions.
Click the Change Owner tab to select it, as shown in Figure 27.26.
From the Object Type list box, select the objects whose ownership you want to change. You can press Ctrl and Shift to select multiple objects.
Select the Groups or Users option button.
Select the name of the group or user who will become the new owner of the objects.
Click Change Owner.
Repeat steps 3 through 7 for all objects that you want to assign to new owners.
You can print a list of each user and the groups he or she is a member of by following these steps:
With the appropriate database open, choose Tools|Security|User and Group Accounts.
Click Print Users and Groups. The Print Security dialog box appears, as shown in Figure 27.27.
Select the Both Users and Groups, Only Users, or Only Groups option button.
Click OK.
Note
You can print the rights to different objects by using the Database Documenter. This is covered in Chapter 29, “Documenting Your Application.”
Securing the Time and Billing Application
Now that you have learned the steps involved in properly securing an Access database, you can apply the steps to the Time and Billing application:
Launch the Workgroup Administrator. Click the Create button to create a new workgroup.
Enter your name and organization. Enter
TimeBillAppas the Workgroup ID, as shown in Figure 27.28. Click OK.Call the workgroup database
Time.MDW. Click OK. Confirm the path to the new workgroup, as shown in Figure 27.29.Choose Tools|Security|User and Group Accounts.
Click New and add a user named
PCGuru. Give PCGuru a personal ID ofHeadCheese, as shown in Figure 27.30. Click OK.Click Add to add PCGuru to the Admins group, as shown in Figure 27.31.
Click the Change Logon Password tab to select it.
Assign a new password of NoPower to the Admin user and click Apply.
Click the Users tab.
From the Name drop-down list, select Admin.
Remove Admin from the Admins group, as shown in Figure 27.32.
Exit and restart Access. Open the Chap27 database and log on as PCGuru (with no password).
Choose Tools|Security|User and Group Accounts.
Click the Change Logon Password tab to select it.
Assign PCGuru the password of
TheGuruand click OK.Choose Tools|Security|User-Level Security Wizard.
Select Modify My Current Workgroup Information File. Click Next.
Click Next, accepting that you want to secure all objects.
Click to add the groups Read-Only Users and Update Data Users. Click Next.
Click Next to accept that the Users group should not have any permissions.
Add the following users: Dan, Alexis, Brendan, Gaby, Hugo, and Sonia. Assign any personal IDs and passwords that you want. Click Next.
Add Dan, Alexis, and Gaby to the Update Data group. Add Brendan and Hugo to the Read-Only Users group. Add Sonia to the Admins group. Click Next.
Enter a name for the backup copy of your database and click Finish.
Exit Access and log back on as each of the various users. Attempt to read, modify, add, and delete records from the tblClients and tblProjects tables. Ensure that security has been implemented as planned.
Don’t forget that if the tables are linked, you need to go into the linked database to secure those tables.
The security system in Access 2002 is quite robust but also somewhat complex. Using Access security, you can fully secure a database and all its objects. As a developer, you might want to prevent people from modifying the objects in your database. Furthermore, you might want to restrict certain users from viewing certain data, using specific forms, or running certain reports.
This chapter walked you through all the steps required to properly secure a database. It began by showing you how to set up a database password and how to encrypt a database. It then covered all the details of implementing user-level security.
Invoking user-level security first involves using the Workgroup Administrator to set up a workgroup. You then must create an administrative user and make that user a member of the Admins group. Next, you change the password for the Admin user and remove the Admin user from the Admins group. You then exit Access, log on as the System Administrator, and assign yourself a password. All these steps were covered in detail in this chapter. In addition, this chapter walked you through using the Security Wizard to perform many necessary tasks, such as changing the owner of the database from Admin to the new Administrator and revoking all permissions from the Users group. This ensures that the database is truly secure. The final step is to assign permissions for groups and/or users to the objects that reside in your newly secured database. The chapter also covered this very powerful process.