• 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

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

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.

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.

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.

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.

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.

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.

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.

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.

---

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

---

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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