This audit trail will be only an intellectual exercise unless you can use the data collected. At one company we’re familiar with, the audit trail application literally saved the day more than once by making deleted data from a critical table recoverable. On one occasion, a new employee, trying to show how quickly she could perform her work, deleted several values from a vital requirements tracking system. Using the information stored in the audit trail application, the deleted information was restored to the appropriate tables almost as quickly as she had deleted the data from them. Since her actions were very random, replacing only the deleted data would have been extremely time-consuming and tedious without this application.
Here is a sample set of SQL*ReportWriter code used to display the complete set of audit trail data. The same queries will work with Oracle Reports.
REM This is the SQL code used in SQL*ReportWriter or Oracle Reports to make
REM an Audit Report.
REM The first query is the parent to the second and third queries.
REM The second and third queries are joined to it by the "raid"
REM field (raid1=raid2, raid1=raid3).
REM Query1
SELECT raid raid1, table_name, rid,
DECODE(action, 'U','UPDATE', 'D','DELETE') action, user_name,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MM:SS') timestamp
FROM audit_row
WHERE user_name LIKE NVL(UPPER(:username),'%')
AND table_name LIKE NVL(UPPER(:tabname),'%')
AND action LIKE NVL(UPPER(:act),'%')
AND rid BETWEEN NVL(:lo_rid,1)
AND NVL(:hi_rid,999999999999)
AND timestamp BETWEEN NVL(:lowdate,'01-JAN-80')
AND NVL(:highdate,SYSDATE)
ORDER BY user_name, table_name, timestamp;
REM Query 2
SELECT raid raid2, column_name, old_value, new_value
FROM audit_column
ORDER BY raid, caid;
REM Query 3
SELECT raid raid3, row_data
FROM audit_delete
ORDER BY raid;Figure 11.1 shows a screen shot of the SQL*ReportWriter Parameter Form customized for use with the report.
To view the contents of the AUDIT_ROW table, you can use the following script in SQL*Plus.
SET LINESIZE 80;
SET PAGESIZE 22;
SET TRIMSPOOL ON;
COLUMN table_name FORMAT a10 HEADING "Table Name";
COLUMN rid FORMAT 9990 HEADING "RID";
COLUMN raid FORMAT 9990 HEADING "RAID";
COLUMN action FORMAT a3 HEADING "Act";
COLUMN user_name FORMAT a8 HEADING "User Name";
COLUMN timestamp FORMAT a20 HEADING "Timestamp";
SELECT ar.table_name, ar.rid, ar.raid, ar.action, ar.user_name,
TO_CHAR(ar.timestamp,'DD-Mon-YYYY HH24:MI:SS') timestamp
FROM audit_row ar
ORDER BY ar.table_name, ar.rid, ar.raid;