You might not always be available to set up security for the users of your application. Of course, one alternative is to make sure that they purchase their own copies of Access and then to instruct them on how to maintain security implementing the user interface. Access security is very complex, though, so this solution is not particularly practical. In fact, if you are distributing your application to a large group of users, this option is an impossibility. Fortunately, you can build in to your application code the capability to maintain all aspects of security directly. It is important that you provide your administrative users with the capability to establish and maintain security for their workgroups. This involves building a front-end interface to all the security functionality provided by Access. Behind the scenes, you can use ActiveX Data Object (ADO) code to implement the security functionality.
Chapter 27, “Database Security Made Easy,” discusses the importance of creating logical groups of users and then assigning rights to those groups. The administrator of your application might want to add or remove groups after you have distributed your application. You can use ADO code to create and manage group accounts at runtime.
You add a group using the Append method of the Groups collection. The Groups collection is part of the ADOX Catalog object. Figure 28.1 shows a form that enables users to add and remove groups.
This form is named frmMaintainGroups and is included in the CHAP28EX.MDB database located on the sample code CD-ROM. Listing 28.1 shows the code under the Add button.
Example 28.1. Adding a Group
Private Sub cmdAdd_Click()
Dim boolSuccess As Boolean
If IsNull(Me.txtGroupName) Then
MsgBox "You Must Fill In Group Name Before Proceeding"
Else
boolSuccess = CreateGroups()
If boolSuccess Then
MsgBox "Group Created Successfully"
Else
MsgBox "Group Not Created"
End If
End If
End SubThis code tests to ensure that entries have been made for the Group Name. If so, the CreateGroups function is called. Based on the return value from CreateGroups, the user is notified whether the group was created successfully. Listing 28.2 uses the Append method of the Groups collection to add a new group to the workgroup.
Example 28.2. The CreateGroups Function Adds a Group
Function CreateGroups() As Boolean On Error GoTo CreateGroups_Err Dim cat As ADOX.Catalog CreateGroups = True Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection 'Append group to the Groups collection 'of the Catalog object cat.Groups.Append (Me.txtGroupName) CreateGroups_Exit: Set cat = Nothing Exit Function CreateGroups_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description CreateGroups = False Resume CreateGroups_Exit End Function
The function uses a Catalog variable. The Catalog variable is part of the Microsoft ADO Extension for DDL and Security (ADOX). You must reference the ADOX library before you can use the Catalog variable. The example sets the ActiveConnection property of the Catalog variable to the connection associated with the current project. After the connection is established, the Append method of the Groups collection of the Catalog object is used to append the group. The Append method of the Groups collection receives one parameter, the name of the group. The Append method, when applied to the Groups collection, adds a new group to the catalog. The function uses the value in txtGroupName as the name of the group to add. After running this routine, you can verify that a new group has been added to the workgroup by choosing Tools|Security|User and Group Accounts. The newly created group should appear in the group drop-down list on the group page.
The code to remove a group is very similar to the code required to add a group. Listing 28.3 shows the code under the cmdRemove command button.
Example 28.3. Removing a Group
Private Sub cmdRemove_Click()
Dim boolSuccess As Boolean
If IsNull(Me.txtGroupName) Then
MsgBox "You Must Fill In Group Name Before Proceeding"
Else
boolSuccess = RemoveGroups()
If boolSuccess Then
MsgBox "Group Removed Successfully"
Else
MsgBox "Group Not Removed"
End If
End If
End SubThis routine ensures that the group name has been filled in and then calls the RemoveGroups function. An appropriate message is displayed, indicating whether the group was removed successfully. Listing 28.4 shows the RemoveGroups function.
Example 28.4. The RemoveGroups Function Removes a Group
Function RemoveGroups()
On Error GoTo RemoveGroups_Err
Dim cat As ADOX.Catalog
RemoveGroups = True
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Delete group from the Groups collection
'of the Catalog object
cat.Groups.Delete Me.txtGroupName.Value
RemoveGroups_Exit:
Set cat = Nothing
Exit Function
RemoveGroups_Err:
If Err.Number = 3265 Then
MsgBox "Group Not Found"
Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
End If
RemoveGroups = False
Resume RemoveGroups_Exit
End FunctionThe RemoveGroups function uses the Delete method of the Groups collection of the Catalog object, taking the value in txtGroupName as the name of the group to remove. If the group does not exist, an error number 3265 results. An appropriate error message appears.
Not only might you want to maintain groups using code, but you might also want to maintain users with code. You can employ ADO to create and manage user accounts at runtime. The frmMaintainUsers form shown in Figure 28.2 illustrates this process.
You add a user with the Append method of the Users collection of the Catalog object. The frmMaintainUsers form, also contained in CHAP28EX.MDB, contains a command button named cmdAddUsers that adds a user. Listing 28.5 shows the code for this.
Example 28.5. Adding a User
Private Sub cmdAdd_Click()
Dim boolSuccess As Boolean
If IsNull(Me.txtUserName) Then
MsgBox "You Must Fill In User Name Before Proceeding"
Else
boolSuccess = CreateUsers(Me.txtUserName.Value, _
Nz(Me.txtPassword.Value, ""))
If boolSuccess Then
MsgBox "User Created Successfully"
Else
MsgBox "User Not Created"
End If
End If
End SubThis code checks to ensure that the username has been filled in and then calls the CreateUsers function shown in Listing 28.6.
Note
The CreateUsers function, along with all the other functions included in this chapter, is found in the Chap28Ex.mdb sample database.
Example 28.6. The CreateUsers Function Creates a User
Function CreateUsers(UserName as String, _ Password as String) As Boolean On Error GoTo CreateUsers_Err Dim cat As ADOX.Catalog CreateUsers = True Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection 'Add User to the Users collection 'of the Catalog object cat.Users.Append UserName, Password CreateUsers_Exit: Set cat = Nothing Exit Function CreateUsers_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description CreateUsers = False Resume CreateUsers_Exit End Function
This routine creates a Catalog variable. It sets the ActiveConnection property of the Catalog object to the connection associated with the current project. It then invokes the Append method of the Users collection of the Catalog object to add the user to the catalog. The values in the txtUserName and txtPassword controls are passed to the Append method as arguments. The Append method adds the user to the collection of users in the catalog.
So far, you have added a user, but you have not given the user group membership. Next, take a look at how you can add a user to an existing group. Listing 28.7 shows the code behind the cmdAssign button on the frmMaintainUsers form.
Example 28.7. Assigning a User to a Group
Private Sub cmdAssign_Click()
Dim boolSuccess As Boolean
If IsNull(Me.txtUserName) Or IsNull(Me.txtGroupName) Then
MsgBox "You Must Fill In User Name and Group Name Before Proceeding"
Else
boolSuccess = AssignToGroup(Me.txtUserName.Value, _
Me.txtGroupName.Value)
If boolSuccess Then
MsgBox "User Successfully Assigned to Group"
Else
MsgBox "User Not Assigned to Group"
End If
End If
End SubThis code makes sure that both the txtUserName and txtGroupName text boxes are filled in and then calls the AssignToGroup function, which attempts to assign the user to the specified group. Listing 28.8 shows the AssignToGroup function.
Example 28.8. The AssignToGroup Function Assigns a User to a Group
Function AssignToGroup(UserName as String, _
GroupName as String)
On Error GoTo AssignToGroup_Err
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
AssignToGroup = True
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Attempt to append group to the Groups
'collection of the Catalog object
cat.Groups.Append GroupName
'Add the user to the specified Group Set usr = cat.Users(UserName)
usr.Groups.Append GroupName
AssignToGroup_Exit:
Set cat = Nothing
Exit Function
AssignToGroup_Err:
Select Case Err.Number
Case -2147467259 'Group already exists
Resume Next
Case 3265
MsgBox "Group Not Found"
Case Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
End Select
AssignToGroup = False
Resume AssignToGroup_Exit
End FunctionThis code creates a Catalog object variable and a User object variable. The ActiveConnection property of the Catalog object is set to the Connection property of the current project.
The Append method of the Groups collection of the Catalog object is used to add the group to the Groups collection of the catalog. If the group already exists, the resulting error is ignored. A Set statement points the User object at the user specified as the UserName input parameter to the function. Finally, the Append method of the Groups collection of the User object adds the user to the group supplied by the value specified as the GroupName parameter.
Just as you will want to add users to groups, you also will want to remove them from groups. The code in Listing 28.9 is located under the cmdRevoke command button on the frmMaintainUsers form.
Example 28.9. Removing a User from a Group
Private Sub cmdRevoke_Click()
Dim boolSuccess As Boolean
If IsNull(Me.txtUserName) Or IsNull(Me.txtGroupName) Then
MsgBox "You Must Fill In User Name and Group Name Before Proceeding"
Else
boolSuccess = RevokeFromGroup(Me.txtUserName.Value, _
Me.txtGroupName.Value)
If boolSuccess Then
MsgBox "User Successfully Removed from Group"
Else
MsgBox "User Not Removed to Group"
End If
End If
End SubThis code ensures that the name of the user and group are filled in on the form and then calls the RevokeFromGroup function, which is shown in Listing 28.10.
Example 28.10. The RevokeFromGroup Function Removes a User from a Group
Function RevokeFromGroup(UserName as String, _
GroupName as String)
On Error GoTo RevokeFromGroup_Err
Dim cat As ADOX.Catalog
RevokeFromGroup = True
Set cat = New ADOX.Catalog
Dim usr As ADOX.User
cat.ActiveConnection = CurrentProject.Connection
'Delete the user from the specified group
Set usr = cat.Users(UserName)
usr.Groups.Delete GroupName
RevokeFromGroup_Exit:
Set cat = Nothing
Exit Function
RevokeFromGroup_Err:
If Err.Number = 3265 Then
MsgBox "Group Not Found"
Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
End If
RevokeFromGroup = False
Resume RevokeFromGroup_Exit
End FunctionThis procedure establishes a Catalog object and points its Connection property to the connection associated with the current project. It establishes a User object and points it to the user specified in the UserName input parameter. It then removes the specified user from the group using the Delete method of the Groups collection of the User object. Notice that the Item of the Users collection referenced is specified in the parameter UserName, which is passed from the text box txtUserName. The group that the user is deleted from is specified by the parameter GroupName, which is passed from the txtGroupName text box.
Sometimes you want to remove a user entirely. The cmdRemove command button on the frmMaintainUsers form accomplishes this task, as shown in Listing 28.11.
Example 28.11. Deleting a User
Private Sub cmdRemove_Click()
Dim boolSuccess As Boolean
If IsNull(Me.txtUserName) Then
MsgBox "You Must Fill In User Name Before Proceeding"
Else
boolSuccess = RemoveUsers(Me.txtUserName.Value)
If boolSuccess Then
MsgBox "User Removed Successfully"
Else
MsgBox "User Not Removed"
End If
End If
End SubThis code needs only a username to proceed. If a username has been supplied, the RemoveUsers function is called, as shown in Listing 28.12.
Example 28.12. The RemoveUsers Function Deletes a User
Function RemoveUsers(UserName as String)
On Error GoTo RemoveUsers_Err
Dim cat As ADOX.Catalog
RemoveUsers = True
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Remove a user from the Users collection
cat.Users.Delete UserName
RemoveUsers_Exit:
Set cat = Nothing
Exit Function
RemoveUsers_Err:
If Err.Number = 3265 Then
MsgBox "User Not Found"
Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
End If
RemoveUsers = False
Resume RemoveUsers_Exit
End FunctionThe RemoveUsers function issues the Delete method on the Users collection of the catalog. This removes the user from the workgroup.
Figure 28.3 shows an enhanced version of the frmMaintainUsers form. It is named frmMaintainAll. The frmMaintainAll form, located in CHAP28EX.MDB, enables an administrator to add and remove users, assign users to groups, and assign passwords to users. The Group Name and User Name text boxes in Figure 28.2 have been replaced with combo boxes so that the user can view and select from existing users and groups.
The ListGroups function loops through each Group object in the Groups collection. As it loops, it uses the AddItem method of the combo box to add the name of the group to the combo box received as a parameter to the function.. The AddItem method is covered in detail in Chapter 9, “Advanced Form Techniques.” Listing 28.13 gathers a list of existing groups in the workgroup.
Example 28.13. The ListGroups Function Creates a List of All Groups
Function ListGroups(cboAny As ComboBox)
Dim cat As ADOx.Catalog
Dim grp As ADOx.Group
Set cat = New ADOx.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Loop through each Group in the Groups collection,
'Adding the name of each group to the
'combo box passed as a parameter
For Each grp In cat.Groups
cboAny.AddItem grp.Name
Next grp
End FunctionListing all users is very similar to listing all groups, as shown in Listing 28.14.
Example 28.14. The ListUsers Function Creates a List of All Users
Function ListUsers(cboAny As ComboBox)
Dim cat As ADOx.Catalog
Dim usr As ADOx.User
Set cat = New ADOx.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Loop through each User in the Users collection,
'Adding the name of each user to the
'combo box passed as a parameter
For Each usr In cat.Users
cboAny.AddItem usr.Name
Next usr
End FunctionThis code loops through each User object in the Users collection. It adds the name of each user to the combo box.
Sometimes it is necessary to print a list of users in a specific group. Listing 28.15 illustrates this process.
Example 28.15. The Code in the cmdListUsers_Click Event Lists All the Users in the Group Selected in the cboGroupName Combo Box.
Private Sub cmdListUsers_Click()
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Dim strUsers As String
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
'Loop through each user object in the Users collection
'of the Group selected in the cboGroupName combo box
For Each usr In cat.Groups(Me.cboGroupName.Value).Users
strUsers = strUsers & usr.Name & vbCrLf
Next usr
MsgBox strUsers
End SubThe code loops through each User object in the Users collection of the group selected in the cboGroupName combo box. As it loops, it builds a string with the name of the user.
You can easily determine whether a user is a member of a specific group. The process is illustrated in Listing 28.16.
Example 28.16. The cmdIsUserInGroup_Click Event Displays Whether the User Selected in the cboUserName Combo Box Is a Member of the Selected Group
Private Sub cmdIsUserInGroup_Click()
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Dim boolInGroup As Boolean
'If an error occurs, continue processing
On Error Resume Next
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
'Attempt to retrieve the Name property of the User selected
'in the Users combo box as part of the Group selected in the
'Group combo box
boolInGroup = (cat.Groups(Me.cboGroupName.Value) _
.Users(Me.cboUserName.Value).Name = _
Me.cboUserName.Value)
'Display message with success or failure
If boolInGroup Then
MsgBox "User in Group"
Else
MsgBox "User Not in Group"
End If
End SubThe code attempts to retrieve the Name property of the user selected in the cboUserName combo box as a member of the group selected in the cboGroupName combo box. If the user is not a member of the group, an error occurs. The code then compares the Name property to the value in the cboUserName combo box. The comparison returns True if the user is in the group, and False if the user is not in the group. The appropriate message is displayed to the user.
Many times, the administrative user needs to add, remove, or modify users’ passwords. By using the user interface, you can modify only the password of the user currently logged in; by using code, however, you can modify any user’s password, as long as you have administrative rights to do so.
The frmMaintainAll form enables the administrative user to assign a password to the user selected in the combo box. Listing 28.17 shows the code to assign a new password for a user.
Example 28.17. Changing a User’s Password
Private Sub cmdPassword_Click()
Dim boolSuccess As Boolean
If IsNull(Me.cboUserName.Value) Then
MsgBox "You Must Fill In User Name and Password Before Proceeding"
Else
boolSuccess = AssignPassword()
If boolSuccess Then
MsgBox "Password Successfully Changed"
Else
MsgBox "Password Not Changed"
End If
End If
End SubThis routine ensures that a username has been entered and then calls the AssignPassword function, located in the frmMaintainAll form, as shown in Listing 28.18.
Example 28.18. The AssignPassword Function Changes a User’s Password
Function AssignPassword()
On Error GoTo AssignPassword_Err
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
AssignPassword = True
'Use ChangePassword method of the User object
'to change the password associated with the user
'selected in the combo box
Set usr = cat.Users(Me.cboUserName.Value)
usr.ChangePassword _
"", Nz(Me.txtPassword.Value)
AssignPassword_Exit:
Set cat = Nothing
Exit Function
AssignPassword_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
AssignPassword = False
Resume AssignPassword_Exit
End FunctionThe AssignPassword function points the User object at the user selected in the cboUserName combo box. It then uses the ChangePassword method of the User object to change the password associated with that user. The first parameter, the old password, is left blank intentionally. Members of the Admins group can modify anyone’s password but their own without having to know the old password. The second parameter, the new password, is the value entered in the txtPassword text box. The Nz function sets the new password to a zero-length string if the administrative user did not supply a new password.
Many times, an administrative user simply wants to obtain a list of all users who do not have passwords. This list can be obtained quite easily by using VBA code and the ADOX library. Figure 28.4 shows the frmMaintainPasswords form, which is located in the CHAP28EX.MDB database.
When the form is loaded, the list box uses a callback function to display a list of all users who do not have passwords. Listing 28.19 shows the code for the frmMaintainPasswords form.
Example 28.19. Locating Users Without Passwords
Function ListUsers()
On Error GoTo ListUsers_Err
Dim cat As ADOX.Catalog
Dim cnn As ADODB.Connection
Dim usr As ADOX.User
Dim boolNoPass As Boolean
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
For Each usr In cat.Users
boolNoPass = True
Set cnn = New ADODB.Connection
cnn.Open CurrentProject.Connection, usr.Name, ""
If boolNoPass Then
Me.lstUserName.AddItem usr.Name
End If
Next usr
ListUsers_Exit:
Set cat = Nothing
Set usr = Nothing
Exit Function
ListUsers_Err:
If Err.Number = -2147217843 Then
boolNoPass = False
Resume Next
Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume ListUsers_Exit
End If
End FunctionThe meat of the code is in the For...Each loop. The code loops through each user in the Users collection. It begins by setting the value of the boolNoPass flag to True. It creates a new catalog and attempts to log on to the new catalog by using the Name property of the current user object and a password that is a zero-length string. If an error occurs, the error-handling code sets the boolNoPass flag to False. The –2147217843 error means that the password was not valid, indicating that the user must have a password because the logon was not successful. If the logon was successful, the user must not have a password and therefore is added to the list box.
You might want to ensure that users who log on to your application have a password. You can accomplish this by using the code in Listing 28.20.
Example 28.20. Ensuring That Your Application’s Users Have Passwords
Function AutoExec()
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Dim strPassword As String
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set usr = cat.Users(CurrentUser)
On Error Resume Next
usr.ChangePassword "", ""
If Err.Number = 0 Then
strPassword = InputBox("You Must Enter a Password _
Before Proceeding", "Enter Password")
If strPassword = "" Then
DoCmd.Quit
Else
usr.ChangePassword "", strPassword
End If
End If
AutoExec = True
End FunctionThe AutoExec function can be called from the startup form of your application. It points a User object variable to CurrentUser. It accomplishes this by using the return value from the CurrentUser function as the user to look at in the Users collection. The CurrentUser function returns a string containing the name of the current user.
When an object variable is pointing at the correct user, the code attempts to set a new password for the user. When modifying the password of the current user, both the old password and the new password must be supplied to the ChangePassword method of the User object. If the old password is incorrect, an error occurs. This indicates that the user has a password and nothing special needs to happen. If no error occurs, you know that no password exists, so the user is prompted for a password. If the user does not supply a password, the application quits. Otherwise, a new password is assigned to the user.
Note
You can find an example of the usefulness of this function in the basUtils module in the Chap28ExNoPass.MDB database located on your sample code CD-ROM.
Note
A user can hold down the Shift key to bypass the code found in Listing 28.20. To keep this from happening, you must programmatically set the AllowBypassKey property of the database to False.
Often, you will want to assign and revoke object permissions using code. Once again, you easily can accomplish this by using ADO code. The form in Figure 28.5 is named frmTableRights and is located in the CHAP28EX.MDB database.
The code in Listing 28.21 assigns view rights for the table selected in the Select a Table list box to the group selected in the Group Name drop-down list.
Example 28.21. Assigning View Rights
Private Sub cmdViewRights_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Groups(Me.cboGroupName.Value).SetPermissions _
Me.lstTables.Value, _
ObjectType:=adPermObjTable, _
Action:=adAccessSet, _
Rights:=adRightRead
End SubThe SetPermissions method of the Groups object is used to establish the permissions for the table. The rights for the table are granted to the group specified in the cboGroupName combo box. The table to which the rights are granted is designated in the lstTables list box. The object type to which the rights are assigned is designated in the ObjectType parameter. The constant adPermObjTable is used to specify the object type as a table. The Action parameter is used to designate the type of action being taken. The constant adAccessSet is used to indicate that the rights are being established. The Rights parameter is used to specify the rights being granted. The constant adRightRead is used to assign read rights to the table. Table 28.1 lists some of the permission constants for queries and tables.
Table 28.1. Permission Constants for Queries and Tables
Grants Permission To | |
|---|---|
| Delete rows from the table or query. |
| Insert new rows into the table or query. |
| Read the definition of the table or query. |
| Modify table or query data. |
| Read data stored in the table or query. Also, implicitly grants read permission to the definition of the table or query. |
| Alter the definition of the table or query. |
| Grant permissions for the object. |
| All rights to the object. |
Listing 28.22 shows an example in which the adRightRead constant is combined with the adRightUpdate constant using a bitwise OR. The adRightUpdate constant does not imply that the user also can read the table definition and data. As you might guess, it is difficult to edit data if you cannot read it. You therefore must combine the adRightRead constant with the adRightUpdate constant in order to allow the user or group to read and modify table data.
Example 28.22. Modifying User Rights
Private Sub cmdModifyRights_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Groups(Me.cboGroupName.Value).SetPermissions _
Me.lstTables.Value, _
ObjectType:=adPermObjTable, _
Action:=adAccessSet, _
Rights:=adRightRead Or adRightUpdate
End SubIt is useful to be able to determine if a user has permissions to an object. This is easy to accomplish using the ADOX object library. The code appears in Listing 28.23.
Example 28.23. Determining User Permissions to an Object
Private Sub cmdHasPermission_Click()
Dim boolCanRead As Boolean
Dim cat As ADOX.Catalog
Dim grp As ADOX.Group
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the Group selected in the cboGroupName combo box
'has the permissions selected in the cboPermissions combo box
'to the table selected in the lstTables list box
Set grp = cat.Groups(Me.cboGroupName.Value)
boolCanRead = ((grp.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
MsgBox boolCanRead
End SubThe code points a Group object at the group selected in the cboGroupName combo box. It then uses the GetPermissions method of the Group object to retrieve the permissions the group has on the specified table. The GetPermissions method receives the name of the object whose permissions you want to retrieve, as well as a constant designating the type of object. GetPermissions returns a long integer indicating all the permissions for an object. Each bit of the integer indicates a different permission or right. You evaluate a specific permission using a bitwise AND, along with a constant for that particular permission. This masks off the bits for the specific permission that you are interested in. When compared to the same bits, the expression evaluates True if the group has the permissions, and False if it does not. Refer to Table 28.1 for a list of some of the permissions that you can test for.
The code in Listing 28.23 evaluates whether a group has one type of rights to the selected table. You can evaluate one type of rights at a time, or you can test for several rights simultaneously. The code in Listing 28.24 evaluates the Users group to see if members have Read, Update, Insert, and Delete rights to the table selected in the lstTables list box.
Example 28.24. Determining Whether a Group Has Multiple Permissions to an Object
Private Sub cmdHasMultiple_Click()
Dim boolRights As Boolean
Dim cat As ADOX.Catalog
Dim grp As ADOX.Group
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the Group selected in the cboGroupName combo box
'has Read, Update Insert and Delete permissions
'to the table selected in the lstTables list box
Set grp = cat.Groups(Me.cboGroupName.Value)
boolRights = ((grp.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And (adRightRead Or _
adRightUpdate Or _
adRightInsert Or _
adRightDelete)) = _
(adRightRead Or _
adRightUpdate Or _
adRightInsert Or _
adRightDelete))
MsgBox boolRights
End SubThe example combines rights using a bitwise OR. The expression returns True only if the selected group has all rights designated (Read, Update, Insert, and Delete).
The code in Listings 28.23 and 28.24 showed how you can evaluate an object to determine whether a particular group has rights to it. The process to determine if an individual user has rights to an object is almost identical. It is shown in Listing 28.25.
Example 28.25. Determining Whether a User Has Permissions to an Object
Private Sub cmdUserHasPermission_Click()
Dim boolCanRead As Boolean
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the User selected in the cboUserName combo box
'has the permissions selected in the cboPermissions combo box
'to the table selected in the lstTables list box
Set usr = cat.Users(Me.cboUserName.Value)
boolCanRead = ((usr.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
MsgBox boolCanRead
End SubThe code in Listing 28.25 points a User object at the user selected in the cboUserName combo box. It then uses the GetPermissions method of the User object to retrieve the permissions the user has on the specified table. The GetPermissions method receives the name of the object whose permissions you want to retrieve, as well as a constant designating the type of object whose permissions you wish to retrieve. GetPermissions returns a long integer indicating all the permissions for an object. Each bit of the integer indicates a different permission or right. You evaluate a specific permission using a bitwise AND, along with a constant for that particular permission. This masks off the bits for the specific permission that you are interested in. When compared to the same bits, the expression evaluates True if the user has the permissions, and False if she does not.
If a user is not explicitly assigned rights to an object, she might have implicit rights to the object. Implicit rights exist if a group that the user belongs to has rights to the object. The code in Listing 28.26 evaluates whether a user has implicit rights to the object.
Example 28.26. Determining Whether a User Has Implicit Rights to an Object
Private Sub cmdImplicit_Click()
Dim boolCanRead As Boolean
Dim cat As ADOX.Catalog
Dim usr As ADOX.User
Dim grp As ADOX.Group
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Determine if the User selected in the cboUserName combo box
'has the permissions selected in the cboPermissions combo box
'to the table selected in the lstTables list box
Set usr = cat.Users(Me.cboUserName.Value)
boolCanRead = ((usr.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
'If the user does not have permissions, see if any group they
'belong to has permissions
If Not boolCanRead Then
For Each grp In usr.Groups
boolCanRead = ((grp.GetPermissions(Me.lstTables.Value, _
adPermObjTable) _
And Val(Me.cboPermissions.Value)) = _
Val(Me.cboPermissions.Value))
If boolCanRead Then
Exit For
End If
Next grp
End If
MsgBox boolCanRead
End SubThe code first evaluates if the user has the designated rights for the object. If the user does not have rights to the object, the code loops through each group that the user is a member of. If it finds the designated rights for any group that the user is a member of, it exits the loop, returning True.
Earlier in the chapter, you learned how to set permissions for existing objects. You might also want to programmatically assign the permissions a user is granted for new objects. The process is shown in Listing 28.27.
Example 28.27. Determining Whether a Group Has Permissions to an Object
Private Sub cmdNewObjects_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Uses the SetPermissions method with a zero-length
'string for the Name parameter, and adInheritObjects
'for the Inherit parameter to assign Read rights to all
'new tables
cat.Groups(Me.cboGroupName.Value).SetPermissions _
"", _
ObjectType:=adPermObjTable, _
Action:=adAccessGrant, _
Rights:=adRightRead, _
Inherit:=adInheritObjects
End SubThe code uses the SetPermissions method of the Group object to assign permissions to the group. Notice that the first parameter to the SetPermissions object, the Name parameter is a zero-length string. The Name parameter, along with the adInheritObjects value for the Inherit parameter, dictates that the rights being assigned apply to new objects.
I’ve covered how to assign permissions to the objects in a database. Often you will want to programmatically grant or remove rights to the database. You can programmatically determine whether a user or group can open the database, open the database exclusively, and more. The code in Listing 28.28 assigns rights for the database to the group selected in the cboGroupName combo box.
Example 28.28. Manipulating Database Permissions
Private Sub cmdDatabaseRights_Click()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Uses the SetPermissions method with an
'ObjectType of adPermObjDatabase, an
'Action of adAccessGrant, and Rights of
'adRightRead to assign Open rights for the group
cat.Groups(Me.cboGroupName.Value).SetPermissions _
"", _
ObjectType:=adPermObjDatabase, _
Action:=adAccessGrant, _
Rights:=adRightRead
End SubThe code uses the SetPermissions method of the group object to assign rights for the database to the group selected in the cboGroupName combo box. Notice the name parameter is a zero-length string. The ObjectType parameter is adPermObjDatabase, and the Rights are set to adRightRead. These parameters grant open rights for the database to the selected group.
Chapter 27 showed how you can encrypt a database by using the user interface. If a database is not encrypted, it is not really secure because a savvy user can use a disk editor to view the data in the file. If you have distributed your application with the runtime version of Access and you want to give your user the ability to encrypt the database, you must write ADO code to accomplish the encryption process. The code looks like this:
Sub Encrypt(strDBNotEncrypted As String, strDBEncrypted As String)
Dim je As New JRO.JetEngine
je.CompactDatabase SourceConnection:="Data Source=" _
& strDBNotEncrypted & ";", _
DestConnection:="Data Source=" & strDBEncrypted & _
"; Jet OLEDB:Encrypt Database=True"
End SubThis subroutine receives two parameters. The first is the name of the database that you want to encrypt. The second is the name you want to assign to the encrypted database. The CompactDatabase method is issued on the JetEngine object. This method receives two parameters: the name of the original database to encrypt and the name for the new encrypted database. Notice that the data source for the destination includes information indicating that you want to encrypt the database being compacted.
In itself, Access does not provide field-level security. You can achieve field-level security by using queries, though. Here’s how it works. You do not provide the user or group with any rights to the table that you want to secure. Instead, you give the user or group rights to a query containing only the fields that you want the user to be able to view. Ordinarily, this would not work because, if users cannot read the tables underlying a query, they cannot read the data in the query result.
The trick is in a query option named WITH OWNERACCESS OPTION. The WITH OWNERACCESS OPTION of a query grants the user running the query the rights possessed by the owner of the query. The Staff group, for example, has no rights to the tblEmployees table. The Supervisors group has Read Design, Read Data, and Update Data permissions to the tblEmployees table. The qryEmployees query is owned by the Supervisors group, as shown in Figure 28.6. Figure 28.7 shows the query itself. Notice in Figure 28.7 that the Run Permissions property has been set to Owner's. Figure 28.8 shows the resulting SQL. Notice the WITH OWNERACCESS OPTION clause at the end of the SQL statement. When any member of the Staff group (who has no other rights to tblEmployees) runs the query, that member inherits the Supervisor group’s ability to read and modify the table data.
You might want to prevent the members of a workgroup from creating new databases or creating specific database objects. Preventing users from creating databases or other objects can be accomplished only by using VBA code.
You might want to prohibit users from creating specific types of objects. For example, you might want to prevent them from creating new tables, queries, or other objects in your application or data database file. Listing 28.29 illustrates this process.
Example 28.29. Prohibiting Users from Creating Other Objects
Sub NoTables(strGroupToProhibit)
On Error GoTo NoTables_Err
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cat.Tables.Refresh
cat.Groups(strGroupToProhibit).SetPermissions Null, _
adPermObjTable, adAccessDeny, adRightCreate
NoTables_Exit:
Set cat = Nothing
Exit Sub
NoTables_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume NoTables_Exit
End SubThe code uses the SetPermissions method of the Groups collection of the Catalog object to set permissions for new tables. The parameters used in the example deny table creation rights for the group specified in the call to the function.
Although you might not want particular users to be able to accomplish particular tasks, you might at times want to go behind the scenes and accomplish the task for them. As you saw in the preceding section, you can prohibit a user or group from creating new tables and queries. This is fine, except when you run into a situation in which your code requires that a temporary table be created. In this situation, you can temporarily log on as a different user, perform the process, and then log off.
It is important to understand that security for client/server applications must be applied on the back-end database server. You can request logon IDs and passwords from users at runtime and pass them to the database server as part of the connection string, but Access security itself does nothing in terms of interacting with client/server data. Any errors returned from the back end must be handled by your application.
Database security cannot be implemented on replicated databases. Only user-level security can be implemented. All the replicas inherit the security applied to the Design Master. Replicate only the database file. Never replicate the security information file (System.mdw). Instead, make sure that exactly the same security information file is available at each location where the replica is used. You can do this by copying the file to each location.
A user must have administrator permission on a database to perform the following tasks:
Converting a nonreplicable database into a replicable database
Making a replica of the Design Master
Making a local object replicable
Making a replicable object local
Access 2000 introduced the ability to implement security using SQL. This was accomplished by adding ANSI SQL-92 extensions to Jet 4.0. This section shows you how you can perform common security tasks using SQL.
Just as you can maintain users with ADOX code, you can also maintain them using SQL. The CREATE USER, ADD USER TO, DROP USER FROM, and DROP USER statements will create users, add users to groups, remove users from groups, and remove users, respectively. These statements are all covered in the following sections.
The CREATE USER statement is used to add a user to a workgroup. Listing 28.30 illustrates the process.
Notice that the Execute method of the Connection object is used to execute the CREATE USER statement. The code creates a user with the name designated in the txtUser text box. Two optional parameters are available with the CREATE USER statement. They are the password and the PID. Listing 23.31 illustrates the use of these optional parameters.
The example creates a user named ALEXIS with a password of GREATKID and a PID of ABCDE.
The ADD USER TO statement adds a user to a group. Listing 28.32 provides an example.
The ADD USER TO statement adds the user specified in the txtUser text box to the group designated in the txtGroup text box. Once again, the Execute method of the Connection object is used to execute the SQL statement.
The DROP USER FROM statement is used to remove a user from a group. Listing 28.33 provides an example.
The DROP USER FROM statement is provided with the name of the user you want to drop and the group he is to be dropped from. The statement is executed using the Execute method of the Connection object.
The DROP USER statement is used to remove a user from the workgroup. An example is provided in Listing 28.34.
Notice that the DROP USER statement with the keyword FROM removes a user from a group. Without the keyword FROM, the user is removed from the workgroup.
The ALTER USER statement is used to modify a password. The process is shown in Listing 28.35.
Example 28.35. Using SQL to Modify a Password
Private Sub cmdChangePassword_Click()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute "ALTER USER " & Me.txtUser & _
" PASSWORD " & _
IIf(IsNull(Me.txtOldPassword), "''", _
Me.txtOldPassword) & " " & _
Me.txtNewPassword
Set cnn = Nothing
End SubNotice that the ALTER USER statement requires both the old password and the new password. If the old password is blank, you must pass a zero-length string as the parameter value for the old password.
Just as you can maintain users with SQL code, you can also maintain groups. The CREATE GROUP and DROP GROUP statements are used to add and remove groups.
The CREATE GROUP statement is used to create a group. The statement receives an optional PID, a unique identifier for the group. Listing 28.36 provides an example.
The DROP GROUP statement is used to remove a group. An example appears in Listing 28.37.
The SQL GRANT and REVOKE statements are used to assign and remove permissions. Listing 28.38 provides an example of granting rights to an object.
The code in Listing 28.38 grants select rights to the tblClients table for the group designated in the txtGroup text box. Listing 28.39 shows how a REVOKE statement is used to remove user or group rights to an object.
Listing 28.39 removes select rights for the tblClients table from the group designated in the txtGroup text box.
In addition to ADOX and SQL, DAO (Data Access Objects) can also be used to implement security. Because DAO is an older technology whose days are numbered, implementing security with DAO is not covered in this text. Full coverage of security implementation using DAO is included in Alison Balter’s Mastering Access 97 Development.
Determining whether to use ADOX, SQL, or DAO to implement security can be a tough decision. Because DAO is supported primarily for backward compatibility, I do not recommend it for new development. Choosing between ADOX, SQL, and DAO involves comparing the methods, and determining what is important to you. Table 28.2 provides a list of features provided by each technology. You should carefully evaluate each feature and decide which implementation of security is appropriate for you.
Table 28.2. ADOX and SQL Comparison
ADOX | SQL | |
|---|---|---|
Adding users and groups | Full support | Full support |
Other account maintenance | Full support | Full support |
Granting and revoking permissions | Full support | Full support |
Querying permissions | Full support | No support |
Managing ownership | Full support | No support |
Backward compatibility | None | None |
Future support | Definitely | Likely |
Ease of use | Simpler than DAO | Very simple |
SQL Server compatibility | Works with SQL Server; doesn’t work with all other databases | Better support than ADOX |
The security features available in Access are extremely rich. Being able to implement security using both code and the user interface gives you immense power and flexibility when implementing security in your application.
This chapter began with a look at maintaining users and groups using code. Next, you learned about using code to assign and maintain passwords, as well as determining whether a user has a password. You also learned about assigning and revoking permissions to objects using code, as well as many other advanced techniques that give you full control over security in your application.
Security requires a lot of planning and forethought. You must make decisions about what groups to define and which rights you will assign to each group. Also, you must decide what features you will build in to your application using VBA code. This chapter illustrated how you can build all aspects of security directly into the user interface of your application.