Oracle – Audit Trail (Logins, Connect Info).

Oracle – Audit Trail (Logins, Connect Info)
May 20, 2019.

**Notes: All scripts have been validated/used with Oracle 11g Ent Edition running on Oracle Linux.

The audit trail lets you see important session information, including who has connected to the database, how long for and where
failed logon attempts have come from. This information is essential for security.

The first step is to turn the audit trail on. This needs to be done in the database’s init.ora file. Add the following parameter to the init.ora file:

audit_trail=DB
or
*.audit_trail=’db_extended’
The extended will give you a more granular audit info even for SYSDBA sessions. The blog below link explains the differences.

https://jhdba.wordpress.com/2014/07/15/the-value-of-audit_traildbextended/

Please note that both types of audit info are going to the DB, not to an external flat file.
The database needs to be shutdown and started up again for the parameter to be read.

Validating the audit option is enabled:
 select value
 from v$parameter
 where name = ‘audit_trail’;

Enable Connection Auditing
SQL>audit connect;

The audit trail should now log every logon and logoff to the database

Querying the Audit Trail
The connection audit trial can be queried via the dba_audit_sessions view.
Some examples.

Count of Connects by User in the Last 7 Days.
SELECT das.username,
        COUNT(*) logonCount
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
  GROUP BY das.username;

Number of Minutes Connected By User in the Last 7 Days.
SELECT das.username,
        ROUND(SUM((NVL(das.logoff_time,SYSDATE)-das.timestamp)*1440)) connectMins
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
  GROUP BY das.username;

Unsuccessful Logon Attempts in the Last 7 Days.
SELECT das.username,
        das.os_username,
        das.terminal,
        TO_CHAR(das.timeStamp,’DD Mon YYYY HH24:MI’) timestamp,
        das.returnCode
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode != 0;

The above is particularly useful in triaging locked service accounts if they are used in scheduled reports. Also useful is identifying errant/unauthorized user accounts.

Connections Made Out Of Working Hours in the Last Week
SELECT das.username,
        TO_CHAR(das.timestamp,’DD Mon YYYY HH24:MI:SS’)   logontime,
        TO_CHAR(das.logoff_time,’DD Mon YYYY HH24:MI:SS’) logofftime
   FROM sys.dba_audit_session das
  WHERE das.timeStamp > SYSDATE-7
    AND das.returnCode = 0
    AND NOT (    TO_NUMBER(TO_CHAR(das.timestamp,’D’)) < 6
             AND TO_NUMBER(TO_CHAR(das.timestamp,’HH24MI’)) BETWEEN 800 AND 1800 )
  ORDER BY das.username;

Prakash.

Leave a Reply

Your email address will not be published. Required fields are marked *