Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Establishing User-Level Security

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 all of these scenarios.

The major steps to implementing user-level security follow (each step is developed in detail later in the chapter):

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.

Step 1: Creating a Workgroup

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.

Understanding the Workgroup: The System.mdw File

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 \Software\Microsoft\Office\11.0\Access\Jet\4.0\Engines \(see Figure 27.4).

TIP

You can access the Windows registry using the RegEdit utility. Select the Run option from the Start menu, and then type

RegEdit .

Establishing a Workgroup

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 was 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, Access asks you to confirm the information, as shown in Figure 27.8. Access gives you one final opportunity to change any information. Click OK to confirm the information. Next, Access notifies you that it has successfully created the workgroup. You can then click OK 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 using the Security Wizard; the results will be the same.

Joining a Different Workgroup

If different groups of users in your organization work with entirely different applications, you might find it appropriate to create multiple workgroup information files. To access a database that you have secured properly with a specific workgroup information file, the user must access the database while he 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. You can accomplish this 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:

Step 2: Changing the Password for the Admin User

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 box 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, the wizard changes the password for the Admin user. This ensures that the Logon dialog box appears when the workgroup file created by the Security Wizard is used.

Step 3: Creating an Administrative User

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 box. 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 you cannot remove rights from the Admins group and the Admin user is the same across all workgroups, you must create another user. 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 workgroupnot 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 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. After you've entered the username and unique personal ID, you click OK to exit the New User/Group dialog box.

Step 4: Making the Administrative User a Member of the Admins Group

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 (in this case, Alison) 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.

Step 5: Exiting Access and Logging On as the System Administrator

You are now 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), Access prompts you 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.

Step 6: Removing the Admin User from the Admins Group

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:

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).

Step 7: Assigning a Password to the System Administrator

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 box, 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.

Step 8: Opening the Database You Want to Secure

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.

Step 9: Running the Security Wizard

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 backup 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.

The first step of the Security Wizard prompts you to select an existing workgroup information file or create a new workgroup information file. Make your selection and click Next to proceed to the second step of the Security Wizard. If you opt to create a new workgroup information file, the second step appears as 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), and optionally 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. Click Next when the information is complete.

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. You secure modules, including the code behind forms and reports, separately. Click Next when you are done.

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. Click Next when you are done.

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. Click Next when you are done.

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 the 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 6 to the groups designated in step 4. 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). Click Next when you are done.

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 security administrator owns the new copy of the database. The Security Wizard revokes all rights from the Users group.

When you close the report, Access prompts you 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.

Finally, if you opted to create a new workgroup in step 1 of the wizard, Access warns you that you must join the workgroup before using the newly secured database (see Figure 27.24).

Step 10: Creating Users and Groups

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. You can assign rights 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.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.

Adding Groups

To add a new group, follow these steps:

CAUTION

The 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.

Adding Users

To add users through the user interface, follow these steps:

Assigning Users to the Appropriate Groups

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:

Figure 27.25 shows a user named Dan, who has been added to the Full Permissions group.

NOTE

Remember that the users and groups you create are for the workgroup as a wholenot just for a specific database.

Step 11: Assigning Rights to Users and Groups

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. Access maintains user and group information in the system database; it stores permissions for objects 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:

NOTE

I recommend 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.

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

Some of these permissions implicitly include associated permissions. A user cannot update data in a table if she does not have the rights to read the data and the design of the table in which that data is located, for example.