Logon Audit Trigger
Jump to navigation
Jump to search
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; /