Session Auditing in Oracle Database - 10g/11g/12c

Oracle session auditing has its importance derived from requirements of keeping track of users logging on to the database and logging off.

At times it becomes important to know which users were available on the system as a part of auditing to determine:

1. Accountability of users
2. Intruders Detection
3. Problem Detection
4. Visibility on system

Since auditing is a vast topic and its study and implementation varies according to requirements, I am only taking up session auditing in this blog as it is most basic and crucial to a database environment.

Steps to Enable Session Auditing

1. Set auditing parameters to enable audit feature of Oracle RDBMS

Ø  audit_trail

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

·         none or false - Auditing is disabled.

·         db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).

·         db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.

·         xml- Auditing is enabled, with all audit records stored as XML format OS files.

·         xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

·         os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

Syntax:
ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

 

Ø  audit_file_dest

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

Since I am using AUDIT_TRAIL=DB, I am not concerned about this parameter.

Ø  audit_sys_operations

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

Its your choice to log sys operations in audit log or not.

2.  Enable Auditing of Sessions

Run below command to enable Oracle instance to log each session logon and logoff in audit tables

AUDIT SESSION;

This would starting creating records for each logon/logoff in AUD$ table. This has been validated to work on 10gR2, 11gR1/11gR2 and 12c.

3. View session audit details

To view details Oracle provides below views that gives most appropriate details: DBA_AUDIT_TRAIL and DBA_AUDIT_SESSION

A query to use:

COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
     username "DBUSER",
     terminal,
     returncode,
     TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
     TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session;

4. Maintenance

It is important to keep check on usage of AUD$ table, it starts flooding with records and in no time can grow into gigabytes. It is strongly advised to decide a retention period of auditing records and purge what is not needed.

Refer to my other post about AUD$ maintenance for detailed steps.

Cheers!

Anurag