Oracle - Audit Trail Generator?

Question

I am looking for a generic procedure that will generate audit trails for Oracle databases. We are currently using a similar procedure on SQL Server and wondering if an Oracle equivalent exists. We are hoping the audit table will be a separate table than the original table and include user/date time info.

Here is the SQL Server equivalent we are using: https://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL

Any advice is greatly appreciated.

Answer 1

If you don't want to use Oracle native mechanism, you could have your very own framework that generates and reads your own auditing table (I know you can, we had similar thing where I once worked).

Here are the main components:

• a_sqnc is the sequence you will use in TrackTable to keep track of the order of actions in column NO_ORD (even though there is also a D_UPD column with the modification time).

---

create sequence a_sqnc
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache;

• TrackTable will have a TABLE_NAME column in order to track changes from different tables. It also have a PK_VALUE and ROW_VALUE where we store the data that changed. Here is the table creation with useful indexes:

---

create table TrackTable (
  table_name VARCHAR2(50) not null,
  action     VARCHAR2(240) not null,
  no_ord     NUMBER(12) not null,
  nature     VARCHAR2(3) not null,
  pk_value   VARCHAR2(4000),
  row_value  VARCHAR2(4000),
  ori        VARCHAR2(250),
  c_user     VARCHAR2(20),
  d_upd      DATE
);

create index AP_D_UPD on TrackTable (D_UPD);
create index AP_NO_ORD on TrackTable (NO_ORD);
create index AP_TABLE_NAME on TrackTable (TABLE_NAME);

• Say you have a simple table BANK with two columns PK_val (the primary key) and val:

---

create table BANK (
  pk_val VARCHAR2(50) not null,
  val    VARCHAR2(240) not null
);

alter table BANK
  add constraint BK_PK primary key (pk_val)
  using index ;

• Use DBMS_APPLICATION_INFO.READ_MODULE(w_sess_mod, w_sess_act) to know what module and what action operates: I concatenate both in column ORI in TrackTable;

• user Oracle session variable will allow you tracking who did the change in column c_user;

• Here is how to create trigger TRCK_BNK to track changes in table BANK; it will categorize in 3 actions: DELETE, UPDATE, INSERT (you can remove the INSERT case if needed).

---

CREATE OR REPLACE TRIGGER "TRCK_BNK" 
AFTER DELETE OR INSERT OR UPDATE 
   ON BANK
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE
    w_a        VARCHAR2(10);
    W_ERRM     VARCHAR2(1000);
    W_CODE     VARCHAR2(1000);
    w_n        VARCHAR2(200) := 'BANK';
    w_id       NUMBER :=  a_sqnc.nextval;
    w_act      v$session.action%type;
    w_mod      v$session.module%type;
    w_ori      TrackTable.ORI%TYPE;    
BEGIN
  DBMS_APPLICATION_INFO.READ_MODULE(w_mod, w_act);
  w_ori := 'Module : '||w_mod ||' ; Action : '||w_act;
  ----------------------------------
  -- test which action is for change
  ----------------------------------
  IF UPDATING
  THEN
    w_a := 'UPDATE';
  ELSIF DELETING
  THEN
    w_a := 'DELETE';
  ELSIF INSERTING
  THEN
    w_a := 'INSERT';
  END IF;
  ----------------------------------
  -- Insert into TrackTable 
  ----------------------------------
If w_a in ('UPDATE', 'DELETE') then
  Insert into TrackTable 
       Select w_n, w_a, w_id, 'OLD', :OLD.pk_val, :OLD.val
            , w_ori, user, sysdate
         From Dual;
End if;

-- if you update, there is a new value and an old value
If w_a in ('UPDATE', 'INSERT') then
  Insert into TrackTable 
       Select w_n, w_a, w_id, 'NEW', :NEW.pk_val, :NEW.val
            , w_ori, user, sysdate
         From Dual;
End if;

Exception
When others then
  Begin
    W_ERRM := SQLERRM;
    W_CODE := SQLCODE;
    -- try inserting in case of error anyway
    Insert into TrackTable 
         Select w_n, w_a, -1, 'ERR', 'Grrr: '||W_CODE, W_ERRM
              , w_ori, user, sysdate
     From Dual;
  End;
End;
/

Then add functions to your framework that generates the triggers given a table, retrieves changes, reverts table to a given date...

NB: This way of tracking every change on the table impairs performances if table changes a lot. But it is great for parameter tables that scarcely change.

Answer 2

Have a look at Oracles Flashback Data Archive which bases upon the UNDO Data. It can be configured to track any change to your data. It is available in any edition of oracle since 11g2 (11.2.0.4). In the Oracle documentation it says that optimazation is limited but basic functionality is available in any edition.