11.3 Using the Audit Data in Reports
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.
11.3.1 The Audit Trail Data Display
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.
Figure 11.1. The Customized Report Parameter Screen
11.3.2 The AUDIT_ROW Table 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;