There are several use cases to consider if you want to use realms in simulation mode.

• Testing an application with all new Database Vault controls: all realms are in simulation mode

• Adding a realm to existing working Database Vault controls: only a subset of realms are in simulation mode

• Adding new objects to an existing enabled realm and then testing the difference with simulation mode and not disabling existing controls

• Dropping one or more existing objects from an existing enabled realm and then testing the difference with simulation mode and not disabling existing controls

• Adding new authorized users to an existing enabled realm and then testing the difference with simulation mode and not disabling existing controls

• Dropping one or more existing authorized users from an existing enabled realm and then testing the difference with simulation mode and not disabling existing controls

• Adding or changing factors in an existing enabled realm and then testing the difference with simulation mode and not disabling existing controls

• Testing changes to a command rule in production while keeping the original command rule enabled

When a user executes a SQL statement and it fails, it may fail for realms that are enabled, fail for realms that are simulated, or it could fail for both of these reasons. There could be mandatory realms, regular realms, or both. These conditions determine the data that is captured in the simulation log.

After you create the use cases that are described in the next sections, regular realms are completely overpowered by mandatory realms when an object has both types of realms protecting it. In all cases where mandatory and regular realms protect the same object, regular realms can be ignored with regard to simulation logs. Only mandatory realm failures are captured in the simulation logs. The only time regular realm failures are entered into the simulation log is when all realms for an object are regular realms. And then, the following must happen for regular realms to be written to the simulation log:

• All regular realms in simulation mode fails and

• All regular realms that are enabled also fail

If at least one enabled or simulation regular realm succeeds, then no simulation regular realms are logged.

In this use case, all realms are either mandatory or regular and all user-created realms are in simulation mode.

Examples are as follows:

• Mandatory realms only, which are all in simulation mode

  • The user is authorized to execute the SQL statement in all mandatory realms. Nothing is captured in the simulation log table.

  • The user fails one or more mandatory realm checks. All realm check failures are logged to the simulation log. Mandatory realm checks where the user’s SQL statement succeeded is not logged.

    In this example, there are three mandatory realms. The user SQL statement succeeds with one realm and fails with the other two. Only the two failed realm checks are recorded in the simulation log.

• Regular realms only, which are all in simulation mode

  • The user is authorized to execute the SQL statement in at least one regular realm. The user should have access to the data so nothing is logged to the simulation log.

  • The user is not authorized to execute the SQL statement in all regular realms. The simulation log captures all the failed realm authorization failures. This enables the user to select which realm to which the user should be authorized. The SQL only needs to be authorized in one regular realm to work and not all regular realms need to be updated to authorize the SQL.

• Mix of mandatory and regular realms, which are all in simulation mode

  • In this case, you capture the key realms when a user is rejected. In the case with mandatory and regular realms, the mandatory realms are the key realms. All mandatory realms must pass the authorization check for the user to gain access. In fact, regular realms could be considered superfluous when mandatory realms are protecting an object. So in cases where there are both mandatory and regular realms protecting the same object, it is only the mandatory realms that control if the SQL statement is blocked or allowed to execute. It does not matter whether the user was authorized to the regular realm or not. This example follows the rules for the first scenario, for mandatory realms in simulation mode.

  • The user is authorized to execute the SQL statement in all mandatory realms. Nothing is captured in the simulation log table. Even though the user may succeed or fail in one or more regular realms, nothing about regular realm failure is captured.

  • The user fails one or more mandatory realm checks. All realm check failures are logged to the simulation log. Mandatory realm checks where the user SQL statement succeeded are not be logged.

    For example, there are three mandatory realms. The user SQL statement succeeds with one realm and fails with the other two. Only the two failed realm checks are recorded in the simulation log.

    No regular realms need to be captured, because only the mandatory realms need to be captured in the simulation log.

In this use case, you add a set of new realms to a database that has an existing set of realms.

The existing realms are enabled and working. The new realms are in simulation mode. This use case applies only if both simulation and enabled realms are protecting the same object.

Examples:

• New mandatory realms in simulation mode with existing enabled mandatory realms. This use case shows some additional mandatory realms for an object: adding more security to an existing object.

  • Enabled mandatory realms and mandatory realms in simulation mode all successful with user SQL statement: in this case, the SQL executes normally and nothing is captured

  • Enabled mandatory realms (at least one) fails and mandatory realms in simulation mode all successful: SQL is blocked and nothing is written to the simulation log

  • Enabled mandatory realms (at least one) fails and mandatory realms in simulation mode has one or more failures: SQL is blocked and all failing mandatory realms in simulation mode are entered into simulation log

  • Enabled mandatory realms all successful and mandatory realms in simulation mode have at least one failure: SQL is not blocked, all failed mandatory realms in simulation mode entered into simulation log

• New regular realms in simulation mode with existing enabled regular realms: More regular realms are added to a security object, providing new ways for users to access sensitive data

  • Enabled regular realms (at least one) and regular realms in simulation mode (at least one) successful: the user SQL executes normally with nothing written to simulation log

  • Enabled regular realms (at least one) is successful, and regular realms in simulation mode all fail: user SQL executes normally, nothing is entered into the simulation log

  • Enabled regular realms all fail and regular realms in simulation mode all fail: the user SQL is blocked and all regular realms in simulation mode are entered into simulation log. The user must evaluate which regular realm to authorize to if needed. The current implementation blocks the SQL and does not add the regular realms in simulation mode into the simulation log because the enabled regular realm would have blocked it anyway. This must change because the user may have added a new realm to authorize the SQL in this use case. There is no way to tell what happened if the new SQL should have worked, but is blocked by all regular realms in simulation mode as well (when one of the regular realms in simulation mode was designed to allow it to work). This would simulate an entry into the audit log for this situation.

  • Enabled regular realms all fail and regular realms in simulation mode (at least one) successful: the user SQL is blocked and nothing is written to the simulation log.

• New regular realms with existing enabled mandatory realms: You do not need to do anything in this situation. The enabled mandatory realms will continue to control the objects and the new regular realms in simulation mode will have no impact if they are enabled or not. No simulation logs should be generated in this case.

• New mandatory realms in simulation mode with existing enabled regular realm: While the enabled regular realm controls the objects for now, when the new mandatory realms in simulation mode are enabled, then they will have full control over the objects with no control by the older enabled regular realms. So, simulation logs will be created for all mandatory realms. This is the same as the scenario for new mandatory realms with existing enabled mandatory realms.

• New regular realms in simulation mode with existing enabled mandatory and regular realms: The enabled mandatory realms will be the deciding realms whether the new regular realms in simulation mode are added to the existing enabled regular realms in the system. This is the same as the scenario as a mix of mandatory and regular realms, all in simulation mode. Nothing is written to the simulation logs.

• New mandatory realms in simulation mode with enabled mandatory and regular realms: The enabled regular realms can be ignored. This is the same as the scenario for new mandatory realms with existing enabled mandatory realms.

• Mix of new mandatory and regular realms in simulation mode with existing enabled mandatory and regular realms: Ignore all enabled and mandatory regular realms. This is simply adding more mandatory realms to an existing object. This is the same scenario as new mandatory realms with existing enabled mandatory realms.

In this use case, you add new objects to an existing realm and then test it using simulation mode without removing the current realm protections.

Oracle recommends that you create a duplicate realm in simulation mode for the new objects with the same authorized users and rule sets. This way, the existing realm can continue to provide protection to the existing objects while testing the new object.

In this use case, you test the removal of objects to an existing realm.

Because you are removing security controls for an existing object, there is no need to use simulation mode. Simply remove the object from the realm.

In this use case, you loosen security controls by adding more users. You do not need to simulate anything if you are simply adding more authorized users.

If you are adding new functionality that is accessing data in a realm, but are not sure which new database users to authorize to the realm, then simply run the new functionality as a test (which will be blocked if not already authorized). Review the Database Vault audit logs to see the user name that attempted to access the realm data and add any new database users that are now authorized.

In this use case, you want to drop an authorized user and use simulation mode to check if the user still needs to access the realm.

You may not be sure about dropping this user because you must check if the authorized user is accessing the realm for authorized activities.

If the data is only protected by a regular realm, then you can clone the realm with authorized users as the only difference. Remove the user to be dropped from the original realm and then add this user to the cloned realm. Then the cloned realm’s audit setting is changed to capture audit on success. This enables the dropped user to be visible in the audit records if they accessed the realm over a period of time. Audit policies can also be used in this case. For data that is protected by a mandatory realm, the best solution is to create an audit policy.

In this use case, you want to test changes to factors.

There are two scenarios where the factors can change:

• Changes to an application or the infrastructure that force a change to the factors

  In this case, you do not need to keep the original factors in place. However, objects and authorized users should be able to remain enabled during the testing of the new factors. With an enabled realm, you can remove the factors from the authorized users. At the same time, create a mandatory realm for the same protected objects in simulation mode with no authorized users. The regular realm will protect the objects from unauthorized users while the simulation realm will capture all access along with the factor information. The simulation log can then be mined for each user to come up with the new factors which can then be added to the mandatory realm in simulation mode to make sure it’s clean before being migrated to the original regular realm.

• No changes to the application or the infrastructure but changes such as new factors being added or factors being removed take place

  When factors are being added, you must clone a second simulation realm from the original, but with the new factors added in. If the simulation logs shows that the usage is clean, then you can safely introduce the new factors into the original realm.

  Dropping factors lowers the security profile, so you can simply drop the factor from the rule set. No testing needs to be done.

In this use case, you test changes to an existing command rule while keeping the original command rule enabled.

Command rules may need to be updated and ideally tested before the changes are enabled in production. For a new command rule that will be added to a set of already existing command rules, put the new command rule into simulation mode when you create it. The other pre-existing command rules are already enabled and offer protection.

If you want to modify an existing command rule, there is no way to maintain the existing protection and test the new modification. Oracle recommends that you create an audit policy to capture what the original command rule was doing and then set an alert for it. The audit will not prevent the SQL as a command rule would do, but at least you can be alerted about the action. Then you can put the new updated command rule into simulation mode and test it.

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

The HR.EMPLOYEES table 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 table. 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, run the show pdbs command. 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, authenticate user psmith as an participant, and set the realm in simulation mode.

   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, COMMAND, SQLTEXT, VIOLATION_TYPE 
   FROM DBA_DV_SIMULATION_LOG, TABLE(DBA_DV_SIMULATION_LOG.REALM_NAME) RN 
   WHERE RN.COLUMN_VALUE = 'HR.EMPLOYEES_realm';
   

   Output similar to the following should appear:

   USERNAME
   --------------------------------------------------------------------------------
   COMMAND
   --------------------------------------------------------------------------------
   SQLTEXT
   --------------------------------------------------------------------------------
   VIOLATION_TYPE
   --------------------------------------------------------------------------------
   TJONES_DBA
   UPDATE
   UPDATE HR.EMPLOYEES SET SALARY = SALARY / 2 WHERE EMAIL = 'SMAVRIS'
   Realm Violation
   
   USERNAME
   --------------------------------------------------------------------------------
   COMMAND
   --------------------------------------------------------------------------------
   SQLTEXT
   --------------------------------------------------------------------------------
   VIOLATION_TYPE
   --------------------------------------------------------------------------------
   TJONES_DBA
   SELECT
   SELECT SALARY FROM HR.EMPLOYEES 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;