134 Part II: Oracle Database Vault
realm_name =>'Sales History'
, grantee => 'ANTHONY'
, rule_set_name => NULL
, auth_options => dbms_macutl.g_realm_auth_participant );
END;
/
PL/SQL procedure successfully completed.
With the successful authorizations, you can see that MARY and ANTHONY can work
in unison to administer the SH application schema, but within the control of their realm
authorization:
mary@aos> Create a table for staging warehouse data
mary@aos>CREATE TABLE sh.sales_staging
(
prod_id NUMBER NOT NULL
,cust_id NUMBER NOT NULL
,time_id DATE NOT NULL
,channel_id NUMBER NOT NULL
,promo_id NUMBER NOT NULL
,quantity_sold NUMBER(10,2) NOT NULL
);
Table created.
mary@aos> As the realm owner, grant access on the new table to OE
mary@aos>GRANT SELECT ON sh.sales_staging TO oe;
Grant succeeded.
mary@aos> As the realm owner, grant a realm protected role
mary@aos>GRANT sales_select_role TO oe;
Grant succeeded.
anthony@aos> Anthony can administrator realm protected objects
anthony@aos>ALTER TABLE sh.sales_staging
ADD amount_sold NUMBER(10,2) NOT NULL;

Table altered.
anthony@aos> Anthony cannot grant access to the new table
anthony@aos> due to his realm participant status
anthony@aos>GRANT SELECT ON sh.sales_staging TO hr;
GRANT SELECT ON sh.sales_staging TO hr
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: realm violation for grant object privilege on
SH.SALES_STAGING
ORA-06512: at "DVSYS.AUTHORIZE_EVENT", line 55
Chapter 5: Database Vault Fundamentals 135
ORA-06512: at line 31
anthony@aos> Anthony cannot grant access to realm roles
anthony@aos> due to his realm participant status
anthony@aos>GRANT sales_select_role TO hr;
GRANT sales_select_role TO hr
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: realm violation for grant role privilege on
SALES_SELECT_ROLE.
ORA-06512: at "DVSYS.AUTHORIZE_EVENT", line 55
ORA-06512: at line 31
Once MARY grants SELECT access on the new table to the OE account, this account can
query the table, because realms honor direct object privilege usage:
oe@aos> SELECT cust_id, amount_sold, tax_amount FROM sh.sales_staging;
CUST_ID AMOUNT_SOLD TAX_AMOUNT

987 1232.16 52.37

1660 616.08 26.18
1762 410.72 17.46
1843 308.04 13.09
1948 246.43 10.47
2273 205.36 8.73
2380 176.02 7.48
2683 154.02 6.55
2865 136.91 5.82
9 rows selected.
Realm Authorizations Controlled with DBV Rule Sets
Realm authorizations can be configured so that authorization is controlled using a DBV rule set
that define the conditions (when, where, how) in which a session may leverage system ANY
privileges on objects protected by the realm. This feature is extremely important if you want to
provide a higher level of assurance that data is accessed or manipulated in the context of its
intended usage. The concept of intended usage is crucial to your ability to ensure compliance
regulations are met and insider threats are minimized.
We can demonstrate the impact of a DBV rule set on realm authorizations. MARY is the
primary database administrator of the objects protected by the Sales History realm and ANTHONY
is the alternate database administrator of these objects, possibly while MARY is on vacation or
has called in sick. If MARY were out on vacation or were sick, we might expect the company’s
security system not to have a record of her “badging-in” for the day or the Human Resources
system may have a record of her vacation dates. A DBV rule set named CheckPrimaryAdminStatus
could be created that integrates into these types of systems to determine whether the primary
application DBA MARY is working on a particular day. The rule set can dynamically query
these systems to decide whether to “turn off” a realm authorization. This type of integration
136 Part II: Oracle Database Vault
can be accomplished by updating the realm authorization previously defined for ANTHONY to
use the new rule set:
dbvowner@aos> modify the realm authorization for ANTHONY
dbvowner@aos> to be based on the Disabled DBV Rule Set

dbvowner@aos>BEGIN
dbms_macadm.update_realm_auth (
realm_name =>'Sales History'
, grantee => 'ANTHONY'
, rule_set_name => 'CheckPrimaryAdminStatus'
, auth_options => dbms_macutl.g_realm_auth_participant );
END;
/
PL/SQL procedure successfully completed.
If ANTHONY attempts to leverage his realm authorization after this modification and MARY is
at work, the previously successful DDL commands he could issue would be denied.
anthony@aos> Attempt an ALTER TABLE command that was previously allowed
anthony@aos> ALTER TABLE sh.sales_staging
ADD unit_of_measure VARCHAR2(10);
ALTER TABLE sh.sales_staging ADD unit_of_measure VARCHAR2(10)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: realm violation for alter table on SH.SALES_STAGING
ORA-06512: at "DVSYS.AUTHORIZE_EVENT", line 55
ORA-06512: at line 31
DBV rule sets powerfully impact realm authorizations in contributing the conditional (when,
where, how) aspect of a sound security policies. DBV rule sets also provide critical auditing and
error-handling capabilities that we will explore a bit later in the chapter.
Command Rules
In the preceding section, we discussed how DBV realms protect an application schema’s objects
and database roles from the use of the powerful system ANY privileges that database administrators
are typically granted. The DBV product will intercept database commands as they are submitted
to the database kernel’s SQL engine. Once the command is intercepted, the DBV kernel-resident
code will examine the realm protection configuration to determine whether or not to allow the

command.
Command rules are a second type of DBV protection and are part of the same enforcement
process flow that provides a layer of security within DBV. Statement-level controls are examined
and enforced immediately after the realm protections are evaluated. Command rules do not
consider whether system ANY privileges or even direct object privileges are used, but focus on
the command being used and the object being affected. Command rules are like database
triggers, but they were designed as a separate DBV security mechanism in which declarative rules
can be applied to the execution of SQL statements and PL/SQL packages, provided a user had
some permission to execute the statement. This means a user must have some level of database
permission to execute a statement before command rule can be enforced.
Chapter 5: Database Vault Fundamentals 137
TIP
Statement execution = DB privilege + DBV command rule.
This implies that a realm authorization that would allow for a command to be executed can
be overruled by a command rule. Further, an object does not need to be protected by a realm for
a command rule to be evaluated. A fundamental difference in the behavior of command rules and
realms is that command rules apply both to the use of system ANY privileges and direct object
privileges. The following table compares the differences between these two controls:
DBV Control Direct Object Privileges System ANY Privileges
DBV realm Implicitly allowed Requires that realm authorization and
realm authorization rule are set to TRUE
(if defined)
DBV command rule Rule set must be TRUE Rule set must be TRUE
Let’s look at an example to demonstrate the relationship of DBV realms and command rules.
An organization’s IT policy should have controls on the types of database administration that
can occur when critical systems are being used by the general user population. An organization’s
IT department will typically create system maintenance windows to make system changes in
addition to configuration management procedures for validating these changes before they are
deployed. Building on the realm examples, we can use DBV command rules to establish controls
based on a business rule such as a system maintenance window for the activities of the database

administrators who manage the objects protected by the Sales History realm. We first need to
identify the rules are a system maintenance window using a DBV rule set:
dbvowner@aos> First, define the "system maintenance window" rule.
dbvowner@aos> The IT department's policy allows for system
dbvowner@aos> maintenance on Fridays from 5 p.m. to 11 p.m.
dbvowner@aos> BEGIN
dbms_macadm.create_rule(
rule_name => 'Is Maintenance Timeframe'
, rule_expr => 'TRIM(TO_CHAR(SYSDATE,''DAY'')) = ''FRIDAY'' AND
TO_CHAR(SYSDATE,''HH24'') BETWEEN 17 AND 23'
);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> We need to define a DBV Rule Set as a container
dbvowner@aos> for the 'Is Maintenance Timeframe' rule.
dbvowner@aos> This rule set defines the auditing and error handling
dbvowner@aos> for any DBV control that uses the Rule Set
dbvowner@aos>BEGIN
dbms_macadm.create_rule_set(
rule_set_name => 'Is System Maintenance Allowed',
description =>
'Checks to determine if the system maintenance is allowed',
enabled =>dbms_macutl.g_yes,
eval_options =>dbms_macutl.g_ruleset_eval_all,
audit_options =>dbms_macutl.g_ruleset_audit_fail,
138 Part II: Oracle Database Vault
fail_options =>dbms_macutl.g_ruleset_fail_show,
fail_message =>NULL,
fail_code =>NULL,

handler_options =>dbms_macutl.g_ruleset_handler_off,
handler =>NULL);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> We can now associate the DBV Rules we want to be true
dbvowner@aos> for the DBV Rule Set to evaluate to true
dbvowner@aos>BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Is System Maintenance Allowed'
, rule_name => 'Is Maintenance Timeframe'
);
END;
/
PL/SQL procedure successfully completed.
A DBV rule set controls the auditing and error-handling aspects for any DBV component with
which it is used. This means that auditing on failure for a DBV rule set will audit when the rule set
is used with a DBV command rule or DBV realm authorization. The DBV rule set contains just
one DBV rule, ‘Is Maintenance Timeframe’, but an organization could define additional rules that
might be required to match the business rules for system maintenance. Once the DBV rule set is
defined, a DBV command rule for a potentially dangerous database command such as DROP
TABLE can be defined to use this DBV rule set to protect any of the tables in the SH object owner
account:
dbvowner@aos> BEGIN
dbms_macadm.create_command_rule (
command => 'DROP TABLE'
,rule_set_name => 'Is System Maintenance Allowed'
,object_owner => 'SH'
,object_name => '%'
,enabled => 'Y'

);
END;
/
PL/SQL procedure successfully completed.
If the junior database administrator ANTHONY attempts to issue the DROP TABLE command
outside of this maintenance window time frame on one of the objects protected by the Sales
History realm, the command will fail our DBV command rule:
anthony@aos>SELECT TO_CHAR(SYSDATE,'DAY') "DAY_OF_WEEK",
TO_CHAR(SYSDATE,'HH24') "HOUR_OF_DAY"
FROM DUAL;
DAY_OF_WEEK HO

MONDAY 10
Chapter 5: Database Vault Fundamentals 139
1 row selected.
anthony@aos>DROP TABLE SH.SALES;
DROP TABLE SH.SALES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: realm violation for drop table on SH.SALES
ORA-06512: at "DVSYS.AUTHORIZE_EVENT", line 55
ORA-06512: at line 31
This example shows that DBV realms allow you to meet compliance regulations for separation
of duty, and DBV command rules allow you to lay your organization’s business rules on top of
these compliance regulations.
Command Rule Components
A DBV command rule is configured for a specific database command being controlled by a DBV
rule set. The DBV security administrator will define the policy to state the following:
When this command is attempted, evaluate this rule set to determine whether or not to allow

the command.
You cannot define a DBV command rule for a group of database commands. For example,
you cannot create a single DBV command rule for a group of DDL commands such as CREATE
TABLE, ALTER TABLE, and DROP TABLE. Each individual database command must have a DBV
command rule configured as we did for the DROP TABLE example earlier. Command rules can
be enabled and disabled by the security administrator in the same way realms are enabled and
disabled. Command rules can be further qualified by configuring an object owner and/or an
object name according to the following options:
1. If the object owner and object name is not specified, the command rule applies to all
object owners and object names when the command is issued. An alternative form of
the DROP TABLE example could have specified the object_owner parameter as '%'.
2. If a command rule is configured with a specific object owner only and any object name
('%'), as demonstrated in the DROP TABLE example, if the result set evaluates to TRUE,
the command is allowed if the command applies to any object the object owner specified.
3. If a command rule is configured with both an object owner and object name, the
command rule evaluation applies to a single object.
It is possible to define multiple command rules for the same database command, such as
DROP TABLE, where one or more command rules are defined for specific object owners and one
command rule is defined for all object owners. For example, the security administrator can define
multiple command rules for the DROP TABLE database command as follows:
dbvowner@aos> DROP TABLE for objects owned by SH
dbms_macadm.create_command_rule (
command => 'DROP TABLE'
,rule_set_name => 'Is System Maintenance Allowed'
,object_owner => 'SH'
140 Part II: Oracle Database Vault
,object_name => '%'
,enabled => 'Y'
);
dbvowner@aos> DROP TABLE for objects owned by any account

dbvowner@aos> BEGIN
dbms_macadm.create_command_rule (
command => 'DROP TABLE'
,rule_set_name => 'Is System Backup Completed'
,object_owner => '%'
,object_name => '%'
,enabled => 'Y'
);
PL/SQL procedure successfully completed.
In this example, the security administrator has defined the first DROP TABLE command rule
to check the system maintenance timeframe when the objects are owned by the account SH. The
second DROP TABLE command rule checks to see if the system backup has completed when
the objects are owned by any account. When multiple DBV command rules apply to a database
command that is attempted, all the DBV command rules must be authorized for the command
to be allowed. With this example, if ANTHONY were to attempt to issue the command DROP
TABLE SH.SALES, the rule sets Is System Maintenance Allowed and Is System Backup Completed
would have to evaluate to TRUE for the command to be allowed.
Certain commands, such as CREATE TABLESPACE or ALTER SYSTEM, can only be defined
with option one because objects such as tablespaces are not defined within an object owner and
the concept of an owner does not apply in the Oracle database. In these cases, simply use the
percent ('%') parameter for the object_owner and object_name parameters.
The ability to specify more than one command rule for any given object is an important point,
because it allows the security administrator to define specific general access control policies. Many
packaged applications for accounting and financials use complex database logic to ensure the
integrity of the information that is managed by the application. If database administrators can
make direct updates to the tables on which these applications rely, outside of the packaged
application code, the guarantee that the compliance regulations are met or insider threats are
mitigated is questionable. These packaged applications often perform monthly, quarterly, and
yearly closing processes in which summary statistics and financial statements are generated.
Once a close process starts, controls should be in place to ensure that data that feeds into these

summary statistics and financial statements are not modified during the process.
With DBV command rules, we can enforce the two types of controls. For example, if we
consider the Sales History schema, we specify the specific to general policy around the SH.SALES
table as follows (and shown in Figure 5-7):
1. UPDATE commands are allowed only on the table SH.SALES when executed from the
SH.SALES_TRANSACTION package.
2. UPDATE commands are not allowed on any table in the SH application schema during
the monthly accounting close process.
Let’s look at an example of the command rule configuration for part of this example. First we
must define the rules and rule set that will establish the conditions in which the command can
be executed. The DBV rule will leverage an Oracle PL/SQL package named DBMS_UTILITY that
comes with Oracle Database to determine whether we are using the SALES_TRANSACTION
package for the SQL UPDATE. The DBMS_UTILITY package has a function named FORMAT_
Chapter 5: Database Vault Fundamentals 141
CALL_STACK that returns the read-only PL/SQL call stack message that is maintained by (and
secured in) the database kernel code. If we embed the DBMS_UTILITY call within our SALES_
TRANSACTION procedure, we can see the information the FORMAT_CALL_STACK function
provides:
sh@aos>CREATE OR REPLACE PACKAGE BODY sh.sales_transaction AS
PROCEDURE update_sales(customer in NUMBER, amount in NUMBER) IS
BEGIN
show the output of the DBMS_UTILITY function
DBMS_OUTPUT.PUT_LINE ( DBMS_UTILITY.FORMAT_CALL_STACK );
perform the SQL UPDATE
UPDATE sh.sales SET amount_sold = amount
WHERE cust_id = customer;
END;
END;
/
Package body created.

sh@aos> enable DBMS_OUTPUT tracing
sh@aos> SET SERVEROUT ON SIZE 10000
sh@aos> execute the procedure to
sh@aos> show the DBMS_UTILITY capability
sh@aos>BEGIN
sh.sales_transaction.update_sales(
customer => 305
, amount => 200
);
END;
/
FIGURE 5-7 DBV command rules support business rules
142 Part II: Oracle Database Vault
PL/SQL Call Stack
object line object
handle number
name
0xa11a3ee0 5 package body SH.SALES_TRANSACTION
0xa1e66428 2
anonymous block
PL/SQL procedure successfully completed.
Note that embedding the DBMS_UTILITY call into your own code is not required to enable
DBV command rule controls. This was simply added to the trusted package to demonstrate the
information maintained by the DBMS_UTILITY package while the trusted package is executing.
Armed with this useful information, we can add logic to our DBV rule set to search the read-only
call stack information provided by the database kernel for the name of the procedure we trust for
SQL UPDATE:
dbvowner@aos> first lets create the conditional rule that
dbvowner@aos> we are using the trusted sales transaction code
dbvowner@aos>BEGIN

dbms_macadm.create_rule(
rule_name => 'Called From Sales Transaction Package'
, rule_expr => 'INSTR(UPPER(DBMS_UTILITY.FORMAT_CALL_STACK),
''PACKAGE BODY SH.SALES_TRANSACTION'') > 0'
);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> next create a rule set that will group our rules
dbvowner@aos>BEGIN
dbms_macadm.create_rule_set(
rule_set_name =>'Using Financials Application',
description =>'Checks to verify commands
are executed from trusted financials packages',
enabled =>dbms_macutl.g_yes,
eval_options =>dbms_macutl.g_ruleset_eval_all,
audit_options =>dbms_macutl.g_ruleset_audit_fail,
fail_options =>dbms_macutl.g_ruleset_fail_show,
fail_message =>NULL,
fail_code =>NULL,
handler_options =>dbms_macutl.g_ruleset_handler_off,
handler =>NULL);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> associate the rule name to the rule set name
dbvowner@aos>BEGIN
dbms_macadm.add_rule_to_rule_set (
rule_set_name => 'Using Financials Application'
Chapter 5: Database Vault Fundamentals 143

, rule_name => 'Called From Sales Transaction Package'
);
END;
/
PL/SQL procedure successfully completed.
dbvowner@aos> finally create our Command Rule for UPDATE on SH.SALES
dbvowner@aos>BEGIN
dbms_macadm.create_command_rule (
command => 'UPDATE'
,rule_set_name => 'Using Financials Application'
,object_owner => 'SH'
,object_name => 'SALES'
,enabled => 'Y'
);
END;
/
PL/SQL procedure successfully completed.
If we test the UPDATE command as our Sales History realm owner, MARY, the command will
fail unless she is using our trusted sales transaction package:
mary@aos> attempt the direct table update
mary@aos> UPDATE sh.sales
SET amount_sold = 200
WHERE cust_id = 305;
UPDATE sh.sales
*
ERROR at line 1:
ORA-01031: insufficient privileges
mary@aos> attempt to use the trusted package
mary@aos>BEGIN
sh.sales_transaction.update_sales(

customer => 305
, amount => 200
);
END;
/
PL/SQL procedure successfully completed.
The trusted PL/SQL package code issues the same type of SQL UPDATE statement MARY
attempted from the SQL*Plus, but our DBV command rule is designed to allow only the SQL
UPDATE statement when it comes from PL/SQL package code. Our example leverages DBV rule
sets to provide auditing and error-handling capabilities that we will explore in the next section.
Commands Supported in Command Rules
Database commands within the Oracle database can be categorized as follows:
SELECT A read-only query against a table or view, for example.
Data Manipulation Language Write actions such as INSERT, UPDATE, or DELETE
against a table or view, for example, or EXECUTE actions on PL/SQL code.
■
■