Building an Audit Trail for Your Data - Creating Audit Tables
(Page 2 of 6 )
The script to create the audit tables (as well as the script to create audit triggers) builds an executable script by spooling to an operating system file.
--audit_tables.sql creates an audit table for user’s tables.
SET SERVEROUTPUT ON SIZE 500000
SET FEEDBACK OFF
SPOOL build_audit_tables.sql
In the declaration of audit_tables.sql, two cursor definitions are required to obtain table information for any table in the user’s schema that does not have an audit table. The audit table will be created as the table name (or, for tables with long names, the 1st 26 letters) followed by ‘$AUD’.
DECLARE
lv_precision_and_scale VARCHAR2
(20);
--Select tables w/o an audit table
CURSOR cur_tbl2audit IS
SELECT table_name
FROM user_tables
WHERE SUBSTR(table_name,1,26)||'$AUD' NOT IN
(SELECT table_name
FROM user_tables)
AND table_name NOT LIKE '%$AUD'
--Add ineligible tables here:
AND table_name NOT IN ('PLAN_TABLE');
--Select table def of unaudited table.
CURSOR cur_col2audit(p_tbl2audit USER_TABLES.TABLE_NAME%TYPE) IS
SELECT column_name,data_type,data_length,data_precision,data_scale
FROM user_tab_columns
WHERE table_name = p_tbl2audit
--Add ineligible datatypes here :
AND data_type NOT IN ('BLOB', 'CLOB','RAW')
ORDER BY column_id;
Any tables to be ignored can be specified. (In the example, ‘PLAN_TABLE’ is used for generating ORACLE Explain Plans.) When selecting columns with the cur_col2audit cursor, you can exclude any column types you wish from the auditing process. Due to their size, it might be a good idea to exclude columns like BLOB and CLOB if at all possible.
In the body of the program, the above cursors are opened and processed:
BEGIN
--Retrieve table names:
FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP
DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||
SUBSTR(cur_tbl2audit_rec.table_name,1,26)||'$AUD (');
--Retrieve table columns:
FOR cur_col2audit_rec
IN cur_col2audit(cur_tbl2audit_rec.table_name) LOOP
IF cur_col2audit_rec.data_type = 'NUMBER' THEN
--Add precision for NUMBER or provide a default.
IF cur_col2audit_rec.data_precision IS NULL THEN
lv_precision_and_scale := '38,0)';
ELSE
lv_precision_and_scale :=
cur_col2audit_rec.data_precision||','||
cur_col2audit_rec.data_scale||')';
END IF;
--RPAD adds spaces for easier reading.
DBMS_OUTPUT.PUT_LINE
(RPAD(cur_col2audit_rec.column_name,35)||
cur_col2audit_rec.data_type||'('||
lv_precision_and_scale||',');
ELSIF cur_col2audit_rec.data_type IN
('CHAR','VARCHAR','VARCHAR2') THEN
DBMS_OUTPUT.PUT_LINE
(RPAD(cur_col2audit_rec.column_name,35)||
cur_col2audit_rec.data_type||'('||
cur_col2audit_rec.data_length||'),');
ELSE –no length required.
DBMS_OUTPUT.PUT_LINE
(RPAD(cur_col2audit_rec.column_name,35)||
cur_col2audit_rec.data_type||',');
END IF;
END LOOP;
--Add audit fields to table:
DBMS_OUTPUT.PUT_LINE
('aud_action CHAR(3),aud_timestamp DATE,aud_user VARCHAR2(30) )');
DBMS_OUTPUT.PUT_LINE('/');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failure creating audit tables : '||
SUBSTR(SQLERRM,1,200));
RAISE;
END;
/
Next: Passing Table Names >>
More Database Articles
More By Michael Kleane
