Do it yourself Transaction Recorder – when standard auditing is not available

In the recently published blog article Database Transaction Recorder – Adding Who to When and What to make Flashback take over from Journalling tables (https://technology.amis.nl/blog/10911/database-transaction-recorder-adding-who-to-when-and-what-to-make-flashback-take-over-from-journalling-tables) I have discussed how the replacement in database oriented applications of journaling tables by flashback data archives can be completed by recording the transaction history – including the client identifier identifiying the real end user behind a transaction – using the standard auditing features of the Oracle Database.

This same article discusses how there may be situations where standard auditing is not an option to implement – for example because of prohibitive DBA regulations or the [perceived] complexity of managing the contents of the sys.aud$ table. In such circumstances, other alternatives can be considered. Note that these are inferior: they are more complex in terms of code, require more effort and introduce more run time overhead. However, they too will do the job and may be more appropriatefor a particular situation.

Fine Grained Auditing

In addition to the standard auditing, the Oracle RDBMS has also supported fine grained auditing since release 9i, configured through the dbms_fga package. Using fine grained auditing, application developers and database administrators can configure audit policies that define the conditions under which an activity should be logged in the fine grained audit trail (table SYS.FGA_LOG$). Alternatively, a custom audit handler can be configured – a custom PL/SQL procedure that is invoked when the audit policy was satisified. This custom code can record whatever information you feel is important – including the triggering SQL statement and any dynamic userenv context setting.

Using fine grained auditing, we could establish the following approach to transaction recording – that is: creating a register that contains a record for every database transaction that logs at least the client identifier at the time of the transaction (because that reveals the real end user of the transaction). In this code we try to achieve journaling for the EMP table in the SCOTT, although nothing is specific about either that schema or that table.

As SYS, execute the following statements:

create or replace
view transaction_history
( transactionid, scn, client_id, db_user, os_user)
as
select transactionid
,      scn
,      fga.client_id
,      fga.db_user
,      fga.os_user
from   dba_fga_audit_trail fga
where  fga.policy_name ='TRANSACTION_AUDIT'

and grant these privileges to user SCOTT:

grant select on transaction_history to scott
grant execute on dbms_fga to scott

Then as user SCOTT establish an audit policy for Table TRANSACTION_RECORDER (and NOT for each table that you want to have journaling for). This table does not yet exist.

BEGIN
  DBMS_FGA.add_policy(
    object_schema    => 'SCOTT',
    object_name      => 'TRANSACTION_RECORDER',
    policy_name      => 'TRANSACTION_AUDIT',
    statement_types  => 'INSERT',
    audit_trail      => DBMS_FGA.DB
  );
END;

Create table transaction recorder – a global temporary table:

CREATE GLOBAL TEMPORARY TABLE transaction_recorder (
  column1  NUMBER
) ON COMMIT DELETE ROWS;

the temporary nature of the table is trick to ensure we create only a single entry in the audit log for each transaction, rather than one for every DML statement in the transaction on the tables we are interested in.

Then create an after statement level trigger on each table that will have journaling:

create trigger emp_transaction_recorder
after
    DELETE OR INSERT OR UPDATE
    ON EMP
    begin
    merge into TRANSACTION_RECORDER tr
    using (select 1 some_number from dual) t
    on (tr.column1 = t.some_number)
    WHEN NOT MATCHED THEN
    INSERT (column1)
    VALUES (t.some_number);
end;

With this in place, we can perform DML operations on EMP and have the transactions logged in the fine grained audit trail. For example:

begin
  dbms_session.set_identifier('John.Doe@somemail.com');
end;

to set the client identifier of the real end user, the update of table EMP

update emp
set sal = sal -9

and the commit of the transaction.

commit

The following query returns the versions for the records in EMP with – thanks to the join with view transaction_history based on the fine grained audit trail – the client identifier at the time of the update:

select ename
,      sal
,      th.client_id
,      th.os_user
,      versions_xid
,      VERSIONS_STARTTIME
,      VERSIONS_STARTSCN
,      VERSIONS_ENDTIME
,      VERSIONS_ENDSCN
from   emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE eh
       join
       sys.transaction_history th
       on ( eh.versions_xid = th.transactionid)

part of the outcome of this query:

ENAME  SAL  CLIENT_ID              OS_USER VERSIONS_XID   VERSIONS_STARTTIME       VERSIONS_STARTSCN
---------- ---------------------- ----------------------------------------------------------------
MILLER 1691 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
FORD   3291 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
JAMES  1341 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
ADAMS  1491 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
TURNER 1791 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
KING  12991 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
SCOTT  3291 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
CLARK  3016 John.Doe@somemail.com  demo  0100130094040000 06-FEB-11 01.12.08.00 PM 2218256
......

Do it yourself transaction recorder

When neither standard auditing can be used nor fine grained auditing, there is always the option of a custom approach.

First, as SYS, create the following view and grant select access to SCOTT

create or replace view transaction_session
as
SELECT t.XID
,      s.sid
FROM   v$transaction t
       join
       v$session s
       on (t.ses_addr = s.saddr)
;

grant select on transaction_session to scott

This view provides an easy way to the trigger that will fire for a transaction to get hold of the current transaction id.

Then, as SCOTT, create the table that will hold the transaction history:

create table transaction_history
( transaction_id    varchar2(100) not null
, client_identifier varchar2(200)
, os_user           varchar2(200)
, scn               number
, transaction_start_timestamp     timestamp default systimestamp
, db_user           varchar2(100)
, application_info  varchar2(2000)
)

Create the package transaction_auditing :

create or replace
package transaction_auditing
is
  procedure record_transaction;
end transaction_auditing;

and the package body

create or replace
package body transaction_auditing
is
g_last_recorded_transaction_id varchar2(100);

procedure record_transaction
is
  l_xid varchar2(100);
  begin
    -- find current transaction XID
    -- SELECT XID AS "txn id" FROM V$TRANSACTION
    select rawtohex(xid)
    into   l_xid
    from   sys.transaction_session
    where  sid = sys_context('USERENV', 'SID')
    ;
	if l_xid != g_last_recorded_transaction_id
	then
	  g_last_recorded_transaction_id := l_xid;
      insert into transaction_history
      ( transaction_id, client_identifier, os_user, db_user)
      values
      ( l_xid, sys_context('USERENV', 'CLIENT_IDENTIFIER'), sys_context('USERENV', 'OS_USER'), user)
      ;
    end if;
end record_transaction;

end transaction_auditing;

Then, for each table that requires journaling, add a trigger (on insert, update an delete – statement) that will invoke the package.

create or replace trigger trnsctn_rcrdr_emp_ins_ar
after
    DELETE OR INSERT OR UPDATE
ON EMP
begin
      transaction_auditing.record_transaction;
end;

After performing multiple DML statements in various transactions against table EMP, the history of EMP can be recalled using the next query:

select ename
,      sal
,      th.client_identifier
,      th.scn
,      th.os_user
,      versions_xid
,      VERSIONS_STARTTIME
,      VERSIONS_ENDTIME
from   emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE eh
       join
       transaction_history th
       on (rawtohex(eh.versions_xid) = th.transaction_id)

Resources

diy_transaction_monitor_for_flashbackquery
Oracle Database 11g Documentation – http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#CEGBAFIA (Security Guide, fine grained auditing), http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_fga.htm#ARPLS015 (DBMS_FGA package)