How to Audit User Connection, Disconnection Date and Time

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>';

Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post