099 - 11.3 Using the Audit Data in Reports


Oracle Security
By William Heney, Marlene Theriault
Table of Contents
Chapter 11.  Developing a Sample Audit Application

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; 


Oracle Security
Oracle Security Handbook : Implement a Sound Security Plan in Your Oracle Environment
ISBN: 0072133252
EAN: 2147483647
Year: 1998
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net