Auditing Login/Logout Session information

Description:

Below method can be used to store Session Login and Logout Information. It stores in to a table.

Step 1: Create UserLogAudit Table using the below script

CREATE TABLE UserLogAudit
(
UserId VARCHAR2(30)
, SessionId NUMBER(8)
, HostName VARCHAR2(30)
, LogonDay DATE
, LogonTime VARCHAR2(10)
, LogoffDay DATE
, LogoffTime VARCHAR2(10)
, ElapsedMinutes Number(8)
, Updated DATE
, UpdatedBy VARCHAR2(50)
);

Step 2: Create Logon Trigger to get Logon Information

CREATE OR REPLACE TRIGGER LogonAuditTrigger AFTER LOGON ON DATABASE
BEGIN

INSERT INTO USERLOGAUDIT VALUES (User
, Sys_Context('USERENV','SESSIONID')
, Sys_Context('USERENV','HOST')
, Sysdate
, to_char(Sysdate, 'hh24:mi:ss')
, null
, null
, null
);

END;

Step 3: Create Logoff Trigger to get Logoff Information

CREATE OR REPLACE TRIGGER LogoffAuditTrigger BEFORE LOGOFF ON DATABASE
BEGIN
-- UPDATE the logoff day
UPDATE USERLOGAUDIT
SET LogoffDay = sysdate
WHERE Sys_Context('USERENV','SESSIONID') = SessionId;

-- UPDATE the logoff time
UPDATE USERLOGAUDIT
SET LogoffTime = to_char(sysdate, 'hh24:mi:ss')
WHERE Sys_Context('USERENV','SESSIONID') = SessionId;

-- Compute the elapsed minutes
UPDATE USERLOGAUDIT
SET ElapsedMinutes = ROUND((LogoffDay - LogonDay)*1440)
WHERE Sys_Context('USERENV','SESSIONID') = SessionId;

END;

Oracle Magazine May/June 2010

Oracle Magazine for May/June 2010 available at the below link

http://www.oraclemagazine-digital.com/oraclemagazine/20100506?sub_id=oW7rAkMPiAA4#pg2