Value Change Auditing

The Value Change Auditing feature tracks changes to values in database tables.

The Value Change Auditing feature tracks changes to values in database tables. For each table in which changes are to be tracked, you select which SQL value-change commands to monitor (insert, update, delete). Each time a value-change command is run against a monitored table, before and after values are captured. On a scheduled basis, the change activity is uploaded to a Guardium® system, where all the reporting and alerting functions can be used. The basic steps to perform to use the Value Change Auditing feature are:

  1. Create an audit database on the database server. This database is where value-change data is stored until it is uploaded to the Guardium system. See Creating an Audit Database.
  2. Identify the tables to be monitored, and for each table select the value-change commands (insert, delete, update) for which changes will be recorded. To record the changes, a trigger is created for each table to be monitored, and that trigger writes the value-change data to the audit database. To allow updates to the audit database (by the trigger), all users with update privileges for the monitored table are given appropriate privileges for the audit database. This has implications for users who are given update privileges for that table later (see step 4). For detailed instructions on how to define the monitoring activities, see Define Monitoring Activities.
  3. Schedule uploads to transfer value-change data from the database server to the Guardium system. See Schedule Value-Change Uploads.
  4. Maintain audit database access privileges. After a trigger has been created, a new user may be given access to the table on which the trigger is based. If that user issues a monitored value-change command, it will fail because that user will not have appropriate privileges to update the audit database. See Maintain Privileged Users Lists.
  5. Monitor change activity from the administrator console, or use the Value Change Tracking query domain to create custom reports on the Guardium appliance. See Value-Change Reporting.

Define Monitoring Activities

After you define an audit database, use the Value Change Auditing Builder to identify the tables to be monitored, and to select the types of changes (inserts, updates, deletes) to be recorded.

  1. Open the Value Change Auditing Builder by navigating to Harden > Configure Change Control (CAS Application) > Value Change Auditing Builder.
  2. Click Add Datasource to open the Select datasource window.
  3. Select a datasource on which an audit database is defined, or click Add database to define a new audit database. For information about defining an audit database, see Creating an Audit Database.
  4. Click Save to close the Select datasource window and add the selected datasource to the Value Change Audit page.
  5. Optionally enter a Schema Owner and/or Object Name to limit the number of tables that are displayed when choosing the tables to be monitored. You can use the % (percent) wildcard character. For example, to limit the display to all tables that begin with the letter a, enter a% in the Object Name box.
  6. Click Choose Tables To Monitor to open the Define Data Audit panel.
  7. Mark the Select box for each table to be monitored.
    Attention: For Microsoft SQL Server, Sybase, and Sybase IQ, the Guardium system does not receive audit updates to any column that is a primary key or part of a composite key.
    Note: You cannot define a trigger for a table that contains one or more user-defined data types.

    The Trigger Defined column indicates if a trigger is already defined for the table. The Audit Insert, Audit Delete, and Audit Update check boxes indicate if the trigger will record changes for that command.

    If the Trigger Defined column is not marked, marking the Select checkbox for a table automatically marks all three the Audit checkboxes (Audit Insert, Audit Delete, and Audit Update). If you do not want to monitor one or two of those commands, clear the appropriate checkbox.

  8. Click Add Selections to define triggers for the selected tables. You will be informed of the action taken.
  9. Click OK to close the message box and re-display the Define Data Audit panel. The selected tables remain selected, and the Trigger Defined column is now marked for those tables. Note: The instant a trigger is defined for a table, it is active and recording changes for the selected commands in the audit database. The configuration of triggers is done entirely on the database server, which is unlike most other Guardium configurations, which are defined on the Guardium database, and then activated or deactivated as a separate task.
  10. To define additional actions, repeat these steps, or remove triggers by marking the appropriate Select check boxes and clicking Remove Selections.
  11. Click Done after you complete all changes.
    Note: The Cancel button does not back out any changes that you have made to triggers using the Add or Remove Selections buttons.

After Defining Monitoring Activities

If you have added value-change monitoring activities to a datasource for the first time, you should schedule uploads for this datasource, because the audit database will be emptied only after the data recorded there has been uploaded to the Guardium system. See the next section.

Schedule Value-Change Uploads

  1. Open the Value Change Auditing Builder by navigating to Harden > Configure Change Control (CAS Application) > Value Change Auditing Builder.
  2. Select the audit datasource for which you want to schedule uploads, and click Schedule Upload to open the general-purpose task scheduler. If you need help defining a schedule, see Scheduling in the Common Tools book.

Maintain Privileged Users Lists

When the value-change feature adds a trigger for a database table, all current users with permission to update that table are granted permission to update the audit database table. This is required because the trigger updates the audit database with new and/or old values. If a new user is granted update permission for a monitored table, when that user attempts an update, the update is not allowed because that user does not also have permission to update the audit database. When this happens, you must update the audit database privileged users list by using the Value Change Auditing Builder.

To update the audit database privileged users list, the database user ID that is used to log in to the monitored database must be the creator of any role to which new users have been added. Otherwise, the members of that role will not be available.

  1. Open the Value Change Auditing Builder by navigating to Harden > Configure Change Control (CAS Application) > Value Change Auditing Builder.
  2. Click Add Datasource to open the Select datasource window. Select the appropriate datasource from the list, and click Save.
  3. Click Update Audit Tables Privileged Users. The permissions for all users who can run triggers to update the audit database tables are updated, and you are informed when the operation completes.
  4. Click OK to close the message box.

Value-Change Reporting

You can view value-change data from the default Values Changed report, or you can create custom reports using the Value Change Tracking domain. By default, the Value Change Tracking domain is restricted to users having the admin role.

Values Changed Default Report

There is one default values-changed report available by navigating to Reports > Real-Time Guardium Operational Reports > Values Changed.

The main entity for the Values Changed report is the Changed Columns entity. In most cases, there is a separate row of the report for every column change that is detected for every audit action (Insert, Update, Delete). However, for MS SQL Server and Sybase, if the monitored table does not have a primary key, there are two rows per change, with the old and new values displayed on separate rows.