Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

The PeopleWatcher application uses code similar to the following to change its user interface depending on the privileges granted to the user. You can examine the complete configuration code in the ConfigureForUser subroutine on the compact disk.

If TheUser.HasPrivilege(“READ_BASIC”) Then 
    ‘ Do stuff for users with this privilege.
    :
End If

PeopleWatcher protects most fields from unauthorized users by hiding them, by setting their Editable properties to false, or by setting their Locked properties to true. These methods do not work for the EmployeeImage control. Image controls do not have Editable or Locked properties. Setting this control’s Enabled property to false does not prevent the user from generating double-click events and starting the picture selection process.

To keep unauthorized users from changing an employee’s picture, EmployeeImage’s double-click event handler uses the following code to see if the user has the UPDATE_BASIC privilege. If not, the subroutine exits silently.

If Not TheUser.HasPrivilege(“UPDATE_BASIC”) Then Exit Sub

GetUserID The GetUserID subroutine prompts the user for a user name and password. It then uses the ValidPassword function to encrypt the password and decide if the combination is valid. If it is, GetUserID sets the DBUser object’s public UserID variable so the application can read the newly entered user ID. If the user name and password combination is invalid, the DBUser object leaves the previous UserID value unchanged.

The ValidPassword function takes as parameters the user’s ID and password. It begins by using an SQL statement to fetch the user’s salt values and the correctly encrypted password from the database. The salt values are stored in the database fields salt1 through salt8. The correctly encrypted password is stored in the fields hash1 through hash8.

Next ValidPassword calls subroutine Encipher to encrypt the entered user name and password using the salt values from the database. It then compares the result to the correct encryption it got from the database.

Private Function ValidPassword(uid As String, pwd As String) As Boolean
Dim query As String
Dim rs As Recordset
Dim i As Integer
Dim salt(1 To 8) As Byte
Dim hash(1 To 8) As Byte

    ‘ Assume we will fail.
    ValidPassword = False
  
    On Error GoTo ValidPasswordError
  
    ‘ Get the salt and hashed values.
    query = “SELECT ” & _
        “salt1, salt2, salt3, salt4, ” & _
        “salt5, salt6, salt7, salt8, ” & _
        “hash1, hash2, hash3, hash4, ” & _
        “hash5, hash6, hash7, hash8 ” & _
        “FROM Passwords WHERE userid = ‘” & _
        uid & “’”
    Set rs = TheDB.OpenRecordset(query, dbOpenSnapshot)
  
    ‘ If we didn’t find it, it’s not a valid ID.
    If rs.EOF Then GoTo ValidPasswordDone
  
    ‘ Copy the salt into the salt array.
    For i = 1 To 8
        salt(i) = rs.Fields(i - 1)
    Next i
  
    ‘ Encipher the uid using the user entered
    ‘ key and password, and the salt from the database.
    Encipher uid, pwd, salt, hash
  
    ‘ Verify that this is the correct hash.
    For i = 1 To 8
        If hash(i) <> rs.Fields(i + 7) Then _
            GoTo ValidPasswordDone
    Next i
  
    ‘ This user ID/password is correct.
    ValidPassword = True

ValidPasswordDone:
    ‘ Finish up.
    rs.Close
    Set rs = Nothing
    Exit Function

ValidPasswordError:
    <Error handling code omitted here>
    :
    Exit Function
End Function

The details of the Encipher subroutine are not very important so they are not described here. All that matters is that Encipher combines the user name, password, and salt values in a way that is hard to invert. In other words, given the encrypted value, the salt, and the user name, it should be difficult for an attacker to reproduce the password.

UpdatePassword The UpdatePassword subroutine allows the user to change the password corresponding to a user name. The program prompts the user to enter a user name, the current password, and a new password. The new password must be entered in two different text boxes. The dialog form that takes this information verifies that the two versions of the new password match. This helps ensure that the user did not make any errors while typing the new password.

UpdatePassword takes the user name and old password and verifies that they are a correct pair. This prevents unauthorized users from changing the password for another account.

If the application sets the DBUser object’s public variable SuperUser to true, UpdatePassword skips this step and does not validate the user name and original password. This feature is useful for resetting forgotten passwords.

The SuperUser variable gives a user the ability to change any user name’s password without knowing the current password. A program could manage the SuperUser variable by adding a SUPER_USER privilege to the GroupPrivileges table. It would then set SuperUser to true for users with this privilege.

Finally, UpdatePassword uses the SetPassword function to give the user name the indicated new password. If the user name did not already have an entry in the Passwords table, SetPassword creates it. Because a nonexistent user name cannot have a valid password, UpdatePassword will not create a new entry unless the DBUser object’s SuperUser property is true.

The SetPassword function uses a new kind of data access object. To update a password entry, this function uses an SQL UPDATE statement. The UPDATE statement sets new values for the salt and hash fields for the given user name. The following statement shows how the program might update the entry for the user “kay.”

UPDATE Passwords SET salt1 = 125, salt2 = 97, ...,
    hash1 = 21, hash2 = 107, ... WHERE userid = kay

This SQL statement modifies data in the Passwords table. Because it is not a SELECT statement, it does not create a recordset so the program cannot use the statement to define a Recordset object. Instead, the program can use a QueryDef object to execute the statement. A QueryDef object describes an SQL statement for later processing. Once it has defined a QueryDef object, the program can use the object’s Execute method to execute the SQL command. The following code fragment shows how the SetPassword function updates a password entry:

Dim qd As QueryDef
Dim query As String

    ‘ Initialize the query string, etc.
    :
    Set qd = TheDB.CreateQueryDef(“”, query)
    qd.Execute

Field Validation

PeopleWatcher uses several field validations to prevent the user from entering invalid data. For example, validations on the Social Security number field prevent the user from entering strings that are not part of a valid Social Security number. These field validations are generally similar to those described in Chapter 2. However, there are a few important ways in which validations interact with database programming.

Values Not Allowed by Controls Bound text boxes can contain almost any data loaded from the database. A text box can be bound to a typical text, integer, currency, or date/time field with no problems. Certain other types of controls combined with certain data fields can be a bit trickier.