How to audit user logins?
 


I try your query, but obtain "no rows" On my init<SID>.ora, I've audit_trail=true What's the problem ?

I'm running Oracle 7.3.3. Is there any way to find out the last time a user successfully logged in? I want to monitor old accounts and eliminate anything  that is no longer used.



Ans1:

You issued an AUDIT SESSION as DBA?

Ans2:
Sybrand outlined how you start AUDTING. Remember that auditing records are all sved in sys.aud$ and kepping all reords since 1993 may lead to some  kind of space problem.
Here's a stat to query logins:
 
SELECT username, userhost, terminal,
to_char(Timestamp, 'DD-MM-YYYY HH24:MI') login_time,
to_char(logoff_time, 'DD-MM-YYYY HH24:MI') logoff_time,
logoff_lread, logoff_pread, logoff_lwrite, logoff_dlock
FROM sys.dba_audit_session;

Maybe you could take this as a starting point to setup a new table that
reords always the most recent login/logoff.
Remember to truncate sys.aud$ from time to time.
 
Ans3:
 
Other people have described auditing, but here's a cheat...  it's not as good as real auditing but it may be sufficient for your needs...

If all your users login via SQL*Net using the TNS Listener, then check the listener log file  ("lsnrctl status" will tell you what it is - typically it is $ORACLE_HOME/network/log/listner.log).  In this SQL*Net connections
are logged.  This file logs the client IP address, the host type and (depending on the client) the username of the person on the remote machine. 16bit clients tend to say "USER=OraUser" but 32bit clients will give the Windows login name.
 

 
 
 
 
 
 
 
 

Hosted by www.Geocities.ws

1