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 copy of Access and then to instruct them on how to maintain security using 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 into your application code the capability to maintain all aspects of security directly. It is important that you give your administrative users the capabilities 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 DAO code to implement the security functionality.
Chapter 32, “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 group data access objects to create and manage group accounts at runtime.
You add a group by appending the Group object to the Groups collection. Figure 33.1 shows a form that enables users to add and remove groups.
This form is called frmMaintainGroups and is included in the CHAP33EX.MDB database located on the sample code CD-ROM. Listing 33.1 shows the code behind the Add button.
Example 33.1. Code behind the Add button.
Private Sub cmdAdd_Click()
Dim fSuccess As Boolean
If IsNull(Me!txtGroupName) Or IsNull(Me!txtPID) Then
MsgBox "You Must Fill In Group Name and PID Before Proceeding"
Else
fSuccess = CreateGroups()
If fSuccess 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 both the group name and PID. If so, the CreateGroups function is called. Based on the return value from CreateGroups, the user is notified as to whether the group was created successfully. Listing 33.2 uses the CreateGroup method of a workspace to add a new group to the workgroup.
Example 33.2. The CreateGroups function.
Function CreateGroups() As Boolean On Error GoTo CreateGroups_Err Dim wrk As Workspace Dim grp As GROUP CreateGroups = True Set wrk = DBEngine.Workspaces(0) Set grp = wrk.CreateGroup(Me!txtGroupName, Me!txtPID) wrk.Groups.Append grp CreateGroups_Exit: Set wrk = Nothing Set grp = Nothing Exit Function CreateGroups_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description CreateGroups = False Resume CreateGroups_Exit End Function
The function uses a Workspace variable and a Group variable. The CreateGroup method of the Workspace object receives two parameters: the name of the group and the PID. The new group is referenced by the Group object variable, grp. The Append method, when applied to the grp object variable, adds a new group to the workspace. The function uses the value in txtGroupName as the name of the group to add and the value in txtPID as the PID for the group. 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 33.3 shows the code under the cmdRemove command button.
Example 33.3. The cmdRemove command button code.
Private Sub cmdRemove_Click()
Dim fSuccess As Boolean
If IsNull(Me!txtGroupName) Then
MsgBox "You Must Fill In Group Name Before Proceeding"
Else
fSuccess = RemoveGroups()
If fSuccess 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 33.4 shows the RemoveGroups function.
Example 33.4. The RemoveGroups function.
Function RemoveGroups()
On Error GoTo RemoveGroups_Err
Dim wrk As Workspace
RemoveGroups = True
Set wrk = DBEngine.Workspaces(0)
wrk.Groups.Delete Me!txtGroupName
RemoveGroups_Exit:
Set wrk = 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 performs the Delete method on the Groups collection of the workspace, using 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 by using code, but you might also want to maintain users by using code. You can employ user data access objects to create and manage user accounts at runtime. The frmMaintainUsers form shown in Figure 33.2 illustrates this process.
You add a user by appending the User object to the Users collection. The frmMaintainUsers form, also contained in CHAP33EX.MDB, contains a command button called cmdAddUsers that adds a user. Listing 33.5 shows the code for this.
Example 33.5. The cmdAddUsers command button code.
Private Sub cmdAdd_Click()
Dim fSuccess As Boolean
If IsNull(Me!txtUserName) Or IsNull(Me!txtPID) Then
MsgBox "You Must Fill In User Name and PID Before Proceeding"
Else
fSuccess = CreateUsers()
If fSuccess Then
MsgBox "User Created Successfully"
Else
MsgBox "User Not Created"
End If
End If
End SubThis code checks to ensure that both the user name and PID have been filled in and then calls the CreateUsers function shown in Listing 33.6.
Example 33.6. The CreateUsers function.
Function CreateUsers() As Boolean On Error GoTo CreateUsers_Err Dim wrk As Workspace Dim usr As User CreateUsers = True Set wrk = DBEngine.Workspaces(0) Set usr = wrk.CreateUser(Me!txtUserName, Me!txtPID) wrk.Users.Append usr CreateUsers_Exit: Set wrk = Nothing Set usr = Nothing Exit Function CreateUsers_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description CreateUsers = False Resume CreateUsers_Exit End Function
This routine creates Workspace and User object variables. It associates the Workspace object variable with the current workspace. It then invokes the CreateUser method to add the user to the workspace. The values in the txtUserName and txtPID are passed to the CreateUser function as arguments. The Append method then is applied to the Users collection of the workspace to add the user to the collection of users in the workspace.
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 33.7 shows the code behind the cmdAssign button on the frmMaintainUsers form.
Example 33.7. The cmdAssign button code.
Private Sub cmdAssign_Click()
Dim fSuccess As Boolean
If IsNull(Me!txtUserName) Or IsNull(Me!txtGroupName) Then
MsgBox "You Must Fill In User Name and Group Name Before Proceeding"
Else
fSuccess = AssignToGroup()
If fSuccess Then
MsgBox "User Successfully Assigned to Group"
Else
MsgBox "User Not Assigned to Group"
End If
End If
End SubThis code ensures that both the txtUserName and txtGroup name are filled in and then calls the AssignToGroup function, which attempts to assign the user to the specified group. Listing 33.8 shows the AssignToGroup function.
Example 33.8. The AssignToGroup function.
Function AssignToGroup()
On Error GoTo AssignToGroup_Err
Dim wrk As Workspace
Dim grp As GROUP
Dim usr As User
AssignToGroup = True
Set wrk = DBEngine.Workspaces(0)
Set grp = wrk.Groups(Me!txtGroupName)
Set usr = wrk.CreateUser(Me!txtUserName)
grp.Users.Append usr
AssignToGroup_Exit:
Set wrk = Nothing
Set grp = Nothing
Set usr = Nothing
Exit Function
AssignToGroup_Err:
If Err.Number = 3265 Then
MsgBox "Group Not Found"
Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
End If
AssignToGroup = False
Resume AssignToGroup_Exit
End FunctionThis code creates three object variables: Workspace, Group, and User. The Workspace variable points to the current workspace. The Group variable points toward the group specified in the txtGroupName text box. The CreateUser method points the User object variable to the user specified in the text box. You might wonder why you should use a CreateUser method even though the user name already must exist in order for this code to run properly. This is because you must create another instance of the account before adding it to a group. Finally, the Append method is applied to the Users collection of the Group object to add the user to the group.
Just as you will want to add users to groups, you also will want to remove them from groups. The code in Listing 33.9 is located under the cmdRevoke command button on the frmMaintainUsers form.
Example 33.9. The cmdRevoke command button code.
Private Sub cmdRevoke_Click()
Dim fSuccess As Boolean
If IsNull(Me!txtUserName) Or IsNull(Me!txtGroupName) Then
MsgBox "You Must Fill In User Name and Group Name Before Proceeding"
Else
fSuccess = RevokeFromGroup()
If fSuccess 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 33.10.
Example 33.10. The RevokeFromGroup function.
Function RevokeFromGroup()
On Error GoTo RevokeFromGroup_Err
Dim wrk As Workspace
Dim grp As GROUP
RevokeFromGroup = True
Set wrk = DBEngine.Workspaces(0)
Set grp = wrk.Groups(Me!txtGroupName)
grp.Users.Delete Me!txtUserName
RevokeFromGroup_Exit:
Set wrk = Nothing
Set grp = 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 an object variable pointing to the group specified on the form. It then removes the specified user from the group by performing the Delete method on the Users collection of the group.
Sometimes you want to remove a user entirely. The cmdRemove command button on the frmMaintainUsers form accomplishes this task, as shown in Listing 33.11.
Example 33.11. The cmdRemove command button code.
Private Sub cmdRemove_Click()
Dim fSuccess As Boolean
If IsNull(Me!txtUserName) Then
MsgBox "You Must Fill In User Name Before Proceeding"
Else
fSuccess = RemoveUsers()
If fSuccess Then
MsgBox "User Removed Successfully"
Else
MsgBox "User Not Removed"
End If
End If
End SubThis code needs only a user name to proceed. If a user name has been supplied, the RemoveUsers function is called, as shown in Listing 33.12.
Example 33.12. The RemoveUsers function.
Function RemoveUsers()
On Error GoTo RemoveUsers_Err
Dim wrk As Workspace
RemoveUsers = True
Set wrk = DBEngine.Workspaces(0)
wrk.Users.Delete Me!txtUserName
RemoveUsers_Exit:
Set wrk = 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 workspace. This removes the user from the workgroup.
Figure 33.3 shows an enhanced version of the frmMaintainUsers form. It is called frmMaintainAll. The frmMaintainAll form, located in CHAP33EX.MDB, enables the user to add and remove users, assign users to groups, and assign passwords to users. The Groups and Users text boxes have been replaced with combo boxes so that the user can view and select from existing users and groups.
The ListGroups function is the callback function used to populate the cboGroups combo box. Callback functions are covered in detail in Chapter 13, “Let's Get More Intimate with Forms: Advanced Techniques.” Listing 33.13 gathers a list of existing groups in the workgroup.
Example 33.13. Creating a list of all groups.
Function ListGroups(ctl As Control, vntID As Variant, _
lngRow As Long, lngCol As Long, intCode As Integer) _
As Variant
Dim wrk As Workspace
Dim grp As GROUP
Dim intCounter As Integer
Static sastrGroups() As String
Static sintNumGroups As Integer
Dim varRetVal As Variant
varRetVal = Null
Select Case intCode
Case acLBInitialize ' Initialize.
Set wrk = DBEngine(0)
sintNumGroups = wrk.Groups.Count
ReDim sastrGroups(sintNumGroups – 1)
For Each grp In wrk.Groups
sastrGroups(intCounter) = grp.Name
intCounter = intCounter + 1
Next grp
varRetVal = sintNumGroups
Case acLBOpen 'Open
varRetVal = Timer 'Generate unique ID for control.
Case acLBGetRowCount 'Get number of rows.
varRetVal = sintNumGroups
Case acLBGetColumnCount 'Get number of columns.
varRetVal = 1
Case acLBGetColumnWidth 'Get column width.
varRetVal = –1 '–1 forces use of default width.
Case acLBGetValue 'Get the data.
varRetVal = sastrGroups(lngRow)
End Select
ListGroups = varRetVal
End FunctionThe gist of the ListGroups function is that it uses the Count property of the Groups collection of the workspace to determine how many groups are contained in the workspace. This number is used by the callback function to designate how many rows will appear in the combo box. Notice the line For Each grp In wrk.Groups. This code loops through each group object in the Groups collection of the workspace. The Name property of the group object is added to the combo box.
Listing all users is very similar to listing all groups, as Listing 33.14 shows.
Example 33.14. Creating a list of all users.
Function ListUsers(ctl As Control, vntID As Variant, _
lngRow As Long, lngCol As Long, intCode As Integer) _
As Variant
Dim wrk As Workspace
Dim usr As User
Dim intCounter As Integer
Static sastrUsers() As String
Static sintNumUsers As Integer
Dim varRetVal As Variant
varRetVal = Null
Select Case intCode
Case acLBInitialize ' Initialize.
Set wrk = DBEngine(0)
sintNumUsers = wrk.Users.Count
ReDim sastrUsers(sintNumUsers – 1)
For Each usr In wrk.Users
sastrUsers(intCounter) = usr.Name
intCounter = intCounter + 1
Next usr
varRetVal = sintNumUsers
Case acLBOpen 'Open
varRetVal = Timer 'Generate unique ID for control.
Case acLBGetRowCount 'Get number of rows.
varRetVal = sintNumUsers
Case acLBGetColumnCount 'Get number of columns.
varRetVal = 1
Case acLBGetColumnWidth 'Get column width.
varRetVal = –1 '–1 forces use of default width.
Case acLBGetValue 'Get the data.
varRetVal = sastrUsers(lngRow)
End Select
ListUsers = varRetVal
End FunctionThis code looks at the Count property of the Users collection of the Workspace object to determine how many users exist. The For Each usr In wrk.Users line loops through each user in the Users collection. The name of each user is added to an array that is used to populate the cboUsers combo box.
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 on; 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 33.15 shows the code to assign a new password for a user.
Example 33.15. Changing a user's password.
Private Sub cmdPassword_Click()
Dim fSuccess As Boolean
If IsNull(Me!cboUserName.Value) Then
MsgBox "You Must Fill In User Name and Password Before Proceeding"
Else
fSuccess = AssignPassword()
If fSuccess Then
MsgBox "Password Successfully Changed"
Else
MsgBox "Password Not Changed"
End If
End If
End SubThis routine ensures that a user name has been entered and then calls the AssignPassword function, as shown in Listing 33.16.
Example 33.16. The AssignPassword function.
Function AssignPassword() On Error GoTo AssignPassword_Err Dim wrk As Workspace Dim usr As User AssignPassword = True Set wrk = DBEngine.Workspaces(0) wrk.Users(Me!cboUserName).NewPassword "", Nz(Me!txtPassword) AssignPassword_Exit: Set wrk = Nothing Set usr = Nothing Exit Function AssignPassword_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description AssignPassword = False Resume AssignPassword_Exit End Function
The AssignPassword function sets the NewPassword method of the User object specified in the cboUserName combo box, which is part of the Users collection. 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 data access objects. Figure 33.4 shows the frmMaintainPasswords form, which is located in the CHAP33EX.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 33.17 shows the code for the frmMaintainPasswords form.
Example 33.17. Locating users without passwords.
Function ListUsers(ctl As Control, vntID As Variant, _
lngRow As Long, lngCol As Long, intCode As Integer) _
As Variant
On Error GoTo ListUsers_Err
Dim wrk As Workspace
Dim wrkNew As Workspace
Dim usr As User
Dim intCounter As Integer
Dim fNoPass As Boolean
Static sastrUsers() As String
Static sintNumUsers As Integer
Dim varRetVal As Variant
varRetVal = Null
Select Case intCode
Case acLBInitialize ' Initialize.
Set wrk = DBEngine(0)
sintNumUsers = wrk.Users.Count
ReDim sastrUsers(sintNumUsers – 1)
For Each usr In wrk.Users
fNoPass = True
Set wrkNew = DBEngine.CreateWorkspace("NewWork", usr.Name, "")
If fNoPass Then
sastrUsers(intCounter) = usr.Name
intCounter = intCounter + 1
End If
Next usr
varRetVal = sintNumUsers
Case acLBOpen 'Open
varRetVal = Timer 'Generate unique ID for control.
Case acLBGetRowCount 'Get number of rows.
varRetVal = sintNumUsers
Case acLBGetColumnCount 'Get number of columns.
varRetVal = 1
Case acLBGetColumnWidth 'Get column width.
varRetVal = –1 '–1 forces use of default width.
Case acLBGetValue 'Get the data.
varRetVal = sastrUsers(lngRow)
End Select
ListUsers = varRetVal
ListUsers_Exit:
Set wrk = Nothing
Set usr = Nothing
Exit Function
ListUsers_Err:
If Err.Number = 3029 Then
fNoPass = 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 fNoPass flag to True. It creates a new workspace and attempts to log onto the new workspace 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 fNoPass flag to False. The 3029 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 onto your application have a password. You can accomplish this by using the code shown in Listing 33.18.
Example 33.18. Ensuring that your application's users have passwords.
Function AutoExec()
Dim usr As User
Dim strPassword As String
Set usr = DBEngine(0).Users(CurrentUser)
On Error Resume Next
usr.NewPassword "", ""
If Err.Number = 0 Then
strPassword = InputBox("You Must Enter a Password Before Proceeding", _
"Enter Password")
If strPassword = "" Then
DoCmd.Quit
Else
usr.NewPassword "", strPassword
End If
End If
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 NewPassword method. 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.
Often, you will want to assign and revoke object permissions by using code. Once again, you easily can accomplish this by using DAO code. The form in Figure 33.5 is called frmTableRights and is located in the CHAP33EX.MDB database.
The code shown in Listing 33.19 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 33.19. Assigning view rights.
Private Sub cmdViewRights_Click() Dim db As DATABASE Dim doc As Document Set db = CurrentDb Set doc = db.Containers!Tables.Documents(lstTables.Value) doc.UserName = Me!cboGroupName.Value doc.Permissions = dbSecRetrieveData End Sub
Notice that the code points a document variable to the table selected in the list box (lstTables.Value). The UserName property of the document is set equal to the group selected in the cboGroupName combo box. Then the Permissions property of the document is set equal to dbSecRetrieveData. The dbSecRetrieveData constant indicates that the user has rights to read the definition and data in the table. Table 33.1 lists the permission constants for queries and tables.
Table 33.1. The permission constants for queries and tables.
| Permission Constant | Grants Permission To |
|---|---|
dbSecDeleteData | Delete rows from the table or query. |
dbSecInsertData | Insert new rows into the table or query. |
dbSecReadDef | Read the definition of the table or query. |
dbSecReplaceData | Modify table or query data. |
dbSecRetrieveData | Read data stored in the table or query. Also, implicitly grants read permission to the definition of the table or query. |
dbSecWriteDef | Alter the definition of the table or query. |
Listing 33.20 shows an example in which the dbSecRetrieveData constant is combined with the dbSecReplaceData constant using a bitwise OR. The dbSecReplaceData 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 dbSecRetrieveData constant with the dbSecReplaceData constant in order to allow the user or group to read and modify table data.
Example 33.20. Modifying user rights.
Private Sub cmdModifyRights_Click()
Dim db As DATABASE
Dim doc As Document
Set db = CurrentDb
Set doc = db.Containers!Tables.Documents(lstTables.Value)
doc.UserName = Me!cboGroupName.Value
doc.Permissions = doc.Permissions Or _
dbSecRetrieveData Or dbSecReplaceData
End SubChapter 32 shows 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 capability to encrypt the database, you must write DAO code to accomplish the encryption process. The code looks like this:
Sub Encrypt(strDBNotEncrypted As String, strDBEncrypted As String)
DBEngine.CompactDatabase strDBNotEncrypted, strDBEncrypted,_
dbLangGeneral, dbEncrypt
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 Database Engine. This method receives five parameters: the name of the database to encrypt, the name for the new encrypted database, the collating order, option settings, and a database password. The last three parameters are optional. For the other option-settings parameter, you can use a constant to indicate that you want to encrypt the database.
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 called 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 and Modify permissions to the tblEmployees table. The qryEmployees query is owned by the Supervisors group, as shown in Figure 33.6. Figure 33.7 shows the query itself. Notice in Figure 33.7 that the Run Permissions property has been set to Owner's. Figure 33.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 capability 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.
By using data access objects, you can programmatically prohibit users from creating new databases. This is quite obviously a very powerful feature. Listing 33.21 shows the code you can use to accomplish this.
Example 33.21. Prohibiting users from creating new databases.
Sub NoDBs(strGroupToProhibit) On Error GoTo NoDBs_Err Dim db As DATABASE Dim con As Container Dim strSystemDB As String 'Obtain name of system file strSystemDB = SysCmd(acSysCmdGetWorkgroupFile) 'Open the System Database Set db = DBEngine(0).OpenDatabase(strSystemDB) 'Point to the Databases Collection Set con = db.Containers!Databases con.UserName = strGroupToProhibit con.Permissions = con.Permissions And Not dbSecDBCreate NoDBs_Exit: Set db = Nothing Set con = Nothing Exit Sub NoDBs_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description Resume NoDBs_Exit End Sub
The NoDBs routine receives the name of the user or group you will prohibit from creating databases. It opens the system database and points to the Containers collection. It then sets the permissions for the database to the existing permissions combined with Not dbSecDBCreate, thereby prohibiting the group or user from creating new databases.
You might not want to prohibit users from creating new databases. Instead, you might want to prevent them from creating new tables, queries, or other objects in your application or data database file. The code is similar to that required to prohibit users from creating new databases, as shown in Listing 33.22.
Example 33.22. Prohibiting users or groups from creating other objects.
Sub NoTables(strGroupToProhibit)
On Error GoTo NoTables_Err
Dim db As DATABASE
Dim con As Container
Dim strSystemDB As String
'Obtain name of system file
strSystemDB = SysCmd(acSysCmdGetWorkgroupFile)
'Point to the Current Database
Set db = CurrentDb
'Point to the Databases Collection
Set con = db.Containers("Tables")
con.UserName = strGroupToProhibit
con.Permissions = con.Permissions And Not dbSecDBCreate
NoTables_Exit:
Set db = Nothing
Set con = Nothing
Exit Sub
NoTables_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume NoTables_Exit
End SubThe difference between this code and the code in Listing 33.21 (which prohibits users from creating new databases) is that this code points the database object variable to the current database instead of to the system database. It then points the Container object to the Tables collection. Other than these differences, the code is identical to the NoDBs routine.
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 administer permission on a database in order 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
The advanced techniques you build into the Time and Billing application depend on how much responsibility you want to give the application for implementing security. You might want to implement security from outside the application instead of building it directly into the application. You can add all the forms contained in CHAP33EX.MDB directly into the Time and Billing application if you want. Also, you can add the code in the AutoExec routine (covered in the section “Ensuring That Users Have Passwords”) into the Time and Billing application so that you force users running the application to assign themselves a password.
The security features available in Access are extremely rich and powerful. 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 by 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 by 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 into your application using VBA code. This chapter illustrated how you can build all aspects of security directly into the user interface of your application.