MANAGING SECURITY THROUGH CODE

Theory is covered, user interfaces are covered, tools are covered, and several pitfalls are covered. Now it's time to start manipulating security through code. The rest of this chapter is dedicated to providing you with several code examples that you can use to help manipulate Access security.

Using DAO to Program Security

When it comes to programming security, you must understand the relative position of security in the Data Access Objects (DAO) hierarchy. If you have a good handle on DAO, creating a new user programmatically is just as easy as creating a recordset programmatically. Chapter 5, “Working with Data Access Objects,” discusses DAO in detail. Figure 21.12 shows the Users and Groups as they are in position in the Data Access Objects object model.

It wasn't until version 2.0 that Access provided programmatic access to security. Before DAO, there were SendKeys and DoMenuItems. (As I mentioned earlier in this chapter, the very first Security Wizard was a giant SendKeys looping structure.) Access has come a long way in providing programmatic security features for power programmers.

The User and Groups Collections

The Users and Groups collections reside off the Workspace object. It's at this level that the following functions can be performed:

• Creating and deleting users

• Creating and deleting groups

• Adding, removing, and changing passwords

• Adding and removing users in groups

Permissions and the Documents Collection

Permissions are stored for each object in the Documents collection of a container object. It's at this level that the following functions can be performed:

• Changing the owner of an object

• Setting database permissions

• Adding, removing, and changing object permissions

Creating a New User Through Code

Listing 21.1 shows how to programmatically create a new user by using the ap_CreateUser() function. This function accepts two or three parameters: Name, PID, and optionally Password.

Listing 21.1. Chap21.mdb: Creating a User

Function ap_CreateUser(strUserName, strUserPID, Optional strUserPassword)
'Note that the password parameter is optional.
'This is a new power programming technique brought to Access via VBA.

   On Error GoTo ErrCreateUser

   Dim usrNewUser As User
   Dim wspNewWorkspace As Workspace

   Set wspNewWorkspace = DBEngine(0)

   'The next line creates the user account with the parameters passed in.
   Set usrNewUser = wspNewWorkspace.CreateUser(strUserName, strUserPID, _
      strUserPassword)

   'The next line makes the user available this line is required.
   wspNewWorkspace.Users.Append usrNewUser

   'This next line is optional but is STRONGLY suggested
   'to follow the Microsoft Access User Interface model
   'of adding all new users to the Users group.
   usrNewUser.Groups.Append wspNewWorkspace.CreateGroup("Users")

   'Function successful
   Exit Function

ErrCreateUser:
   MsgBox "Function 'ap_CreateUser' did not complete successfully."
   Exit Function
End Function

As you can see from Listing 21.1, you have two choices when you call this function. For example, you can pass a name and a PID parameter:

=ap_CreateUser("Chris", "123abc")

Or you can provide the optional password parameter:

ap_CreateUser("Jonathan","999zzz888YYY", "MySecret")

The first example creates a user named Chris with no password; the second example creates a user named Jonathan with a password of MySecret.

Deleting a User Through Code

Listing 21.2 shows how to programmatically delete an existing user. This function accepts one parameter: the name of the user you want to delete.

Listing 21.2. Chap21.mdb: Deleting a User

Function ap_DeleteUser(strUserName)

    On Error GoTo errDeleteUser

    Dim strUser As String
    Dim wspNewWorkspace As Workspace

    Set wspNewWorkspace = DBEngine(0)

    'The next line gets the name of the user passed in as a parameter.
    strUser = wspNewWorkspace.Users(strUserName).Name

    'The next line actually deletes the user.
    wspNewWorkspace.Users.Delete strUser

    'Function successful
    Exit Function

errDeleteUser:
    MsgBox "Function 'ap_DeleteUser' did not complete successfully."
    Exit Function

End Function

You would use the code in Listing 21.2 to delete a user in the following fashion:

=ap_DeleteUser("Ryan")

Notice that no PID or password is required when deleting a user. However, you have to be logged on as a member of the Admins group to delete a user. This is also true for creating a user.

Setting the Database Password Through Code

Listing 21.3 shows how to programmatically set the database password for a share-level security model. This function accepts two parameters: the new password and the previous password.

Listing 21.3. Chap21.mdb: Setting a Database Password

Function ap_DatabasePassword(strDBPassword, strDBPrevPass)
'strDBPassword is the new password for the database.
'strDBPrevPass is the previous password for the database.

   On Error GoTo errDatabasePassword

   Dim db As DATABASE
   Set db = CurrentDb()

       'This line adds the new password to the database.
       db.NewPassword strDBPrevPass, strDBPassword

   'Function successful
   Exit Function

errDatabasePassword:
   MsgBox "Function 'ap_DatabasePassword' did not complete successfully."
   Exit Function

End Function

In Listing 21.3, if you're setting the database password for the first time, you pass in an empty string for the previous password parameter—for example,

=ap_DatabasePassword("MyNewPass", "")

This function is also useful if you want to clear the database password. You simply pass, through the function's arguments, in the proper previous password and the empty string for the new password—for example,

=ap_DatabasePassword("", "OldPassword")

Creating a Group Through Code

Listing 21.4 shows how to programmatically create a new group. This function accepts two parameters: the name of the group and a PID.

Listing 21.4. Chap21.mdb: Creating a Group

Function ap_CreateGroup(strGroupName, strGroupPID)
'Pass in the group name and group PID as string parameters.

  On Error GoTo errCreateGroup

  Dim usrNewGroup As Group
  Dim wspNewWorkspace As Workspace

  Set wspNewWorkspace = DBEngine(0)

  'The next line creates the group account with the parameters passed in.
  Set usrNewGroup = wspNewWorkspace.CreateGroup(strGroupName, strGroupPID)

  'The next line makes the user available this line is required.
  wspNewWorkspace.Groups.Append usrNewGroup

  'Function successful
  Exit Function

errCreateGroup:
  MsgBox "Function 'ap_CreateGroup' did not complete successfully."
  Exit Function

End Function

This function is very similar to the ap_CreateUser() function, except that you don't need to worry about appending it to the Users collection. Also, there are no passwords for groups. Here is an example of how this function might be used:

=ap_CreateGroup("Accountants", "$Money$")

This would create a group named Accountants.

Deleting a Group Through Code

Listing 21.5 shows how to programmatically delete an existing group. This function accepts one parameter: the name of the group you want to delete.

Listing 21.5. Chap21.mdb: Deleting a Group

Function ap_DeleteGroup(strGroupName)
'This function takes one parameter, the name of the group to be deleted.
    On Error GoTo errDeleteGroup

    Dim strGroup As String
    Dim wspNewWorkspace As Workspace

    Set wspNewWorkspace = DBEngine(0)

    'The next line gets the name of the group passed in as a parameter.
    strGroup = wspNewWorkspace.Groups(strGroupName).Name

    'The next line actually deletes the group.
    wspNewWorkspace.Groups.Delete strGroup

    'Function successful
    Exit Function

errDeleteGroup:
    MsgBox "Function 'ap_DeleteGroup' did not complete successfully."
    Exit Function

End Function

This function has almost the exact same syntax as deleting a user. You just provide it a group name, and it will delete the group. Remember that you must be a member of the Admins group for this code to run correctly.

Here's an example of how to call this function:

=ap_DeleteGroup("Managers")

Adding a User to a Group Through Code

Listing 21.6 shows how to programmatically add a user to a group. This function accepts two parameters: the name of the user and the name of the group you want to add the user to.

Listing 21.6. Chap21.mdb: Adding a User to a Group

Function ap_AddUserToGroup(strUserName, strGroupName)
'Two parameters; the user name and the group name

    On Error GoTo errAddUserToGroup

    Dim grpGroup As Group
    Dim wspWorkspace As Workspace

    Set wspWorkspace = DBEngine(0)
    Set grpGroup = wspWorkspace.Groups(strGroupName)

    'This line actually adds the user to the group.
    grpGroup.Users.Append grpGroup.CreateUser(strUserName)

    'Function successful
    Exit Function

errAddUserToGroup:
    MsgBox "Function 'ap_AddUserToGroup' did not complete successfully."
    Exit Function

End Function

This function can be used as follows:

=ap_AddUserToGroup("Jennifer", "Employees")

In this example, user Jennifer would be added to the group Employees. Both Jennifer and Employees must exist for this function to complete successfully.

Removing a User from a Group Through Code

Listing 21.7 shows how to programmatically remove a user from a group. This function accepts two parameters: the name of the user and the name of the group you want to remove the user from.

Listing 21.7. Chap21.mdb: Removing a User from a Group

Function ap_RemoveUserFromGroup(strUserName, strGroupName)
'Two parameters, a user and a group
    On Error GoTo errRemoveUserFromGroup

    Dim strGroup As String
    Dim usrUser As User
    Dim wspWorkspace As Workspace

    Set wspWorkspace = DBEngine(0)
    Set usrUser = wspWorkspace.Users(strUserName)

    'The following two lines perform the deletion of the user from the
    'group given the passed in parameters.
    strGroup = usrUser(strGroupName).Name
    usrUser.Groups.Delete strGroupName

    'Function successful
    Exit Function

errRemoveUserFromGroup:
 MsgBox "Function 'ap_RemoveUserFromGroup' did not complete successfully."
 Exit Function

End Function

In Listing 21.7, when you pass in the user and group as parameters, both must previously exist or the function won't complete successfully. Here's an example of the syntax to remove a user from a group with this function:

=ap_RemoveUserFromGroup("Ryan", "Managers")

Changing the Owner of an Object Through Code

Listing 21.8 shows how to programmatically change the owner of an object. This function accepts three parameters: the new owner name, the object name, and the type of object. The new owner name can be a user or a group. Valid object types are tables, queries, forms, reports, and scripts.

Listing 21.8. Chap21.mdb: Changing the Owner of an Object

Function ap_ChangeOwner(strNewOwner, strObjectName, strObjectType)
'This function requires an object type to be passed in because
'more then one object can have the same name (i.e. a Form and a
'Table named Employees).

'Valid object types include: Tables, Queries, Forms; Reports,
'and Scripts.

    On Error GoTo errChangeOwner

    Dim db As Database
    Dim con As Container
    Dim doc As Document

    'The next three lines of code takes the object type and
    'object name parameters to find the object.
    Set db = CurrentDb
    Set con = db.Containers(strObjectType)
    Set doc = con.Documents(strObjectName)

    'Test to see if the user has permissions
    'to perform this operation, if true then changes ownership
    If (doc.AllPermissions And dbSecWriteOwner) <> 0 Then
        doc.Owner = strNewOwner
    Else
        MsgBox "You don't have permissions to change the" _
           & " ownership of object: " & strObjectName
        Exit Function
    End If

    'Function successful
    Exit Function

errChangeOwner:
    MsgBox "Function 'ap_ChangeOwner' did not complete successfully."
    Exit Function

End Function

Many things can be noted about the ap_ChangeOwner() function. First, remember that groups can own objects; they just can't create them. So the parameter strNewOwner can be an existing user or group.

Also note that it uses the AllPermissions property and the dbSecWriteOwner security constant to determine whether the user has permissions to perform this operation. (For more information about the AllPermissions property and security constants, consult the Answer Wizard in online help.)

The last thing to note about this function is its third parameter. If you want to change the owner of a macro, you must use the keyword Scripts.

Setting Permissions for an Object Through Code

Listing 21.9 shows how to programmatically set permissions for an object. This function accepts three parameters: the name for which you want the permissions set, the object name, and the type of object. The name can be a user or a group. Valid object types are tables, queries, forms, reports, and scripts.

Listing 21.9. Chap21.mdb: Setting the Permissions of an Object

Function ap_AssignPermission(strUserName, strObjectName, strObjectType, _
  flgPermission)
'This function requires an object type to be passed in because
'more then one object can have the same name (i.e. a Form and a
'Table named Employees).

'Valid object types include: Tables, Queries, Forms; Reports,
' and Scripts.

'strUserName can be any valid user or group.
'flgPermission can be any of security permission constants

   On Error GoTo errAssignPermission

   Dim db As DATABASE
   Dim con As Container
   Dim doc As Document

   'The next three lines of code takes the object type and
   'object name parameters to find the object.
   Set db = CurrentDb
   Set con = db.Containers(strObjectType)
   Set doc = con.Documents(strObjectName)

   'The next line of code sets the user (or group).
   doc.UserName = strUserName
   'The next line of code does the actual sending of the permission.
   doc.Permissions = doc.Permissions Or flgPermission

   'Function successful
   Exit Function

errAssignPermission:
   MsgBox "Function 'ap_AssignPermission' did not complete successfully."
   Exit Function

End Function

The ap_AssignPermissions() function takes four parameters. The first parameter is the user name you want to assign permissions to, followed by the object name, the object type, and the security permission constant you want to assign. Here's an example:

=ap_AssignPermission("Scott", "[Chapter 21]", "Forms", acSecModWriteDef)

In this example, the user Scott would get Write Definition permissions on the Chapter 21 form.

Checking Permissions Through Code

Listing 21.10 shows how to programmatically check to see whether a user has permissions to a specific object. This function accepts three parameters: the user name, the object name, and the object type. Valid object types are tables, queries, forms, reports, and scripts.

Listing 21.10. Chap21.mdb: Checking Permissions

Function ap_CheckPermissions(strUserName, strObjectName, strObjectType, _
  flgPermission)
'Valid object types include: Tables, Queries, Forms, Reports,
'and Scripts.

'flgPermission is any legal security permission constants.
'These constants will vary depending on the object type.

   On Error GoTo errCheckPermissions

   Dim db As DATABASE
   Dim con As Container
   Dim doc As Document

   'The next three lines of code takes the object type and
   'object name parameters to find the object.
   Set db = CurrentDb
   Set con = db.Containers(strObjectType)
   Set doc = con.Documents(strObjectName)

   'The next line of code sets the user
   doc.UserName = strUserName

   ' Check if flag against the AllPermissions property.
   If (doc.AllPermissions = flgPermission) Then
       ap_CheckPermissions = True
   Else
       ap_CheckPermissions = False
   End If

   'Function successful
   Exit Function

errCheckPermissions:
   MsgBox "Function 'ap_CheckPermissions' did not complete successfully."
   Exit Function

End Function

The code in Listing 21.10 takes four parameters to allow this function to be as flexible as possible. You can check different permissions for different users on different objects. Here's an example of how this function might be called:

=ap_CheckPermissions("Ryan", "[Chapter 21]", "Forms", dbSecFullAccess)

In this example, the function would return a True or False if the user Ryan had Administer permissions on the Chapter 21 form.

Determining Who You're Logged On As Through Code

Often, developers want to know who they're logged on as. This is extremely useful information when you're creating a secured application and testing many of the different user accounts. Listing 21.11 shows how to programmatically detect what user you're logged on as by using the CurrentUser function provided by VBA.

Listing 21.11. Chap21.mdb: Determining Who You're Logged On As

Function ap_WhoAmI()
    Dim Title As String

    Title = "Who Am I?"

    MsgBox "You are logged in as: " & CurrentUser, , Title

End Function

This little code example is handy to have around. Again, it's nothing that can't be done through the Access user interface: You can always see who you're logged on as from the Tools menu by choosing Security and User and Group Accounts and then clicking the Change Logon Password tab.

Denying Users the Ability to Create Databases

Listing 21.12 is one of the most powerful coding examples in all of security. This function can be performed only through code; Access doesn't provide a way through the user interface to perform this operation. The code shows how to programmatically remove a user's ability to create a database. This function takes one parameter: the name of the user (or group) you want to deny database creation permissions.

Listing 21.12. Chap21.mdb: Denying Database Creation Permissions

Function ap_CanNotCreateDatabase(strName)
'For this function you can pass in any valid User or Group Name.

    On Error GoTo errCanNotCreateDatabase

    Dim dbSystemDB As Database
    Dim conContainer As Container
    Dim strSysPath As String

    'The next line gets the path to the Workgroup Information File
    strSysPath = SysCmd(acSysCmdGetWorkgroupFile)
    'The next two lines opens the Workgroup Information File
    'and grabs the Databases object within the Containers collection
    Set dbSystemDB = DBEngine(0).OpenDatabase(strSysPath)
    Set conContainer = db.Containers!Databases

    'The next line of code sets the user or group name from the
    'passed in parameter
    conContainer.UserName = strName
    'The next line of code removes the permissions to create a database
    conContainer.Permissions = conContainer.Permissions _
    And Not dbSecDBCreate

   'Function successful
    Exit Function

errCanNotCreateDatabase:
    MsgBox "Function 'ap_CanNotCreateDatabase' did not complete " _
       & "successfully."
    Exit Function

End Function

Listing 21.12 shows the use of the SysCmd() function, which performs a multitude of tasks. In this example, SysCmd() is retrieving the path to the workgroup information file. For this function to work, it must modify permissions in the workgroup information file, not in the current database. You might also notice the use of another security constant, dbSecDBCreate.

The code in Listing 21.12 works only for your current workgroup information file. If users create their own or join a different workgroup information file, their user accounts in those workgroup information files will be able to create databases.

Denying the Creation of Table and Query Objects

Often, developers don't want users to create new table objects in their database and fill them with unnecessary data. Listing 21.13 shows you how to programmatically deny users the ability to create new table and query objects in your database. This function takes one parameter: the name of the user who's being denied creation rights.

Listing 21.13. Chap21.mdb: Denying Table and Query Creation

Function ap_CanNotCreateTables(strName)
'For this function you can pass in any valid User or Group Name.
'It denies the user from creating Tables and Queries all at once.

    On Error GoTo errCanNotCreateTables

    Dim db As DATABASE
    Dim con As Container

    Set db = CurrentDb
    Set con = db.Containers("Tables")

    con.UserName = strName

    'This next line of code removes the table (and query) creation
    'privilege from the specified user.
    con.Permissions = con.Permissions And Not dbSecDBCreate

    'Function successful
    Exit Function

errCanNotCreateTables:
 MsgBox "Function 'ap_CanNotCreateTables' did not complete successfully."
 Exit Function

End Function

The code in Listing 21.13 uses the database constant dbSecDBCreate to perform its task.

This function can be performed only through code; there's no equivalent Access user interface option. Remember that to make this fully effective, you must run it on the user account and all the group accounts to which that user belongs. Here's a syntax example of how this function might be used:

=ap_CanNotCreateTables("Jennifer")

Compacting, Encrypting, or Decrypting a Database Through Code

Listing 21.14 shows how to programmatically compact, encrypt, or decrypt a database. This function accepts three parameters: the current name of the database to be compacted, encrypted, or decrypted; the new name of the database; and the flag to tell it to encrypt or decrypt. The flag parameter is optional; if it's left blank, a compact will take place. The two valid options for the flag parameter are dbEncrypt and dbDecrypt.

Listing 21.14. Chap21.mdb: Compacting or Encrypting a Database

Function ap_CompactEncrypt(strCurrentDBName, strNewDBName, _
   Optional flgEncDec)
'Use dbEncrypt or dbDecrypt for the optional flag parameters
'Or leave blank for a compact.

    On Error GoTo errCompactEncrypt

    DBEngine.CompactDatabase strCurrentDBName, strNewDBName, _
    "", flgEncDec

    'Function successful
    Exit Function

errCompactEncrypt:
    MsgBox "Function 'ap_CompactEncrypt' did not complete successfully."
    Exit Function

End Function

You can compact a database, for example, in the following manner:

=ap_CompactEncrypt("c:Olddb.mdb", "Newdb.mdb")

This example takes the existing Olddb.mdb file and creates a new file named Newdb.mdb that is totally compacted.

Disabling the Bypass Key Through Code

Listing 21.15 shows how to programmatically disable the Shift key during startup of your Access application. This very important security function can be done only through code; there's no equivalent user interface option.

Listing 21.15. Chap21.mdb: Disabling the Shift Bypass Key

Function ap_DisableShift()
'This function will disable the shift at startup causing
'the Autoexec macro and Startup properties to always be executed

    On Error GoTo errDisableShift

    Dim db As DATABASE
    Dim prop As Property
    Const conPropNotFound = 3270
    Set db = CurrentDb()

    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False

'function successful
Exit Function

errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
       dbBoolean, False)
       db.Properties.Append prop
       Resume Next
    Else
       MsgBox "Function 'ap_DisableShift' did not complete successfully."
       Exit Function
    End If

End Function

Notice the error-handling routine in Listing 21.15, starting with the line

errDisableShift:

If the AllowByPassKey property doesn't exist—which it doesn't by default—the routine creates it and sets it to False.

A similar function provided in the Chap17.mdb file, ap_EnableShift(), enables the Shift key to be used again on database startup. The only difference from ap_DisableShift() is that False is changed to True when setting the AllowByPassKey property.