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.