Security

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.

'Turn logon procedure on before running this procedure. 'Assign password of "password" to Admin user account. Sub openUserLevel() Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset 'Open connection to target user-level secured data 'source; specify path for workgroup information 'file; designate logon ID and password.     cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"     cnn1.Properties("Jet OLEDB:System database") = _         "C:\Program Files\Microsoft Office\" & _         "Office\systemdemo.mdw"     cnn1.Open "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "User Id=Admin;Password=password;" 'Print first field from first record to confirm connection.     rst1.Open "WebBasedList", cnn1, , , adCmdTable     Debug.Print rst1.Fields(0)     cnn1.Close End Sub 

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.

'Make sure NewUser account does not exist prior to running 'this procedure; for example, run callDeleteUser first. Sub callMakeUser()     makeUser "NewUser", "password" End Sub Sub makeUser(usrName As String, secureWord As String) Dim cat1 As New ADOX.Catalog     cat1.ActiveConnection = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "Jet OLEDB:System database=C:\Program Files\" & _         "Microsoft Office\Office\systemdemo.mdw;" & _         "User Id=Admin;Password=password;"              cat1.Users.Append usrName, secureWord End Sub 

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.

'Make sure NewUser account exists prior to running this 'procedure; for example, run callMakeUser. Sub callDeleteUser()     deleteUser "NewUser" End Sub Sub deleteUser(usrName As String) Dim cat1 As New ADOX.Catalog     cat1.ActiveConnection = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "Jet OLEDB:System database=C:\Program Files\" & _         "Microsoft Office\Office\systemdemo.mdw;" & _         "User Id=Admin;Password=password;"              cat1.Users.Delete usrName End Sub 

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.

Sub callAddGroupToUser()     AddGroupToUser "NewUser", "Users" End Sub Sub AddGroupToUser(usrName As String, grpName As String) On Error GoTo AddTrap Dim cat1 As New ADOX.Catalog Const acctNameAlreadyExist = -2147467259     cat1.ActiveConnection = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "Jet OLEDB:System database=C:\Program Files\" & _         "Microsoft Office\Office\systemdemo.mdw;" & _         "User Id=Admin;Password=password;"          cat1.Groups.Append grpName     cat1.Users(usrName).Groups.Append grpName      AddExit:     Exit Sub      AddTrap:     If Err.Number = acctNameAlreadyExist Then         Resume Next     Else         Debug.Print Err.Number; Err.Description     End If End Sub 

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.

'Make sure the group account exist for the user 'prior to running this procedure; for example, 'run callAddGroupToUser. Sub callRemoveUserFromGroup()     removeUserFromGroup "NewUser", "Users" End Sub Sub removeUserFromGroup(usrName As String, grpName As String) Dim cat1 As New ADOX.Catalog     cat1.ActiveConnection = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "Jet OLEDB:System database=C:\Program Files\" & _         "Microsoft Office\Office\systemdemo.mdw;" & _         "User Id=Admin;Password=password;"          cat1.Users(usrName).Groups.Delete grpName End Sub 

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.

'Make sure MySecretGroup1 does not exist before running 'this procedure; for example, run callDeleteGroup. Sub callMakeGroup()     makeGroup "MySecretGroup1" End Sub Sub makeGroup(grpName As String) Dim cat1 As New ADOX.Catalog     cat1.ActiveConnection = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "Jet OLEDB:System database=C:\Program Files\" & _         "Microsoft Office\Office\systemdemo.mdw;" & _         "User Id=Admin;Password=password;"          cat1.Groups.Append grpName End Sub 

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.

'Make sure MySecretGroup1 exists prior to running this 'procedure; for example, run callMakeGroup. Sub callDeleteGroup()     deleteGroup "MySecretGroup1" End Sub Sub deleteGroup(grpName As String) Dim cat1 As New ADOX.Catalog     cat1.ActiveConnection = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _         "Jet OLEDB:System database=C:\Program Files\" & _         "Microsoft Office\Office\systemdemo.mdw;" & _         "User Id=Admin;Password=password;"          cat1.Groups.Delete grpName      End Sub 

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.

Sub listGroupsInCat() Dim cat1 As New ADOX.Catalog Dim grp1 As New ADOX.Group, usr1 As New ADOX.User cat1.ActiveConnection = _    "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _     "Jet OLEDB:System database=C:\Program Files\Microsoft Office\" & _     "Office\systemdemo.mdw;" & _     "User Id=Admin;Password=password;" 'Groups in overall Catalog     Debug.Print cat1.Groups.Count & " groups are in the catalog"     For Each grp1 In cat1.Groups         Debug.Print String(3, " ") & "* " & grp1.Name     Next grp1     Debug.Print 'Groups in each user     For Each usr1 In cat1.Users         If usr1.Name <> "Creator" And usr1.Name <> "Engine" Then             Debug.Print String(5, " ") & usr1.Groups.Count & _                 " groups are in  " & usr1.Name             For Each grp1 In cat1.Users(usr1.Name).Groups                 Debug.Print String(8, " ") & "* " & grp1.Name             Next grp1             Debug.Print         End If     Next usr1 End Sub 

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.

Sub toggleNewUserInAdminsGroup() On Error GoTo ToggleTrap Dim cat1 As New ADOX.Catalog Const notInCollection = 3265 cat1.ActiveConnection = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _     "Jet OLEDB:System database=C:\Program Files\Microsoft Office\" & _     "Office\systemdemo.mdw;" & _     "User Id=Admin;Password=password;" cat1.Users("NewUser").Groups.Delete ("Admins")     ToggleExit:     listGroupsInCat     Exit Sub      ToggleTrap:     If Err.Number = notInCollection Then         cat1.Users("NewUser").Groups.Append "Admins"     Else         Debug.Print Err.Number; Err.Description     End If     Resume Next End Sub 

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.

'Make sure MySecretGroup1 exists before running procedure. Sub callSetAllTablePermissionsForGroup()     setAllTablePermissionsForGroup "MySecretGroup1" End Sub Sub setAllTablePermissionsForGroup(grpName As String) Dim cat1 As New ADOX.Catalog Dim grp1 As New ADOX.Group, usr1 As New ADOX.User cat1.ActiveConnection = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _     "Jet OLEDB:System database=C:\Program Files\Microsoft Office\" & _     "Office\systemdemo.mdw;" & _     "User Id=Admin;Password=password;" cat1.Groups(grpName).SetPermissions Null, adPermObjTable, _     adAccessSet, adRightFull End Sub 

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.

Sub callSetRIDTablePermissionsForGroupTable() 'This procedure makes a group called MySecretGroup2 and 'assigns Read/Insert/Delete Permissions for 'WebBasedList table to MySecretGroup2. 'Then, it creates NewUser2 and assigns 'MySecretGroup2 to NewUser2. 'Before running this, delete MySecretGroup2 and 'NewUser2 from UserLevel.mdb if they exist. makeGroup "MySecretGroup2" setRIDTablePermissionsForGroupTable "MySecretGroup2", "WebBasedList" makeUser "NewUser2" AddGroupToUser "NewUser2", "MySecretGroup2" End Sub Sub setRIDTablePermissionsForGroupTable(grpName As String, tblName) Dim cat1 As New ADOX.Catalog Dim grp1 As New ADOX.Group, usr1 As New ADOX.User cat1.ActiveConnection = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\Programming Access\Chap10\UserLevel.mdb;" & _     "Jet OLEDB:System database=C:\Program Files\Microsoft Office\" & _     "Office\systemdemo.mdw;" & _     "User Id=Admin;Password=password;" cat1.Groups(grpName).SetPermissions tblName, adPermObjTable, adAccessSet, _     adRightRead Or adRightInsert Or adRightDelete End Sub 

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.