Understanding Oracle Auditing

Oracle auditing monitors user database operations, storing records either in data dictionary tables (specifically SYS.AUD$ within the SYSTEM tablespace, viewable via DBA_AUDIT_TRAIL) or in operating system audit files (typically located at $ORACLE_BASE/admin/$ORACLE_SID/adump/). Standard audit trails reside in the AUD$ table, while Fine-Grained Auditing (FGA) trails use the FGA_LOG$ table. Both tables default to the SYSTEM tablespace but can be relocated using Data Pump utilities.

Certain activities are mandatorily audited regardless of configuration: administrative connections, database startup, and shutdown operations.

Audit Categories

• Mandatory Auditing: Always active, recording critical activities like privileged connections.
• Standard Database Auditing: Enabled via the AUDIT_TRAIL parameter, auditing selected objects and privileges.
• Value-Based Auditing: Extends standard auditing to capture actual data values through database triggers.
• Fine-Grained Auditing (FGA): Captures exact SQL statements with conditional filtering capabilities.
• SYSDBA/SYSOPER Auditing: Separates DBA monitoring duties, storing trails in OS files.

Implementing Audit Infrastructure

Verify audit table installation by querying SYS.AUD$ or DBA_AUDIT_TRAIL. If these objects are missing, execute the installation script:

CONNECT / AS SYSDBA
@?/rdbms/admin/cataudit.sql

Ensure adequate space in the SYSTEM tablespace. A database restart is required post-installation.

Configuring Audit Parameters

audit_sys_operations

Defaulting to FALSE, setting this to TRUE captures all SYS user activities (including SYSDBA/SYSOPER connections). Since AUD$ may be unavailable during certain operations, these records go to OS audit files or Windows Event Log.

audit_trail

Configuration options include:

• NONE: Disables auditing (default for manually created databases).
• DB: Stores trails in database tables, recording connection information.
• DB,EXTENDED: Includes SQL text and bind variables in SYS.AUD$.
• OS: Writes to OS files at AUDIT_FILE_DEST.
• XML: Generates XML files viewable through V$XML_AUDIT_TRAIL.

These static parameters require database restarts to take effect:

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET audit_trail=DB,EXTENDED SCOPE=SPFILE;
STARTUP FORCE;

Standard Auditing Implementation

Statement-Level Auditing

Monitors specific SQL statement types across users:

-- Audit table DDL operations for specific user per execution
AUDIT TABLE BY finance_user BY ACCESS;

-- Audit all session connections, failed only
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

-- Remove auditing
NOAUDIT TABLE BY finance_user;

Privilege-Level Auditing

Tracks system privilege usage:

-- Monitor failed attempts to drop any table
AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL;

-- Track privilege usage by specific user
AUDIT CREATE USER BY hr_admin WHENEVER NOT SUCCESSFUL;

-- Remove privilege auditing
NOAUDIT ALTER ANY TABLE BY scott BY ACCESS;

Object-Level Auditing

Monitors operations on specific schema objects:

-- Audit successful deletes on specific table
AUDIT DELETE ON sales.orders BY ACCESS WHENEVER SUCCESSFUL;

-- Monitor all select operations
AUDIT SELECT ON inventory.products;

-- Remove object auditing
NOAUDIT DELETE ON sales.orders BY ACCESS WHENEVER SUCCESSFUL;

Reviewing Audit Configuration

-- Object audit settings
SELECT object_name, object_type, sel, ins, upd, del 
FROM dba_obj_audit_opts WHERE owner='SALES';

-- Privilege audit settings
SELECT user_name, privilege, success, failure 
FROM dba_priv_audit_opts;

-- Statement audit settings
SELECT user_name, audit_option, success, failure 
FROM dba_stmt_audit_opts;

Value-Based Auditing with Triggers

When standard auditing lacks detail about actual data changes, triggers capture modification values. Note the performance impact from trigger execution on each DML operation:

CREATE OR REPLACE TRIGGER sys.salary_change_logger
AFTER UPDATE OF compensation 
ON hr.employees
FOR EACH ROW
BEGIN
    IF :OLD.compensation != :NEW.compensation THEN
        INSERT INTO sys.audit_salary_history 
        VALUES(
            SYS_CONTEXT('USERENV','OS_USER'),
            SYSDATE,
            SYS_CONTEXT('USERENV','IP_ADDRESS'),
            :NEW.emp_id,
            :OLD.compensation,
            :NEW.compensation
        );
    END IF;
END;
/

Fine-Grained Auditing (FGA)

FGA captures exact SQL statements with conditional policies. Records store in FGA_LOG$, viewable through DBA_FGA_AUDIT_TRAIL.

Creating FGA Policies

-- Sample table
CREATE TABLE finance.transactions (
    txn_id NUMBER PRIMARY KEY,
    client_id NUMBER NOT NULL,
    amount NUMBER(15,2)
);

-- Define policy for sensitive column access
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema   => 'FINANCE',
        object_name     => 'TRANSACTIONS',
        policy_name     => 'HIGH_VALUE_MONITOR',
        audit_column    => 'AMOUNT',
        audit_condition => 'AMOUNT >= 50000'
    );
END;
/

Managing FGA Policies

-- Enable policy
BEGIN
    DBMS_FGA.ENABLE_POLICY(
        object_schema => 'FINANCE',
        object_name   => 'TRANSACTIONS',
        policy_name   => 'HIGH_VALUE_MONITOR'
    );
END;
/

-- Disable policy
BEGIN
    DBMS_FGA.DISABLE_POLICY(
        object_schema => 'FINANCE',
        object_name   => 'TRANSACTIONS',
        policy_name   => 'HIGH_VALUE_MONITOR'
    );
END;
/

-- Remove policy
BEGIN
    DBMS_FGA.DROP_POLICY(
        object_schema => 'FINANCE',
        object_name   => 'TRANSACTIONS',
        policy_name   => 'HIGH_VALUE_MONITOR'
    );
END;
/

Querying FGA Results

COLUMN timestamp FORMAT A20
COLUMN db_user FORMAT A10
COLUMN object_name FORMAT A15
COLUMN sql_text FORMAT A40

SELECT timestamp, db_user, os_user, object_schema, 
       object_name, sql_text 
FROM dba_fga_audit_trail;

Bulk Policy Management

BEGIN
    FOR rec IN (SELECT * FROM dba_audit_policies) LOOP
        DBMS_FGA.DISABLE_POLICY(
            rec.object_schema, 
            rec.object_name, 
            rec.policy_name
        );
    END LOOP;
END;
/

FGA Data Dictionary Views

DBA_AUDIT_POLICIES displays policy definitions including object schema/name, policy name, condition text, and enabled status.

DBA_FGA_AUDIT_TRAIL shows captured audit records with session ID, timestamp, database user, OS user, hostname, object details, SCN, SQL text, and bind variables.

ASM Instance Auditing

ASM instances lack the AUDIT_TRAIL parameter, writing all audit records exclusively to AUDIT_FILE_DEST. SYSASM and SYSDBA connections generate audit files, with frequent logins potentially creating numerous audit files.

-- View ASM audit parameters
SHOW PARAMETER audit

NAME                           TYPE        VALUE
------------------------------ ----------- --------------------
audit_file_dest                string      /u01/app/11.2.0/grid/rdbms/audit
audit_sys_operations           boolean     FALSE

Clearing Audit Trails

-- Remove standard audit entries
DELETE FROM SYS.AUD$;

-- Remove FGA entries
DELETE FROM SYS.FGA_LOG$;

-- Or use the view
DELETE FROM DBA_FGA_AUDIT_TRAIL;