Creating Unified Audit Policies and Accessing Audit Records

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:

Grant the AUDIT_ADMIN role WITH ADMIN OPTION
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

Connect as C##JSMITH:
```
connect c##jsmith
```
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.
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.
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.
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

Connect as C##CMACK:
```
connect c##cmack@pdb_name
```

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;

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

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;
```

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`