Logon Audit Trigger

This is my version of a logon audit trigger. It picks up every iteration of a logon, even to the point of when a session switches from ACTIVE/INACTIVE mode.

The more conventional way of creating a logon audit trigger would be to simply capture environment variables using the SYS_CONTEXT function, utilising USERENV, environment variables, for example ..

• sys_context('USERENV','CURRENT_USER')
• sys_context('USERENV','CURRENT_USERID')
• sys_context('USERENV','HOST')
• sys_context('USERENV','OS_USER')

Anyhow, I digress ...

Create a table to store the information provided by the trigger:

create table nazim_logon_audit 
(  sid number
 , pid number
 , spid varchar2(12)
 , process varchar2(12)
 , status varchar2(8)
 , username varchar2(30)
 , osuser varchar2(30)
 , program varchar2(48)
 , client_info varchar2(64)
 , module varchar2(48)
 , logon_time varchar2(30)
 , last_call_et varchar2(30)
);

Create the trigger

create or replace trigger nazim_logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into nazim_logon_audit
(select s.sid 
     , p.pid 
     , p.spid
     , s.process
     , s.status
     , s.username
     , s.osuser
     , s.program
     , s.client_info
     , s.module
     , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss') 
  from v$session s
     , v$process p
 where s.paddr = p.addr
   and s.username=user 
   and s.logon_time = sysdate
 );
commit;
END;
/