Because Oracle Database Vault Realms restrict actions that a user can take on the database, some actions will fail without proper authorization. This example shows how the C##JSMITH user is unable to grant the RESOURCE role because they are not in the Oracle System Privilege and Role Management Realm.

1. Attempt to create the C##CMACK user and grant them the RESOURCE role:
   1. Connect as C##JSMITH:

      connect c##jsmith

   2. Create C##CMACK role and grant them the RESOURCE role:

      CREATE USER C##CMACK IDENTIFIED BY <password> CONTAINER=ALL;
      GRANT RESOURCE TO C##CMACK WITH ADMIN OPTION CONTAINER=ALL;

      You will receive the following error as C##JSMITH:

      ORA-47410: Insufficient realm privileges to GRANT on RESOURCE

2. Identify which realm is protecting the RESOURCE role:
   1. Connect as C##JSMITH:

      connect c##jsmith

   2. Run the following:

      SELECT REALM_NAME, OBJECT_NAME
          FROM DBA_DV_REALM_OBJECT
          WHERE OBJECT_TYPE = 'ROLE'
              AND OBJECT_NAME = 'RESOURCE'
          ORDER BY 1,2;

      You should see the following output:

      REALM_NAME                                           OBJECT_NAME
      ____________________________________________________ ______________
      Oracle System Privilege and Role Management Realm    RESOURCE 

3. Identify which database users are authorized to access the realm protected objects and, more specifically, which users have the OWNER realm authorization which will allow them to forward-grant privileges on objects they hold WITH ADMIN OPTION privileges:

   SELECT GRANTEE, AUTH_OPTIONS
       FROM DBA_DV_REALM_AUTH
       WHERE REALM_NAME = 'Oracle System Privilege and Role Management Realm'
       ORDER BY 1;

   You should see the following output:

   GRANTEE       AUTH_OPTIONS
   _____________ _______________
   SYS           Owner

You should not use SYS unless it is necessary. Instead, you should authorize C##JSMITH to the Oracle System Privilege and Role Management Realm as an owner, thus allowing C##JSMITH to grant the resource role to other database users.

You have completed the steps to authorize C##JSMITH to the Oracle System Privilege and Role Management Realm as an owner, thus allowing C##JSMITH to grant the resource role to other database users.

Authorize a named user to the Oracle System Privilege and Role Management Realm as an owner, allowing the user to grant the resource role to other database users. If you are granting privileges to a database user in all database containers, authorize a named user on each of the pluggable databases (PDB) where Oracle Database Vault is enabled.

For example, add C##JSMITH to the Database Vault realm:

After a named user has the privileges and realm authorization, they can grant the RESOURCE to other users.

For example, as C##JSMITH, grant the RESOURCE role to C##CMACK:

1. Connect as C##JSMITH:

   connect c##jsmith

2. Create C##CMACK role and grant them the RESOURCE role:

   CREATE USER C##CMACK IDENTIFIED BY <password> CONTAINER=ALL;
   GRANT RESOURCE TO C##CMACK WITH ADMIN OPTION CONTAINER=ALL;

3. As C##JSMITH, create the GRKAMER account in the pluggable database:

   ALTER SESSION SET CONTAINER=pdb_name;
   CREATE USER GKRAMER IDENTIFIED BY <password>;
   GRANT CREATE SESSION TO GKRAMER;

4. Use the C##JSMITH database user to grant several system privileges to GRKAMER:

   GRANT SELECT ANY TABLE, UPDATE ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE TO GKRAMER;
   GRANT CREATE ANY INDEX, DROP ANY INDEX TO GKRAMER;
   GRANT RESOURCE TO GKRAMER;

For additional information on default realms and command rules, refer to the Oracle Database Vault Administrator’s Guide.

Prior to applying realms and command rules, you can query the HR.EMPLOYEES table to see what actions users are able to perform. After applying realms and command rules the results will be different.

Query the HR.EMPLOYEES table as each of the following user and compare the results to the table below:

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

If your results be different from the table, verify you have configured and enabled Oracle Database Vault in the container database and created the users and granted the appropriate privileges.

Notice the Database Vault related accounts C##DVOWNER and C##DVACCTMGR do not have the system or object privileges required to access the HR.EMPLOYEES table. These accounts are not intended to access data, only perform Database Vault administrative activities

Mandatory realms require the grant, either direct or granted through a role, and the Database Vault realm authorization. By creating a mandatory realm, only users who have the appropriate system or object privileges and realm authorization can access the specified tables and views.

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

   connect c##jsmith@pdb_name

2. Create the mandatory realm:

   BEGIN 
       DVSYS.DBMS_MACADM.CREATE_REALM(  
           realm_name     => 'Protect HR tables' 
           ,description    => 'Mandatory realm to protect HR tables' 
           ,enabled        => dbms_macutl.g_yes 
           ,audit_options  => null 
           ,realm_type     => dbms_macadm.mandatory_realm);
   END;
   /

   Note:

   If you are using Oracle Database 19c or earlier, and traditional auditing, you can specify a value for audit_options instead of null. For more information, see the Oracle Database Vault Administrator’s Guide for Oracle Database 19c.
3. Add the objects you wish to protect with the new realm. You could specify a wildcard (%) for the OBJECT_TYPE parameter and protect all existing and new objects in a schema.

   In this example, you will separate TABLES and VIEWS into a single realm, and INDEXES into a separate realm in the following example.

   BEGIN
       DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(  
           realm_name     => 'Protect HR tables' 
           ,object_owner   => 'HR' 
           ,object_name    => '%' 
           ,object_type    => 'TABLE');
   END;
   /
   
   BEGIN
       DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM( 
           realm_name     => 'Protect HR tables' 
           ,object_owner   => 'HR' 
           ,object_name    => '%' 
           ,object_type    => 'VIEW');
   END;
   /

4. Authorize the users to access the realm-protected tables and views. In a mandatory realm you will also authorize the object owner.

   In this example, you will authorize HR to access its own objects.

   BEGIN
       DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(  
           realm_name     => 'Protect HR tables' 
           ,grantee       => 'HR' 
           ,rule_set_name => '' 
           ,auth_options  => dbms_macutl.g_realm_auth_owner);
   END;
   /

5. Determine if you will be enforcing the realm or not:
   • Simulation (non-enforcement) mode: Oracle Database Vault simulation mode allows you to identify which users are accessing data you want to protect with an Oracle Database Vault realm. Only violations of the Database Vault realm authorization list will be recorded.

     Set the ENABLED parameter to DBMS_MACUTL.G_SIMULATION

     BEGIN
         DVSYS.DBMS_MACADM.UPDATE_REALM(   
             realm_name     => 'Protect HR tables' 
             ,description   => 'Mandatory realm to protect HR tables' 
             ,enabled       => dbms_macutl.g_simulation 
             ,audit_options => null 
             ,realm_type    => dbms_macadm.mandatory_realm);
     END;
     /

   • Enforcement mode: Oracle Database Vault enforcement mode means the mandatory realm will enforce the controls on the objects it is protecting and allow database users access if they maintain the appropriate system or object privileges and authorization to the realm.

     Set the ENABLED parameter to DBMS_MACUTL.G_YES

     BEGIN
         DVSYS.DBMS_MACADM.UPDATE_REALM(   
             realm_name     => 'Protect HR tables' 
             ,description   => 'Mandatory realm to protect HR tables' 
             ,enabled       => dbms_macutl.g_yes 
             ,audit_options => null 
             ,realm_type    => dbms_macadm.mandatory_realm);
     END;
     /

For more information on Oracle Database Vault simulation mode, see the Troubleshooting and Tracing Errors section in the Oracle Database Vault Administrator’s Guide.

By separating the tables and indexes into separate realms, you can allow index maintenance without access to table data. There is still a risk because data could be accessed indirectly, but if index maintenance is a task of the DBA and this is a risk you are comfortable assuming, then this separation is a common methodology.

For example:

1. Connect as C##JSMITH:

   connect c##jsmith

2. Create the mandatory realm:

   BEGIN
       DVSYS.DBMS_MACADM.CREATE_REALM(  
           realm_name     => 'Protect HR indexes' 
           ,description    => 'Mandatory realm to protect HR indexes' 
           ,enabled        => dbms_macutl.g_yes 
           ,audit_options  => null 
           ,realm_type     => dbms_macadm.mandatory_realm);
   END;
   /

   Note:

   If you are using Oracle Database 19c or earlier, and traditional auditing, you can specify a value for audit_options instead of null. For more information, see the Oracle Database Vault Administrator’s Guide.
3. Add the index to the realm:

   BEGIN
       DBMS_MACADM.ADD_OBJECT_TO_REALM(  
           realm_name    => 'Protect HR indexes' 
           ,object_owner  => 'HR' 
           ,object_name  => '%' 
           ,object_type  => 'INDEX');
   END;
   /

4. Add authorization to the realm:

   BEGIN
       DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(  
           realm_name    => 'Protect HR indexes' 
           ,grantee      => 'HR' 
           ,rule_set_name => null 
           ,auth_options  => dbms_macutl.g_realm_auth_owner);
   END;
   /

5. Add the DBA, in this case GKRAMER, as a realm-authorized owner so that they can perform index creation, deletion, and updates:

   BEGIN
       DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(  
           realm_name    => 'Protect HR indexes' 
           ,grantee      => 'GKRAMER' 
           ,rule_set_name => null 
           ,auth_options  => dbms_macutl.g_realm_auth_owner);
   END;
   /