Friday, February 11, 2011

Micosoft Access Front-End Security. Part 3: Authentication at the individual control level.

On some of the forms in our collection management database I have buttons that I want everyone to be able to use, some I only want a few to be able to use, and a few I only want one or two people to be able to use. I control these buttons by using the computer’s user name.

First I added a field called “Level,” to the users table, tblUsers. I used four levels, with level 4 being the administrator level, with no restrictions on what that person can do. Level 1 is the most restricted, users at this level can do little more than search and read data.

In the On Click event of each button that is not a level 1 control I wrap the event procedure in the following If-Then-Else logic:

    Dim intUser As Integer                  ‘This variable holds the number for the user level.

    ‘Pick up the user level by matching the computer’s username with the user ID in tblUsers
    intUser = DLookup("[Level]", "tluUsers", "[UserID] = '" & Environ("username") & "'")

    ‘For a level 2 control you want to allow all level 2, 3, and 4 users, so the code allows
    ‘for users greater than level 1.
    ‘For a level 3 control you want to allow only level 3 and 4 users, so the code allows only
    ‘users greater than level 2.
    ‘A level 4 control has code to allow only users greater than level 3.         
    If intUser > 3 Then
        ‘This is where you would put the code for your event procedure.
        MsgBox ("Sorry, you are not authorized to access this procedure.")
    End If

Bound text boxes can be protected by putting this If-Then-Else logic in the Got Focus procedure. If the user level is too low to allow updating the data then reset the focus to another control. In this example level 1 users are not allowed to edit the collection title, but are allowed to edit the donor name.

    If intUser < 2 Then
        MsgBox ("Sorry, you are not authorized to edit the Collection Title")
    End If

Next Week: Back-End Security.

No comments:

Post a Comment