7 Authorizing DDL on a Different Schema

In an Oracle Database Vault environment, when a schema is protected by a realm or has access to realm protected objects, through realm authorization or object privileges, then the schema automatically has DDL controls applied to it to prevent a malicious actor from performing DDL modifications.

The only unexpected failure from the previous test is GKRAMER should be authorized to perform index creation in the HR schema.

For example, if a malicious user had privileges to modify a procedure in the HR schema, then they could insert malicious code into the procedure, and it would be trusted by the Database Vault realm. To avoid this situation, Oracle Database Vault enforces controls on DDL statements.

To allow GKRAMER to perform CREATE INDEX for HR objects, you must authorize GKRAMER to perform DDL:

1. Connect as C##JSMITH to the pluggable database:

   connect c##jsmith@pdb_name

2. SELECT * FROM DBA_DV_DDL_AUTH ORDER BY 1;
   
   EXEC DBMS_MACADM.AUTHORIZE_DDL('GKRAMER','HR');
   
   SELECT * FROM DBA_DV_DDL_AUTH ORDER BY 1;

3. Since you only changed the DDL authorization for GKRAMER, you will only retest the commands for GRKAMER:
   1. Connect as GRKAMER:

      connect GRKAMER

   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;

   3. 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';

      You will also see GKRAMER cannot drop the index they created. This is because of the Database Vault command rule you created preventing DROP INDEX commands on the HR schema.

      Command	GKRAMERWithout DDL Authorization	GKRAMERWith DDL Authorization
      SELECT COUNT(*) FROM HR.EMPLOYEES;	ORA-01031: insufficient privileges	ORA-01031: insufficient privileges
      CREATE TABLE HR.EMP2 AS SELECT * FROM HR.EMPLOYEES;	ORA-01031: insufficient privileges	ORA-01031: insufficient privileges
      CREATE INDEX HR.TEST_IDX ON HR.EMP2(HIRE_DATE,LAST_NAME);	ORA-47415: Insufficient Oracle Database Vault authorization for DDL	Success
      DROP INDEX HR.TEST_IDX;	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

      Note:

      If you have upgraded from an earlier Oracle Database release, you may see (%,%) in the DBA_DV_DDL_AUTH view. As this authorization was added in a later release, Oracle chose to allow the existing DDL-allowed behavior to continue. If you are performing a new installation of Oracle Database 19c or later, you will not have the (%,%) authorization.