DekGenius.com
[ Team LiB ] Previous Section Next Section

Recipe 10.7 List All Users with Blank Passwords

10.7.1 Problem

As database administrator, you need to ensure that every member of your workgroup has an Access password. You can use the NewPassword method to create a new password, and you understand why you can't retrieve the value of a user's password, but you need a way to find out whether a user has established a password yet. You'd like to create a list of all users, indicating which ones don't have passwords. How can you do this?

10.7.2 Solution

You can't retrieve users' passwords, but there's an easy way to find out if a user has a blank password: simply try to log onto the user's account using a blank password. If you succeed, you know the user has no password. With a lot of users this becomes a tiresome process, but fortunately, you can automate it using DAO and the CreateWorkspace method.

The frmUserPasswords form fills a table with a list of users and whether their passwords are blank and then presents this information to you in a list box. To test it, open and run frmUserPasswords from 10-07.MDB. Figure 10-21 shows the form in use for a sample workgroup.

Figure 10-21. frmUserPasswords shows users and password status for a sample workgroup
figs/acb2_1021.gif

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

  1. Create a table to hold the information. Either import the table tblUsers from 10-07.MDB, or use the information in Table 10-16 to create your own table. Figure 10-22 shows the table in design mode.

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

Field name

Field type

Primary key?

UserID

AutoNumber

Yes

UserName

Text

No

PasswordSet

Yes/No

No

Figure 10-22. The tblUsers table in design mode
figs/acb2_1022.gif
  1. Either import the module basFindBlank from 10-07.MDB, or enter the following code into a global module. This is the code you'll use to fill the table you just created.

    Public Sub acbFindBlankPasswords( )
        ' Fill tblUsers with list of users, and
        ' whether or not their password is blank.
        
        Dim intI As Integer
        Dim usr As DAO.User
        Dim db As DAO.Database
        Dim wrk As DAO.Workspace
        Dim wrkTest As DAO.Workspace
        Dim rst As DAO.Recordset
        Dim blnPwdUsed As Boolean
        Dim strUser As String
        
        Const acbcErrInvalidPassword = 3029
        
        ' Set up object variables.
        Set wrk = DBEngine.Workspaces(0)
        Set db = wrk.Databases(0)
        Set rst = db.OpenRecordset("tblUsers")
        
        db.Execute "DELETE * FROM tblUsers"
        
        On Error Resume Next
        ' Loop through all the users.
        For intI = 0 To wrk.Users.Count - 1
        Set usr = wrk.Users(intI)
        strUser = usr.Name
        
        ' Skip the two special users, since you can't log in
        ' as either of them via CreateWorkspace( ).
        If strUser <> "Creator" And strUser <> "Engine" Then
            ' Try to log in with a blank password. If this
            ' doesn't fail, the user has a blank password.
            Set wrkTest = DBEngine. _
             CreateWorkspace("Test", strUser, "")
            blnPwdUsed = (Err = acbcErrInvalidPassword)
            
            ' Add a new row to tblUsers, storing the user's
            ' name and whether or not they have a password.
            rst.AddNew
                rst("UserName") = strUser
                rst("PasswordSet") = blnPwdUsed
            rst.Update
            wrkTest.Close
        End If
        Next intI
        rst.Close
    End Sub
  2. To produce a list of all users whose passwords are blank, execute the code in acbFindBlankPasswords. You can call it from the debug window, or from an event procedure, as in frmUserPasswords. (If you decide to use frmUserPasswords, you must also create a query, qryUserPasswords, which sorts the rows in tblUsers in ascending order on the UserName field. This query fills the list box on the sample form.) You could create a report that pulls its rows from tblUsers as well, allowing you to prepare a regular report listing all users with blank passwords.

10.7.3 Discussion

acbFindBlankPasswords uses DAO to do most of its work. It starts by setting up the object variables it needs to retrieve and store the password information. It uses the Workspace object to loop through all the users (since the Workspace object provides the Users collection that you'll use), and the Recordset object refers to the table into which you'll write the new data:

Set wrk = DBEngine.Workspaces(0)
Set db = wrk.Databases(0)
Set rst = db.OpenRecordset("tblUsers")

You then need to clear out the previous contents of tblUsers, so that later code can fill in the table with the current list of users and their password status:

db.Execute "DELETE * FROM tblUsers"

The next step is to loop through the Users collection of the default Workspace object. For each user, the code attempts to create a new workspace, as shown here:

For intI = 0 To wrk.Users.Count - 1
   Set usr = wrk.Users(intI)
   '
   ' See the next code sample.
   '
Next intI

The final step is the important one. For each user, the code calls the CreateWorkspace method of the DBEngine object. To call this method, you must supply three parameters: the name for the new workspace (of course, since you only need the result of attempting to create the workspace, the actual name doesn't matter), the username, and the user's password. An empty string ("") is passed for the password. An error indicates that the current user has a password, since the new workspace could not be created using the blank password. If there was no error, then that user does not have a password.

The code checks whether an error occurred, comparing the Access built-in Err value with the known error value that occurs when you attempt to create a workspace with an invalid password. Regardless of whether an error occurred, the code adds a new row to tblUsers and stores the username along with the password status in the table. Here is the code for these steps:

' Skip the two special users, since you can't log in
' as either of them via CreateWorkspace( ).
If strUser <> "Creator" And strUser <> "Engine" Then
    ' Try to log in with a blank password. If this
    ' doesn't fail, the user has a blank password.
    Set wrkTest = DBEngine. _
     CreateWorkspace("Test", strUser, "")
    blnPwdUsed = (Err = acbcErrInvalidPassword)
    
    ' Add a new row to tblUsers, storing the user's
    ' name and whether or not they have a password.
    rst.AddNew
        rst("UserName") = strUser
        rst("PasswordSet") = blnPwdUsed
    rst.Update
    wrkTest.Close
End If

As discussed in the Solution in Recipe 10.5, the Users collection contains two users that are not actually part of your workgroup: Creator and Engine. Access creates these two users but doesn't allow you to log on as either one, either from the command line or by creating a new workspace. Therefore, the code just skips these special users, since we don't really care whether their passwords are blank.

If you intend to use acbFindBlankPasswords in a production environment, you may wish to add some error-handling code to the procedure. Any time you write to tables, you should include some method of dealing with errors. At the least, the user (which could well be yourself) should be alerted that an error has occurred and given some information about the error.

    [ Team LiB ] Previous Section Next Section