Oracle Tips by Burleson

Chapter 9 Oracle System Event Trigger Auditing

• User -- this is the Oracle user ID of the person establishing the Oracle session
   

• Session ID -- this uses Oracle's SYS context function in order to capture the Oracle session ID directly from the v$session table
   

• Host -- this uses Oracle’s SYS context function to capture the name of the host from which the Oracle session originated.  Please note that capturing the host name is vital for systems using Oracle parallel server or real application clusters, because we can have many sessions connecting from many different instance hosts.
   

• Log on date -- this captures the date of the actual log on, accurate to 1/1000 of a second. Notice how we partitioned log on date into two separate fields. Having a separate field for log on day and log on time produces a reader friendly report. 

logon_trig.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values('
   user,
   sys_context('USERENV','SESSIONID'),