Troubleshooting and Tracing Errors

12.1 Most Common Database Vault Views

It is important to understand how to navigate the views associated with Oracle Database Vault.

As a user with the DV_ADMIN role, you should familiarize yourself with the views with this query:

connect c##jsmith@pdb_name

SELECT VIEW_NAME
    FROM DBA_VIEWS
WHERE VIEW_NAME LIKE 'DBA_DV_%'
ORDER BY 1;

The most common views you will work with are as follows:

Table 12-1 Common Database Vault Views

View Name	Purpose
`DBA_DV_%_AUTH`	Look for views with `AUTH` in the name. These views list which users are authorized for various activities, such as `PROXY_AUTH`, `DDL_AUTH`, `JOB_AUTH`, and so on.
`DBA_DV_COMMAND_RULE`	Lists command rules created.
`DBA_DV_REALM`	Lists realms created. Pay attention to the `REALM_TYPE` column. `REGULAR` respects direct object grants to access realm protected data, but `MANDATORY` does not. Mandatory requires you to be an authorized participant/owner in the realm.
`DBA_DV_REALM_AUTH`	Lists authorizations to realms created. Pay attention to `AUTH_RULE_SET_NAME` (restrictions to using authorization) and `AUTH_OPTIONS` (owner vs. participant).
`DBA_DV_REALM_OBJECT`	Lists objects protected by realms. `%` means all, including object names or types not yet created.
`DBA_DV_RULE`	Lists rules and the rule expression.
`DBA_DV_RULE_SET`	Lists rule sets, status, and if a fail message is returned to the user. Also lists whether it is static or dynamic (`IS_STATIC`) and whether all rules have to be true or just one (`EVAL_OPTIONS_MEANING`).
`DBA_DV_RULE_SET_RULE`	Lists rule sets and their associated rules.
`DBA_DV_SIMULATION_LOG`	Lists the actions that would have violated a command rule or realm if the command rule or realm was in enforcement mode.
`DBA_DV_STATUS`	Lists the configuration and enablement status of Oracle Database Vault and whether operations control is in use (`DV_APP_PROTECTION`).
`DBA_ROLES`	Lists the roles in the database. Oracle Database Vault creates 12+ roles that help enforce separation of duties. Look for `DV_%` roles.

To determine a list of non-default realms, objects they protect, and authorized users, you can run the following query as a user with DV_OWNER, DV_ADMIN or DV_SECANALYST role:

connect c##jsmith@pdb_name

SELECT * FROM (
    SELECT REALM_NAME, 'PROTECTED OBJECTS' COL2, OWNER COL3, OBJECT_TYPE COL4 ,OBJECT_NAME COL5
    FROM DVSYS.DBA_DV_REALM_OBJECT
    UNION
    SELECT REALM_NAME, 'AUTHORIZATIONS' COL2, GRANTEE COL3, AUTH_RULE_SET_NAME COL4, AUTH_OPTIONS COL5
    FROM DVSYS.DBA_DV_REALM_AUTH)
    WHERE REALM_NAME IN (SELECT NAME FROM DBA_DV_REALM WHERE ORACLE_SUPPLIED = 'NO')
    ORDER BY REALM_NAME ASC, COL2 DESC;

If you followed the examples in this quick start guide, you would end up with results like this:

REALM_NAME            COL2                 COL3    COL4     COL5
_____________________ ____________________ _______ ________ ________
Protect HR indexes    protected objects    HR      INDEX    %
Protect HR indexes    authorizations       HR               Owner
Protect HR tables     protected objects    HR      TABLE    %
Protect HR tables     protected objects    HR      VIEW     %
Protect HR tables     authorizations       HR               Owner

To view user-defined Database Vault command rules, and their associates rule set and rules, you could use a query like this:

SELECT A.COMMAND, A.OBJECT_OWNER, A.OBJECT_NAME, B.RULE_SET_NAME, B.RULE_NAME
    FROM DVSYS.DBA_DV_COMMAND_RULE A
        , DVSYS.DBA_DV_RULE_SET_RULE B
    WHERE A.RULE_SET_NAME = B.RULE_SET_NAME
        AND A.ORACLE_SUPPLIED != 'YES'
ORDER BY 1,2,3;

If you followed the examples in this quick start guide, you would end up with results like this:

COMMAND    OBJECT_OWNER OBJECT_NAME RULE_SET_NAME   RULE_NAME
__________ ____________ ___________ ________________ __________________
DROP TABLE HR           %           Trusted Rule Set Trusted IP Address

For more detailed scripts to collect Oracle Database Vault information, refer to to My Oracle Support Doc ID 1352556.1, Script To List The Database Vault Realms, Command Rules And Rule Sets.

12.2 Realm and Command Rule Enforcement Simulation

Oracle Database Vault simulation mode can also be used to help troubleshoot realms and command rules.

There are times, particularly at the beginning of a project, when you do not want to enforce your custom Database Vault realms or command rules immediately. Oracle Database Vault offers a simulation mode, where the enforcement activity would be logged, but not enforced, allowing you to verify you have the proper database users authorized to the realm or the correct logic in the rule, that is being enforced by the rule set, for a command rule.

For example, if you want to modify the realm you created to protected tables and allow an action to occur, you can update the realm to be in simulation mode. As a user with DV_OWNER or DV_ADMIN role, perform the following:

Connect as C##JSMITH on the pluggable database:
```
connect c##jsmith@pdb_name
```

Create the mandatory realm in simulation mode:

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

Verify the realm is in simulation mode:
```
SELECT ENABLED FROM DBA_DV_REALM WHERE NAME = 'Protect HR tables';
```
You should see:
```
ENABLED
__________
S
```

As GKRAMER, perform a query of the HR.EMPLOYEES table:

connect gkramer@pdb_name

SELECT COUNT(*) FROM HR.EMPLOYEES;

Once you complete your activity, you should review the simulation log to see what would have been prevented by the realm if it was still in enforcement mode:
```
connect c##jsmith@pdb_name

SELECT USERNAME,COMMAND, VIOLATION_TYPE, REALM_NAME, RETURNCODE, SQLTEXT FROM DBA_DV_SIMULATION_LOG;
```
The following output should appear:
```
USERNAME COMMAND VIOLATION_TYPE  REALM_NAME         RETURNCODE SQLTEXT                   
________ _______ _______________ __________________ __________ _________________________________
GKRAMER  SELECT  Realm Violation Protect HR tables  1031       SELECT COUNT(*) FROM HR.EMPLOYEES
```
Note:
Simulation mode does not create records for activity that would be authorized by the realm or command rule. Only activity that would have been denied is recorded in the simulation log.
After you have completed your simulation exercise, Oracle recommends deleting all rows from the simulation log table to not confuse yourself if you perform the action again. As a user with the DV_OWNER role, run the following commands:
```
connect c##jsmith@pdb_name

DELETE FROM DVSYS.SIMULATION_LOG$;
COMMIT;
```

Return your realm to enforcement mode:

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

Verify the realm is in enforcement mode:
```
SELECT ENABLED FROM DBA_DV_REALM WHERE NAME = 'Protect HR tables';
```
You should see:
```
ENABLED
__________
Y
```

12.3 Tracing Database Vault Activity

If you are still unsure as to why your SQL command is being blocked by Oracle Database Vault then you can enable tracing.

Trace files are created on the database server in the diagnostics directory, the same as tracing other database operations. Oracle Database Vault tracing is enabled by session or system-wide, on the container database or a specific pluggable database.

To enable tracing, you must have the following privileges:

Database Vault role DV_OWNER or DV_ADMIN
System privilege ALTER SESSION or ALTER SYSTEM

If you have followed the examples in this guide, you have a database user who has both the DV_ADMIN role and the ALTER SYSTEM system privilege, by having the DBA role.

To enable tracing:

Connect as C##JSMITH on the pluggable database:
```
connect c##jsmith@pdb_name
```

Enable tracing:

ALTER SYSTEM SET EVENTS 'TRACE[DV] DISK=HIGHEST';

Run a command that will fail and show up in the trace log:
1. Connect as GKRAMER on the pluggable database:
```
connect gkramer@pdb_name
```
2. Run a command that will fail:
```
SELECT COUNT(*) FROM HR.EMPLOYEES;
```
  You will receive Oracle error message ORA-01031: insufficient privileges.
Verify that tracing has been enabled by viewing the trace log on the database server.
As the oracle operating system user on the Oracle database, run the following:
```
cd $ORACLE_BASE/diag

find . -type f -name "*_QS_DV_trace.trc"

./rdbms/free/FREE/trace/FREE_ora_225318_QS_DV_trace.trc

vi ./rdbms/free/FREE/trace/FREE_ora_225318_QS_DV_trace.trc
```
In the trace file, you will see the result of the command and the specific realm that is protecting the object. Protect HR Tables is the realm protecting the HR.EMPLOYEES table:
```
Result=Realm Authorization Failed
Realm_Name=Protect HR tables     Required_Auth_Level=0
Current_User=141
Object_Owner=HR     Object_Name=EMPLOYEES     Object_Type=TABLE
SQL_Text=select count(*) from hr.employees
```
When you are finished, you will disable the tracing event and, if appropriate, revoke the DV_ADMIN role.
You should not revoke DV_ADMIN from C##JSMITH in this example.
1. Connect as C##JSMITH on the pluggable database:
```
connect c##jsmith@pdb_name
```
2. Disable tracing:
```
ALTER SYSTEM SET EVENTS 'TRACE[DV] OFF';
```

If the advice in this section does not help you resolve your issues, submit a Support Request and include the relevant information about your environment and Database Vault settings. Uploading the results of MOS 1352556.1 Script To List The Database Vault Realms, Command Rules And Rule Sets, will help your support engineer identify your issue more effectively.