Scenario 4 – Creating Mixed Audit Policy

In this section, we’ll explore how to create a mixed audit policy to monitor users’ activities related to specific roles and actions within your Oracle database environment.

1. Creating a Mixed Audit Policy

Start by creating a mixed audit policy that will audit users while using the STORAGE_ROLE role or performing any action related to tables:

SQL> CREATE ROLE storage_role;
SQL> GRANT DROP TABLESPACE TO storage_role;
SQL> GRANT storage_role TO dev;
SQL> GRANT DROP ANY TABLE TO jim;

SQL> CREATE AUDIT POLICY aud_mixed_pol
ACTIONS CREATE TABLE, DROP TABLE, TRUNCATE TABLE
ROLES storage_role;

 

2. Enabling the Audit Policy

Next, enable the audit policy:

SQL> AUDIT POLICY aud_mixed_pol;

3. Verifying Policy Status

Verify that the audit policy is enabled:

SQL> COL USER_NAME FORMAT A10
SQL> COL POLICY_NAME FORMAT A14
SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME LIKE ‘%MIXED%’;

4. Performing Operations

Execute operations that fall under the scope of the audit policy:

SQL> CONNECT dev/oracle_4U
SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

SQL> CONNECT jim/oracle_4U
SQL> DROP TABLE hr.t1 PURGE;

5. Viewing Audit Data

Access the resulting audit data to review the audited operations:

SQL> CONNECT system/oracle_4U
SQL> COL ACTION_NAME FORMAT A16
SQL> COL POLICY_NAME FORMAT A18
SQL> COL ROLE FORMAT A10
SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A20
SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM unified_audit_trail WHERE DBUSERNAME IN (‘JIM’,’DEV’)
AND UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_MIXED_POL%’ AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’);

6. Flushing Audit Trail

If necessary, flush the unified audit trail to ensure all audit data is available:

SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

7. Viewing Audit Data Again

Once the audit data is flushed to disk, review it again:

SQL> CONNECT system/oracle_4U
SQL> COL ACTION_NAME FORMAT A16
SQL> COL POLICY_NAME FORMAT A18
SQL> COL ROLE FORMAT A10
SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A20
SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM unified_audit_trail WHERE DBUSERNAME IN (‘JIM’,’DEV’)
AND UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_MIXED_POL%’ AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’);

By following these steps, you can effectively create and manage mixed audit policies to monitor users’ activities related to specific roles and actions within your Oracle database environment.