Differences

This shows you the differences between two versions of the page.

--- oracle_audit_tracing [2019/08/09 13:31] – created andonovj
+++ oracle_audit_tracing [2020/03/05 15:26] (current) – andonovj
@@ Line 6: / Line 6: @@
   * 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
@@ Line 13: / Line 14: @@
 =====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:
@@ Line 42: / Line 109: @@
 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>
+. 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;
+/
+. populate table:
+ select * FROM dba_fga_audit_trail where policy_name = 'AUDIT_CH1469152';
+. 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>