You can configure and enable Oracle Database Vault with common users who will use the Database Vault administrative roles from the CDB root.

1. Log into the CDB root of the database instance as a user who has privileges to create users and grant the CREATE SESSION and SET CONTAINER privileges.
2. Select user accounts (or create new users) that will be used for the Database Vault Owner (DV_OWNER role) and Database Vault Account Manager (DV_ACCTMGR role) accounts.

   Oracle strongly recommends that you maintain two accounts for each role. One account, the primary named user account, will be used on a day-to-day basis and the other account will be used as a backup account in case the password of the primary account is lost and must be reset. If you do not have a backup for your account, then you cannot reset passwords. In addition, you must grant the DV_OWNER role, WITH ADMIN OPTION, to be able to reset the password of the primary account. Store these passwords in a safe location, such as a privileged account management (PAM) system, in case they are needed in the future.

   Prepend the names of these accounts with c## or C##. For example:

   GRANT CREATE SESSION, SET CONTAINER TO c##dvowner 
     IDENTIFIED BY password CONTAINER = ALL;
   GRANT CREATE SESSION, SET CONTAINER TO c##dvowner_backup 
     IDENTIFIED BY password CONTAINER = ALL;
   GRANT CREATE SESSION, SET CONTAINER TO c##dvacctmgr 
     IDENTIFIED BY password CONTAINER = ALL;
   GRANT CREATE SESSION, SET CONTAINER TO c##dvacctmgr_backup 
     IDENTIFIED BY password CONTAINER = ALL;

   This specification grants two system privileges, creates the accounts if they do not exist, assigns a password, and does this so all the users have access to the CDB and all PDB databases.

   • Create the primary accounts (c##dvowner and c##dvacctmgr) if these do not already exist for the new roles, DV_ADMIN and DV_ACCTMGR.
   • Replace password with a password that meets the password complexity requirements of the user's profile.
3. Connect to the CDB root as user SYS with the SYSDBA administrative privilege
4. Configure the two backup Database Vault user accounts.
   For example:

   BEGIN
    CONFIGURE_DV (
      dvowner_uname         => 'c##dvowner_backup',
      dvacctmgr_uname       => 'c##dvacctmgr_backup',
      force_local_dvowner   => FALSE);
    END;
   /

   In this example, setting force_local_dvowner to FALSE enables the common users to have DV_OWNER privileges for the PDBs that are associated with this CDB root. Setting it to TRUE restricts the common DV_OWNER user to have the DV_OWNER role privileges for the CDB root only. If you grant DV_OWNER locally to the CDB root common user, then that user cannot grant the DV_OWNER role commonly to any other user.
5. Run the utlrp.sql script to recompile invalidated objects in the root.

   @?/rdbms/admin/utlrp.sql
   

   If the script provides instructions, follow them, and then run the script again. If the script terminates abnormally without giving any instructions, then run it again.

6. Connect to the root as the backup Database Vault Owner user that you just configured.

   For example:

   CONNECT c##dvowner_backup
   Enter password: password
   

7. Enable Oracle Database Vault using one of the following commands:

   • To enable Oracle Database Vault to use regular mode:

     EXEC DBMS_MACADM.ENABLE_DV;

   • If every associated PDB will need to have Database Vault enabled in this database, then use the following command. (You will need to enable each of these PDBs after you complete this procedure.) PDBs that do not have Database Vault enabled will be in restricted mode after the database is restarted and until Database Vault is enabled in the PDB:

     EXEC DBMS_MACADM.ENABLE_DV (strict_mode => 'y');

8. Connect with the SYSOPER administrative privilege.
9. Restart the database.
   For a single-instance database:

   SHUTDOWN IMMEDIATE
   STARTUP

   If you are in an Oracle Real Application Clusters (Oracle RAC) environment, then you can perform an Oracle RAC rolling enablement.

10. Connect with the SYSDBA administrative privilege.
11. Verify that Oracle Database Vault and Oracle Label Security are installed and enabled.

    SELECT * FROM CDB_DV_STATUS;
    SELECT * FROM CDB_OLS_STATUS;

12. Connect as the backup DV_OWNER user and then grant the DV_OWNER role, including the WITH ADMIN OPTION clause, to the primary DV_OWNER user that you created earlier.

    For example:

    CONNECT c##dvowner_backup
    Enter password: password
    
    GRANT DV_OWNER TO c##dvowner WITH ADMIN OPTION CONTAINER = ALL;

13. Connect as the backup DV_ACCTMGR user and then grant the DV_ACCTMGR role, including the WITH ADMIN OPTION clause, to the primary DV_ACCTMGR user.

    For example:

    CONNECT c##dvacctmgr_backup
    Enter password: password
    
    GRANT DV_ACCTMGR TO c##dvacctmgr WITH ADMIN OPTION CONTAINER=ALL;

14. Store the two backup account passwords in a safe location such as a privileged account management (PAM) system in case they are needed in the future.

You must configure and enable Oracle Database Vault in the CDB root first, then in the PDBs afterward.

1. If you have not already done so, then identify or create named common user accounts to be used as the Database Vault accounts along with associated backup accounts.
2. Ensure that you have configured and enabled Oracle Database Vault in the CDB root and that the DV_OWNER role was granted commonly to the common user.
3. Connect to the PDB as an administrator who is local to the PDB.
4. If necessary, open the database.

   ALTER DATABASE OPEN;

5. Grant the CREATE SESSION and SET CONTAINER privileges to the users for this PDB.
   For example:

   GRANT CREATE SESSION, SET CONTAINER TO c##dvowner CONTAINER = CURRENT;
   GRANT CREATE SESSION, SET CONTAINER TO c##dvacctmgr CONTAINER = CURRENT;

6. Connect as user SYS with the SYSDBA administrative privilege
7. While still in the PDB, configure the two backup Database Vault user accounts.

   BEGIN
    CONFIGURE_DV (
      dvowner_uname         => 'c##dvowner_backup',
      dvacctmgr_uname       => 'c##dvacctmgr_backup');
    END;
   /
   

   In this example, the force_local_dvowner parameter is omitted because it is unnecessary. All common users who are configured within a PDB are restricted to the scope of the PDB.

8. Run the utlrp.sql script to recompile invalidated objects in this PDB.

   @?/rdbms/admin/utlrp.sql
   

   If the script provides instructions, follow them, and then run the script again. If the script terminates abnormally without giving any instructions, then run it again.

9. Connect to the PDB as the backup Database Vault Owner user that you just configured.
   For example:

   CONNECT c##dvowner_backup@pdb_name
   Enter password: password
   

10. Enable Oracle Database Vault in this PDB.

    EXEC DBMS_MACADM.ENABLE_DV;

11. Connect to the CDB with the SYSDBA administrative privilege.
12. Close and reopen the PDB.
    For example:

    ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE;
    ALTER PLUGGABLE DATABASE pdb_name OPEN;
    

13. Verify that the PDB is configured and enabled for Database Vault and Oracle Label Security.

    CONNECT SYS@pdb_name AS SYSDBA
    Enter password: password
    
    SELECT * FROM DBA_DV_STATUS;
    SELECT * FROM DBA_OLS_STATUS;

14. Connect as the backup DV_OWNER user and then grant the DV_OWNER role, including the WITH ADMIN OPTION clause, to the primary DV_OWNER user that you created earlier.

    For example:

    CONNECT c##dvowner_backup@pdb_name
    Enter password: password
    
    GRANT DV_OWNER TO c##dvowner WITH ADMIN OPTION;
    

15. Connect as the backup DV_ACCTMGR user and then grant the DV_ACCTMGR role, including the WITH ADMIN OPTION clause, to the primary DV_ACCTMGR user.

    For example:

    CONNECT c##dvacctmgr_backup@pdb_name
    Enter password: password
    
    GRANT DV_ACCTMGR TO c##dvacctmgr WITH ADMIN OPTION;

16. Store the two backup account passwords in a safe location such as a privileged account management (PAM) system in case they are needed in the future.

You can configure Oracle Database Vault for an Oracle Real Application Clusters (Oracle RAC) environment, including each Oracle RAC node.

1. Configure and enable Oracle Database Vault in the CDB root.
2. Log into the PDB as user SYS with the SYSDBA administrative privilege.
3. If necessary, open the database.

   ALTER DATABASE OPEN;

4. Run the following ALTER SYSTEM statements on either of the Oracle RAC nodes:

   ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE; 
   ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' SCOPE=SPFILE;
   ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE; 

5. Close and then reopen the PDB.

   ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE;
   ALTER PLUGGABLE DATABASE pdb_name OPEN;

In this tutorial, you create a realm around the EMPLOYEES table in the HR sample database schema by using the Oracle Database Vault PL/SQL packages.

You will also learn how to create a unified audit policy to record realm violations and review them.

In the HR schema, the EMPLOYEES table has information such as salaries that should be hidden from most employees in the company, including those with administrative access. To accomplish this, you add the HR schema to the secured objects of the protection zone, which in Oracle Database Vault is called a realm, inside the database. Then you grant limited authorizations to this realm. Afterward, you test the realm to make sure it has been properly secured.

You must enable the HR schema for this tutorial.

1. Log in to a PDB as a user who has been granted the DBA role, and then access the HR schema.
2. Query the HR.EMPLOYEES table as follows.

   SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10;
   

   Output similar to the following appears:

   FIRST_NAME           LAST_NAME                     SALARY
   -------------------- ------------------------- ----------
   Steven               King                           24000
   Neena                Kochhar                        17000
   Lex                  De Haan                        17000
   Alexander            Hunold                          9000
   Bruce                Ernst                           6000
   David                Austin                          4800
   Valli                Pataballa                       4800
   Diana                Lorentz                         4200
   Nancy                Greenberg                      12008
   
   9 rows selected.
   

3. If the HR schema is locked and expired, log in to the database instance as the DV_ACCTMGR user and unlock and unexpire the account. For example:

   ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password

   Replace password with a password that meets the password complexity requirements of the user's profile.

   A user with the DBA role has access to the salary information in the EMPLOYEES table of the HR schema. Logging in as SYSTEM works because SYSTEM has the DBA role, which has the SELECT ANY TABLE system privilege.

4. Do not exit SQL*Plus.

Realms can protect one or more schemas, individual schema objects, and database roles.

1. Connect to a PDB as a user who has been granted the DV_OWNER role.
2. Create the HR Apps realm around the HR.EMPLOYEES table.
   1. Create the HR Apps realm itself.

      BEGIN
       DBMS_MACADM.CREATE_REALM(
        realm_name    => 'HR Apps', 
        description   => 'Realm to protect the HR schema', 
        enabled       => DBMS_MACUTL.G_YES, 
        audit_options => DBMS_MACUTL.G_REALM_AUDIT_OFF,
        realm_type    => DBMS_MACADM.REGULAR_REALM);
      END; 
      /

   2. Add the HR.EMPLOYEES table to this realm.

      BEGIN
       DBMS_MACADM.ADD_OBJECT_TO_REALM(
        realm_name   => 'HR Apps', 
        object_owner => 'HR', 
        object_name  => 'EMPLOYEES', 
        object_type  => 'TABLE'); 
      END;
      /

You can create unified audit policies for Oracle Database Vault realms, rule sets, and factors.

1. Connect to a PDB as a user who has been granted the DBA role, AUDIT SYSTEM system privilege, or AUDIT_ADMIN role.
   It may be easier to create a user with this privilege for the tutorial.
   1. As a user with the DV_ACCTMGR role, create a temporary user to act as audit administrator.

      GRANT CREATE SESSION TO cmack IDENTIFIED BY password;

   2. As a user with the SYSDBA administrative privilege, grant this new user the AUDIT_ADMIN role.

      GRANT AUDIT_ADMIN TO cmack;

   3. As a user with the DV_OWNER role, grant the new user authorization to use their AUDIT_ADMIN role.

      EXEC DBMS_MACADM.AUTHORIZE_AUDIT_ADMIN('CMACK');

2. As the AUDIT_ADMIN user, cmack, create a unified audit policy to audit all actions that are violations of the HR Apps realm.
   1. Create the unified audit policy.

      CREATE AUDIT POLICY aud_hrapps_dv
      ACTIONS COMPONENT=DV REALM VIOLATION ON "HR Apps";

   2. Enable the unified audit policy.

      AUDIT POLICY aud_hrapps_dv;

At this stage, only the HR schema and database users, or roles, with direct object grants can manipulate the database objects the realm will protect. Users relying on system privileges cannot.

1. In SQL*Plus, connect to the PDB as the Database Vault Account Manager, who has the DV_ACCTMGR role, and create the local user sebastian.

   For example:

   GRANT CREATE SESSION TO sebastian IDENTIFIED BY password;

   Replace password with a password that meets the password complexity requirements of the user's profile.

2. Connect as SYS with the SYSDBA privilege, and then grant SEBASTIAN the following additional privilege.

   GRANT READ ANY TABLE TO sebastian;

3. Do not exit SQL*Plus.

Next, user SEBASTIAN must be granted authorization to the HR Apps realm, so that they can access the HR.EMPLOYEES table.

1. Connect to the PDB as a user who has been granted the DV_OWNER role.
2. Create an authorization for the HR Apps realm.

   This authorization allows SEBASTIAN to use the READ ANY TABLE system privilege on the HR.EMPLOYEES table that is protected by this realm.

   BEGIN
    DBMS_MACADM.ADD_AUTH_TO_REALM(
     realm_name  => 'HR Apps', 
     grantee     => 'SEBASTIAN'); 
   END;
   /

To test the realm, you must try to access the EMPLOYEES table as a user other than HR.

1. In SQL*Plus, connect to the PDB as SYSTEM.
2. Try querying any of the rows in the EMPLOYEES table again.
   For example:

   SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM <10;
   

   The following output should appear:

   Error at line 1:
   ORA-01031: insufficient privileges
   

   SYSTEM no longer has access to the EMPLOYEES table. (In fact, even user SYS does not have any access to this table.) However, user SEBASTIAN does have access to this information because sebastian is an authorized participant in the HR Apps realm.

3. Connect as user sebastian.
   Perform the following query:

   SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM <10;

   Output similar to the following appears:

   FIRST_NAME           LAST_NAME                     SALARY
   -------------------- ------------------------- ----------
   Steven               King                           24000
   Neena                Kochhar                        17000
   Lex                  De Haan                        17000
   Alexander            Hunold                          9000
   Bruce                Ernst                           6000
   David                Austin                          4800
   Valli                Pataballa                       4800
   Diana                Lorentz                         4200
   Nancy                Greenberg                      12008
   
   9 rows selected.

You should conduct a periodic review of violations on the unified audit policies that you have created.

1. Connect to the PDB as the user cmack.
   At minimum, a user must have the AUDIT_VIEWER role to view unified audit policy records. User cmack has been granted the AUDIT_ADMIN role, and through the DBMS_MACADM.AUTHORIZE_AUDIT_ADMIN authorization that you performed earlier, authorized to use the AUDIT_ADMIN role to manage unified audit policies and view unified audit records.
2. As user cmack, query the UNIFIED_AUDIT_TRAIL view, which stores the unified audit records.

   column dbusername format a20
   column action_name format a20
   column object_name format a20
   column object_schema format a20
   column dv_action_object_name format a25
   
   SELECT DBUSERNAME, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, DV_ACTION_OBJECT_NAME, DV_RETURN_CODE 
   FROM UNIFIED_AUDIT_TRAIL
   WHERE AUDIT_TYPE = 'Database Vault'
   AND DV_ACTION_NAME = 'Realm Violation Audit'
   ORDER BY EVENT_TIMESTAMP;

   Output similar to the following appears:

   DBUSERNAME           ACTION_NAME          OBJECT_SCHEMA        OBJECT_NAME          DV_ACTION_OBJECT_NAME     DV_RETURN_CODE
   -------------------- -------------------- -------------------- -------------------- ------------------------- --------------
   SEBASTIAN            SELECT               HR                   EMPLOYEES            HR Apps                             1031
   SYSTEM               SELECT               HR                   EMPLOYEES            HR Apps                             1031

You can remove the components that you created for this tutorial if you no longer need them.

1. As the user cmack, disable and drop the aud_hrapps_dv unified audit policy.

   NOAUDIT POLICY aud_hrapps_dv;
   DROP AUDIT POLICY aud_hrapps_dv;

2. As a user who has the DV_ACCTMGR role, drop users cmack and sebastian.

   DROP USER cmack;
   DROP USER sebastian;

3. Delete the HR Apps realm.
   1. Connect to the PDB as a user who has been granted the DV_OWNER role.
   2. Run the following statement to drop the HR Apps realm and its authorizations:

      EXEC DBMS_MACADM.DELETE_REALM_CASCADE('HR Apps');

4. If necessary, lock and expire the HR account.
   1. Connect as a user who has the DV_ACCTMGR role (for example, user dvacctmgr).
   2. Run the following ALTER USER statement:

      ALTER USER HR ACCOUNT LOCK PASSWORD EXPIRE;