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
roleWITH ADMIN OPTION
- Use the
DV_OWNER
role to authorizeAUDIT_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
roleWITH ADMIN OPTION
toC##CMACK
:GRANT AUDIT_ADMIN TO C##CMACK WITH ADMIN OPTION CONTAINER=ALL;
As
C##JSMITH
has the appropriateDV_ACCTMGR
andAUDIT_ADMIN
roles,AUDIT_ADMIN
grantedWITH ADMIN OPTION
, two steps can be completed by a single database user.However, to authorize
C##CMACK
to use theirAUDIT_ADMIN
role, a user with theDV_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 bothC##JSMITH
andC##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 theirAUDIT_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 toAUTHORIZE_AUDIT_ADMIN
. This is a simple example with minimal separation of duties. To fully protectC##JSMITH
from granting themselves the authorization, the user should not have bothAUDIT_ADMIN
andDV_ADMIN
roles granted to them. To minimize the risk and enforce separation of duties, you would designate a separate user to grant theAUDIT_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
- Connect as
<user>
:connect <user>
- 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