Oracle AUDIT Explained: A Comprehensive Guide

Oracle Database auditing is a vital feature for tracking database activities, ensuring compliance, and identifying security issues. This guide covers enabling and disabling auditing, audit trail options, cleaning up audit logs, and more.

Configuration Options:

1. Enable database auditing with extended information:

   ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;

   Note: Avoid enclosing DB, EXTENDED in quotes.

2. Direct audit records to operating system files:

   ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;

3. Direct audit records to XML files:

   ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE;

Restart the database for these settings to take effect.

Understanding Audit Trails

Oracle supports multiple audit trail destinations, each serving a specific purpose. Here’s an overview:

• Database Audit Trail: Logs records in the SYS.AUD$ table.
• Operating System Audit Trail: Stores audit logs in operating system files.
• XML Audit Trail: Logs records in XML format for easier parsing and analysis.
• Syslog Audit Trail: Logs audit records to the system’s syslog, commonly used in UNIX environments.

Key Audit Views

Oracle provides several views to help you query and manage audit records. Below is a summary:

• DBA_AUDIT_TRAIL: Displays standard audit trail records.
• DBA_FGA_AUDIT_TRAIL: Displays fine-grained audit (FGA) information.
• DBA_COMMON_AUDIT_TRAIL: Combines standard and FGA audit records.
• V$XML_AUDIT_TRAIL: Displays audit records stored in XML files.
• DBA_AUDIT_SESSION: Tracks session-level auditing information.
• DBA_OBJ_AUDIT_OPTS: Shows audit options for specific objects.
• DBA_PRIV_AUDIT_OPTS: Lists privilege-based auditing configurations.

Configuring Advanced Audit Options

1. SYS and Mandatory Audits

To log SYS user activities and mandatory audits, use the AUDIT_SYS_OPERATIONS parameter:

ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;

2. Limiting the Size and Age of Audit Files

Use the DBMS_AUDIT_MGMT package to manage audit file size and retention:

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    AUDIT_TRAIL_PROPERTY_VALUE => 102400  -- 100 MB
  );
END;
/

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    AUDIT_TRAIL_PROPERTY_VALUE => 10  -- 10 days
  );
END;
/

3. Querying Audit Settings

To verify current audit settings, query the following views:

• DBA_AUDIT_MGMT_CONFIG_PARAMS: Displays audit trail property configurations.
• DBA_AUDIT_MGMT_CLEANUP_JOBS: Lists audit trail purge jobs.

Example: Operating System Audit Record

Here is a sample operating system audit record:

LENGTH: "349"
SESSIONID:[5] "43464"
ENTRYID:[1] "1"
USERID:[6] "DBSNMP"
USERHOST:[7] "ARVIND"
ACTION:[3] "100"
RETURNCODE:[1] "0"
COMMENT$TEXT:[97] "Authenticated by: DATABASE; Client address: (HOST=192.X.X.X)"

Disabling Auditing

If auditing is no longer required, disable it by setting the AUDIT_TRAIL parameter to NONE:

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

Restart the database to apply the changes.

Auditing Options in Oracle Database

Oracle offers various auditing options to meet diverse requirements. These include:

• Standard Auditing: Tracks activities such as DML, DDL, and user logins.
• Fine-Grained Auditing (FGA): Monitors specific conditions, such as access to sensitive data in a table or column.
• Privilege Auditing: Tracks the use of privileges, such as CREATE SESSION or SELECT ANY TABLE.
• SYS Auditing: Logs actions performed by users with SYSDBA or SYSOPER privileges.

Cleaning Up Audit Logs

Over time, audit logs can grow significantly, consuming storage and impacting performance. Periodically clean up the logs to manage storage effectively.

Steps to Clean Up Audit Logs:

1. Delete audit logs from the SYS.AUD$ table:

   DELETE FROM SYS.AUD$ WHERE TIMESTAMP# < SYSDATE - 30;

   This deletes records older than 30 days.

2. Truncate the SYS.AUD$ table if a complete cleanup is needed:

   TRUNCATE TABLE SYS.AUD$;

3. For operating system audit logs, manually delete files from the audit trail directory or configure retention policies.

Advanced Audit Trail Management

1. Limiting Audit File Size

Use the DBMS_AUDIT_MGMT package to set a maximum file size:

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    AUDIT_TRAIL_PROPERTY_VALUE => 102400  -- 100 MB
  );
END;
/

2. Setting Audit File Retention Period

Control the age of audit files using the following command:

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    AUDIT_TRAIL_PROPERTY_VALUE => 10  -- 10 days
  );
END;
/

Example: Listing Active Object Audit Options

To view audit options for specific objects, run:

SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SCOTT' AND OBJECT_NAME LIKE 'EMP%';

Conclusion

Oracle auditing provides robust capabilities to monitor, secure, and manage database activities. By leveraging standard and fine-grained auditing, configuring advanced properties, and cleaning up audit logs periodically, you can ensure database security and compliance while maintaining performance.

Implement these auditing practices to enhance the security and observability of your Oracle Database environment.