Oracle Security

SQL Scripts to Generate Scripts

Creating one or more of the three triggers presented in this chapter on each table you’d like to audit can be very tedious. The following three scripts can be used to generate SQL-generating SQL code to create the before-insert, after-update, and after-delete triggers for any table you would like to audit. By running these scripts, you can automate the process of script creation for all of the tables for which you want to implement auditing.

At the beginning of each script, you will find a brief purpose statement and any comments about what the script will not handle (if applicable).

Note

The script presented here uses the DUAL table. Heavy use of the DUAL table can lead to serialization in the SGA. You might want to create multiple DUAL tables to spread the load.

Generating a Before-Insert Trigger Script

The following example generates a before_insert trigger script.

                  
                  REM Purpose: Audit trail before-insert trigger generator.
                  
                  
                   REM This script creates a BEFORE-INSERT trigger script for REM a given table for auditing. SET SPACE 0; SET VERIFY OFF; SET NUMWIDTH 4; SET HEADING OFF; SET LINESIZE 80; SET PAGESIZE 0; SET FEEDBACK OFF; SET RECSEP OFF; SET LONG 255; SET ECHO OFF; SET TRIMSPOOL ON; PROMPT -----------------------------------------------------------; PROMPT Audit Trail BEFORE-INSERT Trigger Creation Script Generator; PROMPT -----------------------------------------------------------; -- accept tabowner char PROMPT 'Table Owner: '; ACCEPT tabname ...