oracle_audit_tracing [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====Overview=====
Auditing in Oracle is controlled by the audit_trail. Audit trail can be:

  * None = Doesn't store Audit data
  * DB = Audit information is stored in the DB (aud$ and dba_audit_trail)
  * OS = Audit Information is stored on the OS (Audit_dump_location)
  * XML = Writes to the operating system audit record file in XML format. Prints all elements of the AuditRecord node
  * *EXTENDED = Addition to (DB and XML) Provides further audit data



=====Auditing=====


=====Tracing=====
Tracing can be enabled for:

  * Current Session
  * Specific Session
  * Upon login
  * Specific Service
  * Specific OS Process

====Current Session====
<sxh bash>
-- Oracle 10g+
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;

-- All versions
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME;      -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
</sxh>

====Specific Session====
<sxh bash>
-- Oracle 10g+
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

-- All versions
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>'');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>'');

-- With DBMS_SUPPORT
-- This.
@$ORACLE_HOME/rdbms/admin/dbmssupp.sql

-- Or this.
@?/rdbms/admin/dbmssupp.sql

SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
</sxh>


====Specific OS Process====
<sxh bash>
-- Oracle 8i+
SQL> ORADEBUG SETMYPID;            -- Debug current session.
SQL> ORADEBUG SETOSPID 1234;       -- Debug session with the specified OS process.
SQL> ORADEBUG SETORAPID 123456;    -- Debug session with the specified Oracle process ID.
</sxh>

====Login====
Tracing can be enabled on LOGON via trigger as follows:

===Pre 12c===
<sxh bash>
CREATE OR REPLACE TRIGGER USER_TRACE_TRG
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'USERNAME'
  THEN
    execute immediate 'alter session set timed_statistics=true';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
</sxh>

===Post 12c===
<sxh bash>
create or replace trigger USERNAME_startsqltracing after logon on USERNAME.schema
begin
    execute immediate 'alter session set timed_statistics=true';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end;
/
</sxh>


====Specific Client====
<sxh bash>
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');
</sxh>

====Specific Object (e.g. Table)====
<sxh bash>
1. enable policy
begin
    dbms_fga.add_policy (
      object_schema        => 'LICA',
      object_name          => 'T_MEZZI',
      policy_name          => 'AUDIT_CH1469152',
       enable               =>  TRUE,
      statement_types      => 'UPDATE,INSERT,DELETE');
end;
/
2. populate table:
 select * FROM dba_fga_audit_trail where policy_name = 'AUDIT_CH1469152';


3. drop policy
exec DBMS_FGA.DROP_POLICY (object_schema => 'LICA',object_name =>  'T_MEZZI',policy_name => 'AUDIT_CH1469152');
</sxh>

You can of course choose to export to CSV using the following syntax on SQL:

<sxh bash>
set embedded on
set pagesize 0
set colsep ';'
set echo off
set feedback off
set linesize 32767
set trimspool on
set headsep off
spool name.csv
</sxh>