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)
