Article & News.

Database auditing is an essential security measure for tracking database activities and ensuring compliance with data governance standards. In this guide, we’ll explore how to verify auditing configurations, interpret key parameters, and configure audit log locations within Oracle. Follow these steps to ensure your Oracle database auditing is set up and managed correctly.

Checking if Database Auditing is Enabled

To determine if auditing is enabled in your Oracle database, use the SHOW PARAMETER command:

SQL> SHOW PARAMETER audit;

Example Output:

NAME TYPE VALUE
——————————– ———– ——————————
audit_file_dest string /u01/app/oracle/admin/ilprod/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string

The key parameter here is AUDIT_TRAIL, which defines the auditing status and storage type. Here are the possible values for AUDIT_TRAIL:

• none: Auditing is disabled.
• os: Audit logs are stored at the operating system level.
• db: Audit logs are stored within the database in the SYS.AUD$ table.
• db,extended: Similar to db but includes SQL bind and SQL text data.
• xml: Audit logs are stored at the OS level in XML format.
• xml,extended: Similar to xml but includes SQL bind and SQL text data.

Note: The default value for AUDIT_TRAIL is DB if the database was created via DBCA. Otherwise, it defaults to NONE.

Understanding the Audit File Destination

The AUDIT_FILE_DEST parameter specifies the location for audit trail files at the OS level, defaulting to the adump directory. You can check this setting using the following query:

col VALUE format a30
SELECT name, value, issys_modifiable, ismodified
FROM v$parameter
WHERE name LIKE ‘%audit_file_dest%’;

Example Output:

NAME VALUE ISSYS_MOD ISMODIFIED
——————– ———————————— ——— ———-
audit_file_dest /u01/app/oracle/admin/ilprod/adump DEFERRED FALSE

Configuring Audit File Destination for Multi-Node Setup

To change the audit file destination across different nodes, follow these steps.

1. Update Audit File Destination on Both Nodes

If you need to set different audit file destinations for an additional instance, such as testdb1, use the following commands:

ALTER SYSTEM SET audit_file_dest=’/DB_auditlogs/ilptclarc/testdb1′ SCOPE=spfile SID=’testdb1′;
ALTER SYSTEM SET audit_file_dest=’/DB_auditlogs/ilptclarc/testdb2′ SCOPE=spfile SID=’testdb2′;

Restart the Database

After making changes to the audit file destinations for these instances, restart the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP ;