Auditing User Login-Logoff activities using System Level Trigger

Oracle special system-level triggers, which can be created on system events that are supported on DATABASE and SCHEMA. These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers. The user logon/logoff triggers will accurately tell you the time of the user logon and logoff.

Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1007895

Here we are trying to design a single table for auditing user logon and logoff activities.

create table myuser_audit
(
sessionid     number,
username     varchar2(30),
osuser     varchar2(30),
process varchar2(24),
machine varchar2(64),
port    number,
terminal     varchar2(16),
module      varchar2(64),
action      varchar2(64),
client_info varchar2(64),
program varchar2(64),
host     varchar2(30),
ip_address     varchar2(64),
logon_date     date,
logoff_date     date
);

Following is the login trigger, which will create a row in myuser_audit table when a user gets login on database, it will record sid, username, host, ip_address and logon_date

create trigger user_logon_audit_trg
after logon on database
begin
insert into myuser_audit
(
sessionid,
username,
host,
osuser,
ip_address,
logon_date
)
values
(
sys_context('userenv','sessionid'),
sys_context('userenv', 'session_user'),
sys_context('userenv', 'host'),
sys_context('userenv', 'os_user'),
sys_context('userenv', 'ip_address'),
sysdate
);
end;
/

After a login trigger, we need a trigger on logoff event, which will simply update the information from v$session to our auditing table by using sessionid when user is getting logged off.

create trigger logoff_audit_trigger
before logoff on database
begin
update
myuser_audit
set
(
action,
process,
machine,
port,
terminal,
module,
client_info,
program,
logoff_date
)
= 
(
select 
action,
process,
machine,
port,
terminal,
module,
client_info,
program,
sysdate    
from 
v$session 
where     
audsid = sys_context('userenv','sessionid')
)
where
sessionid = sys_context('userenv','sessionid')
and logoff_date is null;
end;
/

Now a various kind of reports/informaction about user login and logoff activities can be retrived from myuser_audit table.



Related Post:
ORA-00604: error occurred at recursive SQL level string
http://nimishgarg.blogspot.in/2013/02/ora-00604-error-occurred-at-recursive.html