Why Audit?
Auditing records who did what and when. It answers questions like: Who dropped that table? Who tried to guess the password? Who read the salary data? Many regulations (finance, healthcare) require it.
Unified Audit
Modern Oracle uses unified auditing: one audit trail, one set of policies and one view called UNIFIED_AUDIT_TRAIL. It replaces the old scattered audit options.
sql
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
Create and Enable a Policy
sql
CREATE AUDIT POLICY audit_ddl
ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE;
AUDIT POLICY audit_ddl;
sql
-- audit access to a sensitive table
CREATE AUDIT POLICY audit_orders_access
ACTIONS SELECT ON shop.orders, UPDATE ON shop.orders, DELETE ON shop.orders;
AUDIT POLICY audit_orders_access;
Use the Oracle-Supplied Policies
| Policy | What it audits |
ORA_LOGON_FAILURES | Failed logins |
ORA_SECURECONFIG | A recommended set of security-sensitive actions |
ORA_ACCOUNT_MGMT | Creating, altering, dropping users and roles |
sql
AUDIT POLICY ORA_LOGON_FAILURES;
AUDIT POLICY ORA_ACCOUNT_MGMT;
Audit Only Some Users
sql
AUDIT POLICY audit_ddl BY shop, report_user;
AUDIT POLICY audit_ddl EXCEPT backup_svc;
AUDIT POLICY audit_orders_access WHENEVER NOT SUCCESSFUL;
Read the Audit Trail
sql
SELECT event_timestamp, dbusername, action_name,
object_schema, object_name, return_code, client_program_name
FROM unified_audit_trail
WHERE event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY event_timestamp DESC
FETCH FIRST 20 ROWS ONLY;
Return code: return_code = 0 means success. Any other number is an ORA error, for example 1017 means a wrong password.
See Which Policies Are On
sql
SELECT policy_name, enabled_option, entity_name
FROM audit_unified_enabled_policies;
Stop Auditing
sql
NOAUDIT POLICY audit_ddl;
DROP AUDIT POLICY audit_ddl;
Keep the Trail from Filling the Database
Audit records are stored in the SYSAUX tablespace. Purge old data on a schedule:
sql
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => FALSE);
END;
/
Best practice: Export audit records to a central store or SIEM first, then purge. And do not audit everything: audit what you will really read.
Conclusion
Use unified auditing to record logins, account changes, DDL and access to sensitive data. Read UNIFIED_AUDIT_TRAIL and purge old records on a schedule.
Quick Summary: CREATE AUDIT POLICY, AUDIT POLICY, read UNIFIED_AUDIT_TRAIL, purge with DBMS_AUDIT_MGMT.