Using Simulation Mode for Logging Realm and Command Rule Activities

11 Using Simulation Mode for Logging Realm and Command Rule Activities

Simulation mode writes the activities performed on realms and command rules to a log file, which is accessible through a data dictionary view.

About Simulation Mode
Simulation mode enables you to capture a record of errors during the development phase of a realm or command rule.
Simulation Mode Use Cases
Simulation mode is useful for testing a development configuration of new realms and command rules.
Tutorial: Tracking Violations to a Realm Using Simulation Mode
This tutorial shows how to create a realm that uses simulation mode and then test violations to the realm.

11.1 About Simulation Mode

Simulation mode enables you to capture a record of errors during the development phase of a realm or command rule.

Simulation mode stores the errors that are captured in one location for easy analysis. To use simulation mode, when you create or update a realm or command rule, instead of enabling or disabling the realm or command rule, you can set it to simulation mode. The realm or command rule is still enabled, but because violations are not blocked and are instead recorded to the simulation log file, you can test it for any potential errors before you enable it for a production environment.

For example, the following creation statement for a realm enables simulation mode:

BEGIN
 DBMS_MACADM.CREATE_REALM(
 realm_name    => 'HR Apps',
 description   => 'Realm to protect the HR realm', 
 enabled       => DBMS_MACUTL.G_SIMULATION,
 audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL,
 realm_type    => 1,
 realm_scope   => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

At this stage, SQL statements that violate realms or command rules are still able to execute, but these activities are recorded to the DBA_DV_SIMULATION_LOG data dictionary view. For example, the following query finds violations against the HR Apps realm:

SELECT USERNAME, COMMAND, SQLTEXT, VIOLATION_TYPE FROM DBA_DV_SIMULATION_LOG WHERE REALM_NAME = "HR APPS";

USERNAME  COMMAND    SQLTEXT                          VIOLATION_TYPE            
    
--------  ---------- -------------------------------  --------------
DGRANT    SELECT     SELECT SALARY FROM HR.EMPLOYEES; Realm Violation

After you have completed testing the realm or command rule, a user who has been granted the DV_ADMIN or DV_OWNER role can clear the DBA_DV_SIMULATION_LOG data dictionary view by deleting the contents of the underlying table of this view, DVSYS.SIMULATION_LOG$.

For example:

DELETE FROM DVSYS.SIMULATION_LOG$;

Or:

DELETE FROM DVSYS.SIMULATION_LOG$ WHERE COMMAND = 'SELECT';

Parent topic: Using Simulation Mode for Logging Realm and Command Rule Activities

11.2 Simulation Mode Use Cases

Simulation mode is useful for testing a development configuration of new realms and command rules.

Use cases are as follows:

Application certification

When you are certifying applications, you can use simulation mode as follows in an application test environment:
1. Put all schemas for the application into mandatory realms with simulation mode enabled.
2. Run a full regression test.
3. Analyze the simulation mode log by querying the DBA_DV_SIMULATION_LOG data dictionary view to find who can access these schemas.
4. Update the realms with new authorizations, and the enable the realms (that is, not using simulation mode).
5. Re-run the regression test.
Introduction of a new command rule

You can use simulation mode on a production database that has Oracle Database Vault enabled.
1. Put the new command rule into production in simulation mode for however many weeks that are necessary.
2. Analyze the simulation mode log by querying DBA_DV_SIMULATION_LOG to determine if the command rule is working correctly.
3. Make changes to the command rule as necessary.
4. Enable the command rule.
Putting a new realm into a production database in simulation mode.

This method can help to find the system context information needed to set the trusted path rules in rule sets and find authorized users for realms.
1. Create the new realm in mandatory mode and add the protected objects
2. Do not add any authorized users.
3. Run applications and development operations from the normal IP addresses that will be used.
4. Check the simulation log file for both authorized users and system context information that you can use to create trusted paths.
5. Create the trusted paths, and then add the authorized users.
6. Clear the simulation log and run the application and development operation tasks again.
7. After a period of time, review the simulation log. If all the controls were updated correctly, then the simulation log is empty. Log entries in the simulation mode indicate additional changes that you need to make to the realm and rule sets or the log entries may indicate a malicious use.

Parent topic: Using Simulation Mode for Logging Realm and Command Rule Activities

11.3 Tutorial: Tracking Violations to a Realm Using Simulation Mode

This tutorial shows how to create a realm that uses simulation mode and then test violations to the realm.

About This Tutorial
In this tutorial, you will create a realm around the HR.EMPLOYEES schema and test violations against it.
Step 1: Create Users for This Tutorial
You must create three users for this tutorial. .
Step 2: Create a Realm and an Oracle Database Vault Policy
Next, you create a realm around the HR.EMPLOYEES table, and then add this realm to an Oracle Database Vault policy.
Step 3: Test the Realm and Policy
User tjones_dba will commit a violation on the realm to test the realm and policy.
Step 4: Query the DBA_DV_SIMULATION_LOG View for Violations
Now you can check the simulation mode log for the violations that user tjones_dba committed.
Step 5: Enable and Re-test the Realm
Now that you have captured the violations, user psmith can update the HR.EMPLOYEES_pol policy.
Step 6: Remove the Components for This Tutorial
You can remove the components that you created for this tutorial if you no longer need them.

Parent topic: Using Simulation Mode for Logging Realm and Command Rule Activities

11.3.1 About This Tutorial

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.

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode

11.3.2 Step 1: Create Users for This Tutorial

You must create three users for this tutorial. .

The users are: psmith, who is the Database Vault policy owner; tjones_dba, who commits violations on the HR.EMPLOYEES table; and smavris, whose salary is the recipient of tjones_dba’s violations.

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.

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.

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

For example:

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

Grant user psmith the DV_POLICY_OWNER role, which enables psmith to manage Database Vault policies.
```
GRANT DV_POLICY_OWNER TO psmith;
```

Connect as user SYS with the SYSDBA administrative privilege.

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

Grant the DBA role to user tjones_dba
```
GRANT DBA TO tjones_dba;
```

Connect as the HR schema owner.

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

Grant the SELECT privilege on the HR.EMPLOYEES table to user smavris
```
GRANT SELECT ON HR.EMPLOYEES TO smavris;
```

At this stage, the users have all been created and granted the appropriate privileges.

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode

11.3.3 Step 2: Create a Realm and an Oracle Database Vault Policy

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

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

For example:

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

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

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

At this point, the realm and policy are ready to be tested.

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode

11.3.4 Step 3: Test the Realm and Policy

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

Connect as user tjones_dba.

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

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
```

Cut smavris’s salary in half.

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

1 row updated.

Connect as user smavris.
```
CONNECT smavris -- Or, smavris@hrpdb
```

Query the salary of smavris.

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

Output similar to the following should appear:

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

At this point, tjones_dba’s violations have been recorded in the DBA_DV_SIMULATION_LOG data dictionary view.

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode

11.3.5 Step 4: Query the DBA_DV_SIMULATION_LOG View for Violations

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

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

For example:

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

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

The output indicates that user tjones_dba has committed two offences: first, he looked at another employee’s salary, and not only that, he cut it in half. The violation type is a realm violation. The query by smavris was not captured because she legitimately can look at her salary.

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode

11.3.6 Step 5: Enable and Re-test the Realm

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

This is so that the HR.EMPLOYEES_realm realm can be enabled. Then you can test the violations again.

Connect as user psmith.

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

Update the policy so that it is enabled.

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

Connect as user tjones_dba.

CONNECT tjones_dba --Or, tjones_dba@hrpdb

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.

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode

11.3.7 Step 6: Remove the Components for This Tutorial

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

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

For example:

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

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.

Remove the HR.EMPLOYEES_realm realm.

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

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';
```

Connect as user HR.

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

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

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

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

For example:

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

Remove the users psmith, smavris, and tjones_dba.

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

Parent topic: Tutorial: Tracking Violations to a Realm Using Simulation Mode