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.
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.