OraFAQ Forum: Security » Oracle Audit Design

Home » RDBMS Server » Security » Oracle Audit Design (Oracle 9.2, R2, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Audit Design [message #509501]

Sat, 28 May 2011 16:38

sam10
Messages: 13
Registered: May 2011
Location: Los Angeles

Junior Member

Let us say I want to audit data updates, deletes on existing table EMP_TAB that
has a few hundred thousands of records.

I created a shadow table Emp_tab_audit and added few audit columns

Emp_tab (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);

CREATE TABLE Emp_tab_audit (
seq number
operation varchar2(3),
user varchar2(20),
Timestamp date,
ip_address varchar2(25),
Terminal varchar2(10,
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);

I am mostly interested in UPDATES and DELETES but I decided to add INSERTS to have full history for
each eomplyee in one table (audit schema) instead of querying two tables all the time (production
table and audit table) to see the changes.

I created this AFTER INSERT, UPDATE, DELETE trigger.

I decided to copy the :NEW values for INSERT and UPDATE and :OLD values for DELETE.
see attached.

so when insert happens, the first audit row is created in EMP_TAB_AUDIT.
when update happens, the 2nd new row is created in EMP_TAB_AUDIT.

The problem I am facing is the old records that curently exist. If someone updates an old row I am
copying the :NEW values so I won't have a copy of the :OLD values unless I create 2 ROWS (one for
the old and one for the new).

Do you think I should copy all the hundreds of thousands of records to the AUDIT tables for this to
work. I am hesitant to do that.

ANy better ideas. I am applying this solution to several tables (not just one).
This is also in 9i and i dont flexibility other than using a trigger to track data changes.

*******************************************************************
CREATE OR REPLACE TRIGGER TRG_EMP_AUDIT
 AFTER INSERT OR DELETE OR UPDATE ON EMP_TAB
 FOR EACH ROW DECLARE
 
 v_operation   VARCHAR2(10) := NULL;
 v_user        VARCHAR2(20);
 v_timestamp   Date;
 v_ip_address  VARCHAR2(25),
 v_terminal    VARCHAR2(10);

BEGIN
 
 v_user := USERENV(user);
 v_timestamp := SYSDATE;
 v_ip_address := USERENV(ip_address);
 v_terminal := USERENV(terminal);
 
 IF INSERTING THEN
   v_operation := 'INS';
  ELSIF UPDATING THEN
   v_operation := 'UPD';
  ELSE
   v_operation := 'DEL';
  END IF; 
  
    
  
  IF INSERTING OR UPDATING THEN
   INSERT INTO EMP_TAB_AUDIT (
    seq,
    operation,
    user
    timestamp,
    ip_address,
    terminal,
    empno,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno )
   VALUES (
     audit_seq.nextval,
     v_operation,
     v_user,
     v_timestamp,
     v_ip_address,
     v_terminal,
    :new.empno,
    :new.job,
    :new.mgr,
    :new.hiredate,
    :new.sal,
    :new.comm,
    :new.deptno);

ELSIF DELETING THEN
   INSERT INTO EMP_TAB_AUDIT (
      seq,
      aud_action,
      user
      timestamp,
      ip_address,
      terminal,
      empno,
      job,
      mgr,
      hiredate,
      sal,
      comm,
      deptno )
     VALUES (
       audit_seq.nextval,
       v_operation,
       v_user,
       v_timestamp,
       v_ip_address,
       v_terminal,
      :old.empno,
      :old.job,
      :old.mgr,
      :old.hiredate,
      :old.sal,
      :old.comm,
      :old.deptno);
END IF; 

END;
/
*******************************************************************************