| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.