In this tutorial, you will create a realm around the HR.EMPLOYEES schema and test violations against it.

The HR.EMPLOYEES schema contains confidential data such employee salaries. To test the realm, an administrator, tjones_dba, will look up and modify the salary of another employee, smavris. The Database Vault administrator, leo_dvowner, will use simulation mode to track the violations to the HR.EMPLOYEES schema. To accomplish this, user leo_dvowner will create a Database Vault policy, which a delegated administrator, user psmith, will own. User psmith will then be able to make limited changes to the policy without needing the DV_OWNER or DV_ADMIN role.

You must create three users for this tutorial. .

1. Log into the database instance as a user who has been granted the DV_ACCTMGR role.

   For example:

   sqlplus bea_dvacctmgr
   Enter password: password
   

   In a multitenant environment, you must log in to the appropriate pluggable database (PDB). For example:

   CONNECT bea_dvacctmgr@hrpdb
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

2. Create the following users and grant them the CREATE SESSION privilege.

   GRANT CREATE SESSION TO psmith IDENTIFIED BY password;
   GRANT CREATE SESSION TO tjones_dba IDENTIFIED BY password;
   GRANT CREATE SESSION TO smavris IDENTIFIED BY password;

   Follow the guidelines in Oracle Database Security Guide to replace password with a password that is secure.

3. Connect as a user who has been granted the DV_OWNER role.
   For example:

   CONNECT leo_dvowner -- Or, leo_dvowner@hrpdb
   Enter password: password

4. Grant user psmith the DV_POLICY_OWNER role, which enables psmith to manage Database Vault policies.

   GRANT DV_POLICY_OWNER TO psmith;

5. Connect as user SYS with the SYSDBA administrative privilege.

   CONNECT SYS AS SYSDBA -- Or, CONNECT SYS@hrpdb AS SYSDBA
   Enter password: password
   

6. Grant the DBA role to user tjones_dba

   GRANT DBA TO tjones_dba;
   

7. Connect as the HR schema owner.

   CONNECT HR -- Or, HR@hrpdb
   Enter password: password

8. Grant the SELECT privilege on the HR.EMPLOYEES table to user smavris

   GRANT SELECT ON HR.EMPLOYEES TO smavris;
   

Next, you create a realm around the HR.EMPLOYEES table, and then add this realm to an Oracle Database Vault policy.

1. Connect as a user who has been granted the DV_OWNER role.

   For example:

   CONNECT leo_dvowner -- Or, leo_dvowner@hrpdb
   Enter password: password

2. Create the realm around HR.EMPLOYEES table as follows.

   These procedures create the HR.EMPLOYEES_realm realm, add the HR.EMPLOYEES table to this realm, authenticate HR as an owner, and authenticate user psmith as an participant.

   BEGIN
    DBMS_MACADM.CREATE_REALM(
     realm_name    => 'HR.EMPLOYEES_realm', 
     description   => 'Realm to protect HR.EMPLOYEES', 
     enabled       => DBMS_MACUTL.G_SIMULATION, 
     audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL,
     realm_type    => 0);
   END; 
   /
   
   BEGIN
    DBMS_MACADM.ADD_OBJECT_TO_REALM(
     realm_name   => 'HR.EMPLOYEES_realm', 
     object_owner => 'HR', 
     object_name  => 'EMPLOYEES', 
     object_type  => 'TABLE'); 
   END;
   /
   
   

3. Create the HR.EMPLOYEES_pol Database Vault policy and set it to be in simulation mode.

   These procedures create the HR.EMPLOYEES_pol policy, add the realm that was just created to the policy, and then add user psmith as the owner of the policy.

   BEGIN
    DBMS_MACADM.CREATE_POLICY(
     policy_name  => 'HR.EMPLOYEES_pol',
     description  => 'Policy to protect HR.EMPLOYEES',
     policy_state => DBMS_MACADM.G_SIMULATION);
   END;
   /
   
   BEGIN
    DBMS_MACADM.ADD_REALM_TO_POLICY(
     policy_name  => 'HR.EMPLOYEES_pol',
     realm_name   => 'HR.EMPLOYEES_realm');
   END;
   /
   
   BEGIN
    DBMS_MACADM.ADD_OWNER_TO_POLICY(
     policy_name  => 'HR.EMPLOYEES_pol',
     owner_name   => 'PSMITH');
   END;
   /
   

User tjones_dba will commit a violation on the realm to test the realm and policy.

1. Connect as user tjones_dba.

   CONNECT tjones_dba -- Or, tjones_dba@hrpdb
   Enter password: password

2. Query the HR.EMPLOYEES table for the salary of smavris.

   SELECT SALARY FROM HR.EMPLOYEES WHERE EMAIL = 'SMAVRIS';

   Output similar to the following should appear:

       SALARY
   ----------
         6500
   
   

3. Cut smavris’s salary in half.

   UPDATE HR.EMPLOYEES
   SET SALARY = SALARY / 2
   WHERE EMAIL = 'SMAVRIS';
   
   1 row updated.

4. Connect as user smavris.

   CONNECT smavris -- Or, smavris@hrpdb

5. Query the salary of smavris.

   SELECT SALARY FROM HR.EMPLOYEES WHERE EMAIL = 'SMAVRIS';

   Output similar to the following should appear:

       SALARY
   ----------
         3250
   

Now you can check the simulation mode log for the violations that user tjones_dba committed.

1. Connect as a user who has been granted the DV_OWNER role.
   For example:

   CONNECT leo_dvowner -- Or, leo_dvowner@hrpdb
   Enter password: password

2. Query the DBA_DV_SIMULATION_LOG data dictionary view.

   SELECT USERNAME, SQLTEXT, VIOLATION_TYPE FROM (
   SELECT T1.*, T2.COLUMN_VALUE AS REALM
   FROM DVSYS.DBA_DV_SIMULATION_LOG T1, TABLE(T1.REALM_NAME) T2
   ) WHERE REALM = 'HR.EMPLOYEES_realm';
   

   Output similar to the following should appear:

   USERNAME
   --------------------------------------------------------------------------------
   SQLTEXT
   --------------------------------------------------------------------------------
   VIOLATION_TYPE
   --------------------------------------------------------------------------------
   TJONES_DBA
   SELECT SALARY FROM HR.EMPLOYEES WHERE EMAIL = 'SMAVRIS'
   Realm Violation
   
   TJONES_DBA
   UPDATE HR.EMPLOYEES SET SALARY = SALARY / 2 WHERE EMAIL = 'SMAVRIS'
   Realm Violation
   

Now that you have captured the violations, user psmith can update the HR.EMPLOYEES_pol policy.

1. Connect as user psmith.

   CONNECT psmith -- Or, psmith@hrpdb
   Enter password: password

2. Update the policy so that it is enabled.

   BEGIN
    DBMS_MACADM.UPDATE_POLICY_STATE(
     policy_name   => 'HR.EMPLOYEES_pol',
     policy_state  => 1);
   END;
   /

3. Connect as user tjones_dba.

   CONNECT tjones_dba --Or, tjones_dba@hrpdb

4. Try lowering smavris’s salary to new depths.

   UPDATE HR.EMPLOYEES
   SET SALARY = SALARY / 2
   WHERE EMAIL = 'SMAVRIS';

   Output similar to the following should appear:

   ERROR at line 1:
   ORA-01031: insufficient privileges
   
   

   The policy, now enabled, enables the realm to protect the HR.EMPLOYEES table. smavris’s salary can shrink no more.

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

1. Connect as a user who has been granted the DV_OWNER role.
   For example:

   CONNECT leo_dvowner -- Or, leo_dvowner@hrpdb
   Enter password: password

2. Remove the HR.EMPLOYEES_pol Database Vault policy.

   EXEC DBMS_MACADM.DROP_POLICY('HR.EMPLOYEES_pol');

   You first must remove the policy before you can drop its contents.
3. Remove the HR.EMPLOYEES_realm realm.

   EXEC DBMS_MACADM.DELETE_REALM('HR.EMPLOYEES_realm');

4. Remove the simulation mode log data that was accumulated.

   Because the simulation mode log only captured information about user tjones_dba, you can remove only the rows that relate to this user.

   DELETE FROM DVSYS.SIMULATION_LOG$ WHERE USERNAME = 'TJONES_DBA';

5. Connect as user HR.

   CONNECT HR -- Or, CONNECT HR@hrpdb
   Enter password: password

6. Revert smavris’s salary back to its pre-violated state.

   UPDATE HR.EMPLOYEES
   SET SALARY = 6500
   WHERE EMAIL = 'SMAVRIS';

7. Connect as a user who has been granted the DV_ACCTMGR role.

   For example:

   CONNECT bea_dvacctmgr -- Or, bea_dvacctmgr@hrpdb
   Enter password: password
   

8. Remove the users psmith, smavris, and tjones_dba.

   DROP USER psmith;
   DROP USER smavris;
   DROP USER tjones_dba;