Auditing
How you set up auditing depends on the level you wish to audit.
1. Oracle auditing can audit a statement, a privilege or an object for a user (failing actions can be shown), but it does not include the exact sql statement or the old and new values.
2. You can use triggers to monitor old and new values for insert, update and delete. With autonomous transactions you can also monitor failed actions.
3. Using fine grained auditing (9i) you can monitor select statements. It is possible to monitor for a select of a specific column, obtaining the exact sql statement, the user, the scn change number and any bind variables used.
Using triggers for auditing
Triggers can be used to create an audit trail of inserts, updates and deletes.
1. Create a table to hold the audit log.
The following table concatenates all the tables column into one large one, so that all transactions can be audited into one large table.
CREATE TABLE audit_log
(
audit_log_id NUMBER(10, 0),
audit_date DATE,
audit_table VARCHAR2(30),
audit_action VARCHAR2(10),
audit_desc VARCHAR2(2000)
);
2. Create a package to update the audit_log (this one only does insert):
CREATE OR REPLACE PACKAGE auditpackage AS
PROCEDURE auditloginsert (auditdesc CHAR);
END auditpackage;
/
CREATE OR REPLACE PACKAGE BODY auditpackage AS
PROCEDURE changeloginsert (auditdesc CHAR) IS
time_now DATE;
BEGIN
time_now := SYSDATE;
INSERT INTO audit_log
(audit_log_id, audit_date, audit_action, audit_detail)
VALUES (
1,
time_now,
'INSERT',
auditdesc
);
END auditloginsert;
END auditpackage;
/
3. Create a trigger to call the above package for each table to be audited.
This can pass all the columns (new or old) concatenated to the package.
CREATE OR REPLACE TRIGGER notice_item_tr
AFTER INSERT OR UPDATE ON notice_item
FOR EACH ROW
CALL changepackage.changeloginsert(
:new.notice_item_id||
:new.header)