Microsoft Office 2000/Visual Basic Programmer's Guide

Managing Security Accounts

The Users collection of a Catalog object contains all users defined for the workgroup information file specified for the Connection object that is used to open the Catalog object. Similarly, the Groups collection contains all groups in that workgroup information file. By manipulating these collections, you define and control the security accounts that Microsoft Jet uses. The Groups and Users collections are interesting in that they are self-referencing — the Users collection contains a Groups collection, and the Groups collection contains a Users collection. By using this structure, you can easily determine which users belong to which groups, and which groups contain which users.

There are a few things to keep in mind when you're working with users and groups:

• To add a new user or group, you first create the user or group by passing strings for the Name and SID properties for the user or group you want to create to the Append method of the Users or Groups collection. (You can also create a User or Group object, set its Name and SID properties, and then pass the object itself to the Append method.)

  Note   Even though the ADOXL User and Group objects use an SID property to create a user or group, the value you pass as the SID property to create a user or group for a Jet engine database is really a personal ID (PID) string. In Microsoft Jet, the user or group's SID is created by passing the user's or group's name and PID string to an encryption routine that stores the generated SID in the workgroup information file. For more information about Microsoft Jet SIDs, see "Users and Groups" earlier in this chapter.

  After you've created a new User object, you must also add the new user to one or more groups. Similarly, after you've created a new Group object, you must add users to that group. Because each User object has its own Groups collection, and each Group object has its own Users collection, you can work with either collection. You can add a user to a group by appending the user's name to the Users collection of that group, or you can "add a group to a user" by appending the name of the group to the Groups collection for that user.

• When you use the Access user interface to add a new user, that user is automatically added to the default Users group. However, if you add a user programmatically, you must explicitly add the user to the Users group. That is, you must append the new user's name to the Users collection of the default Users group, or append the name of the default Users group ("Users") to the Groups collection of the new User object.
  
  
• If you're using ADOX to manipulate users and groups in an application other than Access, you must set the Jet OLEDB:System Database property in the Properties collection of the ADO Connection object to the path and file name of the workgroup information file you want to work with before opening the database. (Or pass the Jet OLEDB:System Database property in the ConnectionString property when you use the Open method of the Connection object.) Otherwise, Microsoft Jet will not know which workgroup information file you want to update. In Access, you can work with the connection to the current database by using the Connection property of the CurrentProject object. The Connection property of the CurrentProject object returns the connection string used to open the current database. The string includes the Jet OLEDB:System Database property, which is set to the workgroup information file that is active when the database is opened. Just as when you are working with ADO from other Office applications, you can also explicitly open a separate Connection to another database and specify the workgroup information file when doing so.

The following examples illustrate how to create and manage user accounts. These examples are available in the modSecurity module in AccessSecurity.mdb in the ODETools\V9\Samples\ODETools\V9\Samples\OPG\Samples\CH18 subfolder on the Office 2000 Developer CD-ROM.

The following procedure is designed to be used within Access after logging on and opening a database. It creates a new user account, and then appends it to the default Users group in the workgroup information file that is used for the current database.

Sub CreateUserInAccess(strUser As String, _
                       strPID As String, _
                       Optional strPwd As String)
   Dim catDB As ADOX.Catalog

   Set catDB = New ADOX.Catalog
   With catDB
      ' Open Catalog object by using connection to the current database.
      .ActiveConnection = CurrentProject.Connection
      ' Create new user account.
      .Users.Append strUser, strPwd, strPID
      ' Append new user account to default Users group.
      .Groups("Users").Users.Append strUser
   End With

   ' Close Catalog object.
   Set catDB = Nothing
End Sub

The next procedure creates a new user by opening another database while using the specified workgroup information file (system database), and logging in by using the specified user account and password. This procedure can be used from Access or any other Office application that supports VBA.

Sub CreateUserInSystemDB(strDB As String, _
                         strSystemDb As String, _
                         strUserID As String, _
                         strUserIDPwd As String, _
                         strNewUser As String, _
                         strPID As String, _
                         Optional strNewUserPwd As String)
   Dim catDB As ADOX.Catalog
   Dim cnnDB As ADODB.Connection

   ' Open connection to database by using specified system database, user ID,
   ' and password.
   Set cnnDB = New ADODB.Connection
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Jet OLEDB:System Database") = strSystemDb
      .Properties("User ID") = strUserID
      .Properties("Password") = strUserIDPwd
      .Open strDB
   End With

   Set catDB = New ADOX.Catalog
   With catDB
      ' Open Catalog object by using cnnDB.
      .ActiveConnection = cnnDB
      ' Create new user account.
      .Users.Append strNewUser, strNewUserPwd, strPID
      ' Append new user account to default Users group.
      .Groups("Users").Users.Append "MyUser"
   End With

   ' Close Catalog object.
   Set catDB = Nothing
   ' Close connection.
   cnnDB.Close
   Set cnnDB = Nothing
End Sub

The process of creating a new group is nearly identical to the one used to create a new user. The following procedure creates a new group from within Access after you log on and open a database. To create a new group for a database other than the current database, or from another Office application, open a connection to that database as shown in the previous example and pass this connection to the ActiveConnection property of the Catalog object.

Sub CreateGroupInAccess(strGroup As String, _
                        strPID As String)
   Dim catDB As ADOX.Catalog

   Set catDB = New ADOX.Catalog
   With catDB
      ' Open Catalog object by using connection to the current database.
      .ActiveConnection = CurrentProject.Connection
      ' Create new group.
      .Groups.Append strGroup, strPID
   End With
   ' Close Catalog object.
   Set catDB = Nothing
End Sub

To add a new user account to an existing group, simply append that user's name to the Users collection of that group. The following procedure is designed to add a new user account to a group from within the current Access database.

Sub AddUserToGroupInAccess(strUser As String, _
                           strGroup As String)
   Dim catDB As ADOX.Catalog

   Set catDB = New ADOX.Catalog
   With catDB
      ' Open Catalog object by using connection to the current database.
      .ActiveConnection = CurrentProject.Connection
      ' Add strUser to strGroup.
      .Groups(strGroup).Users.Append strUser
   End With
   ' Close Catalog object.
   Set catDB = Nothing
End Sub

To delete a user or group account, use the Delete method of the Users or Groups collection. The following example deletes a user account from within the current Access database.

Sub DeleteUserInAccess(strUser As String)
   Dim catDB As ADOX.Catalog

   Set catDB = New ADOX.Catalog
   With catDB
      ' Open Catalog object by using connection to the current database.
      .ActiveConnection = CurrentProject.Connection
      ' Delete strUser.
      .Users.Delete strUser
   End With
   ' Close Catalog object.
   Set catDB = Nothing
End Sub

Keep the following additional points in mind when you are working with User and Group objects:

• If a user or group account is deleted from the workgroup information file, any object that has specific permissions for that user or group retains those permissions; that is, those permissions are still stored with that object in the database. If that user or group is re-created by using the same user name and PID, those permissions will become active again. For this reason, you should delete a user’s or group’s permissions before deleting the account.
  
  
• Not everyone has permissions to view user and group information. Generally, it’s best to be logged on as an administrator account — that is, as a user who is a member of the Admins group — before attempting to retrieve this information. This is also true if you want to modify security settings: You must be logged on as a user with sufficient permissions.