Security auditing in Amazon RDS for Oracle

In Part 1, we enabled auditing in Oracle RDS. In this article, we will continue with advanced auditing techniques in Oracle 19c AWS RDS, focusing on specific object auditing and audit policy management.

This is useful when you want to monitor changes only for critical objects instead of auditing the entire database.

Topics Covered

Specific table auditing
Specific view auditing
Unified audit policy creation
Viewing audit logs
Disabling audit policies
Removing audit policies
Extracting object DDL

Environment

Oracle Database: 19c
Platform: AWS RDS for Oracle
Audit Mode: Mixed Mode Unified Auditing

Step 1: Verify Audit Configuration

Check audit trail setting:

SHOW PARAMETER audit_trail;

Expected output:

DB, EXTENDED

Check specific table auditing policies:

SELECT * FROM dba_obj_audit_opts;

Check unified auditing policies:

SELECT policy_name, enabled_option
FROM audit_unified_enabled_policies;

Step 2: Enable Specific Table Auditing

If you want to monitor changes only for a critical table:

Example table:

HR.EMPLOYEES

Enable auditing:

AUDIT ALTER, INDEX
ON HR.EMPLOYEES
BY ACCESS;

This audits:

ALTER TABLE
Index creation/modification on this table

To capture drop activity:

AUDIT DROP TABLE;

Step 3: Verify Specific Table Audit Logs

Make a test change:

ALTER TABLE HR.EMPLOYEES ADD TEST_COL VARCHAR2(100);

Check logs:

SELECT username,
       owner,
       obj_name,
       action_name,
       timestamp,
       sql_text
FROM dba_audit_trail
WHERE owner='HR'
AND obj_name='EMPLOYEES'
ORDER BY timestamp DESC;

Sample output:

USERNAME   OWNER   OBJ_NAME    ACTION_NAME    TIMESTAMP
--------   ----    --------    -----------    ----------------
ADMIN      HR      EMPLOYEES   ALTER TABLE    27-MAY-2026

Step 4: Enable Specific View Auditing

For a critical application view:

Example:

HR.EMPLOYEE_VIEW

Enable:

AUDIT ALTER
ON HR.EMPLOYEE_VIEW
BY ACCESS;

Capture view drop:

AUDIT DROP VIEW;

Verify:

SELECT username,
       owner,
       obj_name,
       action_name,
       timestamp
FROM dba_audit_trail
WHERE obj_name='EMPLOYEE_VIEW'
ORDER BY timestamp DESC;

Step 5: Create Unified Audit Policy for Broader DDL Monitoring

If you need broader schema monitoring:

Create policy:

CREATE AUDIT POLICY DDL_AUDIT_POLICY
ACTIONS
CREATE TABLE,
ALTER TABLE,
DROP TABLE,
CREATE VIEW,
ALTER VIEW,
DROP VIEW,
CREATE USER,
ALTER USER,
DROP USER,
CREATE PROCEDURE,
ALTER PROCEDURE,
DROP PROCEDURE;

Enable:

AUDIT POLICY DDL_AUDIT_POLICY;

Verify:

SELECT policy_name, enabled_option
FROM audit_unified_enabled_policies;

Step 6: Check Unified Audit Logs

Query audit trail:

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name,
       sql_text
FROM unified_audit_trail
ORDER BY event_timestamp DESC;

Filter for a specific object:

SELECT event_timestamp,
       dbusername,
       action_name,
       object_name,
       sql_text
FROM unified_audit_trail
WHERE object_name='EMPLOYEES'
ORDER BY event_timestamp DESC;

Step 7: Disable Audit Policy

Temporarily disable:

NOAUDIT POLICY DDL_AUDIT_POLICY;

Verify:

SELECT *
FROM audit_unified_enabled_policies;

Step 8: Remove Audit Policy Permanently

Drop policy:

DROP AUDIT POLICY DDL_AUDIT_POLICY;

Verify:

SELECT policy_name
FROM audit_unified_policies;

Step 9: Extract Current Object DDL

Check current object definition:

Table:

SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
FROM dual;

View:

SELECT DBMS_METADATA.GET_DDL('VIEW','EMPLOYEE_VIEW','HR')
FROM dual;

Troubleshooting

ORA-01031 insufficient privileges

Check:

SELECT * FROM session_privs
WHERE privilege LIKE '%AUDIT%';

No audit entries visible

Verify:

SHOW PARAMETER audit_trail;

and:

SELECT *
FROM audit_unified_enabled_policies;

Best Practice Recommendation

Use:

Specific object auditing → for critical production tables/views

Use:

Unified audit policies → for broader compliance monitoring

This reduces unnecessary audit volume while still maintaining visibility into important schema changes.

Security auditing in Amazon RDS for Oracle

0 Comments