6.8 Back Up Selected Objects to Another Database

10.5 Programmatically Track Users and Groups

10.5.1 Problem

As the database administrator, you want to be able to track users and their groups within your workgroup. You know you can use Tools figs/u2192.gif Security figs/u2192.gif Print Security to print a report of users and groups, but you'd like to be able to use that information as part of the applications you write. How can you gather the information you need?

10.5.2 Solution

Using Data Access Objects (DAO), you can retrieve all the information you need about users' names and groups. Once you have that information, you can use it in creating your applications.

The sample form frmUserGroups in 10-05.MDB fills tables with the information you need and presents it to you in a list box. To test it, open and run frmUserGroups. Figure 10-20 shows the form in use for a sample workgroup.

Figure 10-20. frmUserGroups shows users and groups for a sample workgroup

figs/acb_1020.gif

To gather this information in your own applications, follow these steps:

  1. Create the tables you'll need to hold the information. Either import the three tables from 10-05.MDB, or use the information in Table 10-13 to create your own.

     

    Table 10-13. Table layouts for gathering user/group information

    Table name

    Field name

    Field type

    Primary key?

    tblGroups

    Group

    Text

    No

     

    GroupID

    Counter

    Yes

    tblUserGroups

    UserID

    Number (Long Integer)

    Yes

     

    GroupID

    Number (Long Integer)

    Yes

    tblUsers

    UserName

    Text

    No

     

    UserID

    Counter

    Yes

     

  2. If you created your own tables in Step 1, you'll need to add an index to tblGroups. In the Indexes properties sheet (available by choosing View figs/u2192.gif Indexes when tblGroups is open in design mode), add a row as described in Table 10-14 for the index properties. Table 10-14 also shows the primary key row that should already exist in the Indexes properties sheet.

     

    Table 10-14. Index settings for tblGroups

    Index name

    Field name

    Sort order

    Group

    Group

    Ascending

    PrimaryKey

    GroupID

    Ascending

     

  3. Either import the module basListUsers from 10-05.MDB, or enter the following code into a global module. This is the code you'll use to fill the three tables you just created:

    Public Sub acbListUsers(  )    ' Create tables containing all the users    ' and groups in the current workgroup.    '    ' The results will be in tblUsers, tblGroups,    ' and tblUserGroups. Run qryUserGroups to see    ' the sorted list.        Dim db As DAO.Database    Dim wrk As DAO.Workspace    Dim rstUsers As DAO.Recordset    Dim rstGroups As DAO.Recordset    Dim rstUserGroups As DAO.Recordset    Dim usr As User    Dim intI As Integer    Dim intJ As Integer    ' Set up object variables.    Set wrk = DBEngine.Workspaces(0)    Set db = wrk.Databases(0)    Set rstUsers = db.OpenRecordset("tblUsers")    Set rstGroups = db.OpenRecordset("tblGroups")    Set rstUserGroups = db.OpenRecordset("tblUserGroups")    ' Refresh the Users and Groups collections so we see    ' any recently added members.    wrk.Users.Refresh    wrk.Groups.Refresh        ' Clear out the old values.    db.Execute "Delete * From tblUserGroups"    db.Execute "Delete * From tblUsers"    db.Execute "Delete * From tblGroups"        ' Build up a list of all the groups in tblGroups.    For intI = 0 To wrk.Groups.Count - 1       With rstGroups          .AddNew             !Group = wrk.Groups(intI).Name          .Update       End With    Next intI        ' Loop through all the users, adding rows    ' to tblUsers and tblUserGroups.    For intI = 0 To wrk.Users.Count - 1       ' Add a user to tblUsers.       Set usr = wrk.Users(intI)       With rstUsers          .AddNew             !UserName = usr.Name          .Update          .Move 0, .LastModified       End With       ' Now loop through all the groups to which that user       ' belongs, hooking up the rows in tblUserGroups.       For intJ = 0 To usr.Groups.Count - 1          rstGroups.Index = "Group"          rstGroups.Seek "=", usr.Groups(intJ).Name          With rstUserGroups             If Not .NoMatch Then                .AddNew                   !UserID = rstUsers!UserID                   !GroupID = rstGroups!GroupID                .Update             End If          End With       Next intJ    Next intI        rstUsers.Close    rstGroups.Close    rstUserGroups.Close End Sub
  4. Either import the query qryUserGroups from 10-05.MDB, or create a new query, as follows. When Access asks you to add a table, just close the dialog. In design mode, click on the SQL button on the toolbar and enter the following expression:

    SELECT tblUsers.UserName, tblGroups.Group FROM tblUsers INNER JOIN (tblGroups INNER JOIN tblUserGroups  ON tblGroups.GroupID = tblUserGroups.GroupID)  ON tblUsers.UserID = tblUserGroups.UserID ORDER BY tblUsers.UserName, tblGroups.Group;

    Then save the query as qryUserGroups.

  5. To produce the current list of users and groups, execute the code in acbListUsers. You can call it directly, use a button whose Click event calls the procedure, or call it from the debug window. (The sample form calls acbListUsers from the Click event of the cmdRequery button on the form.) Once you've executed that code, you'll have filled in the three tables. You can use qryUserGroups to retrieve the information you need, or create your own queries based on the three tables.

10.5.3 Discussion

This solution relies on the DAO object model to gather its information. The DBEngine object is at the root (the highest level) of the DAO object hierarchy, and it has a single collection, the Workspaces collection. Each workspace represents a session of the Access database engine (and unless you're writing sophisticated applications, you'll most likely never see more than a single concurrent workspace). The default workspace contains information about the collection of open databases (only one is open in the user interface all others must be opened via VBA code) along with the available user and group collections. These are the collections you'll need for filling tables with the usernames and their groups. The code in the acbListUsers subroutine does all the work.

The acbListUsers function starts out by setting up object variables to refer to several recordset objects, and refreshes the Users and Groups collections of the workspace. This is necessary to make sure we see any recent changes to these collections made via the Access user interface or by another Access session. The relevant code is:

Set wrk = DBEngine.Workspaces(0) Set db = wrk.Databases(0) Set rstUsers = db.OpenRecordset("tblUsers") Set rstGroups = db.OpenRecordset("tblGroups")    Set rstUserGroups = db.OpenRecordset("tblUserGroups") ' Refresh the Users and Groups collections so we see ' any recently added members. wrk.Users.Refresh wrk.Groups.Refresh

The next step entails deleting all the existing rows in the three tables, using the Execute method of the database object:

db.Execute "Delete * From tblUserGroups" db.Execute "Delete * From tblUsers" db.Execute "Delete * From tblGroups"

Once these lines of code have executed, the three tables will be empty.

The next step is to build up a list of all the groups. This is accomplished by looping through all the elements of the workspace's Groups collection. Just like all other collections in Access, the Groups collection provides a Count property indicating how many elements it contains. These items are numbered from 0 through Count-1, and we loop through them all, adding a row to tblGroups for each group in the collection:

' Build up a list of all the groups in tblGroups. For intI = 0 To wrk.Groups.Count - 1    With rstGroups       .AddNew          !Group = wrk.Groups(intI).Name       .Update    End With Next intI

Once tblGroups is filled in, we do the same for users. Just as the workspace contains a collection of groups, it also contains a collection of users. We can walk through the Users collection, adding a row at a time to tblUsers, as shown here:

' Loop through all the users, adding rows ' to tblUsers and tblUserGroups. For intI = 0 To wrk.Users.Count - 1    ' Add a user to tblUsers.    Set usr = wrk.Users(intI)    With rstUsers       .AddNew          !UserName = usr.Name       .Update       .Move 0, .LastModified    End With    ' See the following code example... Next intI

Once a user is added, rows are added to tblUserGroups for each group that contains the current user. This is accomplished by enumerating through the Groups collection for the current user. (Note that there was a choice here. Each member of the workspace's Users collection has its own Groups collection, listing the groups to which it belongs, and each member of the workspace's Groups collection has its own Users collection, listing the members of the group. The code can either walk through the users, looking at the Groups collection in each, or walk through the groups, looking at the Users collection in each. This example walks through the workspace's Users collection, one at a time, studying the Groups collection in each one.) The following code loops through every item in the user's Groups collection, finding the matching name in tblGroups, and then adding a row to tblUserGroups containing both the user's UserID field (from tblUsers) and the GroupID field (from tblGroups). This way, tblUserGroups contains a single row for every user/group pair. The code is:

' Now loop through all the groups to which that user ' belongs, hooking up the rows in tblUserGroups. For intJ = 0 To usr.Groups.Count - 1    rstGroups.Index = "Group"    rstGroups.Seek "=", usr.Groups(intJ).Name    With rstUserGroups       If Not .NoMatch Then          .AddNew             !UserID = rstUsers!UserID             !GroupID = rstGroups!GroupID          .Update       End If    End With Next intJ

Once the code has looped through all the users and all the groups to which each user belongs, it closes all the objects:

rstUsers.Close rstGroups.Close rstUserGroups.Close

Now tblUsers, tblGroups, and tblUserGroups contain information about each user and the groups to which he or she belongs.

Once you've filled the three tables, you can easily perform lookups in your Access Basic code or create reports displaying security settings. You could also just lift pieces of the code from acbListUsers for use in your own applications. The next solution shows a much simpler function, acbAmMemberOfGroup, which uses a similar technique to query on the fly if the current user is a member of a specific group.

The acbListUsers procedure is not production-quality code. To keep it simple, we left out the error-handling code, and any procedure of this nature that manipulates tables must include sufficient error-handling capabilities. Though it's not likely, some other user may have locked the output tables or, worse, deleted them, or you may not have permissions for the system tables you need in order to gather this information. In a production environment, it's best to trap errors and handle them.

In the list of users found in tblUsers, notice that there are two users that you might not have seen before: Creator and Engine. These two users are created by the Jet engine itself and cannot be used or manipulated by VBA code. As you'll see in Section 10.7.2, you can create a Workspace object for any normal user, allowing that user to log into a new session of the Jet engine, but you can't use Creator or Engine to create new workspace objects. It's a good thing, too! Since neither can have a password (their passwords are always blank), this would otherwise provide a security breach. Because you can neither log on manually nor log on using the CreateWorkspace method with either user, these two special users don't pose a security risk.

Once you know how to enumerate through collections, as shown in this solution, you should be able to apply the same techniques to other database collections and their objects. For more information, see Chapter 4.



Access Cookbook
Access Data Analysis Cookbook (Cookbooks)
ISBN: 0596101228
EAN: 2147483647
Year: 2005
Pages: 174

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net