Implementing an Audit Trail

Imagine that a customer orders a large quantity of miniature plastic dinosaurs from your online dinosaur business and then disputes the credit card transaction after the dinosaurs have been shipped, claiming he never ordered the dinosaurs. As the owner of the online business, how can you verify that the customer actually placed the order? In security terms, when a user denies doing something such as placing an order, this is known as repudiation. In other words, repudiation refers to someone denying his obligations in a contract.

When a customer disputes a transaction, ultimately you will take follow- up action such as contacting the customer or talking to the credit card company. However, before doing this you must verify that the user actually made the order and it wasn’t the result of an unfortunate computer glitch. This is where an audit trail is useful. An audit trail is simply a mechanism for determining who did what and at what time. The simplest way to do this is to add an entry to a database table every time someone does something in the system.

Add auditing to the EmployeeManagementWeb application

In this exercise, you will add functionality to track each time someone updates her profile in the EmployeeManagementWeb application.

1. In Visual Basic .NET, open the EmployeeManagementWeb solution.

2. Open the MainModule.vb module, and add the following code to the end of the file:

   Namespace AuditLog
    Module AuditLog
    Sub Add(ByVal strUsername As String, _
    ByVal strDescription As String)
    ’Add an entry to the AuditTrail table 
    ’summarizing what the user did
    Dim strSQL As String
    ’WARNING: This example doesn’t scrub the user input,
    ’see chapter 7 for information on how to do this.
    strSQL = "INSERT INTO AuditTrail (Username, " & _
     "Description) VALUES (‘" & strUsername & _
     "‘, ’" & strDescription & "‘)"
    ’Exceute the INSERT statement, adding the item
    Dim cn As New Data.OleDb.OleDbConnection(G_CONNECTIONSTRING)
    Dim cmd As New System.Data.OleDb.OleDbCommand(strSQL, cn)
    cn.Open()
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    cn.Close()
    End Sub
    End Module
   End Namespace

3. Open the page EditMyProfile.aspx, and double-click the Save button to open the btnSave_Click event. Find the line that reads .SaveToDatabase(), and insert two lines around it:

   AuditLog.Add(strUserName, "Edited profile. Begin saving") .SaveToDatabase()
   AuditLog.Add(strUserName, "Edited profile. Finished saving")

   so that the btnSave_Click event now looks like this:

   Private Sub btnSave_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnSave.Click
    Dim strUserName As String = _
    HttpContext.Current.User.Identity.Name()
    Dim Employee As New clsEmployee(strUserName)
    With Employee
    .FirstName = Me.txtFirstname.Text
    .LastName = Me.txtLastName.Text
    .FullName = Me.txtFullName.Text
    .BankAccount = Me.txtBankAccount.Text
    AuditLog.Add(strUserName, "Edited profile. Begin saving")
    .SaveToDatabase()
    AuditLog.Add(strUserName, "Edited profile. Finished saving")
    End With
    Response.Redirect("MyProfile.aspx")
   End Sub

4. Press F5 to run the application, and log in using the name RKing and password RKing. After editing, save the profile information. After doing this, the system will insert two new records in the AuditTrail table: one when it began saving the updated information, and one when it finished. If you open the AuditTrail table in the Microsoft Access database EmployeeDatabase.mdb, the newly added entries will look similar to what is shown in the following illustration.

   

Why did we add two records instead of just one? The audit trail should track every event. Beginning to write to the database and finishing writing to the database are two different events. If we added a log entry only after the profile record was saved, we would miss situations where the profile editing succeeded but the AuditTrail adding failed. Likewise, if we added a log entry only before the record was edited, we would miss circumstances where the log entry was added but saving the profile failed. Logging two events ensures we can tell when a successful write happens. Another way to do the same thing in Microsoft SQL Server would be to enclose both the profile editing and the log appending in a transaction that would be committed only if both writes succeeded.