|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 21
  - Securing Your Database Applications
    - Database Security
      - Limitations of the Microsoft Access SYSTEM Security
      - Limitations of Microsoft Jet Encryption
    - Application Security
    - Developing a User Login/Logout System
      - Building the User Maintenance Form
        - Figure 21.1.
    - Listing 21.1. Setting up the User Maintenance form.
    - Listing 21.2. Adding the BindInputs method.
    - Listing 21.3. Coding the StartProc routine.
    - Listing 21.4. Coding the cmdBtn_Click event.
    - Listing 21.5. Resizing the controls at runtime.
    - Listing 21.6. Disabling entry in the Text1_KeyPress event.
    - Listing 21.7. User-related declarations for the usrObject class.
    - Listing 21.8. Opening the dataset with the usrInit function.
    - Listing 21.9. Coding the Class_Initialize event.
    - Listing 21.10. Building a second project to test the usrObject library.
      - Figure 21.2.
    - Building the User LogIn and LogOut Routines
      - Figure 21.3.
    - Listing 21.11. Coding the declaration section of the form.
    - Listing 21.12. Initializing form values.
    - Listing 21.13. Code for the OK button.
    - Listing 21.14. Code for the Cancel button.
    - Listing 21.15. Adding the LoginUser function to the DLL library.
    - Listing 21.16. Coding the CheckUser routine.
    - Listing 21.17. Coding the LoadUsers method.
    - Listing 21.18. Adding the LogUser routine.
    - Listing 21.19. Coding the properties for the modUsrObject.bas module.
    - Listing 21.20. Modifying the Main routine to add the new User Login form.
      - Figure 21.4.
    - Developing a User Access Rights System
      - Defining the User Access Rights Scheme
      - Building the User Access Rights Maintenance Form
        - Figure 21.5.
    - Listing 21.21. Selecting the Access Rights form.
    - Listing 21.22. Coding the accLoadLists method.
    - Listing 21.23. Setting up access levels and command buttons.
    - Listing 21.24. Creating the accAddObject routine.
    - Listing 21.25. Deleting an object with accDelObject.
    - Listing 21.26. Deleting all existing rights objects with accDelAll.
    - Listing 21.27. The accCopyAll routine.
    - Listing 21.28. The accSetLevel routine.
      - Figure 21.6.
    - Listing 21.29. Coding the frmRights form.
      - Running the Access Rights Maintenance Forms
        - Figure 21.7.
        - Figure 21.8.
        - Figure 21.9.
      - Implementing Access Rights Security in Your Application
    - Listing 21.30. Adding code to the declarations section of the UsrObject class module.
    - Listing 21.31. Adding the LoadRights function to the DLL.
    - Listing 21.32. Checking access rights for a secured object with accRights.
    - Listing 21.33. Before the change in the Main procedure.
      - Figure 21.10.
    - Auditing User Actions
      - Developing a User Audit System
      - The Audit Log Library Routines
    - Listing 21.34. Declaring class-level variables for the logObject module.
    - Listing 21.35. Adding the Public properties of logObject.
    - Listing 21.36. Coding the Class_Initialize and Class_Terminate events.
    - Listing 21.37. Coding the WriteLog method.
    - Listing 21.38. Coding the OpenLogFile method.
    - Listing 21.39. Coding the ClearLog method.
      - Recording User Activity in an Audit File
    - Listing 21.40. Adding audit logging to the Main routine in the test project.
    - Adding Security to a Data Entry Form
      - Figure 21.11.
      - Figure 21.12.
    - Listing 21.41. Coding the declarations and the Form_Load event.
    - Listing 21.42. Coding the SetForm method.
    - Listing 21.43. Coding the SetLog method.
    - Listing 21.44. Coding the SetUser method.
    - Listing 21.45. Adding code to the cmdBtn_Click event.
    - Listing 21.46. Coding the GetAction method.
    - Listing 21.47. Adding code to the Data1_Validate event.
      - Figure 21.13.
    - Summary
    - Quiz
    - Exercise

## 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.

- You cannot encrypt selected tables within a database. When you turn encryption on, it affects all objects in the database. If you have only a few tables that are sensitive, you should consider moving those tables into a separate database for encryption.

- If you are deploying your database in an environment where multiple applications access your data, it is possible that these applications might not be able to read the encrypted data.

- If you want to take advantage of the replication features of Microsoft Jet, you cannot use encrypted databases.

### 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.

- Microsoft Data Bound Grid control

- Microsoft Tabbed Dialog control 5.0

- `TYSDBVB5`, the data entry library (`prjRecObject.dll`)

- Microsoft DAO 3.5 object library

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:

- Use Data Manager to create a new database for fixed assets. Name this database `CH20EX.MDB`, and add a table called Assets. Include the following fields in this table:

  Field | Type | Length
  AssetID | TEXT | 12
  Description | TEXT | 40
  Cost | CURRENCY |
  DateAcq | DATE/TIME |
  SerialNo | TEXT | 20
  Department | TEXT | 10

- Build a form to enter and edit the data records for this table. Use a data control to manage the records. Use the default (Text1, Text2, and so on) for text field's Name property. Set the Name property of the form to frmFixedAssets. Make this the first form displayed after the login process.

- Make your system write to an audit log any time a record is changed. Include `Ch20ex.mdb.Assets` as the name of the changed object, the user who made the change, and the AssetID of the changed record in the log. Use the same log as used by the login and logout routine. (Hint: Research the `Data Changed` event.)

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.