6 Creating Unified Audit Policies and Accessing Audit Records

In Oracle Database Vault 23ai, the unified audit trail is protected and not accessible unless the proper Oracle Database Vault authorization is given. This protection extends to privileged users, such as SYS and SYSTEM, users with DBA role, and users with AUDIT_VIEWER or AUDIT_ADMIN roles.

To perform this task in Oracle Database 23ai, you will do the following:

1. Grant the AUDIT_ADMIN role WITH ADMIN OPTION
2. Use the DV_OWNER role to authorize AUDIT_ADMIN for the user

Because you are enforcing separation of duties, you will use two distinct database users to create this new database user. For example:

Grant and Authorize the AUDIT_ADMIN role WITH ADMIN OPTION

1. Connect as C##JSMITH:

   connect c##jsmith

2. Grant the AUDIT_ADMIN role WITH ADMIN OPTION to C##CMACK:

   GRANT AUDIT_ADMIN TO C##CMACK WITH ADMIN OPTION CONTAINER=ALL;

   As C##JSMITH has the appropriate DV_ACCTMGR and AUDIT_ADMIN roles, AUDIT_ADMIN granted WITH ADMIN OPTION, two steps can be completed by a single database user.

   However, to authorize C##CMACK to use their AUDIT_ADMIN role, a user with the DV_ADMIN role must perform the authorization. This ensures database users with highly privileged roles, such as viewing or managing audit data, cannot do so without explicit authorization.

3. Attempt to query the UNIFIED_AUDIT_TRAIL data dictionary view using both C##JSMITH and C##CMACK:

   connect c##jsmith@pdb_name
   
   SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;

   connect c##cmack@pdb_name
   
   SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;

   The expected outcome for both users, on Oracle Database 23ai, is ORA-1031, insufficient privileges.

4. Authorize C##CMACK to use their AUDIT_ADMIN role on the container database and each pluggable database:

   connect c##jsmith
   
   EXEC DBMS_MACADM.AUTHORIZE_AUDIT_ADMIN('C##CMACK');

   connect c##jsmith@pdb_name
   
   EXEC DBMS_MACADM.AUTHORIZE_AUDIT_ADMIN('C##CMACK');

   C##CMACK can now query and managed the unified auditing operations in both the container database and each pluggable database. However, C##JSMITH still can't.

   Caution:

   C##JSMITH could grant themselves authorization to AUTHORIZE_AUDIT_ADMIN. This is a simple example with minimal separation of duties. To fully protect C##JSMITH from granting themselves the authorization, the user should not have both AUDIT_ADMIN and DV_ADMIN roles granted to them. To minimize the risk and enforce separation of duties, you would designate a separate user to grant the AUDIT_ADMIN role.
5. As C##CMACK, query the unified auditing operations to confirm authorization:

   connect c##cmack@pdb_name
   
   SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;

   You will see the number of unified audit trails.

Create Audit Policies

1. Connect as C##CMACK:

   connect c##cmack@pdb_name

2. Create these audit policies:

   CREATE AUDIT POLICY aud_protect_hr_tables
   ACTIONS COMPONENT=DV REALM VIOLATION ON "Protect HR tables";
   AUDIT policy aud_protect_hr_tables;

   CREATE AUDIT POLICY aud_protect_hr_indexes
   ACTIONS COMPONENT=DV REALM VIOLATION ON "Protect HR indexes";
   AUDIT policy aud_protect_hr_indexes;

   CREATE AUDIT POLICY aud_protect_rule_set_trs
   ACTIONS COMPONENT=DV RULE SET ON "Trusted Rule Set";
   AUDIT policy aud_protect_rule_set_trs;

3. Verify the Unified Audit policies exist and are enabled:

   SELECT POLICY_NAME
       FROM AUDIT_UNIFIED_ENABLED_POLICIES
   WHERE POLICY_NAME LIKE 'AUD%'
   ORDER BY 1;

   The output will be similar to:

   POLICY_NAME
   _________________________________
   AUD_PROTECT_HR_INDEXES
   AUD_PROTECT_HR_TABLES
   AUD_PROTECT_RULE_SET_TRS

4. To generate audit events, query the HR.EMPLOYEES table as each of the following user:
   • SYS
   • SYSTEM
   • C##DVOWNER
   • C##DVACCTMGR
   • C##JSMITH
   • C##CMACK
   • GKRAMER
   • HR
   1. Connect as <user>:

      connect <user>

   2. Attempt the following commands:

      SELECT COUNT(*) FROM HR.EMPLOYEES;
      CREATE TABLE HR.EMP2 AS SELECT * FROM HR.EMPLOYEES;
      CREATE INDEX HR.TEST_IDX ON HR.EMP2(HIRE_DATE,LAST_NAME);
      DROP INDEX HR.TEST_IDX;
      DROP TABLE HR.EMP2;

5. As C##CMACK, query the Unified Audit view to identify Database Vault related records and compare the results to the table below:

   SELECT EVENT_TIMESTAMP, DBUSERNAME, OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT, RETURN_CODE
       FROM UNIFIED_AUDIT_TRAIL
   WHERE AUDIT_TYPE = 'Database Vault'
       AND OBJECT-SCHEMA = 'HR';

   Command	SYS	SYSTEM	C##DVOWNER	C##DVACCTMGR	C##JSMITH	C##CMACK	GKRAMER	HR
   SELECT COUNT(*) FROM HR.EMPLOYEES;	ORA-01031: insufficient privileges	ORA-01031: insufficient privileges	ORA-00942: table or view does not exist	ORA-00942: table or view does not exist	ORA-01031: insufficient privileges	ORA-41900: missing READ privilege on "HR"."EMPLOYEES"	ORA-01031: insufficient privileges	Success
   CREATE TABLE HR.EMP2 AS SELECT * FROM HR.EMPLOYEES;	ORA-01031: insufficient privileges	ORA-01031: insufficient privileges	ORA-00942: table or view does not exist	ORA-00942: table or view does not exist	ORA-01031: insufficient privileges	ORA-41900: missing READ privilege on "HR"."EMPLOYEES"	ORA-01031: insufficient privileges	Success
   CREATE INDEX HR.TEST_IDX ON HR.EMP2(HIRE_DATE,LAST_NAME);	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-00942: table or view does not exist	ORA-00942: table or view does not exist	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-41900: missing READ privilege on "HR"."EMPLOYEES"	ORA-47415: Insufficient Oracle Database Vault authorization for DDL	Success
   DROP INDEX HR.TEST_IDX;	ORA-01418: specified index does not exist	ORA-01418: specified index does not exist	ORA-01418: specified index does not exist	ORA-01418: specified index does not exist	ORA-01418: specified index does not exist	ORA-01418: specified index does not exist	ORA-01418: specified index does not exist	Success
   DROP TABLE HR.EMP2;	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47401: realm violation for CREATE INDEX on HR.TEST_IDX	ORA-47306: 20000: Access is blocked. Please speak to your security team