Tip
|
Auditing LOGOFF and LOGON Through DATABASE
TRIGGER in Oracle 8i |
|
|
Oracle
Techniques
By Sameer Wadhwa
|
Here is a
way to create a logon trigger for audition logoff /logon of database users.
connect with sys or system and proceed with the following steps
Step 1:-
Create a table log_info as
create table
SYSTEM.log_info( logindatetime date,LogoffDateTime Date,LoggedUser
Varchar2(30),SESSIONID
Number );
Step 2:-
CREATE OR REPLACE
TRIGGER logon_audit AFTER LOGON ON DATABASE
BEGIN
insert into system.log_info values(sysdate,null,user,sys_context('USERENV','SESSIONID')
);
END;
/
CREATE OR REPLACE TRIGGER logoff_audit Before LOGOFF ON DATABASE
BEGIN
insert into system.log_info
values(Null,sysdate,user,sys_context('USERENV','SESSIONID') );
END;
/
This will create trigger name logon_audit,logoff_audit in the database
Step 3:
Shutdown normal and startup the database.
All set now , whenever any user logged in to the database , the login time and
logoff time is recorded
in the system.log_info table.
Now you can write down a simple sql , to know how many time particular user
logged , at what time he/she
logged, How long user was connected with the database etc.
I made a t1.sql for you .
t1.sql
select
to_char(logindatetime,'DD-MON hh:mi') logon,
to_char(logoffdatetime,'DD-MON hh:mi') logoff,loggeduser,sessionid from
system.log_info
where sessionid != 0
/
SVRMGR> @t1
LOGON LOGOFF
LOGGEDUSER
SESSIONID
------------ ------------ ------------------------------ ----------
30-AUG 03:49
SCOTT
16209
30-AUG 03:49
SCOTT
16210
30-AUG 03:51
SCOTT
16211
30-AUG
03:51 SCOTT
16210
30-AUG
03:51 SCOTT
16209
The sessionid will tell you when a particular user has logged in and logged
out.
End
of Tip
|
Welcome to send comments or
feedback at
Wadhwa_s@Hotmail.com |
|
Sameer Wadhwa |
Copyright © Oracle Techniques All Rights Reserved |