Friday, February 22, 2013

schema / user Logon logoff track oracle

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
user_v VARCHAR2(20) := USER;
count_v NUMBER(2);
BEGIN
SELECT COUNT(1) INTO count_v FROM stats_user WHERE user_id = user_v;
IF count_v = 1 THEN
UPDATE stats_user SET LOGOFF_TIME = SYSDATE WHERE user_id= user_v;
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
user_v VARCHAR2(20) := USER;
count_v NUMBER(2);
BEGIN
SELECT COUNT(1) INTO count_v FROM stats_user WHERE user_id = user_v;
IF count_v = 1 THEN
UPDATE stats_user SET LOGON_time = SYSDATE WHERE user_id= user_v;
ELSE
INSERT INTO stats_user VALUES(
USER,
SYS_CONTEXT('USERENV','SESSIONID'),
SYS_CONTEXT('USERENV','HOST'),
SYSDATE,
NULL
);
END IF;
END;
/




This also help to track the user i.e. ever logged on in database, and you can delete the extra users in database. 
SQL> select username from all_users where user_id >100 and username !='XS$NULL'
2 minus
3 select user_id from stats_user;

No comments:

Post a Comment

web stats