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

Finally, some user privilege issues deal more with the user interface than with data access. For example, a program might want to allow a user to view or modify data in a table but only using a certain program and not any other. A secretary might have access to a department budget program that could calculate the total of the salaries of a department’s employees. At the same time, the secretary might not be allowed to use another program that listed each employee’s salary individually. Databases do not provide this type of security.

PeopleWatcher uses two database tables to manage user privileges. The UserGroups table contains entries that determine the groups to which each user belongs. Each record contains a user ID and a group name. For example, the user “harry” belongs to the group “HR” so the table contains a record with UserID = harry and Group = HR.

The second table is called GroupPrivileges. This table contains pairs of group names and privilege names. For example, the HR group should be able to read Basic information so the table contains a record with Group = HR and Privilege = READ_BASIC. The HR group should also have permission to read Home, HR, and Salary information, so the table also contains entries for Group = HR with Privilege values READ_HOME, READ_HR, and READ_SALARY. In addition, the GroupPrivileges table includes records defining the corresponding update privileges, UPDATE_BASIC and so forth.

GroupPrivileges includes records with two special privileges: CREATE_RECORDS and DELETE_RECORDS. These allow users in the appropriate groups to create and delete records in the Employee table. Table 8.3 shows the privileges granted to each group in PeopleWatcher’s GroupPrivileges table.

User Passwords To use the UserGroups and GroupPrivileges tables, PeopleWatcher needs to know which user is running the program. Then the program can use the UserGroups table to see to which groups the user belongs. Knowing the user’s groups, it can use the GroupPrivileges table to see what privileges are granted to those groups.

Of course, the application cannot simply ask the users who they are. If the program could trust the users completely, it would not need to worry about privacy in the first place. The next best thing is to ask the user for a user name and a password.

In a fairly trusting environment, one might think user names and passwords could be stored in a database table. Unfortunately, that would mean anyone who could read the table would be able to read all the passwords. At first glance, this might not seem too horrible. After all, anyone who can read the password table can probably also read the Employee table and thus the private employee information, with or without the passwords.

Unfortunately, despite countless instructions to the contrary, many users pick the same password for several different computer systems. Someone who captured passwords from your table could try them on other corporate database systems. Chances are good that many of the user names and passwords would work on another system. Even if one application does not require the tightest security in the world, it must guard its passwords to protect the other systems in its environment.

PeopleWatcher protects its passwords using a method that is common in UNIX systems. When the user selects a password, the user’s name and an encrypted version of the password are stored in the Passwords table.

Later, when the program needs to verify a user’s identify, the user reenters the user name and password. The application encrypts the newly entered password and compares it to the encrypted version stored in the database. If the two match, the user name and password are valid. Notice that the actual unencrypted password is never stored in the database or anywhere else in the system. This makes it harder for attackers to pull passwords out of the system.

The encryption system used by PeopleWatcher uses a series of hard-to-invert operations to grind the user name and password into an eight-byte output string. The process also incorporates an eight-byte randomly chosen “salt” value. The randomly chosen value means that a particular user name and password will encrypt to different values depending on the salt values chosen. For example, if the program encrypts the user name “rod” with the password “H#sg1_?6” and then it encrypts the same user name and password again with a different salt, it will get different results. This makes it very difficult for an attacker to precompute a large dictionary of encrypted user name and password pairs.

Passwords and password validation are described further in the next section.

The DBUser Class

To make working with privileges and passwords easier, PeopleWatcher uses a DBUser class. A DBUser object represents a database user with a verified user name and password.

When an application first creates a DBUser object, it should invoke the object’s Initialize method to tell the object what database contains the password and privilege information. The DBUser object will search this database when it needs to validate passwords.

Dim TheDB As Database
Dim TheUser As DBUser
    :
    ‘ Open the database.
    :
    ‘ Tell the DBUser object about the database.
    TheUser.Initialize TheDB

The following sections describe the public routines provided by the DBUser class for working with privileges and passwords. Only the most important points are explained here. You can see the complete code for the DBUser class on the compact disk.

GetPrivileges and HasPrivilege The GetPrivileges subroutine examines the database to see what privileges belong to the groups to which this user belongs. The heart of the routine is the following database query:

query = “SELECT DISTINCT privilege ” & _
        “FROM GroupPrivileges, UserGroups ” & _
        “WHERE userid = ‘” & UserID & “’ AND ” & _
        “GroupPrivileges.group = UserGroups.group”

The subroutine adds the selected privileges to the DBUser object’s Privileges collection using the following command:

Privileges.Add 1, Trim$(rs!Privilege)

This statement adds the value 1 to the Privileges collection. It uses the value of the Privilege fields returned by the query as the value’s key.

The HasPrivilege property procedure searches the collection for a specific key to decide whether the user has a particular privilege.

Property Get HasPrivilege(priv As String) As Boolean
    On Error GoTo PrivError
    HasPrivilege = (Privileges.Item(priv) = 1)
    Exit Property
  
PrivError:
    HasPrivilege = False
    Exit Property
End Property