default auditing


AUDIT INSERT, UPDATE, DELETE ON SCOTT.EMP

begin
  dbms_session.set_identifier('TheRealUser');
end;
	
	
	
SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name,
	   client_id
FROM   dba_audit_trail
WHERE  owner = 'SCOTT'
ORDER BY timestamp;


update emp set sal = sal+5

commit

select ename
, sal
, uat.client_id
, uat.os_username
, uat.terminal
, uat.userhost
, uat.username
, uat.scn
, versions_xid
,VERSIONS_STARTTIME 
,VERSIONS_STARTSCN 
,VERSIONS_ENDTIME 
,VERSIONS_ENDSCN 
from   emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE eh
       join
       user_audit_trail uat
       on ( eh.versions_xid = uat.transactionid)

	   
	   
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)
)

	   
insert into transaction_history
( transaction_id    
, client_identifier 
, os_user           
, scn               
, transaction_start_timestamp     
, db_user           
)
select uat.transactionid
,      uat.client_id
,      uat.os_username
,      uat.scn
,      uat.extended_timestamp
,      uat.username
from   ( select uat.*
         ,      row_number() over (partition by transactionid order by extended_timestamp) rn
         from   user_audit_trail uat
        ) uat
where  rn = 1
and    transactionid is not null


select ename
,      sal
,      versions_xid
,      nvl(th.client_identifier, uat.client_id) client_id
,      nvl(th.os_user, uat.os_username) os_user
,      uat.userhost userhost
,      nvl(th.db_user, uat.username) db_user
,      nvl(th.scn,uat.scn) scn
,      VERSIONS_STARTTIME 
,      VERSIONS_STARTSCN 
,      VERSIONS_ENDTIME 
,      VERSIONS_ENDSCN 
from   emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE eh
       left outer join
       ( select uat.*
         ,      row_number() over (partition by transactionid order by extended_timestamp) rn
         from   user_audit_trail uat
        ) uat
       on ( eh.versions_xid = uat.transactionid)
       left outer join
       transaction_history th
       on ( eh.versions_xid = th.transaction_id)
where  job = 'CLERK'
and    uat.rn = 1
order
by     ename, versions_starttime 
	   

all flashbackable tables should have statement level trigger
to merge row into temporary table
fine grained auditing on INSERT into a single tempory table (delete on commit)
=> writes transactionid (like XID in Flasback Versions), client_identifier, SCN? (the correct one?), OS process,  



rem dbms_fga requires enterprise edition

as SYS

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'

grant select on transaction_history to scott


as SCOTT

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

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 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;

update emp
set sal = sal -3


select * from transaction_recorder

select ename
, sal
, versions_xid
,VERSIONS_STARTTIME 
,VERSIONS_STARTSCN 
,VERSIONS_ENDTIME 
,VERSIONS_ENDSCN 
from   emp VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE

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)
       
begin
  dbms_session.set_identifier('John.Doe@somemail.com');
end;

update emp
set sal = sal -9

commit

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)

       
rem non EE approach

rem instead of dbms_fga policy on EMP
rem create an insert trigger on transaction_recorder
rem that create a record in a real (non temporary) table 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 or replace 
package transaction_auditing
is
procedure record_transaction;
end transaction_auditing;

/

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;


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






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)


as sys:


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


select *
from   FLASHBACK_TRANSACTION_QUERY ftq
       join
       transaction_history th
       on (rawtohex(ftq.xid) = th.transaction_id)