Friday, February 22, 2013

Audit Users Logon & Logoff

We design a trigger that can be used for single table to record both logon and logoff events in ORACLE. This table will contain distinct username , session ID , Host name , including Log ON/OFF time. Trigger will check the existence for user, if user already exists in table then it will update only its Log ON/OFF time depending upon the happening.


CREATE TABLE STATS_USER
(
USER_ID VARCHAR2(30),
SESSION_ID NUMBER(8),
HOST VARCHAR2(30),
LOGON_TIME TIMESTAMP,
LOGOFF_TIME TIMESTAMP
);






CREATE OR REPLACE TRIGGER
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
puser VARCHAR2(20) := USER;
pcount NUMBER(2);
BEGIN
SELECT COUNT(1) INTO pcount FROM stats_user WHERE user_id = puser;
IF pcount>=1 THEN
UPDATE stats_user SET LOGOFF_TIME = SYSDATE WHERE user_id= puser;
ELSE
INSERT INTO stats_user VALUES(
user,
sys_context('USERENV','

SESSIONID'),
sys_context('USERENV','HOST'),
null,
sysdate
);
end if;
end;
/



create or replace trigger
logon_audit_trigger
BEFORE LOGOFF ON DATABASE
declare
puser varchar2(20) := user;
pcount number(2);
Begin
select count(1) into pcount from stats_user where user_id = puser;
if pcount>=1 then
update stats_user set LOGON_time = SYSDATE WHERE user_id= puser;
ELSE
INSERT INTO stats_user values(
USER,
SYS_CONTEXT('USERENV','
SESSIONID'),
SYS_CONTEXT('USERENV','HOST'),
SYSDATE,
null
);
end if;
end;
Made by
www.j4info.blogspot.in is
gurjeetkamboj@gmail.com

No comments:

Post a Comment