Introduction to Oracle Database Auditing

Introduction to Oracle Database Auditing

  • Presented by: Dr. S. Gokhan Ozden at Penn State Abington

Lesson Objectives

  • Understand common auditing techniques

  • Learn to:

    • Audit SYSDBA connections

    • Use the AUDIT SQL statement to capture user activity

    • Code simple triggers to capture user activity

Auditing Technique Overview

  • SYSDBA connections and activity can be audited.

  • Use of AUDIT statement for general auditing.

  • Identify:

    • Who performed actions and when

    • Objects targeted by DML

    • Add triggers or use LogMiner utility

    • See values changed (before and after)

    • Fine Grained Auditing for conditional auditing of SELECT statements:

      • Captures user, SCN, statement, bind variables.

Enabling the Audit Trail

  • Enabled with initialization parameters:

    • Connect to SQLPlus as SYSDBA:

      SQL> alter system set audit trail=db scope=spfile;
SQL> alter system set audit_sys_operations=true scope=spfile;

Auditing SYSDBA

  • SYSDBA connections are automatically audited.

  • Use AUDIT_SYS_OPERATIONS to audit actions:

    • Logs to Unix/Linux OS file

    • Files located at: $ORACLE_HOME/rdbms/audit.

Auditing SYSDBA Tips

  • Clean OS directory periodically:

    • Directory $ORACLE_HOME/RDBMS/AUDIT automatically captures all SYSDBA connections.

    • Protect the directory:

      • Allow Oracle OS user to write

      • Prohibit others from accessing

  • Example path for audit logs: /u01/app/oracle/admin/orcl12c/adump.

AUDIT Statement

  • Use to audit SQL statement usage:

    • By “statement option”

    • By system privilege or role

    • Audit operations on specific objects.

Auditing Logons

  • Monitor logon attempts using:

    SQL> AUDIT SESSION;

  • Requires AUDIT SYSTEM privilege.

  • Query to monitor logon attempts:

    SQL> select user_name, audit_option, success, failure from dba_stmt_audit_opts;

    • Results indicate success or failure in session creation.

Monitoring Logon Attempts

  • Retrieve logon session details:

    SQL> select os_username, username, terminal, timestamp, logoff_time from dba_audit_session;

  • Example output:

    • USER: SUPERMIKE1, TERMINAL: pts/0, TIMESTAMP: 19-MAR-18.

Audit System Grants

  • Use to audit grants and revokes:

    • SYSTEM GRANT option audits subsequent grants

    • Each grant captured in the audit trail.

Monitor System Grants

  • Query to monitor grants:

    SELECT username, action_name, sys_privilege, grantee, DECODE(returncode, '0', 'Granted') code, 
       TO_CHAR(timestamp, 'mm/dd/yy/hh24:mi') time 
FROM dba_audit_statement;

  • Example output:

    • USER: SYSTEM, ACTION: GRANT, TIME: 03/20/18 12:25.

Object-Level Auditing

  • Audit DML actions against specific objects.

    • Capable of auditing SELECT actions.

    • Cannot see values changed; requires object ownership or AUDIT ANY privilege.

Object-Level Auditing Query

  • To show object audits in effect:

    SELECT * FROM dba_obj_audit_opts WHERE owner='HR' AND object_name='EMPLOYEES';

  • Example output:

    • Shows Audit options (ALTER, SELECT, DELETE, etc.) for HR.Employee.

Monitoring Object Activity

  • Retrieve actions on specific objects:

    SELECT username, obj_name, action_name, ses_actions, to_char(timestamp, 'mm/dd/yy hh24:mi:ss') time 
FROM dba_audit_object WHERE owner='HR' ORDER BY time;

  • Example output:

    • USER: HR, ACTION: SELECT, TIME: 03/20/18 12:16:58.

The SYS.AUD$ Table

  • Important for monitoring:

    • Sufficient free space is crucial for user connection and operations.

    • Only SYSDBA can log in if space limitations exist.

  • Periodic maintenance is needed:

    • Export data and mass DELETE to maintain space.

Auditing with Triggers on PDB Level

  • Creating audit history table:

    CREATE TABLE audit_history_emp (employee_id number, name varchar2(30), old_salary number, new_salary number, change_date DATE, changer varchar2 (30));

  • Updating table and capturing changes with triggers.

    • Example trigger code for auditing updates or inserts.

Auditing Summary

  • Essentials of auditing include:

    • Audit SYSDBA connections (optional)

    • Capture SQL statements by type or privilege

    • Perform object-level auditing for DML actions

      • By session or access approach

      • Track successes or failures

    • Audit triggers for value changes.

Fine Grained Auditing

  • Release 1 introduced conditional auditing of SELECT statements.

  • 8i and 9i enhancements reveal user actions, statements, bind values, and terminal.

    • Also allows seeing what the user viewed with flashback query.