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.
This would starting creating records for each logon/logoff in AUD$ table. This has been validated to work on 10gR2, 11gR1/11gR2 and 12c.
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;
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;
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
