Programming Microsoft Access 2000 (Microsoft Programming Series)

Access offers a rich array of security features to support the needs of different types of Access applications. Most multi-user Access applications can benefit from user-level security, which lets developers designate groups of users. But some applications have more specialized needs. The following section offers a brief overview of security techniques other than user-level security. Subsequent sections cover programmatic approaches to managing user-level security using ADO.

Alternatives to User-Level Security

One of the strengths of Access is its ability to serve different audiences. Some applications are code-intensive, so you need to secure your investment in source code. Other applications serve small workgroups with limited technical capabilities but still require minimum levels of security to restrict access to data. Still other applications benefit from a custom user interface that restricts functionality simply by exposing a restricted set of commands.

Using a custom interface

Sometimes you can adequately secure an application by simply replacing the standard Access interface with a custom one. Choose Startup from the Tools menu to open a dialog box that lets you specify a custom application title and icon, a custom startup form, and custom menus to replace the standard Access ones. This dialog box also lets you suppress the Database window and the status bar. You can also manipulate the features of the Startup dialog box programmatically. If this type of manipulation is suitable for your security needs, consider augmenting it with the Show and Hide methods, the Visible property, and the Hidden property for objects in the Database window. (See earlier chapters for techniques on creating custom features: Chapter 8 describes how to program the CommandBars object model to build your own custom menus as well as adapt built-in ones, and Chapter 5 shows how to build a custom startup form.)

Setting a database password

You can require users to enter a password to gain unrestricted access to all Access data and database objects. Passwords are easy to administer compared to user-level security. Password security is appropriate if you have a group whose members need equal access to all elements of a database file but not everyone in the office is a member of that group.

You cannot use a password-protected file as a member in a replica set because Jet database replication cannot synchronize with a password-protected file. (See Chapter 11 for details.) You should also be careful about linking to database files with password protection because anyone who can access the file that links the protected file has unrestricted access to the protected file. Furthermore, Access stores an encrypted version of the password along with other information about the linked file. Finally, if someone changes the password for a linked file, Access prompts for the new password the next time another database file links to it.

To assign and remove a database password, you need exclusive access to the file. Take the following steps:

1. Open a file by choosing Open Exclusive from the Open button in the Open dialog box to assign a password to a file.
2. Choose Security-Set Database Password from the Tools menu.
3. In the Set Database Password dialog box, enter your password of choice in the Password and Verify text boxes and then click OK. The next time a user opens the file, the application will ask for the password.
4. After opening a database exclusively, choose Tools-Security-Unset Database Password. Remove the password by typing the password in the Unset Database Password dialog box. This removes the initial prompt for a password before a database is made available.

Setting a module password

Access 2000 uses password security for modules instead of user-level security. This new approach makes Access consistent with the other Office 2000 components. It applies to all standard and standalone class modules as well as the modules behind forms and reports.

You set password security once for all the modules in a VBA project from the Visual Basic Editor (VBE). Choose the Properties command for the project from the Tools menu to open the Project Properties dialog box. The Protection tab (shown in Figure 10-1) offers the Lock Project For Viewing check box and text boxes for entering and confirming a password for the module. Assigning a password for viewing modules in a project does not prevent your code from running as if it were not protected. If you assign a password but do not select the Lock Project For Viewing check box, anyone can edit the code but the Project Properties dialog box will be protected. You remove password security from the modules in a project by clearing all entries on the Protection tab.

Figure 10-1. You use the Protection tab of the Project Properties dialog box to set password security for the modules in a project.

After securing your modules with a password, you must enter the password once per session before you can view, edit, or add new code. You can secure forms and reports with both user-level security and module password security. User-level security applies to designing and using forms and reports. You can require a user to have Modify Design permission to add controls to forms. That user will also need the password for modules in a project in order to write event procedures for the control. Conversely, knowing the password for the modules in a project does not enable a user to add controls to or remove controls from a form. Also, Modify Design permission does not allow a user to change the HasModule property of forms and reports to No; the user must first enter the password for the modules in a project.

Using .mde files

An .mde file totally secures the code for an Access database file. When you convert an .mdb file to an .mde file, Access compiles all your modules, removes editable code, and compacts the destination database while preserving the original .mdb file. The size of your database will shrink because of the removal of editable code. Also, since the conversion optimizes memory usage, your code will run faster.

To convert an .mdb file to an .mde file, you must have exclusive access to the file (see the earlier section titled "Setting a Database Password"). Choose Database Utilities from the Tools menu and then choose Make MDE File. After saving your converted file, be sure to save your original file. The only way to edit or add to the code in a database file is to modify the original file and then convert it to an .mde file.

An .mde file has some restrictions:

• You cannot modify or add forms, reports, or modules.
• You cannot import or export forms, reports, or modules to a standard .mdb file. You can, however, freely import and export tables, queries, macros, and shortcuts for data access pages with other database files.
• You cannot add, delete, or change references to other object libraries or databases.
• You cannot dynamically change code because .mde files contain no editable code. (See Chapter 7 for a sample illustrating this restriction.)
• You cannot convert any existing member of a replica set to an .mde file, but an .mde file can participate in a replica set.
• An .mde file can reference another database file only if that file is also an .mde file. You must start converting .mdb files (or .mda add-in files) that are referenced before you convert the .mdb file that references them. The new reference must point at the new .mde file.

Programmatically Controlling User-Level Security

With user-level security, you can define a workgroup composed of user accounts and group accounts. You can programmatically create user and group accounts as well as assign permissions to those accounts. The new ADOX model supports this functionality through its Catalog object as well as its Users and Groups collection objects. Figure 10-2 shows the hierarchy: groups can belong to users, and users can belong to groups; users and groups both belong to the Catalog object.

Figure 10-2. The relationship of the User and Group objects to the Catalog object.

You can assign permissions to users, groups, or both. It is generally most efficient to assign permissions to groups. You clear all default permissions from existing individual user accounts and then assign users to all appropriate groups. With this type of user-level design, you can administer permissions by assigning users to groups and assigning permissions to groups because users inherit all permissions from the groups to which they belong. By restricting permission assignments to groups, this design provides a single focal point for managing permissions.

The upcoming samples show how to code typical user-level administration tasks. Because they are meant to highlight basic security management procedures, only a couple of the samples demonstrate error-trapping logic.

Connecting to a secure database

The following procedure connects to a secure database file. The connection string includes four phrases. The first phrase designates the Provider property for the procedure's Connection object. It specifies the Jet 4 OLE DB provider. The second phrase assigns the system database property for the connection. This is the name and path to the workgroup information file (systemdemo.mdw in the Office folder; you must manually copy the file to the Office folder). The third phrase specifies the data source, which in this case is the secure database file, UserLevel.mdb. The fourth phrase denotes user ID and password values for logging into the secure database. In this sample, the procedure logs on as the Admin user. Unless you change the default settings, this user has special administrative permissions.

The two lines following the comment open a recordset based on the connection and print the first field's value from the first record. This simply confirms the operation of the sample. The table, WebBasedList, is the same one used in the earlier multi-user sample.

For the OpenUserLevel procedure to work, you must invoke the logon procedure. This involves giving the Admin user a password. The procedure also requires a workgroup information file. In this case, its name must be systemdemo.mdw, and you must save it in the path indicated by the procedure. The companion CD includes both the secure database file and the workgroup information file for easy testing of the procedure.

Adding and deleting users

When you develop and manage a custom application with user-level security, you are likely to add and delete users. Before you can add users, you must log on as a member of the Admins group, such as Admin. You can use the Append method of the Users collection to add users to a catalog or group. You must specify a name for the new user, and you can designate a password. ADO lets you assign a password later using the ChangePassword method for the User object. Unfortunately, you cannot assign a PID. ADO picks one randomly.

The following two procedures show one approach to invoking the Append method to add a new user to an application. The callMakeUser procedure launches the makeUser procedure as it passes along two arguments. The first argument designates a new user's name. The second argument sends a password. In the sample, the string "password" is the value of the password argument.

The makeUser procedure specifies a target for the new group using the Catalog object's ActiveConnection setting. Note that it designates a user ID with the authority to make a new user, and it points to a workgroup information file. The Append method in makeUser adds a new member to the Catalog object. Therefore, this new user is not yet a member of any groups. You can also add a member to a Group object so that the user has immediate membership in that group. One of the following samples uses this technique.

The next two procedures remove a user from the catalog for a database. The Delete method for the Users collection has the same syntax as the Delete method for the Tables, Procedures, and Views collection objects. The first procedure, callDeleteUser, passes a single argument—the user name—to the second procedure, deleteUser. The second procedure removes the user from the catalog and concurrently removes the user from any groups as well.

You must log on to a database as a member of the Admins group to delete a user. The Delete method does not require a password. All that the second procedure needs is a string argument naming the user to delete.

Assigning groups to users

One common technique for administering permissions is to assign groups to users and manage permissions for groups. Users derive all their permissions implicitly through their group memberships. The samples in this section add and remove group memberships from a user account. Both samples use the built-in Users group, but the same techniques work for custom groups.

The following two procedures add a group to a user account called NewUser. Make sure the user account exists before running the procedure. The first procedure, callAddGroupToUser, passes a user name and a group name to the second procedure, AddGroupToUser, which uses the Append method to add the Group object to the Groups collection for the user. The sample passes arguments to the second procedure that tell it to add the Users group to the NewUser user.

The second procedure invokes the Append method in an attempt to create a group with the name of the second argument passed to it. This procedure works for groups whether or not they already exist. Since Users is a built-in group account, it will always exist. If a group with the name of the second argument does not already exist, the Append method succeeds; otherwise, the procedure falls into an error trap with error number _2147467259 and moves on to the next statement. Then, the procedure appends the group to the Groups collection for the NewUser object. Again, if the group is already in the Groups collection for the user, the procedure progresses to the next statement.

The next two procedures remove a group from a user's Groups collection. The first procedure, callRemoveUserFromGroup, passes user and group name parameters to the second procedure, removeUserFromGroup, which does the work. Since there is no error checking in this sample, make sure the group belongs to the user. You can do this by running the preceding sample.

You invoke the Delete method to remove a group from the Groups collection for a User object. Notice the hierarchical specification for an individual user. After identifying a user, the syntax requires the designation of the Groups collection and, finally, the Delete method. The syntax designates the group name as a parameter for the Delete method.

Creating, deleting, and tracking groups in a catalog

When you develop custom user-level solutions, you'll probably want to create custom groups with names that are meaningful to your clients and whose permissions fit the special requirements of your custom application. The four upcoming samples do the following: create a custom group, delete a custom group, prepare a report itemizing all the groups in a catalog and the groups associated with each user account, and toggle the membership of a group in the Users collection, respectively.

The two procedures below add a group named MySecretGroup1. After referencing a database file with a user ID sufficient to make the addition, the procedure invokes the Append method of the Groups collection. You must specify a container for the Groups collection. When you add a new group to the project's Users collection, the container is a Catalog object. When you assign a group to the Groups collection of a User object, you must specify the user as the root object for the Groups collection.

The two procedures below remove a group from a catalog. You must make sure that the group already exists in the catalog before running the procedures. You can do this by running the preceding sample. In fact, the sample below removes the group added in the preceding sample.

The syntax for deleting a group very closely parallels that for adding a group. It invokes the Delete method of the catalog's Groups collection. You pass the method one parameter—the name of the group to delete.

As you add and delete groups and users and reassign groups to users, you can easily create a custom report that tracks the group memberships for the Catalog and individual User objects. The procedure below itemizes the groups in a Catalog object that points at a specific database. Then it itemizes the Groups collection members for each user in the catalog's Users collection.

At the head of each listing of Groups collection members, the procedure reports the number of members in the collection by referencing the Counts property for the collection. Notice in Figure 10-2 that this varies by user. The Admin user belongs to the built-in Admins and Users groups. The NewUser user belongs to a single group, Users. You can use the preceding samples to create and delete users, groups, and user membership in groups.

Figure 10-3. A group membership report from the listGroupsInCat procedure.

The following procedure shows one possible application of the listGroups InCat procedure above. The toggleNewUserInAdminsGroup procedure does what its name implies. It toggles the membership of the NewUser object in the Admins group. It also documents the current status of the NewUser object in the Admins group by calling the listGroupsInCat procedure.

Notice that the toggling procedure relies on error trapping. After connecting to the target database and working group information file through the cat1 object reference, the procedure attempts to delete Admins from the Groups collection of NewUser. If it is successful, the procedure closes by calling listGroupsInCat and exiting. Otherwise, an error occurs. If the error occurs because the group is not in the user's Groups collection, the procedure adds Admins to the NewUser Groups collection. Then it closes by resuming as if no error occurred.

Setting permissions

You can use the SetPermissions method for Group and User objects to manage the permissions available to a security account. You invoke the GetPermissions method for these objects to return a Long value that specifies the types of permissions assigned to a group or to a user. Both methods offer a wide array of outcomes; they can assign and report various permissions for a number of database object types. In addition, you can use the SetPermission method to assign, revoke, and deny permissions as well as audit their use.

The two procedures below grant a group full permissions for any new table. Setting the permission for new tables has no impact for existing tables. Therefore, a group can have full permissions for all new tables and no permissions for existing tables.

The first procedure passes a group name, MySecretGroup1, to the second procedure. The second procedure invokes the SetPermissions method for the group member with that name. Therefore, you must make sure that the group exists before you run the procedure or add error-trapping logic. The method's first parameter has an explicit Null value. This parameter normally specifies the name of a database object, such as a table. A Null value indicates that you want to set permissions for any new database objects. The second parameter designates a Table object type. The third parameter serves as a verb; it indicates that the command will set a permission. Other constants indicate different actions, such as revoking permissions, that the method can launch. The fourth parameter grants the user full rights. The method and its parameters grant MySecretGroup1 full rights for all new tables in the UserLevel.mdb database file with the systemdemo.mdw workgroup information file.

This basic design is flexible and can serve in many different situations. For example, to revoke all rights for new tables, you change the third parameter for the SetPermissions method from adAccessSet to adAccessRevoke. To set rights for an existing database object, you replace the Null for the first parameter with the database object's name.

Putting it all together

The following two procedures tap a cross-section of prior samples and show a new twist to the SetPermissions method. The first procedure calls the makeGroup procedure to create a new group in the systemdemo.mdw workgroup information file. Then it invokes the second procedure and passes along the new group's name as well as the name of a database object for which it wants to assign permissions. The last two lines in the first procedure create a new user named NewUser2 and add MySecretGroup2 to its Groups collection. In this way, NewUser2 inherits the permissions assigned to MySecretGroup2 by the second procedure.

The second procedure assigns read, insert, and delete permissions for the WebBasedList table in UserLevel.mdb to MySecretGroup2. This procedure is similar to the earlier sample that applied for rights to a specific database object, but this one concatenates three separate rights to get a combined set of permissions. Notice that the syntax uses an Or operator for concatenating rights.