How to Audit User Connection, Disconnection Date and Time
1. Set the initialization parameter "audit_trail" in the "initSID.ora" file.audit_trail=true(db)
2. Shutdown and restart the database in order to apply the changes.
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup
3. Connect as SYSTEM and set the audit option to record user connection and disconnection.
SQL> connect system/password
SQL> audit connect
Check audit connect is succesfully:
SQL> connect system/xxxxx
Check the audit is set or not
SQL> select user_name, audit_option, success, failure
from sys.dba_stmt_audit_opts;
Outpt:
USER_NAME AUDIT_OPTION SUCCESS FAILURE
----------- -------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
4. Query table AUD$, for example to know connection and disconnection information about <username> user:
SQL> select sessionid, to_char(timestamp#,'DD-MON-YY:HH24:MI:SS') login,userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff from sys.aud$ where userid='<username>';
Output:
SESSIONID LOGIN USERID LOGOFF
---------- ------------------ -------- ------------------
132 09-SEP-22:06:52:05 <username> 09-SEP-00:10:03:06
If on 10g or 11g the query should be:
select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff from sys.aud$ where userid='<username>';