Previous Page TOC Index Next Page Home


31

Complex Security Issues

Controlling Security Via Code

You might not always be there 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 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 provide your administrative users with the ability to establish and maintain security for their workgroups. This involves building a front-end interface to all of the security functionality provided by Access. Behind the scenes, you can use DAO code to implement the security functionality.

Maintaining Groups Using Code

Chapter 29, "Database Security Made Easy," discusses the importance of creating logical groups of users and then assigning rights to those groups. The user 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.

Adding a Group

You add a group by appending the Group object to the Groups collection. The form shown in Figure 31.1 enables the user to add and remove groups. This form is called frmMaintainGroups and is included in the CHAP31EX.MDB database located on the sample code CD. The code under the Add button looks like this:

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 Sub


Figure 31.1. This form allows the administrative user to add and remove groups.

This 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 or not the group was created successfully.

The CreateGroups function looks like this:

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 see that a new group has been added to the workgroup (as can be viewed via the Tools|Security|User and Group Accounts menu).

Removing a Group

The code to remove a group is very similar to the code required to add a group. The code under the cmdRemove command button looks like this:

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 Sub

The routine ensures that the Group Name has been filled in. If it has, the RemoveGroups function is called. An appropriate message is displayed, indicating whether the group was removed successfully. The RemoveGroups function looks like this:

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 Function

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

Maintaining Users Using Code

Not only might you want to maintain groups using code, but you might also want to maintain users with code. You can employ User data access objects to create and manage user accounts at runtime. The form called frmMaintainUsers, shown in Figure 31.2, illustrates this process.


Figure 31.2. This form allows the administrative user to add and remove users.

Adding a User

You add a user by appending the User object to the Users collection. The form called frmMaintainUsers, also contained within CHAP31EX.MDB, contains a command button called cmdAddUsers that adds a user. The code looks like this:

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 Sub

This code checks to ensure that both the User Name and PID have been filled in. If so, the CreateUsers function is called. It looks like this:

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 is then applied to the Users collection of the workspace to add the user to the collection of users within the workspace.

Assigning a User to a Group

So far, you have added a user, but you have not given the user group membership. Next, let's take a look at how you can add a user to an existing group. The following code is found under the cmdAssign button on the frmMaintainUsers form.

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 Sub

This code ensures that both the txtUserName and txtGroup name are filled in. The code then calls the AssignToGroup function that attempts to assign the user to the specified group. The AssignToGroup function looks like this:

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 Function

This code creates three object variables: Workspace, Group, and User. The Workspace variable is pointed at the current workspace. The Group variable is pointed toward the group specified in the txtGroupName text box. The CreateUser method is used to point 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 must already exist 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.

Removing a User from a Group

Just as you will want to add users to groups, you will also want to remove them from groups. The following code is found under the cmdRevoke command button on the frmMaintainUsers form.

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 Sub

The code ensures that the name of the user and group have both been filled in on the form. If they have, the RevokeFromGroup function is called, which looks like this:

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 Function

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

Removing a User

Sometimes you want to remove a user entirely. The cmdRemove command button on the frmMaintainUsers form accomplishes this task.

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 Sub

This code needs only a user name to proceed. If a user name has been supplied, the RemoveUsers function is called. It looks like this:

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 Function

The RemoveUsers function issues the Delete method on the Users collection of the workspace. This deletes the user entirely.

Listing All Groups and Users

Figure 31.3 shows an enhanced version of the frmMaintainUsers form. It is called frmMaintainAll. The frmMaintainAll form, found within CHAP31EX.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.


Figure 31.3. This form allows the administrative user to maintain users, groups, and passwords.

Listing All Groups

The ListGroups function is the call-back function used to populate the cboGroups combo box. Call-back functions are covered in detail in Chapter 13, "Let's Get More Intimate with Forms: Advanced Techniques."

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 Function

The 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 call-back 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

Listing all users is very similar to listing all groups. The code looks like this:

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 Function

This code looks at the Count property of the Users collection of the Workspace object to determine how many users there are. The For Each usr In wrk.Users line loops through each user in the Users collection. The name of each user is used to populate the cboUsers combo box.

Working with Passwords

Many times, the administrative user needs to add, remove, or modify users' passwords. By using the user interface, you can only modify the password of the user currently logged in, but using code you can modify any user's password, as long as you have administrative rights to do so.

Assigning a Password to a User

The form called frmMaintainAll allows the administrative user to assign a password to the user selected in the combo box. The code looks like this:

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 Sub

This routine ensures that a user name has been entered. If it has, the AssignPassword function is called.

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 intentionally left blank. 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.

Listing Users without Passwords

Many times an administrative user simply wants to obtain a list of all of the users who do not have passwords. This list can be obtained quite easily using VBA code and data access objects. Figure 31.4 shows the form called frmMaintainPasswords, located in the CHAP31EX.MDB database. When the form is loaded, the list box uses a call-back function to display a list of all of the users who do not have passwords. The code looks like this:

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 Function


Figure 31.4. This form allows the administrative user to view users without passwords.

The 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 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 is therefore added to the list box.

Ensuring That a User Has a Password

You might want to ensure that users who log onto your application have a password. This can be accomplished with the following code:

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 Function

The AutoExec function can be called from the startup form of your application. It points a User object variable to the CurrentUser. It accomplishes this using the return value from the CurrentUser function as the user to look at within 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. In this case, if an error occurs, it means that the user has a password and nothing special needs to happen. In the absence of an error, you know that no password exists, so the user is prompted for a password. If the user does not supply one, the application quits. Otherwise, the new password is assigned to the user.


An example of the usefulness of this function can be found in the MustHavePass.MDB database located on your sample code CD.

Assigning and Revoking Permissions to Objects Using Code

Often, you will want to assign and revoke object permissions using code. Once again, this can be easily accomplished using DAO code. The form shown in Figure 31.5 is called frmTableRights. It is found in the CHAP31EX.MDB database.


Figure 31.5. This form allows the administrative user to assign rights to groups.

The following code assigns view rights for the table selected in the list of tables to the group selected from the group combo box:

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 within the table. A listing of the permission constants for queries and tables is listed in Table 31.1.

Permission Constant

Permission Description

dbSecReadDef

Grants read permission to the definition of the table or query.

dbSecWriteDef

Grants permission to alter the definition of the table or query.

dbSecRetrieveData

Grants permission to read data stored within the table or query. Also, it implicitly grants read permission to the definition of the table or query.

dbSecInsertData

Grants permission to insert new rows into the table or query.

dbSecDeleteData

Grants permission to delete rows from the table or query.

dbSecReplaceData

Grants permission to modify table or query data.

Notice that in following example, the dbSecRetrieveData constant is combined with the dbSecReplaceData constant using a bit-wise OR. The dbSecReplaceData constant does not imply that the user can also read the table definition and data. As you might guess, it is difficult to edit data if you cannot read it. It is therefore necessary that you combine the dbSecRetrieveData constant with the dbSecReplaceData constant in order to allow the user or group to read and modify table data.

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 Sub

Encrypting a Database Using Code

Chapter 29, "Database Security Made Easy," shows how you can encrypt a database using the user interface. If a database is not encrypted, it is not really secure because a savvy user can utilize 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 provide your user with the ability 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 Sub

This 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 four parameters: the name of the database to encrypt, the name for the new encrypted database, the collating order, and other options. The other options parameter is where you use a constant to indicate that you want to encrypt the database.

Accomplishing Field-Level Security Using Queries

In itself, Access does not provide field-level security. You are able to achieve field-level security using queries. 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 a user cannot read the tables underlying a query, the user 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. For example, the group called Staff has no rights to the table called tblEmployees. The group called Supervisors has Read Design and Modify permissions to the tblEmployees table. The query called qryEmployees is owned by the Supervisors group (see Figure 31.6). The query itself is shown in Figure 31.7. Notice in Figure 31.7 that the Run Permissions property has been set to Owner's. The resulting SQL appears in Figure 31.8. Notice the WITH OWNERACCESS OPTION clause at the end of the SQL statement. When any member of the Staff group, who therefore has no other rights to tblEmployees, runs the query, that member inherits the Supervisor group's capability to read and modify the table data.


Figure 31.6. The query owned by the Supervisors group.


Figure 31.7. The Design view of a query with Run Permissions set to Owners.


Figure 31.8. The SQL view of a query with Run Permissions set to Owners.

Denying Users or Groups the Ability to Create Objects

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 only be accomplished using VBA code.

Denying Users or Groups the Ability to Create Databases

Using data access objects, you can programmatically prohibit users from creating new databases. This is quite obviously a very powerful feature. The code looks like this:

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

Denying Users or Groups the Ability to Create Other Objects

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 within your application or data database file. The code is similar to that required to prohibit users from creating new databases:

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 Sub

The difference between this code and the code required to prohibit users from creating new databases is that this code points the database object variable at the current database rather than at 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.

Performing a Function Not Available to the User Currently Logged on to the System

Although you might not want a particular user to be able to accomplish some task, you might at times want to go "behind the scenes" and accomplish the task for them. For example, as you saw in the previous 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 yourself on as a different user, perform the process, and then log yourself off.

Security and Client/Server Applications

It is important to understand that security for client/server applications must be applied on the back-end database server. You can request a logon ID and password from the user 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 that are returned from the back-end must be handled by your application.

Security and Replication

Database security cannot be implemented on replicated databases. Only user-level security can be implemented. All of 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 do the following:

Practical Examples: Applying Advanced Techniques to Your Application

Which advanced techniques you choose to build into the Time and Billing application depends upon how much you want the application to be responsible for implementing security. You might want to implement security from outside the application rather than building it directly into the application. If you like, you can add all of the forms contained in CHAP31EX.MDB directly into the Time and Billing application. Furthermore, you can add the code within the AutoExec routine (covered in the section called "Ensuring That a User Has a Password") into the Time and Billing application so that you force users running the application to assign themselves a password.

Summary

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 over how you can implement security within your application.

The chapter began by showing how you can maintain users and groups using code. Next you learned how you can use code to assign and maintain passwords, as well as how you can determine whether a user has a password. Also covered in the chapter was how to assign and revoke permissions to objects using code, as well as many other advanced techniques that afford you full control over security within your application.

Security requires a lot of planning and forethought. Decisions must be made as to what groups will be defined and the rights that each group will be assigned. Furthermore, 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.

Previous Page TOC Index Next Page Home