Today we'll 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 that will limit the function rights of users within your applications. You can even develop routines that will 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 will build a new set of library routines. You will be able to use these routines 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, audit trails that show when database records have been updated, and recording each time users perform critical application operations.
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'll learn the most notable of these limitations, as well as some suggestions on how you can avoid unexpected results.
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). This file must be available to your Visual Basic application either in the application path, or pointed to via the application .INI file or system registry. After the SYSTEM security file is defined, all attempts to open the secured database will cause Microsoft JET to request a user name and password before opening the database.
NOTE: Some 32-bit systems will have a Microsoft JET security file called SYSTEM.MDW (for example, Access 95). Others will continue to use the SYSTEM.MDA in both 16- and 32- bit modes (such as Visual Basic 4.0). The difference between these two files (SYSTEM.MDW and SYSTEM MDA) is in name only. Throughout this chapter you will see SYSTEM, SYSTEM.MDW, and SYSTEM.MDW. They can be used interchangeably.
We won't review the details of creating and updating the SYSTEM security file here (see Day 8, "Visdata" 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.
Once you have a SYSTEM.MDA file 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 SYSTEM.MDA file. You cannot use any Visual Basic application to create a SYSTEM file. You can only use Visual Basic to modify existing SYSTEM.MDA files.
You can have multiple versions of the SYSTEM.MDA security file available on your workstation or network. 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.MDA file for an application. This could result in preventing all users from accessing any of the data. Depending on the SYSTEM.MDA file installed, it could also result in reducing security to the point of allowing all users access to critical data normally not available to them. If you are using multiple SYSTEM.MDA files, be sure to store these files in the same directory as the application files and to include the specific path to this SYSTEM.MDA in all installation procedures.
Because all security features are stored in a single file, removing the SYSTEM.MDA from the workstation or network can result in effectively eliminating all database security. You can limit this possibility by storing the SYSTEM.MDA 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.
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.MDA at all. These applications might be able to open the database without having to go through the SYSTEM.MDA 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.MDA file. By doing this, you can completely ignore any security features built into the SYSTEM security file.
You can also use the encryption feature of Microsoft JET to encode sensitive data. However, you have no control over the type of encryption algorithm used to encode your data. You can only turn encryption on or off using the dbEncrypt or dbDecrypt option constants with the CreateDatabase and CompactDatabase methods.
The following list outlines other limitations to consider when using Microsoft JET encryption.
Application security 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 will see later in this chapter, forcing users to log into and out of your application will also give 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 only want to allow 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, files exports, and so on.
NOTE: Because application security only works 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, you will build a set of routines in a new procedure library called LIBUSER.BAS. This library will contain all the Sub and Function procedures needed to install and maintain application-level security for all your Visual Basic database applications.
The first routines you'll need to build as part of your application security 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 will 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'll construct these routines in a way that makes it easy for you to use them in any future Visual Basic database applications.
Load Visual Basic and start a new project. The first thing you'll 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 AppUsers. This is the same table that will be used to verify user logins at the start of all your secured applications. Use Table 20.1, Table 20.2, and Figure 20.1 to build the User Maintenance form.
Before building this form, however, you need to add a number of library files, custom controls, and object references to your project. Refer to the following lists to make sure you load all the additional files needed for this project.
You will also need the following library modules for this project. They can be found in the C:\TYSDBVB\CHAP20 directory. Add these to your project using the File | Add Files command from the Visual Basic main menu.
frmRpt.frm libError.bas libRecs.bas LibValid.bas
WARNING:
The library files in C:\TYSDBVB\CHAP20 contain changes that were not covered in previous chapters of this book. Be sure to use the versions stored in the C:\TYSDBVB\CHAP20 directory and not the ones you built earlier in the course.
NOTE: 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'll still have to retype some property settings, but it will be considerably less tedious than if you had to set them all manually.
Table 20.1. Controls for the User Maintenance form.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left Top Width | frmUserMaint User Maintenance 3495 1785 1530 5740 |
| SSPanel | Name Height Left Top Width | SSPanel1 2235 120 120 5355 |
| CommandButton | Name Caption Height Left Top Width | cmdOK &OK 300 4080 2880 1200 |
| CommonDialog | Name Left | CMDialog1 540 |
| CommandButton | Name (Add eight buttons) Height Left Top Width | cmdBtn(0-7) 495 0 0 1215 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1(0) 1—Fixed Single UserID: 300 120 120 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1(1) 1—Fixed Single Password: 300 120 540 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1(2) 1—Fixed Single Name: 300 120 960 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1(3) 1—Fixed Single Last Log In: 300 120 1380 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1(4) 1—Fixed Single Last Log Out: 300 120 1800 1200 |
| TextBox | Name Height Left Tag Top Width Name Height Left PasswordChar Tag Top Width | Text1(0) 300 1440 UserID 120 1200TextBox Text1(1) 300 1440 * Password 540 1200 |
| TextBox | Name Height Left Tag Top Width | Text1(2) 300 1440 Name 960 2400 |
| TextBox | Name Height Left Tag Top Width | Text1(3) 300 1440 LastIn 1380 1800 |
| TextBox | Name Height Left Tag Top Width | Text1(4) 300 1440 LastOut 1800 1800 |
Laying out the User Maintenance form.
Table 20.2. Menu table for the User Maintenance form.
| Menu Name | Menu Caption |
|---|---|
| mnuFile | &File |
| —mnuFilePrint | &Print... |
| —mnuFilePrintSet Printer | &Setup... |
| —mnuFileSp01 | - |
| —mnuFileExit | E&xit |
Save the form as FRMUSERM.FRM and the project as USERDEMO.VBP after you add all the controls, position them on the form, and add the menus. 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 Dim nBtnAlign As Integer
Next, place the code in Listing 20.1 in the Form_Load event of the form.
Listing 20.1. Setting up the User Maintenance form.
Private Sub Form_Load()
'
' set error trap and
' put name on trace stack
'
On Error GoTo FormLoadErr
errProcStack errPush, Me.Name + ".FormLoad"
'
' initialize the form
' and read the first rec
'
If RecInit(Me) = recOK Then
nResult = RecRead(Me, rsUsers)
End If
'
' report error and exit
'
If nResult <> recOK Then
cResult = "Unable to Open Users Table"
nErrExit = errExit
GoTo FormLoadErr
End If
'
' set form fields and
' report error if needed
'
If RecEnable(Me, False) <> recOK Then
cResult = "Unable to Enable Button Bar"
nErrExit = errExit
GoTo FormLoadErr
End If
'
usrRightsCheck ' get user rights
'
' set button stuff
nBtnAlign = btnAlignTop
BtnBarInit Me, nBtnAlign
BtnBarEnable Me, recEnableList
'
' center form
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
'
GoTo FormLoadExit ' exit
'
FormLoadErr:
nResult = errHandler(nResult, cResult, nErrExit)
Unload Me
'
FormLoadExit:
errProcStack errPop, ""
'
End Sub
Listing 20.1 performs several initialization operations. Notice that you have added the error trapping library routines to the project. You've seen most of the rest of the procedure in previous chapters.
Add the code to the cmdBtn_Click event. This code line calls the libRecs routine to handle all data entry functions.
Private Sub cmdBtn_Click(Index As Integer)
'
' handle user clicks
'
BtnBarProcess Index, Me, rsUsers, "UserID", ""
End Sub
Next, add the code line that resizes the controls when the user resizes the form.
Private Sub Form_Resize()
BtnBarInit Me, nBtnAlign ' resize buttons
End Sub
You also need to add some code (Listing 20.2) to the Text1_KeyPress event. This code prevents users from editing the Last Log In or Last Log Out fields on the form.
Listing 20.2. Disabling entry in the Text1_KeyPress event.
Private Sub Text1_KeyPress(Index As Integer, KeyAscii As Integer)
'
' disable entry for lastin/lastout fields
'
If Index = 3 Or Index = 4 Then
KeyAscii = 0
End If
End Sub
Now, you need to add code behind the three menu items you defined earlier. First, add Listing 20.3 behind the File | Print command.
Listing 20.3. Setting up the Print command.
Private Sub mnuFilePrint_Click()
'
' set error trap and
' put name on stack
'
On Error GoTo mnuFilePrintErr
errProcStack errPush, Me.Name + ".mnuFilePrintClick"
'
Load frmReport ' load the report form
'
' set report form vars
frmReport.txtReportName = App.Path + " \UserList.rpt"
frmReport.txtReportDBName = App.Path + " \USERDEMO.mdb"
frmReport.txtWindowTitle = "Application User List"
'
' write report run to log
logWriteFile "RunReport", frmReport.txtReportName
'
frmReport.Show vbModal ' show form to user
'
GoTo mnuFilePrintExit ' all done
'
mnuFilePrintErr:
nResult = errHandler(Err, Error$, errResume)
GoTo mnuFilePrintExit
'
mnuFilePrintExit:
errProcStack errPop, ""
'
End Sub
This routine loads the library form, sets several report parameters, and then shows the report front-end form to the user. The User Application Report has already been built and is stored in the C:\TYSDBVB\CHAP20 directory.
Now add code Listing 20.4 to launch the printer setup dialog behind the Printer Setup button.
Listing 20.4. Launching the Printer setup dialog.
Private Sub mnuFilePrintSet_Click() ' ' force print setup dialog ' CMDialog1.Flags = &H40 CMDialog1.Action = 5 End Sub
Finally, add a line of code (Listing 20.5) behind the File | Exit command and the OK button.
Listing 20.5. Programming the Exit and OK buttons.
Private Sub mnuFileExit_Click()
Unload Me
End Sub
Private Sub cmdOK_Click()
Unload Me
End Sub
Now save the project. Before you can run this project, you need to create a BAS module to handle a few general chores. Load a module into the project and set its Name property to LIBUSER.BAS. First, add some declarations to the top of the module, based on Listing 20.6.
Listing 20.6. Global declarations for the LIBUSER.BAS project.
Option Explicit ' ' global vars ' Global usrAction As Integer Global usrMaxTries As Integer Global usrUserID As String ' Global Const usrErr = -1 Global Const usrOK = 0 ' Global nResult As Integer Global cResult As String Global nErrExit As Integer ' Global dbUsers As Database Global rsUsers As Recordset Global cDBName As String Global cRSName As String ' Global Const accNone = 0 Global Const accRead = 1 Global Const accModify = 2 Global Const accAdd = 3 Global Const accDelete = 4 Global Const accExtended = 5 ' Global rsAccess As Recordset Global Const accOK = 0 Global Const accErr = -1 ' Global logWrFile As String
You'll use these variables for all your application security routines.
Now let's add a new routine (see Listing 20.7) that opens the user data set for you. Create a Function called usrInit and add the following code to the routine.
Listing 20.7. Opening the data set with the usrInit function.
Function usrInit() As Integer
'
' perform basic startup for form
'
On Error GoTo usrInitErr
errProcStack errPush, "LibUser.usrInit"
'
' open the recordset
nResult = RSOpen(cDBName, cRSName, dbOpenDynaset, dbUsers, rsUsers)
If nResult <> recOK Then
cResult = "Unable to Load Database"
nErrExit = errExit
GoTo usrInitErr
End If
'
nResult = recOK
GoTo usrInitExit
'
usrInitErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errExit
End If
errHandler nResult, cResult, nErrExit
GoTo usrInitExit
'
usrInitExit:
errProcStack errPop, ""
usrInit = nResult
'
End Function
Most of the code in Listing 20.7 is very familiar to you. This function opens the database and Dynaset, and reports any errors along the way.
You need one more routine that calls the User Maintenance form. Create a Sub called usrMaint and add the code in Listing 20.8.
Listing 20.8. Calling the User Maintenance form.
Sub usrMaint()
'
' perform general maintance on user table
'
On Error GoTo usrMaintErr
errProcStack errPush, "LibUser.usrMaint"
'
nResult = usrInit()
If nResult <> usrOK Then
cResult = "Unable to continue..."
nErrExit = errExit
GoTo usrMaintErr
End If
'
frmUserMaint.Show vbModal
'
GoTo usrMaintExit
'
usrMaintErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errExit
End If
errHandler nResult, cResult, nErrExit
GoTo usrMaintExit
'
usrMaintExit:
errProcStack errPop, ""
'
End Sub
This routine calls the function that initializes the database files and, if all is okay, it loads and runs the maintenance form. Any errors it encounters are reported.
Finally, let's add a Main module to start the whole thing off. You'll build on this module throughout the lesson today. First, you should only call this maintenance form. Create a Sub called Main, and add the code segment that follows the tip box.
TIP: You'll find 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.
Sub Main()
'
usrMaint ' run main routine
dbUsers.Close ' close database
End ' exit program
'
End Sub
Now save and run the project. Your screen should look similar to the one in Figure 20.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 text box to show only an asterisk (*) for every character in the field. The actual characters are stored in the database table.
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 a routine to verify the login and a routine to automatically log the user out when the application is terminated.
First, build the user login form. Add a new form to the existing USERDEMO.VBP project, using Table 20.3 and Figure 20.3 as a guide in building the form.
Table 20.3. Controls for the User Log In form.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left Top Width | frmUserLogIn User Login 2310 1995 2415 4575 |
| SSPanel | Name Height Left Caption Top Width | SSPanel1 1095 120 <blank> 660 2835 |
| Label | Name Alignment BorderStyle Caption Font FontSize Height Left Top Width | lblAppTitle 2—Center 1—Fixed Single Application Title MS Sans Serif/Bold 12 375 120 120 4215 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1 1—Fixed Single UserID: 300 120 180 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label2 1—Fixed Single Password: 300 120 600 1200 |
| TextBox | Name Height Left Top Width | txtUserID 300 1440 180 1200 |
| TextBox | Name Height Left Top Width PasswordChar | txtPass 300 1440 600 1200 * |
| CommandButton | Name Caption Default Height Left Top Width | Command1 &OK True 300 3120 1260 1200 |
| CommandButton | Name Caption Cancel Height Left Top Width | Command2 &Cancel True 300 3120 1260 1200 |
Laying out the User Login form.
You only need to add a few lines of code (Listing 20.9) to this form. First, add code to the Form_Load event that centers the form on the screen.
Listing 20.9. Centering a form.
Private Sub Form_Load()
' center form
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
End Sub
Next, add code (Listing 20.10) to the Form_Activate event to initialize form values at startup.
Listing 20.10. Initializing form values.
Private Sub Form_Activate()
'
' init input vars
' and set focus
'
txtUserID = ""
txtPass = ""
txtUserID.SetFocus
End Sub
You need to add a few lines behind the command buttons. First, add the code in Listing 20.11 for the OK button.
Listing 20.11. Code for the OK button.
Private Sub Command1_Click()
'
' user ok exit
'
usrAction = usrOK ' update global var
Me.Hide ' hide form (don't unload)
End Sub
This code sets a global variable and then hides the login form. Now add code from Listing 20.12 behind the Cancel button.
Listing 20.12. Code for the Cancel button.
Private Sub Command2_Click()
'
' user cancel exit
'
usrAction = usrErr ' usedr cancels
Me.Hide ' hide form (don't unload)
End Sub
That's it for the User Login form. Save this form as FRMUSERL.FRM.
Now you need to add code to the LIBUSER.BAS library file. You need three routines. The first routine calls the login form, the second routine validates the user's ID and password, and the third routine handles the user logout at the end of an application.
Create a new Function called usrLogin and add Listing 20.13.
Listing 20.13. Adding the usrLogin function to LIBUSER.BAS.
Function UsrLogin() As Integer
'
' load form and perform login
'
errProcStack errPush, "LibUser.UsrLogin"
'
Dim nTries As Integer
'
' init vars
cDBName = App.Path + " \USERDEMO.mdb"
cRSName = "AppUsers"
'
nResult = usrInit() ' load database, etc.
If nResult <> usrOK Then
cResult = "Unable To Continue."
nErrExit = errExit
GoTo usrLoginErr
End If
'
Load frmUserLogIn ' load login form
frmUserLogIn.lblAppTitle = "Test Login Application"
'
usrMaxTries = 3
nTries = 0
While nTries < usrMaxTries
nTries = nTries + 1
frmUserLogIn.Show vbModal ' show form to user
'
' check results
If usrAction = usrOK Then
' user pressed OK
nResult = usrValid(frmUserLogIn.txtUserID, frmUserLogIn.txtPass)
' if user is valid, exit
If nResult = usrOK Then
nTries = usrMaxTries
End If
Else
' user pressed cancel
nResult = usrErr
nTries = usrMaxTries
End If
Wend
'
GoTo usrLoginExit
'
usrLoginErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errExit
End If
errHandler nResult, cResult, nErrExit
GoTo usrLoginExit
'
usrLoginExit:
UsrLogin = nResult
errProcStack errPop, ""
'
End Function
The module in Listing 20.13 first sets some variables and then calls the usrInit routine to open the database and table. If that goes off without an error, the routine loads the user login form (without showing it) and then sets the application title on the form. (You can modify this for your own applications.) Next, the routine allows the user three login attempts. If the user presses the OK button on the form, the routine calls the usrValid function to check for a valid user. If the user is valid, the program exits the loop and exits the routine. If not, the loop returns to give the user another chance to log in or click Cancel. Note that the program unloads the form before leaving this routine.
Now let's code the usrValid routine. This is the module that looks up the user ID and (if it locates it) compares the password on the form to the one in the data table. Create a new Function called usrValid and enter the code in Listing 20.14.
Listing 20.14. Coding the usrValid routine.
Function usrValid(cUserID, cPass) As Integer
'
' attempt to validate the user login
'
' inputs:
' cUserID user login
' cPassword user login password
'
' returns usrOK if user is valid
' usrErr if error
'
' on error goto usrValidErr
errProcStack errPush, "LibUser.usrValid"
'
rsUsers.FindFirst "UserId='" + cUserID + "'"
If rsUsers.NoMatch = False Then
If UCase(rsUsers.Fields("password")) = UCase(cPass) Then
nResult = usrOK
Else
nResult = usrErr
cResult = "User Login Failed" + Chr(13)
cResult = cResult + "Invalid Password"
nErrExit = errResume
GoTo usrValidErr
End If
Else
nResult = usrErr
cResult = "User Login Failed" + Chr(13)
cResult = cResult + "Invalid UserID"
nErrExit = errResume
GoTo usrValidErr
End If
'
' all ok, so upate database
'
rsUsers.Edit
rsUsers.Fields("lastin") = Now
rsUsers.Fields("lastout") = Empty
rsUsers.Update
'
nResult = usrOK
GoTo usrValidExit
'
usrValidErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errExit
End If
errHandler nResult, cResult, nErrExit
GoTo usrValidExit
'
usrValidExit:
usrValid = nResult
errProcStack errPop, ""
'
End Function
This routine first searches the data set for the User ID. If the User ID is found, the routine then compares passwords. If all is okay, the routine updates the LastIn and LastOut fields of the data set and exits. If either entry is invalid, the user is shown an error message, and the routine exits for another attempt. After three attempts, the program exits.
You need only one more routine—the LogOut routine. This procedure needs simply to locate the requested user record and update the LastOut field. Create a new Function called usrLogOut and add Listing 20.15.
Listing 20.15. Adding the LogOut routine.
Function usrLogOut(cUserID As String) As Integer
'
' log user out of application
'
' inputs:
' cUserID userid to log out of app
'
' returns: usrOK if all ok, else error
'
On Error GoTo usrLogOutErr
errProcStack errPush, "LibUser.usrLogOut"
'
rsUsers.FindFirst "UserID='" + cUserID + "'"
If rsUsers.NoMatch = False Then
rsUsers.Edit
rsUsers.Fields("lastout") = Now
rsUsers.Update
nResult = usrOK
Else
cResult = "UserLogout Failed" + Chr(13)
cResult = cResult + "Unable to Locate UserID [" + cUserID + "]"
nResult = usrErr
nErrExit = errResume
GoTo usrLogOutErr
End If
'
GoTo usrLogOutExit
'
usrLogOutErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errExit
End If
errHandler nResult, cResult, nErrExit
GoTo usrLogOutExit
'
usrLogOutExit:
usrLogOut = nResult
errProcStack errPop, ""
'
End Function
Before continuing, save this project as USERDEMO.VBP.
You need to modify the Main procedure you created earlier to add the new User Login form. Modify the Main routine to match the lines of code in Listing 20.16.
Listing 20.16. Modifying the Main routine to add the new User Login form.
Sub Main()
Dim nlog As Integer
'
nlog = UsrLogin() ' attempt login
If nlog <> 0 Then
MsgBox "Login Failed!" ' invalid user
Else
usrUserID = frmUserLogIn.txtUserID ' update variable
usrMaint ' call main form routine
nResult = usrLogOut(usrUserID) ' log out user
End If
dbUsers.Close ' close database
End ' end program
End Sub
Instead of just calling the usrMaint routine right away, you first make the user log in with a valid ID and password. If the user successfully logs in, the program saves the user ID and runs the usrMaint routine. When the user returns from the User Maintenance form, usrMaint executes the usrLogOut before ending the program.
Save and run this project. Your screen should look similar to the one in Figure 20.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 will automatically update 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
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.
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 involves a scale of access rights from a level with no rights at all through a level with all possible rights. Table 20.4 shows the proposed set of access rights.
Table 20.4. The set 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 |
| Level 5 | All, Plus Extended Rights |
In the Table 20.4, each rights level adds additional privileges. The final level (Level 5) includes all previously defined rights plus a special extended right. You can use this level to define any special powers depending on the object or system (supervisor control, for example).
You'll set up a data table that contains three columns, User ID, Object, and Level. The User ID should match one in the AppUser table you have 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 data set for each secured program object. This default set will be 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 two primary routines to the LIBUSER library file in order to implement an access rights scheme. First, you need a routine that calls a data entry form, which allows administrators to manage user access records. Next, you need a routine to verify user access information when requested. You need a few support routines along the way, but you'll get to those later.
The first order of business is to create the data entry form needed to create and edit user access rights. This form will be launched from the User Maintenance Form. Use Table 20.5 and Figure 20.5 as guides in laying out the Access Rights Maintenance Form. Save this form as FRMACCM.FRM
Laying out the Access Rights Maintenance Form.
NOTE: 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 on the form.
Table 20.5. Control table for the Access Rights Maintenance form.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left MaxButton MinButton Top Width | FrmAccM Access Rights Maintenance 4365 1335 False False 1650 6915 |
| SSPanel | Name Height Left Top Width | SSPanel1 3315 120 120 6555 |
| CommandButton | Name Caption Default Height Left Top Width | cmdOK &OK True 300 5280 3540 6915 |
| CommandButton | Name Caption Height Left Top Width | cmdAccess(0) &Add 300 2640 1080 1200 |
| CommandButton | Name Caption Height Left Top Width | cmdAccess(1) &Delete 300 2640 1500 1200 |
| CommandButton | Name Caption Height Left Top Width | cmdAccess(2) C&opy All 300 2640 1920 1200 |
| CommandButton | Name Caption Height Left Top | cmdAccess(3) &Erase All 300 2640 2360 |
| CommandButton | Name Caption Height Left Top Width | cmdAccess(4) &Set Level 300 2640 2760 1200 |
| DBGrid | Name AllowUpdates Caption DataSource Height Left Top Width | dbgDefault False Secure Objects dtaDefault 1995 120 1080 2400 |
| DBGrid | Name AllowUpdates Caption DataSource Height Left Top Width | dbgUserID False Secure Objects dtaUserID 1995 3960 1080 2400 |
| DataControl | Name Caption DatabaseName Height Left RecordSource Top Visible Width | dtaDefault Data1 USERDEMO.mdb 300 120 AppAccess 2160 False 2400 |
| DataControl | Name Caption DatabaseName Height Left RecordSource Top Visible Width | dtaUserID Data2 USERDEMO.mdb 300 3960 AppAccess 2160 False 2400 |
| Label | Name BorderStyle Caption Height Left Top Width | label1 1—Fixed Single UserID: 300 120 180 1200 |
| Label | Name BorderStyle Height Left Top Width | Label2 1—Fixed single 300 120 600 1200 |
| Label | Name BorderStyle Height Left Top Width | lblUserID 1—Fixed Single 300 1440 180 1200 |
| Label | Name BorderStyle Height Left Top Width | lblName 1—fixed Single 300 1440 600 2415 |
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 (right) 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 setting up the User Access Rights system it to add three form-level variables.
Option Explicit Dim cSQLDefault As String Dim cSQLUserID As String Dim cAccDB As String
Next, add code to the Form_Load event to center the form on the page.
Listing 20.17. Centering the User Access Rights form.
Private Sub Form_Load()
' center the form on screen
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
End Sub
Then add code to the Form_Activate event. This line calls a routine that populates the data-bound grids. You'll add the grid code a bit later.
Private Sub Form_Activate()
accLoadLists ' refresh the grids
End Sub
Now add a bit of code behind the cmdOK button.
Private Sub cmdOK_Click()
Me.Hide
End Sub
Notice that you're just hiding the form, not unloading it. You'll need some of the information that is stored on this form a bit later on, so you need to keep it in memory for now.
Add the code from Listing 20.18 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 object.
Listing 20.18. Setting up access levels and command buttons.
Private Sub cmdAccess_Click(Index As Integer)
'
' handle command buttons
'
Select Case Index
Case Is = 0
' handle single add
accAddObject
Case Is = 1
' handle single delete
If dtaUserID.Recordset.RecordCount > 0 Then
accDelObject
Else
MsgBox "No Objects to Delete", vbInformation, "Delete Object"
End If
Case Is = 2
' handle full add
accAddAll
Case Is = 3
' handle full delete
If dtaUserID.Recordset.RecordCount > 0 Then
accDelAll
Else
MsgBox "No Objects to Delete", vbInformation, "Delete All Objects"
marla End If
Case Is = 4
' handle access level
If dtaUserID.Recordset.RecordCount > 0 Then
accSetLevel
Else
MsgBox "No Objects on File", vbInformation, "Set Access 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, too.
Now for the tough stuff. First, you add the routine that populates the two data-bound grids. Create a Sub called accLoadLists and add the code in Listing 20.19.
Listing 20.19. Populating the two data-bound grids with accLoadLists.
Sub accLoadLists()
'
' load data controls for DBLists
'
cSQLDefault = "SELECT * FROM AppAccess WHERE
UserID='Default' ORDER BY Object"
cSQLUserID = "SELECT * FROM AppAccess WHERE UserID='" +
Trim(frmUserMaint.Text1(0)) + "' ORDER BY Object"
cAccDB = App.Path + " \USERDEMO.mdb"
'
dtaDefault.DatabaseName = cAccDB
dtaDefault.RecordSource = cSQLDefault
dtaDefault.Refresh
dbgDefault.ReBind
'
dtaUserID.DatabaseName = cAccDB
dtaUserID.RecordSource = cSQLUserID
dtaUserID.Refresh
dbgUserID.ReBind
End Sub
This procedure initializes the two data controls and then refreshes them and rebinds the data grids.
Next, add the code that adds an object from the Default Set to the current User's Set. Create a new Sub called accAddObject and place Listing 20.20 in the routine.
Listing 20.20. Create the accAddObject routine.
Function UsrLogin() As Integer
'
' load form and perform login
'
errProcStack errPush, "LibUser.UsrLogin"
'
Dim nTries As Integer
'
' init vars
cDBName = App.Path + " \USERDEMO.mdb"
cRSName = "AppUsers"
'
nResult = usrInit() ' load database, etc.
If nResult <> usrOK Then
cResult = "Unable to continue"
nErrExit = errExit
GoTo usrLoginErr
End If
'
Load frmUserLogIn ' load login form
frmUserLogIn.lblAppTitle = "Test Login Application"
'
usrMaxTries = 3
nTries = 0
While nTries < usrMaxTries
nTries = nTries + 1
frmUserLogIn.Show vbModal ' show form to user
'
' check results
If usrAction = usrOK Then
' user pressed OK
nResult = usrValid(frmUserLogIn.txtUserID, frmUserLogIn.txtPass)
' if user is valid, exit
If nResult = usrOK Then
nTries = usrMaxTries
End If
Else
' user pressed cancel
nResult = usrErr
nTries = usrMaxTries
End If
Wend
'
GoTo usrLoginExit
'
usrLoginErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errExit
End If
errHandler nResult, cResult, nErrExit
GoTo usrLoginExit
'
usrLoginExit:
UsrLogin = nResult
errProcStack errPop, ""
'
End Function
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 (see Listing 20.21) deletes an object from the User List. Create a new Sub called accDelObject and add the following code.
Listing 20.21. Deleting an object with accDelObject.
Sub accDelObject()
'
' attempt to remove an object
'
Dim cObject As String
Dim nResult As Integer
'
' get confirmation
cObject = dtaUserID.Recordset.Fields("Object")
nResult = MsgBox("Delete [" + cObject + "] from User Access?",
vbInformation + vbYesNo, "Delete Object")
If nResult = vbYes Then
dtaUserID.Recordset.Delete ' drop it
accLoadLists ' refresh lists
End If
'
End Sub
The routine first asks for confirmation before deleting the object from the list.
Now you'll tackle a tougher one. The Sub called accDelAll removes all the existing rights objects for the current user. Add the code in Listing 20.22.
Listing 20.22. Deleting all existing rights objects with accDelAll.
Sub accDelAll()
'
' remove all objects for this user
'
Dim cUserID As String
Dim nResult As Integer
Dim cSQL As String
Dim cDBLocal As String
Dim dbLocal As Database
'
' init vars
cUserID = Trim(frmAccM.lblUserID)
cSQL = "DELETE * FROM AppAccess WHERE UserID='" + cUserID + "'"
cDBLocal = dtaUserID.DatabaseName
'
' get confirmation
nResult = MsgBox("Delete All Object for UserID [" + cUserID + "]?",
vbInformation + vbYesNo, "Delete All Objects")
If nResult = vbYes Then
Set dbLocal = DBEngine.OpenDatabase(cDBLocal)
'
On Error Resume Next
Workspaces(0).BeginTrans ' start trans
dbLocal.Execute cSQL ' delete SQL
If Err = 0 Then
Workspaces(0).CommitTrans ' ok
Else
Workspaces(0).Rollback ' oops!
MsgBox "Unable to Complete Transaction - Request Denied",
vbInformation, "Delete All Objects"
End If
On Error GoTo 0
'
dbLocal.Close ' close local db
accLoadLists ' refresh lists
End If
'
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 (dbLocal). 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 accAddAll routine. Because some records might already be on file, you first must delete any existing items. The routine in Listing 20.23 contains several SQL statements and, of course, they are covered by Visual Basic transactions, too.
Listing 20.23. The accAddAll routine.
Sub accAddAll()
'
' add all objects for this user
'
Dim cUserID As String
Dim cSQLDel As String
Dim cSQLTmp As String
Dim cSQLUpd As String
Dim cSQlDrp As String
Dim cSQLAdd As String
Dim nResult As Integer
Dim cDBLocal As String
Dim dbLocal As Database
'
' set error trap and put routine on trace stack
On Error GoTo accAddAllErr
errProcStack errPush, Me.Name + ".accAddAll"
'
' init vars for this routine
cUserID = Trim(frmAccM.lblUserID)
cDBLocal = dtaUserID.DatabaseName
cSQLDel = "DELETE * FROM AppAccess WHERE UserID='" + cUserID + "'"
cSQLTmp = "SELECT * INTO AppTemp FROM AppAccess
WHERE AppAccess.UserID='Default'"
cSQLUpd = "UPDATE AppTemp SET UserID='" + cUserID + "'"
cSQLAdd = "INSERT INTO AppAccess SELECT * FROM AppTemp"
cSQlDrp = "DROP Table AppTemp"
'
' main event here
nResult = MsgBox("Replace all Current Objects for UserID [" + cUserID +
"] with Default Set?", vbInformation + vbYesNo, "Copy All Objects")
If nResult = vbYes Then
' open the db
Set dbLocal = DBEngine.OpenDatabase(cDBLocal)
'
On Error Resume Next
Workspaces(0).BeginTrans ' start the trans
dbLocal.Execute cSQLDel ' delete old recs
dbLocal.Execute cSQLTmp ' pull out defaults
dbLocal.Execute cSQLUpd ' rename defaults
dbLocal.Execute cSQLAdd ' add back to access
dbLocal.Execute cSQlDrp ' drop temp table
If Err = 0 Then
Workspaces(0).CommitTrans ' all ok
Else
Workspaces(0).Rollback ' oops!
MsgBox "Unable to Complete Transaction - Request Denied",
vbInformation, "Copy All Objects"
End If
On Error GoTo accAddAllErr
'
dbLocal.Close ' close db
accLoadLists ' re-load the grids
End If
'
GoTo accAddAllExit ' exit
'
' local error stuff
accAddAllErr:
If Err <> 0 Then
nResult = Err
cResult = Error$
nErrExit = errResume
End If
nResult = errHandler(nResult, cResult, nErrExit)
GoTo accAddAllExit
'
accAddAllExit:
errProcStack errPop, "" ' remove name from stack
'
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'll build next. The second form is where you can set the access level for the selected rights object. Create a new Sub called accSetLevel and add the Listing 20.24.
Listing 20.24. The accSetLevel routine.
Sub accSetLevel()
'
' set vars and call access level form
'
Dim cTitle As String
'
' init vars
cTitle = Trim(frmAccM.lblUserID) + "["
cTitle = cTitle + Trim(dtaUserID.Recordset.Fields("Object")) + "]"
frmAccR.fraRights = cTitle
frmAccR.lblLevel = dtaUserID.Recordset.Fields("Level")
frmAccR.Caption = "User Access Rights"
'
frmAccR.Show vbModal ' show rights form
'
' update object w/ new rights value
dtaUserID.Recordset.Edit
dtaUserID.Recordset.Fields("Level") = Val(frmAccR.lblLevel)
dtaUserID.Recordset.Update
'
accLoadLists ' refresh list
'
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 project. Use Table 20.6 and Figure 20.6 as guides in laying out the Rights List.
Laying out the Rights List form.
Table 20.6. Control table for the Rights List form.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left MaxButton MinButton Top Width | frmAccR Rights List 3900 2520 False False 1485 3435 |
| CommandButton | Name Caption Height Left Top Width | cmdOK &OK 300 1800 3060 1200 |
| SSPanel | Name Caption Height Left Top Width | SSPanel1 <blank> 2835 120 120 3075 |
| Frame | Name Height Left Top Width | fraRights 2475 180 180 2715 |
| OptionButton | Name Caption Height Left Top Width | Option1 No Access 300 180 240 2400 |
| OptionButton | Name Caption Height Left Top Width | Option2 Read Only 300 180 600 2400 |
| OptionButton | Name Caption Height Option3 Left Top Width | Read/Modify 300 180 960 2400 |
| OptionButton | Name Caption Height Left Top Width | Option4 Read/Modify/Add 300 180 1320 2400 |
| OptionButton | Name Caption Height Left Top Width | Option5 Read/Modify/Add/Delete 300 180 1680 2400 |
| OptionButton | Name Caption Height Left Top Width | Option6 Read/Mod/Add/Del/Xtended 300 180 2040 2400 |
| Label | Name Visible | lblLevel False |
There is very little code to add to this form. First, add the centering routine to the Form_Load event.
Listing 20.25. Centering the new form.
Private Sub Form_Load()
' center form on screen
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
End Sub
Next, add some code to the Form_Activate event. This code initializes the set of radio buttons based on the value in the lblLevel control.
Listing 20.26. Initializing radio buttons with the lblLevel control.
Private Sub Form_Activate()
'
' set radio button based on label value
'
Select Case lblLevel
Case Is = 0
' no access
Option1 = True
Case Is = 1
' read only
Option2 = True
Case Is = 2
' read/modify
Option3 = True
Case Is = 3
' read/mod/add
Option4 = True
Case Is = 4
' read/mod/add/del
Option5 = True
Case Is = 5
' read/mod/add/del/extended
Option6 = True
End Select
'
End Sub
Now you need to add a single line of code (see Listing 20.27) in the Click events of each of the radio buttons. This sets the new rights level each time a radio button is clicked.
Listing 20.27. Coding the radio buttons in the Click events.
Private Sub Option1_Click()
' set for no access
lblLevel = 0
End Sub
Private Sub Option2_Click()
' set for read only
lblLevel = 1
End Sub
Private Sub Option3_Click()
' set for read/modify
lblLevel = 2
End Sub
Private Sub Option4_Click()
' set for read/mod/add
lblLevel = 3
End Sub
Private Sub Option5_Click()
' set for read/mod/add/del
lblLevel = 4
End Sub
Private Sub Option6_Click()
' set for read/mod/add/del/extended
lblLevel = 5
End Sub
Finally, add a line of code behind the CmdOK_Click event to exit the form. Notice that you are leaving the form up in memory because you'll need some information from some of its controls.
Private Sub cmdOK_Click()
Me.Hide
End Sub
Now save this form as FRMACCR.FRM. Before the project is complete, you need to add two more menu items to the User Maintenance Form. Call up the FRMUSERM form and open the menu editor by selecting Tools | Menu Editor from the Visual Basic main menu. Add two menu items at the bottom of the list. Set the first caption to &Access and the name to mnuAccess. Set the second item, indented under the first, with a caption of &Set User Access and a name of mnuAccessSetUser.
Now add the code in Listing 20.28 in the mnuAccessSetUser_Click event.
Listing 20.28. The mnuAccessSetUser_Click event.
Private Sub mnuAccessSetUser_Click()
'
' set some vars and load access maint
'
frmAccM.lblUserID = Text1(0)
frmAccM.lblName = Text1(2)
frmAccM.Show vbModal
End Sub
Save the modified FRMUSERM form. In the next section, you'll walk through a session of setting user rights and adding new secured objects to the database.
After building the Access Rights forms, you are ready to run the project. When you start the program, you'll be prompted to enter a password. As before, enter USERA for both the User ID and the Password. This will bring 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, use the Find button to locate the TEMPUSER record and then select Access | Set User Access from the main menu. Your form should look similar to the one in Figure 20.7.
Editing the access rights for a user.
You can see a set of default access objects on the left of Figure 20.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 on a row selector in the Secure Objects list (the Default List) and clicking the Add button. You'll see that the selected object has been copied to the User Access list with the default access rights setting (see Figure 20.8).
Adding an object to the User Access list.
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 20.9).
Changing the Access Level.
TIP: If you want to make things a little easier for your users when they move from the numbering system for the various access levels to the text definitions in this dialog box, you can simply add the level number to the caption shown in Table 20.6 when you create the form.
Select the Read/Modify radio button and click the OK button. When you return to the previous form, you'll 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 will make it impossible to add or edit existing access rights of other new users.
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'll add two procedures to the LIBUSER.BAS library file. The first procedure creates a Snapshot set that contains all the defined rights for the requested user. The second procedure will be used to verify that the user has rights to perform a requested task.
Create a new Function in LIBUSER.BAS called accGetSet, and enter the following code. The routine in Listing 20.29 creates a Snapshot data object that contains all the defined access rights for the user that is logged into the application.
Listing 20.29. Adding the accGetSet function to LIBUSER.BAS.
Function accGetSet(cUserID As String, dbUsers As Database,
rsAccess As Recordset) As Integer
'
' load the recordset with a
' snapshot of the users' rights
'
' inputs:
' cUserID user to get rights for
' dbUsers init'ed db containing table
' rsAccess access rights returned
'
' returns: accOK if all ok
' accErr if error
'
On Error GoTo accGetSetErr
errProcStack errPush, LibUser.accGetSet
'
Dim cSQL As String
'
cSQL = "SELECT * FROM AppAccess WHERE USerID='" + cUserID + "'"
Set rsAccess = dbUsers.OpenRecordset(cSQL, dbOpenSnapshot)
'
nResult = accOK
GoTo accGetSetExit
'
accGetSetErr:
nResult = errHandler(Err, Error$, errExit)
nResult = Err
GoTo accGetSetErr
'
accGetSetExit:
accGetSet = nResult
errProcStack errPop, ""
'
End Function
Now add the routine to check the access rights for a particular secured object. Create a Function called accRights and enter the segment of code in Listing 20.30.
Listing 20.30. Checking access rights for a secured object with accRights.
Function accRights(cObject As String, rsSet As Recordset) As Integer
'
' check for requested rights acccess
'
' inputs:
' cObject app object user wants
' rsSet snapshot of all user's rights
'
' returns: user's rights level
'
Dim nTemp As Integer
'
rsSet.FindFirst "object='" + cObject + "'"
If rsSet.NoMatch = False Then
accRights = rsSet.Fields("level")
Else
accRights = accNone
End If
End Function
This function accepts two parameters (the object and the Snapshot set), and it returns the rights level on file. If no rights level is on file, it returns zero (no access).
Save the LIBUSER.BAS file. Next, you will add code to the User Maintenance form that uses the access rights to limit user access to the system.
For this example, you employ security on a report and an entire data entry form, and you also set security levels for modify, add, delete privileges on a data form. To keep this example brief, you establish all this security from the User Maintenance form.
To set all of these security rights, you need a single routine that is invoked at the form level each time the form is loaded. Load the FRMUSERM.FRM file and create a new Sub procedure called usrRightsCheck and add Listing 20.31.
Listing 20.31. The usrRightsCheck Sub procedure.
Sub usrRightsCheck()
'
' check user's rights to do stuff
'
' check on access to rights form
If accRights("frmAccM", rsAccess) >= accRead Then
mnuAccess.Enabled = True
Else
mnuAccess.Enabled = False
End If
'
' check on access to report
If accRights("rptUserList", rsAccess) >= accRead Then
mnuFilePrint.Enabled = True
Else
mnuFilePrint.Enabled = False
End If
'
' check on form function buttons
Select Case accRights("frmUserM", rsAccess)
Case Is = accModify
recEnableList = "01011111"
Case Is = accAdd
recEnableList = "11011111"
Case Is = accDelete
recEnableList = "11111111"
End Select
End Sub
In Listing 20.31, you are checking user security for three different program objects. The first is the Access Rights Form (frmAccM). Notice that you compare the results of AccRights to the accRead constant. If the user has at least read only rights, you can load the form. If not, you disable the menu option to prevent the user from attempting the operation.
You do a similar rights check on the Application User list report. In this case, as long as the user has rights to read the report, you allow her to call it up and print it. If you wanted to, you could add additional security at the report form level to prevent users from actually printing the report unless they have additional rights. This way users could view the reports, but not create hard copies.
Finally, you check the user's rights levels for access to the User Maintenance form itself. As you check the user's rights, you adjust the string used to enable the command buttons on the form.
After adding this routine to the data entry form, you need to modify an existing line of code in the User Maintenance Form. Bring up the Form_Load procedure and find and replace the line that first enables the button bar.
Before modification:
BtnBarEnable Me, "11111111"
After modification:
BtnBarEnable Me, recEnableList
You also need to make a modification to the Main procedure of the LIBUSER.BAS module. This added code loads the Rights Snapshot before running the User Maintenance Form. The next two excerpts (Listing 20.32 and Listing 20.33) show a copy of the code before modification and a version after modification.
Listing 20.32. Before the change in the Main procedure.
Else
cUserID = frmUserLogIn.txtUserID
usrMaint
nResult = usrLogOut(cUserID)
End If
Listing 20.33. After modifying the Main procedure.
Else
cUserID = frmUserLogIn.txtUserID
If accGetSet(cUserID, dbUsers, rsAccess) = accOK Then
usrMaint
End If
nResult = usrLogOut(cUserID)
End If
That's it! Now save and run the project. This time, log into the application using MCA as the user and the password. This user has restricted rights for all the sections you installed in the preceding modifications. When the User Maintenance form comes up, you'll see grayed out buttons and grayed out menu items showing where the logged in user (MCA) has limited access (see Figure 20.10).
Running the User Maintenance form with restricted access.
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 individual forms or reports, disable menu items or command buttons, and even disable or hide individual fields within a form. It is also very easy to add these security features to all your Visual Basic programs.
Now that you have a way to force users to log in and out of your application and a method of establishing and restricting user access to program objects, you can allows users to create an audit trail for all the secured activity. Audit trails are a 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 have performed. Audit trails can also provide vital information you can use to debug your applications. Often users will not be able to remember just what it was they were doing when they received an error message. Good audit trails can often tell you the exact date and time the user experienced the error.
Adding a User Audit system to your applications is really very easy. You need only a few additional routines in your LIBUSER.BAS library. First, you need a method of writing information to an audit log file. Second, you need a method of triggering the creation of audit records. You can write audit information any time. Typically, you'll 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.
You only need to add three routines to the library in order to provide detailed audit trails for your Visual Basic applications. First, you need a routine to establish the name and location of the audit trail file. Next, you need a routine that writes the audit information to the audit file. Last, you add a routine that loops through all the controls on a data entry form and creates audit records for each field that has been updated.
Before you add code routines, you need to define a new global variable for the audit trail file. Add the following line to the declaration section of the LIBUSER.BAS file.
Global logWrFile As String
The first routine you need establishes the name of the audit trail file. This short routine simply initializes a global variable. If an empty string is passed, the routine creates its own name for the audit file (based on the application name). You'll make the audit trail file an ASCII text file in the comma-delimited format. This is easy to read without special programs or utilities and can also be quickly converted into a Microsoft Access format database if needed.
Create a new Sub called logInitFile in the LIBUSER.BAS module and enter the code in Listing 20.34.
Listing 20.34. Adding the logInitFile routine to LIBUSER.BAS.
Sub logInitFile(cLogFile)
'
' sets up logfile name for system
'
If Len(cLogFile) = 0 Then
logWrFile = App.EXEName + ".log"
Else
logWrFile = cLogFile
End If
End Sub
Add the routine that writes the audit trail information to the log file. This routine accepts one required parameter and several optional ones. The notes in the code explain most of how this routine works. When you add the routine to check for changed fields, this will make more sense, too.
Create a new Sub called logWriteFile and add Listing 20.35.
Listing 20.35. Recording the audit trail with the logWriteFile routine.
Sub logWriteFile(cLogType, Optional cRecordSet, Optional cKey,
Optional cField, Optional cOld, Optional cNew)
'
' write action out to log file
'
' inputs:
' cLogType type of log record
' cRecordSet database or program object
' cKey table key field or other ID
' cField record field
' cOld old value
' cNew new value
'
Dim nCh As Integer
Dim Qt As String
'
On Error GoTo logWriteFileErr
errProcStack errPush, LibUser.logWriteFile
'
Qt = Chr(34)
'
If Len(logWrFile) = 0 Then
logInitFile ""
End If
'
If Len(usrUserID) = 0 Then
usrUserID = "SYSTEM"
End If
'
nCh = FreeFile
Open logWrFile For Append As nCh
'
' write date/time, user, and action
'
Print #nCh, Qt + Format(Now, "General Date") + Qt + ",";
Print #nCh, Qt + usrUserID + Qt + ",";
Print #nCh, Qt + cLogType + Qt;
'
' write recordset if we have it
'
If IsMissing(cRecordSet) = False Then
Print #nCh, "," + Qt;
Print #nCh, cRecordSet;
Print #nCh, Qt;
End If
'
' write record key info, if we have it
'
If IsMissing(cKey) = False Then
Print #nCh, "," + Qt;
Print #nCh, cKey;
Print #nCh, Qt;
End If
'
' write record field, if we have it
'
If IsMissing(cField) = False Then
Print #nCh, "," + Qt;
Print #nCh, cField;
Print #nCh, Qt;
End If
'
' write old data, if we have it
'
If IsMissing(cOld) = False Then
Print #nCh, "," + Qt;
Print #nCh, cOld;
Print #nCh, Qt;
End If
'
' write updated data, if we have it
'
If IsMissing(cNew) = False Then
Print #nCh, "," + Qt;
Print #nCh, cNew;
Print #nCh, Qt;
End If
'
' end line and close file
'
Print #nCh, ""
Close nCh
'
GoTo logWriteFileExit
'
logWriteFileErr:
nResult = errHandler(Err, Error$, errResume)
On Error Resume Next
Close nCh
GoTo logWriteFileExit
'
logWriteFileExit:
errProcStack errPop, ""
'
End Sub
In Listing 20.35, you first check to make sure that a valid audit file and user are declared. Then you open the audit file and begin adding a new line. Only one parameter is required for the routine, but it can have several optional ones. The program tests for the existence of each parameter and, if it's there, writes it to the audit line.
TIP: Notice that you are enclosing all items in quotation marks. This will make it easier for you to convert this file into a database in the future (if you want to) since most conversion tools expect strings in quotations.
Now add the final routine. This one is designed to work with the LIBREC library. This new routine loops through all the controls on a data entry form and creates entries in the audit log for each field that has been changed. Create a new Sub called logChanged to the LIBUSER library file and add the code in Listing 20.36.
Listing 20.36. Tracking changes in each field with the logChanged routine.
Function logChanged(frmName As Form, rsName As Recordset) As Integer
'
' checks for controls that have changed
'
On Error GoTo logChangedErr
errProcStack errPush, "LibUser.logChanged"
'
Dim cTag As String ' field tag
Dim cKey As String ' record key field
Dim cOld As String ' old column value
Dim cNew As String ' new column value
Dim ctlTemp As Control ' for collection
'
For Each ctlTemp In frmName.Controls
cTag = UCase(Trim(ctlTemp.Tag)) ' get field name
cKey = rsName.Fields(0).Name + "=" +
rsName.Fields(0) ' get key field info
If Len(cTag) <> 0 Then
If ctlTemp <> rsName.Fields(cTag) Then
' write out log record
logWriteFile "RecUpdate", rsName.Name, cKey, cTag,
rsName.Fields(cTag), ctlTemp
End If
End If
Next
'
GoTo logChangedExit
'
logChangedErr:
nResult = errHandler(Err, Error$, errResume)
GoTo logChangedExit
'
logChangedExit:
errProcStack errPop, ""
'
End Function
The routine in Listing 20.36 loops through all the controls on the form. If it finds one that has its Tag property set, the routine builds a log record to send to the audit file.
Now save the project. You have created all the routines you need in order to add detailed audit trails to any Visual Basic project. In the next section, you'll add code to the data entry forms and the Main procedure that will actually make the audit entries.
The next step is to add code to the current project that logs each time a user logs in or out of the application. You also add code that logs all changes to the AppUsers table. Finally, you add code that creates a log entry each time a user runs the User List report.
To add login and logout auditing to this application, you need to add two lines to the Main routine in LIBUSER. Listing 20.37 shows the modified Main routine with the new lines marked with multiple asterisk comments. Make the indicated changes to your version of Main.
Listing 20.37. The modified Main routine for login/logout auditing.
Sub Main()
Dim nlog As Integer
Dim cUserID As String
'
nlog = UsrLogin()
If nlog <> 0 Then
MsgBox "Login Failed!"
Else
usrUserID = frmUserLogIn.txtUserID
logWriteFile "UserLogIn" ' **** added audit line
'
If accGetSet(usrUserID, dbUsers, rsAccess) = accOK Then
usrMaint
End If
'
logWriteFile "UserLogOut" '**** added audit line
nResult = usrLogOut(usrUserID)
End If
dbUsers.Close
End
End Sub
It's time to add auditing to the User Maintenance Form. Actually, you'll add a single line to the RecWrite routine of the LIBREC.BAS file. Open the LIBREC.BAS file and insert the following line right after the last DIM statement and before the first FOR EACH statement.
logChanged frmName, rsName ' *** added auditing
That's all you need to do. Of course, all applications that use the LIBREC.BAS library can now provide audit trail logs.
Finally, let's add a line to the frmUserM form to log each time a user runs the User List report. Open the frmUserM form and select the File | Print command to bring up the mnuFilePrint_Click event. Add the following line of code just before the frmReport.Show vbModal line.
logWriteFile "RunReport", frmReport.txtReportName
This line creates a log entry that shows the date and time the user ran the named report.
Save and run the project. Log into the application with default as the User ID and Password. Edit a record, run the User List report, and then exit the application. You have just created an audit file called USERDEMO.log in the Visual Basic default directory. Open the file using Notepad and review its contents. You'll see the login record, the list of changed fields from the time you modified a record, the record of the report run, and the final user logout. The results of a similar run are included in the following lines:
"08/30/95 02:36:28 PM","default","UserLogIn" "08/30/95 02:36:56 PM","default","RecUpdate","AppUsers","UserID=Default ", "PASSWORD","default","DEFAULT" "08/30/95 02:37:17 PM","default","RunReport",App.Path + " \UserList.rpt" "08/30/95 02:37:23 PM","default","UserLogOut"
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 chapter 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 chapter, 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 existing libraries that will log 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.
Assume that you are a system 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 tab 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:
| Field | Type | Length |
|---|---|---|
| AssetID | TEXT | 12 |
| Description | TEXT | 40 |
| Cost | CURRENCY | |
| DateAcq | DATE/TIME | |
| SerialNo | TEXT | 20 |
| Department | TEXT | 10 |