Before/After Value Change Auditing in Guardium

by John Haldeman, Security Practice Lead

 

Overview

A little known but useful Guardium function is Value Change Auditing. We just had our second customer ask about it, so we thought it was time for a blog post on the subject. This post will explain what Before/After Value Change Auditing is and explain how to configure it to monitor an Oracle data source. While Oracle is used as an example, the same mechanisms and concepts apply to the other database types that are supported for this function (DB2, Informix, MS SQL Server, Sybase).

Traditionally, when Guardium audits traffic, it monitors the communication stream between the database client and database server. So, you can see the SQL statement executed. For example:

UPDATE CUSTOMER SET CCN = ‘1234 1234 1234 1234’ WHERE NAME = ‘John';

The Problem is that we know what value CCN is after the update, but cannot easily see what the value of CCN was before this update.

Some customers need this information for certain compliance requirements that stress accuracy and change control. Theoretically, you could find out this information if you had a fairly complete audit trail. You would go into your audit logs, look for the all previous updates or inserts for that record, find the last two executed, and see what the value was and is now. This isn’t so easy, though. Most database systems in an environment are older than the Guardium deployment, and you’d only be able to see the previous values of records and fields that were affected since Guardium was deployed. Additionally, you likely aren’t logging all of the SQL to this level of detail if you are following best practices. Finally, it’s difficult to build reports that would show the before/after values consistently for each type of SQL statement that modifies the data.

So, using the standard Guardium audit trail generated by the STAP is an awkward solution. Luckily Guardium has the capabilities to help you solve the problem more easily with Value Change Auditing. It works like this:

1. Guardium deploys a new “audit database” (what this database is depends on the platform. For example in MS SQL it’s a new DB, in Oracle it’s a new Schema).
2. Guardium then deploys triggers to monitor and log changes to individual tables. Those triggers are set up populate corresponding audit tables in the audit database that Guardium created in step 1. When you are using Oracle, you can deploy streams instead of triggers to do this
3. As transactions are run the before/after values are populated by the database management system using the triggers and the audit database that Guardium created
4. On a regularly scheduled basis, Guardium imports the resulting data into its own database and then purges that data from the audit database

Diagram of Value Change Auditing Architecture in Guardium

Let’s look at how you would accomplish this in Guardium for an Oracle database.

STEP 1:  Create the Audit Database

This can be completed using the “Value Change Audit DB Creation” area of the Tools menu in the Guardium admin user interface. First, add a data source for the database you would like to implement value change auditing with. For Oracle, you would specify a user that has the SYSDBA role and log in as SYSDBA with that user (the creation of users that can create streams requires this level of permissions). We’ve had a lot more luck in using the standard Oracle JDBC driver (as opposed to the default Data Direct driver) in connecting to databases as SYSDBA. You can upload a standard Oracle JDBC driver in the Customer Upload section of the administration console. You then specify the tablespaces that this audit database will use. It’s a good idea to use a new purpose-built tablespace for value change auditing data to help prevent I/O contention with the database you are monitoring.

The DataSource Created to create the audit database - Note the use of the standard Oracle driver and logging in as SYDBA

Other Parameters - Entering a password for the Audit DB User, and tablespace parameters

STEP 2:  Deploy Triggers

Once you have successfully created the audit database, you would deploy some triggers. This can be done using the Value Change Auditing Builder application in the tools menu of the admin user interface. Add the same datasource as in Step 1 and choose the tables you would like to have monitored. Then deploy your triggers (or streams) by selecting the tables you want and clicking Add Selections.

Defining the Triggers/Streams to be Created and Whether to Monitor inserts, updates, and deletes

STEP 3:  Watch for Changes in the Monitored Table

Step 3 is done automatically by the database but feel free to change some values on a table so that you can see value change auditing in action.

STEP 4:  Importing the Results into Guardium

Step 4 is performed in the Value Change Audit DB Update & Upload application in the tools menu of the admin user interface. Select the audit database you created in Step 1 and schedule imports to occur on a regular basis. Once data is imported you can view the data in a report. One such report can be found in the Daily Monitor tab of the admin user interface. That report’s name is “Values Changed”.

The Change Value Data Imported into Guardium (click here to enlarge)

Conclusion

As you can see, this report is easier to read and understand compared to ones that would be generated by Guardium’s standard audit trail for this purpose. This does come at a cost, however, because you have effectively implemented a native logging solution and you should be concerned about the overhead introduced by the triggers and additional logging. As such, you should be careful to only implement value change auditing when the business requires this kind of tracking and when the business is willing to accept the costs of the mechanism.