The various security mandates these days required a lot of auditing on the database. Though there are a host of various third party tools and Oracle tools (Audit Vault) available for this. In this exercise we will design a simple login and logout capture mechanism for the database.
1. Create Auditing Table
create table SYS.LOGIN_AUDIT_INFO_ALL ( user_id varchar2(30), session_id number(8), host varchar2(30), last_program varchar2(48), last_action varchar2(32), last_module varchar2(32), logon_day date, logon_time varchar2(10), logoff_day date, logoff_time varchar2(10), elapsed_minutes number(8) ) ;
2. Create Logon Trigger
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into LOGIN_AUDIT_INFO_ALL values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/
3. Create Logoff Trigger
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/
4. Check the audit table after opening another session
SELECT * FROM SYS.LOGIN_AUDIT_INFO_ALL
