Oracle Database Logon Trigger

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s