Day 21

Securing Your Database Applications

In our final lesson, we cover topics related to securing your database and your application. Almost all software that is deployed in a multiuser environment should use some level of security. Security schemes can be used for more than just limiting user access to the database. Security schemes can also limit user access to the applications that use the database. You can also install security features in your Visual Basic database applications to limit the function rights of users within your applications. You can even develop routines that record user activity within your applications--including user login/logout activity--each time a user updates a database record, and even each time a user performs a critical operation such as printing a sensitive report or graph, updating key data, or running restricted routines.

Throughout today's lesson, you build a new OLE Server library. You can use this library to add varying levels of security to all your future Visual Basic database applications.

When you have completed this chapter, you will understand how Microsoft Access database security and encryption works and the advantages and disadvantages of both. You'll also know how to implement an application security scheme, including adding user login and logout history, implementing audit trails that show when database records have been updated, and recording each time users perform critical application operations.

Database Security

The first level of security you can employ in Visual Basic database applications is at the database level. The Microsoft Jet database format enables you to establish user and group security schemes using the Microsoft Access SYSTEM security file. You can also add database encryption to your Microsoft Jet databases to increase the level of security within your database.

Although the Microsoft Access SYSTEM security file and Microsoft Jet data encryption are powerful tools, they have some disadvantages. When adding either of these features, you should understand the limitations and pitfalls of the security features. In the following sections, you learn the most notable of these limitations, as well as some suggestions on how you can avoid unexpected results.

Limitations of the Microsoft Access SYSTEM Security

If you have a copy of Microsoft Access, you can install a database security scheme for your Visual Basic applications. The security scheme requires the presence of a single file (called SYSTEM.MDA or SYSTEM.MDW). This file must be available to your Visual Basic application either in the application path, or pointed to through the application .INI file or system Registry. After the SYSTEM security file is defined, all attempts to open the secured database cause the Microsoft Jet engine to request a user name and password before opening the database.

NOTE: Some 32-bit systems have a Microsoft Jet security file called SYSTEM.MDW (for example, Access 95). Others continue to use SYSTEM.MDA in both 16- and 32-bit modes (for example, Visual Basic 4). The difference between the SYSTEM.MDW and SYSTEM.MDA files is in name only. Throughout this lesson, you will see SYSTEM, SYSTEM.MDW, and SYSTEM.MDA. They can be used interchangeably.

We won't review the details of creating and updating the SYSTEM security file here (see Day 7, "Using the Visdata Program" for details on defining SYSTEM security). Instead, this section covers the advantages and limitations of using the SYSTEM security scheme employed by Microsoft Access and Microsoft Jet. Microsoft Access Is Required Once you have a SYSTEM security file registered on your workstation, you can use Microsoft Access or you can use Visdata to define the system security details. However, only Microsoft Access can create the original SYSTEM file. You cannot use any Visual Basic application to create a SYSTEM file. You can, however, use Visual Basic to modify existing SYSTEM security files. Multiple SYSTEM Files Are Possible You can have multiple versions of the SYSTEM security file available on your workstation or network. In this way you can create unique security schemes for each of your Microsoft Jet databases. The disadvantage here is that it is possible to install the wrong SYSTEM security file for an application. This could result in preventing all users from accessing any of the data. Depending on the SYSTEM file installed, it could also result in reducing security to the point of allowing all users access to critical data not normally available to them. If you are using multiple SYSTEM security files, be sure to store these files in the same directory as the application files and include the specific path to the SYSTEM file in all installation procedures. Removing the SYSTEM File Removes the Security Because all security features are stored in a single file, removing SYSTEM from the workstation or network effectively eliminates all database security. You can limit this possibility by storing the SYSTEM file on a network in a directory where users do not have delete or rename rights. Setting these rights requires administrator-level access to the network and knowledge of your network's file rights utilities. Some Applications Might Not Use SYSTEM Files If you are using the database in an environment where multiple applications can access the database, you might find that some applications do not use the SYSTEM files at all. These applications might be able to open the database without having to go through the security features. For example, you could easily write a Visual Basic application that opens a database without first checking for the existence of the SYSTEM file. By doing this, you can completely ignore any security features built into the SYSTEM security file.

Limitations of Microsoft Jet Encryption

You can also use the encryption feature of Microsoft Jet to encode sensitive data. However, you have no control over the type of encryption algorithm used to encode your data. You can only turn encryption on or off using the dbEncrypt or dbDecrypt option constants with the CreateDatabase and CompactDatabase methods.

The following list outlines other limitations to consider when using Microsoft Jet encryption.

Application Security

Application security is quite different from database security. Application security focuses on securing not only data but also processes. For example, you can use application security to limit users' ability to use selected data entry forms, produce certain graphs or reports, or run critical procedures (such as month-end closing or mass price updates).

Any good application security scheme has two main features. The first is a process that forces users to log into your application using stored passwords. This provides an additional level of security to your Visual Basic database application. As you see later in this chapter, forcing users to log into and out of your application also gives you the opportunity to create audit logs of all user activity. These audit logs can help you locate and fix problems reported by users and give you an additional tool for keeping track of just who is using your application.

The second process that is valuable in building an application security system is an access rights scheme. You can use an access rights scheme to limit the functions that particular users can perform within your application. For example, if you want to allow only certain users to perform critical tasks, you can establish an access right for that task and check each user's rights before he or she is allowed to attempt that operation. You can establish access rights for virtually any program operation, including data form entry, report generation, even special processes such as price updates, file exports, and so on.

Because application security works only within the selected application, it cannot affect users who are accessing the database from other applications. Therefore, you should not rely on application-level security as the only security scheme for your critical data. Still, application security can provide powerful security controls to your Visual Basic database applications.
In order to provide user login and logout and access rights checking, in this lesson you build a set of routines in a new OLE Server library called usrObject. This library contains all the properties and methods needed to install and maintain application-level security for all your Visual Basic database applications.

Developing a User Login/Logout System

The first routines you need to build as part of your application security OLE library enable application administrators to create and maintain a list of valid application users. This involves creating a simple data entry form that contains add, edit, and delete operations for a Users table. Next, you need routines to process user logins and logouts. The login routine prompts potential users for their user ID and password, and checks the values entered against the data table on file. As usual, you construct these routines in a way that makes it easy for you to use them in any future Visual Basic database applications.

Building the User Maintenance Form

Load Visual Basic 5 and start a new ActiveX DLL project. The first thing you do is create a form to manage the list of valid application users. This form enables you to add, edit, and delete users from a table called secUsers. This is the same table used to verify user logins at the start of all your secured applications. Use Table 21.1 and Figure 21.1 to build the first page of the User Maintenance tabbed dialog.

Before building this form, however, you need to add two reference entries and two custom controls to your project. Refer to the following list to make sure you load all the additional files needed for this project.

This project uses several control arrays. You can save yourself additional typing by building the first member of the control array, setting all the control properties, and then copying the additional members. You still have to retype some property settings, but it is considerably less tedious than if you had to set them all manually.

Figure 21.1. Laying out the User Maintenance form.


Table 21.1. Controls for the User Maintenance form.
Control Property Setting
VB.Form Name frmUserMaint
Caption "Form1"
ClientHeight 3195
ClientLeft 60
ClientTop 345
ClientWidth 6510
StartUpPosition 2 `CenterScreen
VB.CommandButton Name cmdBtn
Caption "Command1"
Height 495
Index 0
Left 180
TabIndex 0
Top 2520
Width 1215
TabDlg.SSTab Name SSTab1
Height 2475
Left 0
TabIndex 6
Top 0
Width 6495
Style 1
Tabs 2
TabsPerRow 2
TabCaption(0) "Users"
TabCaption(1) "Access Rights"
VB.TextBox Name txtField
Height 300
Index 0
Left 1440
TabIndex 1
Text "Text1"
Top 480
Width 1200
VB.TextBox Name txtField
Height 300
Index 4
Left 1440
TabIndex 5
Text "Text1"
Top 1920
Width 1800
VB.TextBox Name txtField
Height 300
Index 3
Left 1440
TabIndex 4
Text "Text1"
Top 1560
Width 1800
VB.TextBox Name txtField
Height 300
Index 2
Left 1440
TabIndex 3
Text "Text1"
Top 1200
Width 2400
VB.TextBox Name txtField
Height 300
Index 1
Left 1440
PasswordChar "*"
TabIndex 2
Text "Text1"
Top 840
Width 1200
VB.Label Name Label5
BorderStyle 1 `Fixed Single
Caption "Last Log Out"
Height 300
Left 180
TabIndex 11
Top 1920
Width 1200
VB.Label Name Label4
BorderStyle 1 `Fixed Single
Caption "Last Log In"
Height 300
Left 180
TabIndex 10
Top 1560
Width 1200
VB.Label Name Label3
BorderStyle 1 `Fixed Single
Caption "Full Name"
Height 300
Left 180
TabIndex 9
Top 1200
Width 1200
VB.Label Name Label2
BorderStyle 1 `Fixed Single
Caption "Password"
Height 300
Left 180
TabIndex 8
Top 840
Width 1200
VB.Label Name Label1
BorderStyle 1 `Fixed Single
Caption "User Name"
Height 300
Left 180
TabIndex 7
Top 480
Width 1200


Save the form as FRMUSERMAINT.FRM and the project as PRJUSROBJET.VBP after you add all the controls and position them on the form. Now you need to add some Visual Basic code to make the form work.

Place the following initialization code in the Declaration section of the User Maintenance form.

Option Explicit
`
` user maint and login vars
Dim objRec As Object
Dim lgnResult As Long
Public strDBName As String
`

Next, place the code in Listing 21.1 in the Form_Load event of the form.

Listing 21.1. Setting up the User Maintenance form.

Private Sub Form_Load()
    `
    Me.Caption = "User Maintenance"
    Bin8dInputs
    StartProc

`
End Sub


Listing 21.1 calls two routines that perform the initialization operations. Now add a new subroutine to the form called BindInputs and enter the code from Listing 21.2.

Listing 21.2. Adding the BindInputs method.

Public Sub BindInputs()
    `
    ` bind inputs to database
    `
    txtField(0).Tag = "UserID"
    txtField(1).Tag = "Password"
    txtField(2).Tag = "UserName"
    txtField(3).Tag = "LastLogIn"
    txtField(4).Tag = "LastLogOut"
    `
End Sub


Now add the code from Listing 21.3 to the new StartProc subroutine.

Listing 21.3. Coding the StartProc routine.

Public Sub StartProc()
    `
    ` start database
    `
    Set objRec = New recObject
    `
    objRec.DBName = strDBName
    objRec.RSName = "SELECT * FROM secUsers ORDER BY UserID"
    objRec.rsType = rsDynasetType
    objRec.RSFocus = "UserID"
    `
    objRec.RSOpen Me
    objRec.RSEnable Me, False
    `
    objRec.BtnBarAlign = bbBottom
    objRec.BBInit Me
    objRec.BBEnable Me, "11111111"
    `
End Sub


The code in Listing 21.3 initializes the record object, sets several properties, and then it's ready for you to begin.

Add the code in Listing 21.4 to the cmdBtn_Click event. This code calls the BBProcess method of the record object library to handle all data entry functions.

Listing 21.4. Coding the cmdBtn_Click event.

Private Sub cmdBtn_Click(Index As Integer)
    `
    objRec.BBProcess Me, Index, "UserID"
    `
    ` add default date/time for new recs
    If Index = 0 And cmdBtn(0).Caption = "&Save" Then
        txtField(3) = Now()
        txtField(4) = Now()
    End If
    `
End Sub


Next, add the code in Listing 21.5 to your project. This is the code that resizes the controls when the user resizes the form.

Listing 21.5. Resizing the controls at runtime.

Private Sub Form_Resize()
    `
    If Me.WindowState <> vbMinimized Then
        With SSTab1
            .Left = 1
            .Top = 1
            .Width = Me.ScaleWidth
            .Height = Me.ScaleHeight - 540
        End With
    End If
    `
    objRec.BBInit Me
    `
End Sub


You also need to add some code (shown in Listing 21.6) to the Text1_KeyPress event. This code prevents users from editing the Last Log In or Last Log Out fields on the form.

Listing 21.6. Disabling entry in the Text1_KeyPress event.

Private Sub txtField_KeyPress(Index As Integer, KeyAscii As Integer)
    `
    ` trap keystrokes
    `
    Select Case Index
        Case 0 ` userid
            KeyAscii = Asc(UCase(Chr(KeyAscii)))
        Case 1 ` password
        Case 2 ` username
        Case 3 ` last log in
            KeyAscii = 0
        Case 4 ` last log out
            KeyAscii = 0
    End Select
    `
End Sub


Now save the project. Before you can run this project, you need to add some code to the class module, too. First, set its name to usrObject and save the project just to be safe. Next, add some declarations to the top of the module, based on Listing 21.7.

Listing 21.7. User-related declarations for the usrObject class.

Option Explicit
`
` user login/out vars
Private wsUsers As Workspace
Private dbUsers As Database
Private rsUsers As Recordset
Private blnUsersLoaded As Boolean
Private strDBName As String
Private intMaxTries As Integer
Private strUserIDProp As String
Private strTitle As String
`
Enum urUserAction
    urLogIn = 0
    urLogOut = 1
End Enum


You use these variables to handle user logins and logouts, too.

Now add the Property Let and Property Get routines to the class module (see Listing 21.8)

Listing 21.8. Opening the dataset with the usrInit function.

Public Property Get DBName() As Variant
    DBName = strDBName
End Property

Public Property Let DBName(ByVal vNewValue As Variant)
    strDBName = vNewValue
End Property

Public Property Get UserID() As Variant
    UserID = strUserIDProp
End Property

Public Property Let UserID(ByVal vNewValue As Variant)
    ` na
End Property

Public Property Get LoginTitle() As Variant
    LoginTitle = strTitle
End Property

Public Property Let LoginTitle(ByVal vNewValue As Variant)
    strTitle = vNewValue
End Property

Public Property Get MaxTries() As Variant
    MaxTries = intMaxTries
End Property

Public Property Let MaxTries(ByVal vNewValue As Variant)
    intMaxTries = vNewValue
End Property


This code just declares Public properties for the class. You need to add the Class_Initialize routine, too. Enter the code from Listing 21.9.

Listing 21.9. Coding the Class_Initialize event.

Private Sub Class_Initialize()
    `
    ` initial settings
    `
    strDBName = App.Path & "\security.mdb"
    intMaxTries = 3
    strTitle = "User Maintenance Library"
    strUserIDProp = ""
    blnRightsLoaded = False
    `
End Sub


This routine passes the local version of the database name property to the form and then calls the form. Note the use of the Err.Raise method in the error handler. This simply passes the error back to the calling program with some additional information that you can use to diagnose the problem.

Finally, to test this library you need to start a second project (Visual Basic 5 Standard EXE) in the same group (select File | New Project from the main menu). This second project is used to instantiate a new recObject and call it from within your program. This is all handled through a single BAS module, not a form. All you need to do is remove the default form from your EXE project, and add the module to your form. Add the code from Listing 21.10 to your new module file.

Listing 21.10. Building a second project to test the usrObject library.

Option Explicit
`
` project-level vars
`
Public objUser As Object

Public Sub Main()
    `
    ` main entry for form-less project
    `
    Set objUser = New usrObject
    objUser.DBName = App.Path & "\..\..\data\security\security.mdb"
    `
    objUser.UsersForm
    `
    End
    `
End Sub

TIP: There are several advantages to using a Main() routine as the startup for your application. You can handle numerous initialization processes before you load a form, and you can even design your application to use different forms from the same Main() routine. Programs that start with a Main() routine are usually easier to maintain and modify than programs that start with a startup form.

Now save the new module as modUsrTest.bas and the project as prjUsrTest.vbp, and run the project. Your screen should look similar to the one in Figure 21.2.

Figure 21.2. Running the User Maintenance form.


You can now add, edit, and delete user records. A few records have already been added for you. Make sure this includes a record for USERA. If one does not exist, add it. If it is already on file, edit the record and set the Password field to USERA. Notice that the Password field does not display its contents. This is because you set the PasswordChar property of the textbox to show only an asterisk (*) for every character in the field. The actual characters are stored in the database table.

Building the User LogIn and LogOut Routines

Now that you have a method for managing the list of valid users, it's time to create the routines that enable users to log into and out of your applications. First you need to create a user login form. Then you need to add a routine to verify the user login and a routine to log the user out when the application is terminated.

First, build the user login form. Add a new form to the existing PRJUSROBJECT.VBP project, using Table 21.2 and Figure 21.3 as guides in building the form.

Figure 21.3. Laying out the User Login form.


Table 21.2. Controls for the User Login form.
Control Property Setting
VB.Form Name frmUserLogIn
BorderStyle 3 `Fixed Dialog
Caption "User Login"
ClientHeight 1440
ClientLeft 45
ClientTop 330
ClientWidth 4605
StartUpPosition 2 `CenterScreen
VB.CommandButton Name cmdCancel
Cancel -1 `True
Caption "Cancel"
Height 300
Left 3300
Top 1020
Width 1200
VB.CommandButton Name cmdOK
Caption "OK"
Default -1 `True
Height 300
Left 3300
Top 660
Width 1200
VB.TextBox Name txtPassword
Height 300
Left 1440
PasswordChar "*"
Text "Text1"
Top 1020
Width 1800
VB.TextBox Name txtUserID
Height 300
Left 1440
Text "Text1"
Top 660
Width 1800
VB.Label Name lblPassword
Caption "Password"
Height 300
Left 180
Top 1080
Width 1200
VB.Label Name lblUserID
Caption "UserID"
Height 300
Left 180
Top 720
Width 1200
VB.Label Name lblAppTitle
Alignment 2 `Center
BorderStyle 1 `Fixed Single
Caption "Application Title"
Font Name="MS Sans Serif"
Size=13.5
Height 435
Left 120
Top 120
Width 4335


You need to add only a few lines of code to this form. First, add the code shown in Listing 21.11 to the declaration area of the form.

Listing 21.11. Coding the declaration section of the form.

Option Explicit
`
` public vars
Public blnOK As Boolean
Public strTitle As String


Next, add the code in Listing 21.12 to the Form_Activate event to initialize form values at startup.

Listing 21.12. Initializing form values.

Private Sub Form_Activate()
    `
    ` form setup
    `
    lblAppTitle = strTitle
    txtUserID = ""
    txtPassword = ""
    txtUserID.SetFocus
    `
End Sub


You need to add a few lines to support the command buttons. First, add the code in Listing 21.13 for the OK button.

Listing 21.13. Code for the OK button.

Private Sub cmdOK_Click()
    `
    blnOK = True
    Me.Hide
    `
End Sub


This code sets a global variable and hides the login form. Now add the code from Listing 21.14 to support the Cancel button.

Listing 21.14. Code for the Cancel button.

Private Sub cmdCancel_Click()
    `
    blnOK = False
    Me.Hide
    `
End Sub


That's it for the User Login form. Save this form as FRMUSERLOGIN.FRM.

Now you need to add more code to the PRJUSROBJECT class library file. You need three routines. The first routine to add is the UserLogin method. This method calls the login form and then calls other routines to verify the login and update the user's record in the secUsers table.

Create a new Public function called UserLogin and add the code from Listing 21.15.

Listing 21.15. Adding the LoginUser function to the DLL library.

Public Function UserLogin() As Boolean
    `
    ` handle user login attempts
    `
    On Error GoTo LocalErr
    `
    Static intTries As Integer
    Dim blnValidUser As Boolean
    Dim intAnswer As Integer
    `
UserLoginAttempt:
    `
    frmUserLogIn.strTitle = strTitle
    frmUserLogIn.Show vbModal
    `
    ` did user cancel login?
    If frmUserLogIn.blnOK = False Then
        UserLogin = False
        GoTo UserLoginExit
    End If
    `
    ` verify login
    If frmUserLogIn.blnOK = True Then
        intTries = intTries + 1
        blnValidUser = CheckUser(frmUserLogIn.txtUserID, frmUserLogIn.txtPassword)
        If blnValidUser = True Then
            strUserIDProp = frmUserLogIn.txtUserID ` save to properties
            modUsrObject.strUserIDSaved = strUserIDProp
            LogUser urLogIn ` update table
            UserLogin = True ` set flag
            GoTo UserLoginExit ` exit
        End If
    End If
    `
    ` did user max attempts?
    If intTries = intMaxTries Then
        MsgBox "Login failed - access denied", vbCritical, "User Login"
        UserLogin = False
        GoTo UserLoginExit
    End If
    `
    ` go try again
    intAnswer = MsgBox("Invalid Login", vbRetryCancel + vbInformation, "User Login")
    If intAnswer = vbRetry Then
        GoTo UserLoginAttempt
    Else
        UserLogin = False
        GoTo UserLoginExit
    End If
    `
UserLoginExit:
    Unload frmUserLogIn
    `
    Exit Function
    `
LocalErr:
    Err.Raise Err.Number, App.EXEName & ".usrObject", Err.Description
    `
End Function


The module in Listing 21.15 first sets some variables and then calls the login form and waits for the user to press OK or Cancel on the form. The default settings allow the user three login attempts. If the user presses the OK button on the form, the routine calls the CheckUser function to check for a valid user. If the user is valid, the routine stores the user's ID, updates the user's record in the secUsers table, and then exits. If the user cannot pass the security check after three tries, the login is terminated.

Now let's code the CheckUser routine. This is the module that looks up the user ID and password to see if they match in the data table. Create a new Private function called CheckUser and enter the code in Listing 21.16.

Listing 21.16. Coding the CheckUser routine.

Private Function CheckUser(strUserID As String, strPassword As String) As Boolean
    `
    ` check login parms against table
    `
    Dim strFind As String
    `
    strFind = "UserID = `" & strUserID & "` AND Password='" & strPassword & "`"
    `
    If blnUsersLoaded = False Then
        LoadUsers
    End If
    `
    rsUsers.FindFirst strFind
    If rsUsers.NoMatch = True Then
        CheckUser = False
    Else
        CheckUser = True
    End If
    `
End Function


This routine first checks to see if the dataset containing the users is already loaded. If not, the LoadUsers method is called. Then, the method searches the dataset for the User ID and password in a single record. If all is okay, the routine updates the LastLogIn field of the dataset and exits. If the search comes up empty, the routine reports a value of FALSE upon exit.

Now add the Private subroutine LoadUsers from Listing 21.17.

Listing 21.17. Coding the LoadUsers method.

Private Sub LoadUsers()
    `
    ` load users data set
    `
    Dim strSQL As String
    `
    strSQL = "SELECT * FROM secUsers ORDER BY UserID"
    `
    Set wsUsers = DBEngine.CreateWorkspace("usrLogin", "admin", "")
    Set dbUsers = wsUsers.OpenDatabase(strDBName)
    Set rsUsers = dbUsers.OpenRecordset(strSQL, dbOpenDynaset)
    `
    blnUsersLoaded = True
    `
End Sub


This routine simply initializes the data connection between the class object and the database that holds the security tables.

You need only one more routine--the LogUser routine. This procedure just needs to locate the requested user record and update the LastLogIn or LastLogOut fields--depending on the parameter passed. Create a new function called usrLogOut and add the code from Listing 21.18.

Listing 21.18. Adding the LogUser routine.

Public Sub LogUser(urAction As urUserAction)
    `
    ` make entry in the user table
    `
    On Error GoTo LocalErr
    `
    Dim strAction As String
    `
    If urAction = urLogIn Then
        strAction = "In"
    Else
        strAction = "Out"
    End If
    `
    If blnUsersLoaded = False Then
        LoadUsers
    End If
    `
    With rsUsers
        .FindFirst "UserID='" & strUserIDProp & "`"
        If .NoMatch = False Then
            .Edit
            .Fields("LastLog" & strAction) = Now()
            .Update
        End If
    End With
    `
    Exit Sub
    `
LocalErr:
    Err.Raise Err.Number, App.EXEName & ".usrObject", Err.Description
    `
End Sub


There is a line in the UserLogin method that saves a copy of the UserID to a Public property of a BAS module. This is needed so that you can share the user ID with other class objects in this DLL (you build another one a bit later today). Add a BAS module to the DLL project. Set its Name to modUsrObject and save it as modUsrObject.bas. Now add the code from Listing 21.19 to this new module.

Listing 21.19. Coding the properties for the modUsrObject.bas module.

Option Explicit
`
Private strLocalUserID As String

Public Property Get strUserIDSaved() As Variant
    strUserIDSaved = strLocalUserID
End Property

Public Property Let strUserIDSaved(ByVal vNewValue As Variant)
    strLocalUserID = vNewValue
End Property


That's all the coding you need to do in the DLL project for now. Before continuing, save this project (PRJUSEROBJEC.VBP) and all its components.

Now you need to modify the Main procedure in the test project you created earlier to call the new User Login form. Modify the Main routine to match the lines of code in Listing 21.20.

Listing 21.20. Modifying the Main routine to add the new User Login form.

Public Sub Main()
    `
    ` main entry for form-less project
    `
    Set objUser = New usrObject
    objUser.DBName = App.Path & "\..\..\data\security\security.mdb"
    `
    ` now try to start up
    If objUser.UserLogin = True Then
        objUser.UsersForm
        objUser.LogUser urLogOut
    Else
        MsgBox "You do not have rights to this application", vbExclamation, "Login Failed"
    End If
    `
    End
    `
End Sub


Now, instead of just calling the UsersForm routine right away, you first make the user login with a valid ID and password. If the user successfully logs in, the program runs the UsersForm routine. When the user returns from the User Maintenance form, the LogUser method is called with the urLogOut parameter. This updates the LastLogOut field of the secUsers table.

Save and run this project. Your screen should look similar to the one in Figure 21.4.

Figure 21.4. Running the User Login form.


When you see the login form, enter USERA as the User ID and USERA as the password (remember, you added this in the previous example). Next, you see the User Maintenance form. When you exit this form, the routine automatically updates your logout time stamp.

You now have a complete and portable user login and logout system for your Visual Basic applications. Now let's add an additional application security feature--user access rights.

Developing a User Access Rights System

You can add an increased level of application security to your Visual Basic programs by establishing a user access rights scheme. An access rights scheme enables you to define a set of secured operations within your program and then define access rights for each of the operations on a user-by-user basis. For example, you might want to restrict the ability to print certain reports to specifically qualified users. You might also want to limit the number of users who can access data entry forms. You might even want to allow some users to modify data, but not create new records or delete existing records. Any of these arrangements can be handled by defining and implementing a user access rights security scheme.

Defining the User Access Rights Scheme

Before you can code the new features, you need to consider how the scheme will be implemented in your Visual Basic applications. This exercise uses a typical rights scheme that uses a sliding scale--the lowest level on the scale has no rights at all; the highest level has all possible rights. Table 21.3 shows the proposed set of access rights.

Table 21.3. The scale of access rights levels.
Rights Level Access Rights
Level 0 No rights
Level 1 Read-only rights
Level 2 Read and modify rights
Level 3 Read, modify, and add rights
Level 4 Read, modify, add, and delete rights
Level 5 All, plus extended rights


In Table 21.3, each level adds additional privileges. The final level (Level 5) includes all previously defined rights plus special extended rights. You can use this level to define any special powers, depending on the object or system (supervisor control, for example).

Set up a data table that contains three columns--User ID, Object, and Level. The User ID should match the one in the AppUser table you already defined. The Level column contains values 0 through 5, and the Object column contains the name of a secured program object. This object could be a report, a data entry form, or even a menu item or command button.

There is a single record in the dataset for each secured program object. This default set is used to establish the base security profile for the system. If an object is in the default set, it is a secured object, and any users who attempt access to the program object must have their own access record defined for the requested object. If no object is present for a particular user, the user cannot access the program object.

You need to add just a bit of new code to the prjUsrObject class library in order to implement an access rights scheme. First, you need a routine that verifies the user access information when requested. You need a few support routines along the way, too (you get to those later). But first, you need to modify the User Maintenance form to include data entry for access rights.

Building the User Access Rights Maintenance Form

The first order of business is to create the data entry form needed to create and edit user access rights. This form is the second page of the tabbed dialog you built to manage the User table. Use Table 21.4 and Figure 21.5 as guides in laying out the Access Rights tab of the User Maintenance form.

Figure 21.5. Laying out the Access Rights tab.


This form contains a control button array. Be sure to add the first button (cmdAccess), set its properties, and then copy and paste the button onto the form.
Table 21.4. Controls for the access rights maintenance form.
Controls Properties Settings
VB.Data Name dtaUserID
Caption "Data1"
Connect "Access"
DatabaseName ""
DefaultCursorType= `DefaultCursor
DefaultType 2 `UseODBC
Exclusive 0 `False
Height 345
Left -70560
Options 0
ReadOnly 0 `False
RecordsetType 1 `Dynaset
RecordSource ""
Top 1380
Visible 0 `False
Width 1275
VB.Data Name dtaDefault
Caption "Data1"
Connect "Access"
DatabaseName ""
DefaultCursorType= `DefaultCursor
DefaultType 2 `UseODBC
Exclusive 0 `False
Height 345
Left -74400
Options 0
ReadOnly 0 `False
RecordsetType 1 `Dynaset
RecordSource ""
Top 1440
Visible 0 `False
Width 1155
VB.CommandButton Name cmdAccess
Caption "&Set Level"
Height 300
Index 4
Left -72360
TabIndex 20
Top 2100
Width 1200
VB.CommandButton Name cmdAccess
Caption "&Erase All"
Height 300
Index 3
Left -72360
TabIndex 19
Top 1740
Width 1200
VB.CommandButton Name cmdAccess
Caption "&Copy All"
Height 300
Index 2
Left -72360
TabIndex 18
Top 1380
Width 1200
VB.CommandButton Name cmdAccess
Caption "&Delete"
Height 300
Index 1
Left -72360
TabIndex 17
Top 1020
Width 1200
VB.CommandButton Name cmdAccess
Caption "&Add"
Height 300
Index 0
Left -72360
TabIndex 16
Top 660
Width 1200
MSDBGrid.DBGrid Name dbgDefault
Height 1725
Left -74880
TabIndex 12
Top 660
Width 2400
MSDBGrid.DBGrid Name dbgUserID
Height 1725
Left -71040
TabIndex 13
Top 660
Width 2400
VB.Label Name lblUserName
BorderStyle 1 `Fixed Single
Caption "Label7"
Height 255
Left -71040
TabIndex 15
Top 360
Width 2400
VB.Label Name lbluserID
BorderStyle 1 `Fixed Single
Caption "Label6"
Height 255
Left -74880
TabIndex 14
Top 360
Width 2400


After you add the data-bound grid objects to the form, you need to set some of their properties using the pop-up menu. Select the dbgDefault grid and click the alternate mouse button. Then select Retrieve Fields to load the fields. Now click the alternate button again and select Properties and click the Columns tab. Make the User ID column invisible. Perform the same steps for the dbgUserID data grid. Save the project before you add the code.

The first step in coding the User Access Rights page is to add three form-level variables to the User Maintenance Form.

Option Explicit
`
` user maint and login vars
Dim objRec As Object
Dim lgnResult As Long
Public strDBName As String
`
`***new >>> access rights vars
Dim strSQLDefault As String
Dim strSQLUserID As String
Public intRights As Integer

Next, add the code in Listing 21.21 to the SSTab1_Click event. This code fills out the rights form for the selected user.

Listing 21.21. Selecting the Access Rights form.

Private Sub SSTab1_Click(PreviousTab As Integer)
    `
    ` handle tab changes
    `
    Select Case PreviousTab
        Case 0 ` leaving users
            lblUserID = txtField(0)
            lblUserName = txtField(2)
            accLoadLists
        Case 1 ` leaving access
    End Select
    `
End Sub


Then add code for the new accLoadLists method. This is the code that actually loads the grid boxes with live data (see Listing 21.22).

Listing 21.22. Coding the accLoadLists method.

Public Sub accLoadLists()
    `
    ` load form dbgrids
    `
    strSQLDefault = "SELECT Object,[Level],UserID FROM secAccess WHERE UserID='DEFAULT' ORDER BY Object"
    strSQLUserID = "SELECT Object,[Level],UserID FROM secAccess WHERE UserID = `" & lblUserID & "` ORDER BY Object"
    `
    dtaDefault.DatabaseName = strDBName
    dtaDefault.RecordSource = strSQLDefault
    dbgDefault.Caption = "Secure Objects"
    dtaDefault.Refresh
    dbgDefault.ReBind
    dbgDefault.Columns(0).Width = 1440
    dbgDefault.Columns(1).Width = 600
    `
    dtaUserID.DatabaseName = strDBName
    dtaUserID.RecordSource = strSQLUserID
    dbgUserID.Caption = "User Access"
    dtaUserID.Refresh
    dbgUserID.ReBind
    dbgUserID.Columns(0).Width = 1440
    dbgUserID.Columns(1).Width = 600
    `
End Sub


Next, add the code from Listing 21.23 behind the cmdAccess button array. This control array handles all the routines that add and delete rights objects and set the access level for the rights objects.

Listing 21.23. Setting up access levels and command buttons.

Private Sub cmdAccess_Click(Index As Integer)
    `
    ` handle user selections
    `
    Select Case Index
        Case 0 ` add object
            accAddObject
        Case 1 ` delete object
            If dtaUserID.Recordset.RecordCount > 0 Then
                accDelObject
            Else
                MsgBox "No objects to Delete", vbExclamation, "Delete Object"
            End If
        Case 2 ` copy all
            accCopyAll
        Case 3 ` erase all
            If dtaUserID.Recordset.RecordCount > 0 Then
                accDelAll
            Else
                MsgBox "No objects to Delete", vbExclamation, "Erase All"
            End If
        Case 4 ` set level
            If dtaUserID.Recordset.RecordCount > 0 Then
                accSetLevel
            Else
                MsgBox "No objects on File", vbInformation, "Set Level"
            End If
    End Select
    `
End Sub


This module calls a set of routines. Each of them handles the real dirty work. You also add some error checking here to make the program a bit more friendly.

To start off, you enter the code that adds an object from the Default Set to the current User's Set. Create a new subroutine called accAddObject and place the code in Listing 21.24 in the routine.

Listing 21.24. Creating the accAddObject routine.

Public Sub accAddObject()
    `
    ` add an object to the UserID set
    `
    On Error GoTo LocalErr
    `
    Dim strObject As String
    `
    ` is this the default user account?
    If lblUserID = "DEFAULT" Then
        strObject = InputBox("Enter new secure object:", "Create new Object")
        strObject = UCase(Trim(strObject))
    Else
        strObject = dtaDefault.Recordset!object
    End If
    `
    ` try to add it to the collection
    If strObject <> "" Then
        With dtaUserID.Recordset
            .FindFirst "UserID='" & lblUserID & "` AND object='" & strObject & "`"
            If .NoMatch = True Then
                .AddNew
                .UserID = lblUserID
                !object = strObject
                ![Level] = 0
                .Update
            Else
                MsgBox "Object Already on File", vbExclamation, "Add Object"
            End If
        End With
    End If
    `
    accLoadLists
    `
    Exit Sub
    `
LocalErr:
    MsgBox Err.Description, vbExclamation, Err.Number
    `
End Sub


This routine gets some variables from the form and then checks to see whether you are trying to add an object to the Default user. If so, you are prompted for the new object name, and if a valid one is entered, that object is added to the Default list. If you are attempting to add a new object to a real user, the routine checks to make sure the object does not already exist for that user before adding it to your list.

The next routine to add (shown in Listing 21.25) deletes an object from the User List. Create a new subroutine called accDelObject and add the code in Listing 21.25.

Listing 21.25. Deleting an object with accDelObject.

Public Sub accDelObject()
    `
    ` delete an object from the User list
    `
    Dim strObject As String
    Dim intAnswer As Integer
    `
    strObject = dtaUserID.Recordset!object
    intAnswer = MsgBox("Delete [" & strObject & "] from User Access?", vbQuestion + vbYesNo, "Delete Object")
    If intAnswer = vbYes Then
        dtaUserID.Recordset.Delete
        accLoadLists
    End If
    `
End Sub


The routine first asks for confirmation before deleting the object from the list.

Now you tackle a tougher one. The subroutine called accDelAll removes all the existing rights objects for the current user. Add the code in Listing 21.26.

Listing 21.26. Deleting all existing rights objects with accDelAll.

Public Sub accDelAll()
    `
    ` delete all objects from user list
    `
    Dim strUserID As String
    Dim intAnswer As Integer
    Dim strSQL As String
    Dim ws As Workspace
    Dim db As Database
    `
    ` init vars
    strUserID = lblUserID
    strSQL = "DELETE FROM secAccess WHERE UserID='" & strUserID & "`"
    `
    ` confirm delete
    intAnswer = MsgBox("Delete ALL objects for UserID [" & strUserID & "]?", vbQuestion + vbYesNo, "Erase All")
    If intAnswer = vbYes Then
        Set ws = DBEngine.CreateWorkspace("usrDelAll", "admin", "")
        Set db = ws.OpenDatabase(strDBName)
        `
        On Error Resume Next
        ws.BeginTrans
        db.Execute strSQL
        If Err.Number = 0 Then
            ws.CommitTrans
        Else
            ws.Rollback
            MsgBox "Unable to complete transaction", vbExclamation, "Erase All"
        End If
        `
        db.Close
        ws.Close
        Set db = Nothing
        Set ws = Nothing
    End If
    `
    accLoadLists
    `
End Sub


Notice that you use an SQL statement to perform this task. Because you are using the Execute method, you need to open another copy of the database. Also, because the single SQL statement might be deleting multiple records in the same table, you encapsulate the delete process in a BeginTrans_CommitTrans loop.

Now for the hardest one of the bunch, the accCopyAll routine. Because some records might already be on file, you first must delete any existing items. The routine in Listing 21.27 contains several SQL statements and, of course, they are covered by Visual Basic transactions, too.

Listing 21.27. The accCopyAll routine.

Public Sub accCopyAll()
    `
    ` copy all objects from default to user
    `
    Dim strUserID As String
    Dim strSQLDelete As String
    Dim strSQLTemp As String
    Dim strSQLUpdate As String
    Dim strSQLDrop As String
    Dim strSQLInsert As String
    Dim intAnswer As Integer
    Dim ws As Workspace
    Dim db As Database
    `
    ` init vars
    strUserID = lblUserID
    strSQLDelete = "DELETE FROM secAccess WHERE UserID='" & strUserID & "`"
    strSQLTemp = "SELECT * INTO secTemp FROM secAccess WHERE UserID='DEFAULT'"
    strSQLUpdate = "UPDATE secTemp SET UserID='" & strUserID & "`"
    strSQLInsert = "INSERT INTO secAccess SELECT * FROM secTemp"
    strSQLDrop = "DROP Table secTemp"
    `
    ` now do it
    intAnswer = MsgBox("Replace all current objects for UserID [" & strUserID & "]?", vbQuestion + vbYesNo, "CopyAll")
    If intAnswer = vbYes Then
        `
        Set ws = DBEngine.CreateWorkspace("wsCopyAll", "admin", "")
        Set db = ws.OpenDatabase(strDBName)
        `
        On Error Resume Next
        ws.BeginTrans
        db.Execute strSQLDelete
        db.Execute strSQLTemp
        db.Execute strSQLUpdate
        db.Execute strSQLInsert
        db.Execute strSQLDrop
        `
        If Err.Number = 0 Then
            ws.CommitTrans
        Else
            ws.Rollback
            MsgBox "Unable to complete transaction", vbExclamation, "Copy All"
        End If
        `
        db.Close
        ws.Close
        Set db = Nothing
        Set ws = Nothing
        `
        accLoadLists
    End If
    `
End Sub


The last routine you need to add is the one for the Set Level button. This routine calls another small form that you build next. The second form is where you set the access level for the selected rights object. Create a new subroutine called accSetLevel and add the code in Listing 21.28.

Listing 21.28. The accSetLevel routine.

Public Sub accSetLevel()
    `
    ` set user access level
    `
    Dim strRightsName As String
    `
    strRightsName = lblUserID & " ["
    strRightsName = strRightsName & dtaUserID.Recordset.Fields("Object") & "]"
    intRights = dtaUserID.Recordset![Level]
    `
    frmRights.fraRights = strRightsName
    frmRights.Caption = "User Access Rights"
    `
    frmRights.Show vbModal
    `
    ` update object rights
    With dtaUserID.Recordset
        .Edit
        .Fields("[Level]") = intRights
        .Update
    End With
    `
    accLoadLists
    `
End Sub


This routine loads some controls on the new form and then shows the form for input. When the form is closed, this routine transfers some of the information back into the data control and refreshes the on-screen lists.

Now you need to build the last data form. Add a new form to the DLL project. Use Table 21.5 and Figure 21.6 as guides in laying out the Rights List.

Figure 21.6. Laying out the access level form.


Table 21.5. Controls for the Rights List form.
Control Property Setting
VB.Form Name frmRights
BorderStyle 3 `Fixed Dialog
Caption "User Rights"
ClientHeight 2715
ClientLeft 45
ClientTop 330
ClientWidth 3390
ControlBox 0 `False
LinkTopic "Form1"
MaxButton 0 `False
MinButton 0 `False
ScaleHeight 2715
ScaleWidth 3390
ShowInTaskbar 0 `False
StartUpPosition 2 `CenterScreen
VB.CommandButton Name cmdOK
Caption "OK"
Height 315
Left 2040
TabIndex 1
Top 2280
Width 1200
VB.Frame Name fraRights
Height 2055
Left 120
TabIndex 0
Top 60
Width 3135
VB.OptionButton Name optRights
Caption "(5)-Read/Mod/Add/Del/Xtended"
Height 255
Index 5
Left 180
TabIndex 7
Top 1740
Width 2775
VB.OptionButton Name optRights
Caption "(4)-Read/Mod/Add/Delete"
Height 255
Index 4
Left 180
TabIndex 6
Top 1440
Width 2775
VB.OptionButton Name optRights
Caption "(3)-Read/Modify/Add"
Height 255
Index 3
Left 180
TabIndex 5
Top 1140
Width 2775
VB.OptionButton Name optRights
Caption "(2)-Read/Modify"
Height 255
Index 2
Left 180
TabIndex 4
Top 840
Width 2775
VB.OptionButton Name optRights
Caption "(1)-Read Only"
Height 255
Index 1
Left 180
TabIndex 3
Top 540
Width 2775
VB.OptionButton Name optRights
Caption "(0)-No Access"
Height 255
Index 0
Left 180
TabIndex 2
Top 240
Width 2775


There is very little code to add to this form. All the code you need for this form is found in Listing 21.29. Add this code to the frmRights form.

Listing 21.29. Coding the frmRights form.

Option Explicit

Private Sub cmdOK_Click()
    Unload Me
End Sub

Private Sub Form_Load()
    `
    ` set rights button
    `
    optRights(frmUserMaint.intRights) = True
    `
End Sub

Private Sub optRights_Click(Index As Integer)
    `
    ` set rights level
    `
    frmUserMaint.intRights = Index
    `
End Sub


Now save this form as FRMRIGHTS.FRM. In the next section, you walk through a session of setting user rights and adding new secured objects to the database.

Running the Access Rights Maintenance Forms

After building the Access Rights forms, you are ready to test the project again. When you start the program, you are prompted to enter a password. As before, enter USERA for both the User ID and the Password. This brings up the User Maintenance form. First, add a new user, TEMPUSER. Be sure to include a password and a name. After saving the new user record, select the User Access tab to add user access rights. Your form should look similar to the one in Figure 21.7.

You can see a set of default access objects on the left of Figure 21.7, and you can see that the new user does not have any defined security levels for the objects in the box on the right. First, add one of the default objects to the user's list by clicking a row selector in the Secure Objects list (the Default List) and clicking the Add button. You see that the selected object has been copied to the User Access list with the default access rights setting (see Figure 21.8).

You can change the access level for the new object by pressing the Set Level button. This brings up a window that shows all the possible access levels (see Figure 21.9).

Figure 21.7. Editing the access rights for a user.

Figure 21.8. Adding an object to the User Access list.

Figure 21.9. Changing the Access Level for an object.


Select the Read/Modify/Add radio button and click the OK button. When you return to the previous form, you see that the access level for that user has been updated.

You can practice adding, deleting, and modifying secured objects for any user you add to the database. You can define new secured objects by opening the Default user profile and selecting Access | Set User Access from the main menu. Any entirely new objects must first be added to the Default user.

WARNING: Although it is possible to delete all the objects from the Default user profile, it is not recommended. Doing so makes it impossible to add or edit existing access rights of other new users.

Implementing Access Rights Security in Your Application

Now that you have the tools to create and manage user access rights, you need to build a routine to check those user rights and then add rights-checking to a working Visual Basic application.

First, you add two new methods to the PRJUSROBJECT DLL file. The first procedure (CheckRights) is the main routine that verifies the user's rights to an object. The second routine (LoadRights) is a support routine that is called by CheckRights. You also need to add some more code to the general declarations portion of the 4class module (see Listing 21.30). Place this right after the code you added for the Login/Logout features of the class.

Listing 21.30. Adding code to the declarations section of the UsrObject class module.

`
` user access verification vars
Private wsRights As Workspace
Private dbRights As Database
Private rsRights As Recordset
Private blnRightsLoaded As Boolean
`
Enum urLevel
    urNone = 0
    urReadOnly = 1
    urReadModify = 2
    urReadModAdd = 3
    urReadModAddDel = 4
    urextended = 5
End Enum


Now add the method that loads the dataset into local objects. Enter the code from Listing 21.31 into your project.

Listing 21.31. Adding the LoadRights function to the DLL.

Private Sub LoadRights()
    `
    ` load user's rights collection
    `
    Dim strSQL As String
    `
    strSQL = "SELECT * FROM secAccess WHERE UserID='" & strUserIDProp & "`"
    `
    Set wsRights = DBEngine.CreateWorkspace("wsUser", "admin", "")
    Set dbRights = wsRights.OpenDatabase(strDBName)
    Set rsRights = dbRights.OpenRecordset(strSQL, dbOpenSnapshot)
    `
    blnRightsLoaded = True
    `
End Sub


Now add the routine to check the access rights for a particular secured object. Create a function called CheckRights and enter the segment of code in Listing 21.32.

Listing 21.32. Checking access rights for a secured object with accRights.

Public Function CheckRights(strObject As String, urCheck As urLevel) As Boolean
    `
    ` see if user has proper rights
    `
    On Error GoTo LocalErr
    `
    If blnRightsLoaded = False Then
        LoadRights
    End If
    `
    CheckRights = False ` assume it's not good!
    `
    With rsRights
        .MoveLast
        If .RecordCount <> 0 Then
            .FindFirst "Object='" & strObject & "`"
            If .NoMatch = False Then
                CheckRights = (urCheck <= .Fields("[Level]"))
            End If
        End If
    End With
    `
    Exit Function
    `
LocalErr:
    Err.Raise Err.Number, App.EXEName & ".usrObject", Err.Description
    `
End Function


This function accepts two parameters (the object and requested rights level), and it returns TRUE or FALSE depending on whether the user has been granted the requested rights level. If no rights level is on file, this function returns zero (no access).

Now save the DLL project files. Next, you add code to the Test project that uses the access rights to limit user access to the system.

For this example, you check the user's login rights at the very start of the application. If they do not have the proper rights, they cannot see the first screen.

The code in Listing 21.33 shows how the Main method should now look. This version of Main checks the user's access rights to the user maintenance form before displaying it to the user.

Listing 21.33. Before the change in the Main procedure.

Public Sub Main()
    `
    ` main entry for form-less project
    `
    Set objUser = New usrObject
    objUser.DBName = App.Path & "\..\..\data\security\security.mdb"
    `
    ` now try to start up
    If objUser.UserLogin = True Then
        If objUser.CheckRights("UserMaint", urReadModAdd) Then
            objUser.UsersForm
        Else
            MsgBox "You do not have rights to this application", vbExclamation, "Login Failed"
        End If
        objUser.LogUser urLogOut
End If
    `
    End
    `
End Sub


That's it! Now save and run the test project. This time, log into the application using MCA as the user and the password. This user does not have the proper rights to the User Maintenance data entry form. When you attempt to log in, you get a message telling you that you do not have rights to run the application (see Figure 21.10).

Figure 21.10. Failing the rights validation test.


With this tool, you can create and manage any type of secured program object you like. You can create security levels that restrict user access to entire programs or to individual forms or reports, disable menu items or command buttons, and even disable or hide individual fields within a form. It is very easy to add these security features to all your Visual Basic programs.

Auditing User Actions

Now that you have a way to force users to log in and out of your application and a way to establish and restrict user access to program objects, you can allow users to create an audit trail for all the secured activity. Audit trails are very valuable tools for tracking application use. With good audit trails you can tell when users log in and out of your application and what kinds of program operations they perform. Audit trails can also provide vital information you can use to debug your applications. Often users are not able to remember just what it was they were doing when they received an error message. Good audit trails can tell you the exact date and time the user encountered the error.

Developing a User Audit System

Adding a user audit system to your applications is really very easy. You need only a few additional routines in your DLL library. First, you need a way to write information to an audit log file. Second, you need a way to trigger the creation of audit records. You can write audit information any time. Typically, you want to keep track of each time a user logs into and out of an application. You might also want to log each time a user performs any critical operation, such as printing a sensitive report or running a mass update routine. One of the most common uses for audit logs is to track any modifications made to database records. Let's look at how you can create detailed audit logs that show all the fields that were modified, including the old value and the new value for each field.

The Audit Log Library Routines

You need only a handful of properties and methods to implement an audit log class. First, add a new class module to the DLL project. Set its name to logObject and save it as logObject.cls.

Now add the code in Listing 21.34 to the general declarations section of the new class module.

Listing 21.34. Declaring class-level variables for the logObject module.

Option Explicit
`
` local storage
Private strFileName As String
Private intFileHandle As Integer
Private strHeader As String
`


Next, you need to create the Property Let and Property Get statements for the logObject properties. The code in Listing 21.35 shows you how to do this. Add this code to your logObject class module.

Listing 21.35. Adding the Public properties of logObject.

Public Property Get FileName() As Variant
    FileName = strFileName
End Property

Public Property Let FileName(ByVal vNewValue As Variant)
    strFileName = vNewValue
End Property

Public Property Get LogHeader() As Variant
    LogHeader = strHeader
End Property

Public Property Let LogHeader(ByVal vNewValue As Variant)
    strHeader = vNewValue
End Property


Finally, add the code from Listing 21.36 to the Class_Initialize and Class_Terminate events of the module. This handles all the initial setup and final cleanup of the logObject class.

Listing 21.36. Coding the Class_Initialize and Class_Terminate events.

Private Sub Class_Initialize()
    `
    ` init stuff
    `
    strFileName = App.EXEName & ".log"
    intFileHandle = -1
    strHeader = "Audit Log"
    `
End Sub

Private Sub Class_Terminate()
    `
    ` clean up loose ends
    `
    On Error Resume Next
    `
    Close #intFileHandle
    `
End Sub


The main method of the class is the WriteLog method. This method is the one you call to send an audit line to the log. Add a new Public subroutine called WriteLog to your logObject class and enter the code from Listing 21.37.

Listing 21.37. Coding the WriteLog method.

Public Sub WriteLog(ParamArray varParms() As Variant)
    `
    ` write a log entry
    `
    On Error GoTo LocalErr
    `
    Dim lngItems As Long
    Dim lngLoop As Long
    Dim strLine As String
    Dim vbQuote As String
    Dim strUser As String
    Dim strDate As String
    `
    vbQuote = Chr(34)
    `
    If intFileHandle = -1 Then
        OpenLogFile
    End If
    `
    If modUsrObject.strUserIDSaved = "" Then
        strUser = "NO_USER"
    Else
        strUser = modUsrObject.strUserIDSaved
    End If
    `
    ` basic stuff
    strLine = vbQuote & Format(Now(), "general date") & vbQuote & ","
    strLine = strLine & vbQuote & strUser & vbQuote & ","
    `
    ` stuff from caller
    lngItems = UBound(varParms)
    For lngLoop = 0 To lngItems
        strLine = strLine & vbQuote & varParms(lngLoop) & vbQuote & ","
    Next
    strLine = Left(strLine, Len(strLine) - 1) ` drop last comma
    `
    ` send it out
    Print #intFileHandle, strLine
    `
    Exit Sub
    `
LocalErr:
    Err.Raise Err.Number, App.EXEName & ".logObject", Err.Description
    `
End Sub

TIP: Notice that you are enclosing all items in quotation marks. This makes it easier for you to convert this file into a database in the future (if you want to) because most conversion tools expect strings in quotations.

Notice that there is only one parameter sent to the WriteLog method. This parameter is actually an array of parameters. By using the ParamArray keyword, you are telling Visual Basic to put all parameters sent to this method into a single variant array. Now, whether the caller sends you 1 or 100 items in the call, you can access them all through this array.

Now you can add the two Private support routines OpenLogFile and WriteHeader. These are called as needed from the WriteLog method (see Listing 21.38).

Listing 21.38. Coding the OpenLogFile method.

Private Sub OpenLogFile()
    `
    ` open the file for output
    `
    On Error Resume Next
    `
    ` see if the file exists
    intFileHandle = FreeFile
    Open strFileName For Input As intFileHandle
    `
    ` if missing, make a new one
    If Err.Number <> 0 Then
        Open strFileName For Output As intFileHandle
        WriteHeader
    End If
    Close #intFileHandle
    `
    ` open existing file
    Open strFileName For Append As intFileHandle
    `
End Sub

Private Sub WriteHeader()
    `
    ` send out standard header
    `
    Print #intFileHandle, String(60, "*")
    Print #intFileHandle, strHeader
    Print #intFileHandle, "Created: " & Format(Now(), "general date")
    Print #intFileHandle, String(60, "*")
    Print #intFileHandle, ""
    `
End Sub


Notice that the OpenLogFile method first checks to see whether the file exists. If it does not, a new one is created and a log header is written to the new file.

Now add the final routine, ClearLog. This one allows you to clear an existing log file and start out fresh. Add the code from Listing 21.39 to your class module.

Listing 21.39. Coding the ClearLog method.

Public Sub ClearLog()
    `
    ` clear all records from the log
    `
    On Error GoTo LocalErr
    `
    Dim intFile As Integer
    `
    intFile = FreeFile
    `
    Open strFileName For Output As intFile
    WriteHeader
    Close #intFile
    `
    Exit Sub
    `
LocalErr:
    Err.Raise Err.Number, App.EXEName & ".logObject", Err.Description
    `
End Sub


Now save the DLL project again. You have created all the routines you need in order to add detailed audit trails to any Visual Basic project. This is the last of the modifications to the prjUsrObject DLL library. But before going on to the next section, let's make a final modification to the test project to verify that the audit routines are working.

Recording User Activity in an Audit File

The next step is to add code to the current project that records each time a user logs in or out of the application. You need to modify the general declarations section to add a module-level object variable for the logging object:

Option Explicit
`
` project-level vars
`
Public objUser As Object
Public objLog As Object ` <<< new line

You also need to modify the Main routine to include the initialization of the log object and writes to the log file. Listing 21.40 shows how your Main routine should look now.

Listing 21.40. Adding audit logging to the Main routine in the test project.

Public Sub Main()
    `
    ` main entry for form-less project
    `
    Set objUser = New usrObject
    objUser.DBName = App.Path & "\..\..\data\security\security.mdb"
    `
    Set objLog = New logObject
    objLog.filename = App.Path & "\" & App.EXEName & ".log"
    objLog.LogHeader = App.EXEName & " Audit Log"
    `
    ` now try to start up
    If objUser.UserLogin = True Then
        objLog.WriteLog "UserLogin", "Main"
        If objUser.CheckRights("UserMaint", urReadModAdd) Then
            objLog.WriteLog "UserMaint", "Main", "StartForm"
            objUser.UsersForm
            objLog.WriteLog "UserMaint", "Main", "ExitForm"
        Else
            MsgBox "You do not have rights to this application", vbExclamation, "Login Failed"
        End If
        objUser.LogUser urLogOut
        objLog.WriteLog "UserLogOut", "Main"
    End If
    `
    End
    `
End Sub


Now save and run the test project. Log into the application with default as the User ID and Password and then log back out. You have just created an audit file called PRJUSRTEST.LOG in the same directory as the test project. Open the file using Notepad and review its contents. You see the login record, the start of the form, the end of the form, and the log out record. The results of a similar run are included in the following lines:

************************************************************
prjTest Audit Log
Created: 19-Feb-97 12:39:19 AM
************************************************************

"19-Feb-97 6:04:59 AM","default","UserLogin","Main"
"19-Feb-97 6:04:59 AM","default","UserMaint","Main","StartForm"
"19-Feb-97 6:06:22 AM","default","UserMaint","Main","ExitForm"
"19-Feb-97 6:06:27 AM","default","UserLogOut","Main"

Once you confirm that your objects are working properly, compile the ActiveX DLL and close the project. You're now ready to create a short data entry application that uses all the features of your new security library.

Adding Security to a Data Entry Form

In this final example, you build a quick data entry form using data-bound controls, and add user login, access rights, and audit logging to the form. This shows how you can provide tight security even in simple data-bound forms.

WARNING: You need to run the SecTest.SQV script using the SQLVB5 interpreter to create the proper tables and entries in the BOOKS5.MDB that is used by this exercise. You can find this SQV script in the Chap21 folder on the CD. See Appendix A for more information on running SQLVB5.

First, start a new Visual Basic 5 Standard EXE project and select Project | References... to load the new Security library (prjUsrObject.dll) to your project (see Figure 21.11).

Refer to Table 21.6 and Figure 21.12 to lay out the new data entry form.

WARNING: The command buttons on this form are in a control array. You must pay close attention to how they are arranged on the form in order for the security features to work properly.


Figure 21.11. Loading the new security library into a Visual Basic project.

Figure 21.12. Laying out the new data entry form.


Table 21.6. Controls for the Data Entry form.
Control Property Setting
VB.Form Name frmSecTest
Caption "Form1"
ClientHeight 2010
ClientLeft 60
ClientTop 345
ClientWidth 4890
LinkTopic "Form1"
ScaleHeight 2010
ScaleWidth 4890
StartUpPosition 3 `Windows Default
VB.CommandButton Name cmdBtn
Caption "&Delete"
Height 300
Index 4
Left 1020
TabIndex 7
Top 1260
Width 900
VB.CommandButton Name cmdBtn
Caption "&Add"
Height 300
Index 3
Left 60
TabIndex 6
Top 1260
Width 900
VB.CommandButton Name cmdBtn
Caption "&Update"
Height 300
Index 2
Left 1980
TabIndex 5
Top 1260
Width 900
VB.CommandButton Name cmdBtn
Caption "&Refresh"
Height 300
Index 1
Left 2940
TabIndex 4
Top 1260
Width 900
VB.CommandButton Name cmdBtn
Caption "&Close"
Height 300
Index 0
Left 3900
TabIndex 3
Top 1260
Width 900
VB.TextBox Name txtField
DataSource "Data1"
Height 300
Index 2
Left 1380
TabIndex 2
Text "Text1"
Top 840
Width 1200
VB.TextBox Name txtField
DataSource "Data1"
Height 300
Index 1
Left 1380
TabIndex 1
Text "Text1"
Top 480
Width 2400
VB.TextBox Name txtField
DataSource "Data1"
Height 300
Index 0
Left 1380
TabIndex 0
Text "Text1"
Top 120
Width 600
VB.Data Name Data1
Align 2 `Align Bottom
Caption "Data1"
Connect "Access"
DatabaseName ""
DefaultCursorType= `DefaultCursor
DefaultType 2 `UseODBC
Exclusive 0 `False
Height 345
Left 0
Options 0
ReadOnly 0 `False
RecordsetType 1 `Dynaset
RecordSource ""
Top 1665
Width 4890
VB.Label Name lblPrompt
Caption "Label1"
Height 300
Index 2
Left 120
TabIndex 10
Top 840
Width 1200
VB.Label Name lblPrompt
Caption "Label1"
Height 300
Index 1
Left 120
TabIndex 9
Top 480
Width 1200
VB.Label Name lblPrompt
Caption "Label1"
Height 300
Index 0
Left 120
TabIndex 8
Top 120
Width 1200


Save this new project as PRJSECTEST.VBP and save the form as FRMSECTEST.FRM.

Next you need to add code to the form. Listing 21.41 shows the code for the general declarations area and the Form_Load event.

Listing 21.41. Coding the declarations and the Form_Load event.

Option Explicit
`
Dim objUser As Object
Dim objLog As Object

Private Sub Form_Load()
    `
    SetForm ` set up form
    SetLog ` set up log stuff
    SetUser ` set up user stuff
    `
End Sub


Now add the code for the three support routines called from Form_Load. The SetForm routine binds the input controls to the data fields, sets the form captions, and fills the data control with live records (see Listing 21.42).

Listing 21.42. Coding the SetForm method.

Public Sub SetForm()
    `
    ` set up form controls
    `
    `
    Me.Caption = "Security Test Form"
    `
    lblPrompt(0).Caption = "Author ID"
    lblPrompt(1).Caption = "Name"
    lblPrompt(2).Caption = "DOB"
    `
    txtField(0).DataField = "AUID"
    txtField(1).DataField = "Name"
    txtField(2).DataField = "DOB"
    `
    Data1.BOFAction = vbMoveFirst
    Data1.EOFAction = vbMoveLast
    Data1.DatabaseName = App.Path & "\..\..\data\books5.mdb"
    Data1.RecordSource = "Authors"
    Data1.Refresh
    `
End Sub


The SetLog method simply initializes the logObject and sets its properties in preparation for writing audit log entries (see Listing 21.43).

Listing 21.43. Coding the SetLog method.

Public Sub SetLog()
    `
    ` setup logging
    `
    Set objLog = New logObject
    objLog.filename = App.Path & "\" & App.EXEName & ".log"
    objLog.LogHeader = App.EXEName & " Audit Log"
    `
End Sub


Finally, the SetUser method initializes the user object, then lets the user login. If the login is successful, the row of command buttons is enabled, based on the user's rights for each item (see Listing 21.44).

Listing 21.44. Coding the SetUser method.

Public Sub SetUser()
    `
    ` set up user details
    `
    Dim ctlTemp As Control
    `
    ` create object & set properties
    Set objUser = New usrObject
    objUser.DBName = Data1.DatabaseName
    objUser.LoginTitle = "Security Test Form"
     `
    ` login and set up rights
    If objUser.UserLogin = True Then
        objLog.WriteLog "LogIn", objUser.UserID
        For Each ctlTemp In Controls
            If TypeOf ctlTemp Is CommandButton Then
                ctlTemp.Enabled = objUser.CheckRights(ctlTemp.Caption, ctlTemp.Index)
            End If
        Next
    Else
        End ` reject user
    End If
    `
End Sub


Notice that the control array index was used to actually indicate the rights level for each button. The Close button is index 0 (no rights needed), but the Delete button is index 4 (Read/Mod/Add/Del rights). This is a very efficient way to gather rights data for a user. Note also that the audit log is written if the user is successful in logging into the form.

Now you can add the code for the button array. This allows users (if they have the proper rights) to add, edit, or delete records from the table. Add the code from Listing 21.45 to the cdmBtn_Click event.

Listing 21.45. Adding code to the cmdBtn_Click event.

Private Sub cmdBtn_Click(Index As Integer)
    `
    ` handle button clicks
    `
    On Error GoTo LocalErr
    `
    Select Case Index
        Case 0 ` close
            objLog.WriteLog "LogOut", objUser.UserID
            objUser.LogUser urLogOut
            Unload Me
        Case 1 ` refresh
            Data1.UpdateControls
        Case 2 ` update
            Data1_Validate vbDataActionUpdate, 1
            Data1.UpdateRecord
        Case 3 ` add
            Data1.Recordset.AddNew
            txtField(0).SetFocus
        Case 4 ` delete
            objLog.WriteLog "Delete", Data1.RecordSource, txtField(0).DataField, txtField(0), ""
            objLog.WriteLog "Delete", Data1.RecordSource, txtField(1).DataField, txtField(1), ""
            objLog.WriteLog "Delete", Data1.RecordSource, txtField(2).DataField, txtField(2), ""
            Data1.Recordset.Delete
            Data1.Recordset.MovePrevious
    End Select
    `
    Exit Sub
    `
LocalErr:
    MsgBox Err.Description, vbExclamation, Err.Number
    `
End Sub


Note that there are steps to write deleted records to the audit file, but not to log edits or adds. These transactions are handled in the Validate event of the data control.

There is one support routine you need to add to the form. This routine is used to generate a "friendly name" for each of the action codes that occur in the Validate parameter list. Add the code in Listing 21.46 to your form.

Listing 21.46. Coding the GetAction method.

Public Function GetAction(intAction As Integer) As String
    `
    ` convert action constant into friendly name
    `
    Select Case intAction
        Case vbDataActionMoveFirst `1
            GetAction = "MoveFirst"
        Case vbDataActionMovePrevious `2
            GetAction = "MovePrevious"
        Case vbDataActionMoveNext `3
            GetAction = "MoveNext"
        Case vbDataActionMoveLast `4
            GetAction = "MoveLast"
        Case vbDataActionAddNew `5
            GetAction = "AddNew"
        Case vbDataActionUpdate `6
            GetAction = "Update"
        Case vbDataActionDelete `7
            GetAction = "Delete"
        Case vbDataActionFind `8
            GetAction = "Find"
        Case vbDataActionBookmark `9
            GetAction = "Bookmark"
        Case vbDataActionClose '10
            GetAction = "Close"
        Case vbDataActionUnload '11
            GetAction = "Unload"
    End Select
    `
End Function


Now you need to add one more set of code. Listing 21.47 shows the code you need to enter into the Data1_Validate event. This code checks to see if any of the columns have been altered. If so, an audit log is generated.

Listing 21.47. Adding code to the Data1_Validate event.

Private Sub Data1_Validate(Action As Integer, Save As Integer)
    `
    ` log any changes
    `
    Dim ctlTemp As Control
    `
    MousePointer = vbHourglass
    `
    ` check text boxes for changed data
    For Each ctlTemp In Controls
        If TypeOf ctlTemp Is TextBox Then
            If ctlTemp.DataChanged Then
                objLog.WriteLog GetAction(Action), Data1.RecordSource, ctlTemp.DataField, Data1.Recordset.Fields(ctlTemp.DataField), ctlTemp
            End If
        End If
    Next
    `
    MousePointer = vbNormal
    `
End Sub


That's all there is to it. Now save the form as FRMSECTEST.FRM and the project as PRJSECTEST.VBP, and run it. If you log in as USERA, you have very limited access to the data (see Figure 21.13).

Figure 21.13. Viewing the data entry form with limited access rights.


You now have a data-bound entry form with user login/logout, access checking, and field-level audit trails for any updated records. You can use this library on data bound forms, forms that use Microsoft DAO, or even other VBA-complaint applications that require auditing, user logins, or access rights management.

Summary

In today's lesson, you learned several methods you can use to increase the level of security for your Visual Basic database applications. You learned about the limitations of using the Microsoft Access SYSTEM security file and database encryption.

This lesson also showed you how you can add application-level security to your Visual Basic programs by adding user login/logout routines and creating a user access rights scheme for your applications. In this lesson, you designed and implemented a login screen that you can use for all your Visual Basic applications, and you created several screens for maintaining user lists and managing access rights for each user.

You also learned how to add an audit trail option to your programs. You added routines to a new OLE Server DLL library that logs all critical user activity to an audit trail file, including user logins, database modifications, and all critical program operations, such as running reports or processing mass database updates.

Best of all, the routines you built here can be used in all your future Visual Basic applications.

Quiz

1. What are the disadvantages and limitations of using the Microsoft Access SYSTEM.MDA file to secure a database?

2. What are the disadvantages of using data encryption to secure a database?

3. What is the difference between application security and database security?

4. What are the two main features of a good application security scheme?

5. Can application security schemes prevent unauthorized access to data by tools such as Visdata and Data Manager?

6. Why would you use an access rights security scheme in your application?

7. Why add audit trails to an application?

Exercise

Assume that you are a systems developer for a large corporation. Your company has had a problem keeping track of the fixed assets (desks, chairs, computers) in one of its divisions. Your manager has asked you to develop a system to help manage the tracking of these fixed assets.

These assets are a large portion of the net worth of this organization. Therefore, management wants to keep track of any changes made to the items in this database. You decide that the best way to assist them in their efforts is to place an audit log in your application.

Use the skills you developed in this chapter to modify project 20ABC01.VBP to construct a fixed asset tracking system. Follow these guidelines in the construction of this project: