Guardium’s 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 executed against a monitored table, before and after values are captured. On a scheduled basis, the change activity is uploaded to a Guardium appliance, where all of Guardium’s reporting and alerting functions can be used.
The basic steps to perform to use the Value Change Auditing feature are:
From the Administration Console, create an audit database on the database server. This is where value-change data will be stored until it is uploaded to the Guardium appliance. See Create an Audit Database.
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 will be created for each table to be monitored, and that trigger will write the value-change data to the audit database. To allow updates to the audit database (via the trigger), all users with update privileges for the monitored table will be given appropriate privileges for the audit database. This has implications for users who may be given update privileges for that table later (see step 4, below). For detailed instructions on how to define the monitoring activities, see Define Monitoring Activities, below.
Schedule uploads to transfer value-change data from the database server to the Guardium appliance. See Schedule Value-Change Uploads, below.
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.
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.
For Oracle, there is an alternative to the trigger-based Value-Change Auditing. Before and after values for Oracle can be captured using Oracle Streams, and the changes uploaded to the Guardium appliance via the external data connector feature. Once the Streams data has been uploaded to the Guardium appliance, users can create reports, combine the data with other details, and add those reports to workflow sign-off processes. See the Oracle Before and After Values appendix for a tutorial illustrating this alternative approach for Oracle.
After defining 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.
Do one of the following to open the Value Change Auditing Builder:
Administrators, select Tools > Config & Control > Value Change Auditing Builder.
Users, select the Value Change Auditing Builder from a custom tab. (This application is available to users, but does not appear on a default layout. To add this application to a custom tab, see Portal Customization in the Common Tools book.)
Click Add Datasource to open the Datasource Finder panel.
Select a datasource on which an audit database has been defined. If an audit database has not yet been defined, see Create an Audit Database.
Click Add to close the Finder and add the selected datasource to the Value Change Audit panel.
Optionally enter a Schema Owner and/or Object Name to limit the number of tables that will be displayed when choosing the tables to be monitored. You can use the % (percent) wildcard character. For example, to limit the display to all tables beginning with the letter a, enter a% in the Object Name box.
Click Choose Tables To Monitor to open the Define Data Audit panel.
Mark the Select box for each table to be monitored.
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 has already been defined for the table. The Audit Insert, Audit Delete, and Audit Update checkboxes 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.
Click the Add Selections button to define triggers for the selected tables. You will be informed of the action taken.
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.
To define additional actions, repeat the steps above, or remove triggers by marking the appropriate Select checkboxes and clicking Remove Selections.
Click Done after you have completed all changes.
Note: Be aware that the Cancel button does not back out any changes that you have made to triggers using the Add or Remove Selections buttons.
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 appliance. See the following topic.
Do one of the following to open the Audit Datasource Finder:
Administrators, select Tools > Config & Control > Value Change Database Builder.
Users, select the Value Change Database Builder from a custom tab. (This application is available to users, but does not appear on a default layout. To add this application to a custom tab, see Portal Customization in the Common Tools book.)
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.
When the value-change feature adds a trigger for a database table, all current users with permission to update that table will be granted permission to update the audit database table as well. 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 will not be allowed because that user will not also have permission to update the audit database. When this happens, follow the procedure outlined below to update the audit database privileged users list via the Value Change Auditing Builder.
Be aware that to update the audit database privileged users list, the database user ID that is used to log into 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.
Do one of the following to open the Value Change Auditing Builder:
Administrators, select Tools > Config & Control > Value Change Auditing Builder.
Users, select the Value Change Auditing Builder from a custom tab. (This application is available to users, but does not appear on a default layout. To add this application to a custom tab, see Portal Customization in the Common Tools book.)
Click Add Datasource to open the Datasource Finder panel, select the appropriate Datasource from the list, and click Add.
Click Update Audit Tables Privileged Users. The permissions for all users who may execute triggers to update the audit database tables will be updated, and you will be informed when the operation completes.
Click OK to close the message box.
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.
For a description of the entities and attributes of the Value Change Tracking domain, see the Domains, Entities, and Attributes appendix.
For instructions on how to build reports and queries, see the Audit & Report help book.
There is one default report available on the administrator portal (select Daily Monitor > Values Changed), and one drill-down report available from that one (Values Changed Details).
The main entity for the Values Changed report is the Changed Columns entity. In most cases, there will be a separate row of the report for every column change 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 will be two rows per change, with the old and new values displayed on separate rows.