Appendix: Oracle Streams for Before and After Value Tracking

In addition to the native facilities within the Guardium product used for showing before and after values of DML, getting before/after values for Oracle can be accomplished through the use of Oracle Streams and through the use of Guardium’s external data connector (upload) facility. Streams are used to create change records for any change that affects a sensitive column, and the upload job is used to bring the data into the Guardium repository, where you can issue reports, combine the data with other details, and add these reports into the sign-off process.

This appendix provides an end-to-end example on how this is done. In this example you will set up a Streams change table for changes made to the EMP table in the SCOTT schema. More specifically, you will capture all DML operations. Additionally, if the SAL column changes you will capture both the before and the after value of the salary. You then define the upload job on the Guardium appliance, the domains and the reports. This is a simple example but even highly complex examples will only change in the CDC definition; not in the Guardium setup. Additional will be mainly more columns for which the after values are required and more extra attributes that can associate these records with records inspected natively within Guardium.

The process described below uses Oracle 10g but is the same on 9i and 11g. Note that the example can be refined – e.g. for assigning fewer privileges to the users etc. Consult your DBA for more information on Streams.

Step 1: Set Up Streams on the Oracle System

  1. Log onto Oracle and create a user:

  2. Grant Streams privileges to the user:

  3. Assign privileges on the target table:

  4. Create the EMP_AUDIT table that will accept the change records from EMP:

  5. Grant access to the audit table:

  6. Create the queue:

  7. You only want change records for DML, not DDL. Makes sure your database is in ARCHIVEMODE and run:

  8. Tell the capture process that you want to know who made the change:

  9. Tell Oracle where to start the capture (replace on0satu with your database name):

  10. Now build the handler that will massage a change record in the format for the EMP table into the format of the EMP_AUDIT table (including the additional username information):

  11. Create the DML handlers using the procedure:

  12. Create the apply rule. This tells Oracle where to insert the massaged records. Again, replace on0satur with your database name:

  13. You want this to continue even if there is an error, so:

  14. Start the apply and capture processes:

You’re done!

Step 2: Test the Streams on the Oracle System

  1. Connect as scott:

  2. Make a few changes:

  3. Connect as another user and make a change:

  4. Now let’s check that it worked and that the records are in place:

Perfect!

Step 3: Define the Upload Job

It’s time to move to define the upload process.

  1. Log onto the Guardium appliance as a user who has access to the upload facility (as installed, users with the user role have access).

  2. Open the Custom Tables panel by clicking Monitor/Audit > Build Reports > Custom domain builder.

  3. Click the Upload Definition button to open the Import Table Structure panel.

  4. Enter EMP_AUDIT in both the Entity Desc and Table Name boxes.

  5. Enter select * from EMP_AUDIT in the SQL Statement box.

  6. Click Add Datasource, and define a datasource to access the EMP_AUDIT table you created in the previous steps. See the Datasources topic in the Common Tools book for instructions on defining datasources.

  7. Click the Retrieve button

  8. On the Custom Tables panel, click the Upload Data button.

  9. On the Import Data panel, add a DML statement to delete the audit records you’ve already read. You can also manage this deletion elsewhere and you can also define conditions for deleting exactly the records read. For now, let’s use a feature within the Guardium system based on dates where the Guardium system remembers the last time data was brought back:

  10. Normally you would define a schedule for the upload to occur by clicking the Add to Tables Schedule to Upload Data checkbox.

  11. For now, just click Save, and then click Upload.

The data is now in your database.

Step 4: Define a Simple Custom Domain and Report

  1. Back on the Custom domain builder, click on Domains and click on New.

  2. Fill in a domain name (EMP_AUDIT_DOMAIN1, for example) and select the EMP_AUDIT entity.

  3. Select EMP_AUDIT.UPD_DATE as the timestamp attribute and click Apply:

  4. Click on the Build Reports tab again and click on the Custom query builder.

  5. Select your domain and click Search.

  6. Click New to generate a new report.

  7. Fill in a Query Name and select the EMP_AUDIT entity.

  8. Drag and drop all your attributes onto the report:

  9.   

  10. Click Save, then click Generate Tabular, then click Add to Pane.

  11. Navigate to your new report on the My New Reports tab.

  12. Click the (Customize) button on your report panel, and enter appropriate dates to view your data, which should look something like this:

  13.     

The before after values are displayed based on your capture definitions.