7 Configuring Command Rules

You can create command rules to protect DDL and DML statements. Oracle Database Vault provides a set of default command rules.

Topics:

What Are Command Rules?
Default Command Rules
SQL Statements That Can Be Protected by Command Rules
Creating or Editing a Command Rule
Deleting a Command Rule
How Command Rules Work
Tutorial: Using a Command Rule to Control Table Creations by a User
Guidelines for Designing Command Rules
How Command Rules Affect Performance
Related Reports and Data Dictionary View

What Are Command Rules?

You can create custom command rules or use the default command rules. You can create and use command rules in a multitenant environment.

Topics:

About Command Rules
Using Command Rules in a Multitenant Environment

About Command Rules

A command rule is a rule that you create to protect SELECT, ALTER SYSTEM, database definition language (DDL), and data manipulation language (DML) statements that affect one or more database objects.

To customize and enforce the command rule, you associate it with a rule set, which is a collection of one or more rules. The command rule is enforced at run time. Command rules affect anyone who tries to use the SQL statements it protects, regardless of the realm in which the object exists. If you want to protect realm-specific objects, see "About Realm Authorization".

A command rule has the following attributes, in addition to associating a command rule to a command:

SQL statement the command rule protects
Owner of the object the command rule affects
Database object the command rule affects
Whether the command rule is enabled
An associated rule set

For more information about SQL statements and operations, refer to Oracle Database SQL Language Reference. See also "SQL Statements That Can Be Protected by Command Rules".

Command rules can be categorized as follows:

Command rules that have a system-wide scope. With this type, you can only create one command rule for each database instance. Examples are command rules for the ALTER SYSTEM and CONNECT statements.
Command rules that are schema specific. An example is creating a command rule for the DROP TABLE statement.
Command rules that are object specific. An example is creating a command rule for the DROP TABLE statement with a specific table included in the command rule definition.

When a user executes a statement affected by a command rule, Oracle Database Vault checks the realm authorization first. If it finds no realm violation and if the associated command rules are enabled, then Database Vault evaluates the associated rule sets. If all the rule sets evaluate to TRUE, then the statement is authorized for further processing. If any of the rule sets evaluate to FALSE, then the statement is not allowed to be executed and a command rule violation is raised. Chapter 6, "Configuring Rule Sets," describes rule sets in detail.

You can define a command rule that uses factors for the CONNECT event to permit or deny sessions after the usual steps–user authentication process, factor initialization, and Oracle Label Security integration–are complete.

For example, you can configure a command rule that allows DDL statements such as CREATE TABLE, DROP TABLE, and ALTER TABLE in the BIZAPP schema to be authorized after business hours, but not during business hours.

You can run reports on the command rules that you create in Oracle Database Vault. See "Related Reports and Data Dictionary View" for more information.

This chapter explains how to configure command rules by using Oracle Database Vault Administrator. To configure command rules by using the DBMS_MACADM PL/SQL package, refer to Chapter 15, "Oracle Database Vault Command Rule APIs."

Using Command Rules in a Multitenant Environment

In a multitenant environment, you can create command rules for the CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE, and DROP PLUGGABLE DATABASE statements.

To apply these command rules to the entire multitenant environment, create them in the root.

Default Command Rules

Table 7-1 lists default command rules that Oracle Database Vault provides.

Table 7-1 Default Command Rules

SQL Statement	Object Name	Rule Set Name
`CREATE USER`	-	Can Maintain Accounts/Profiles
`ALTER USER`	-	Can Maintain Own Account
`DROP USER`	-	Can Maintain Accounts/Profiles
`CREATE PROFILE`	-	Can Maintain Accounts/Profiles
`ALTER PROFILE`	-	Can Maintain Accounts/Profiles
`DROP PROFILE`	-	Can Maintain Accounts/Profiles
`ALTER SYSTEM`	-	Allow Fine Grained Control of System Parameters
`CHANGE PASSWORD`	-	Can Maintain Own Account^Foot 1

^Footnote 1The actual SQL statement that the Can Maintain Own Account rule refers to is PASSWORD.

The following set of command rules helps you to achieve separation of duty for user management:

ALTER PROFILE
ALTER USER
CREATE PROFILE
CREATE USER
DROP PROFILE
DROP USER

To grant a user the ability to use these commands, you can grant the user the role that the rule set checks. For example, the CREATE USER command rule ensures that a user who tries to run a CREATE USER statement has the DV_ACCTMGR role.

SQL Statements That Can Be Protected by Command Rules

You can protect the a large number of SQL statements by using command rules.

The SQL statements that you can protect are as follows:

`ALTER CLUSTER`	`CREATE EDITION`	`DROP DIMENSION`
`ALTER DIMENSION`	`CREATE USER`	`DROP DIRECTORY`
`ALTER FUNCTION`	`CREATE CLUSTER`	`DROP FUNCTION`
`ALTER INDEX`	`CREATE CONTEXT`	`DROP INDEX`
`ALTER INDEXTYPE`	`CREATE DATABASE LINK`	`DROP INDEXTYPE`
`ALTER JAVA`	`CREATE DIMENSION`	`DROP JAVA`
`ALTER LIBRARY`	`CREATE DIRECTORY`	`DROP LIBRARY`
`ALTER OPERATOR`	`CREATE FUNCTION`	`DROP OPERATOR`
`ALTER OUTLINE`	`CREATE INDEX`	`DROP OUTLINE`
`ALTER MATERIALIZED VIEW`	`CREATE INDEXTYPE`	`DROP MATERIALIZED VIEW`
`ALTER MATERIALIZED VIEW LOG`	`CREATE JAVA`	`DROP MATERIALIZED VIEW LOG`
`ALTER PACKAGE`	`CREATE LIBRARY`	`DROP PACKAGE`
`ALTER PACKAGE BODY`	`CREATE OPERATOR`	`DROP PACKAGE BODY`
`ALTER PLUGGABLE DATABASE`	`CREATE OUTLINE`	`DROP PROCEDURE`
`ALTER PROCEDURE`	`CREATE PACKAGE`	`DROP PLUGGABLE DATABASE`
`ALTER PROFILE`	`CREATE PACKAGE BODY`	`DROP PROFILE`
`ALTER RESOURCE COST`	`CREATE PLUGGABLE DATABASE`	`DROP ROLE`
`ALTER ROLE`	`CREATE PROCEDURE`	`DROP ROLLBACK SEGMENT`
`ALTER ROLLBACK SEGMENT`	`CREATE PROFILE`	`DROP SEQUENCE`
`ALTER SEQUENCE`	`CREATE ROLE`	`DROP SYNONYM`
`ALTER SESSION`	`CREATE ROLLBACK SEGMENT`	`DROP TABLE`
`ALTER SYNONYM`	`CREATE SCHEMA`	`DROP TABLESPACE`
`ALTER SYSTEM`	`CREATE SEQUENCE`	`DROP TRIGGER`
`ALTER TABLE`	`CREATE MATERIALIZED VIEW`	`DROP TYPE`
`ALTER TABLESPACE`	`CREATE MATERIALIZED VIEW LOG`	`DROP TYPE BODY`
`ALTER TRIGGER`	`CREATE SYNONYM`	`DROP USER`
`ALTER TYPE`	`CREATE TABLE`	`DROP VIEW`
`ALTER TYPE BODY`	`CREATE TABLESPACE`	`EXECUTE`
`ALTER USER`	`CREATE TRIGGER`	`GRANT`
`ALTER VIEW`	`CREATE TYPE`	`INSERT`
`ANALYZE CLUSTER`	`CREATE TYPE BODY`	`NOAUDIT`
`ANALYZE INDEX`	`CREATE VIEW`	`RENAME`
`ANALYZE TABLE`	`DELETE`	`REVOKE`
`ASSOCIATE STATISTICS`	`DISASSOCIATE STATISTICS`	`SELECT`
`AUDIT`	`DROP CLUSTER`	`TRUNCATE CLUSTER`
`CHANGE PASSWORD`	`DROP CONTEXT`	`TRUNCATE TABLE`
`COMMENT`	`DROP DATABASE LINK`	`UPDATE`
`CONNECT`	`DROP EDITION`

See Also:

"Using Command Rules in a Multitenant Environment" for information about using the CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE, and DROP PLUGGABLE DATABASE in a multitenant container database (CDB)

Creating or Editing a Command Rule

You can create or edit a command rule in Oracle Database Vault Administrator.

From Cloud Control, log into Oracle Database Vault Administrator as a user who has been granted the DV_OWNER or DV_ADMIN role.

"Logging into Oracle Database Vault" explains how to log in.
In the Administration page, under Database Vault Components, click Command Rules.
In the Command Rules page:
- To create a new command rule, click Create to display the Create Command Rule page.
  
  Description of the illustration create_command_rule.gif
- To edit an existing command rule, select it from the list and then click Edit.
In the Create (Edit) Command Rule page, enter the following settings:
- Command: Select the SQL statement or operation for which you want to create a command rule. This attribute is mandatory.
- Status: Select either Enabled or Disabled to enable or disable the command rule during run time. This attribute is mandatory.
- Applicable Object Owner: From the list, select the owner of the object the command rule affects. You can use wildcard character % to select all owners. (However, you cannot use wildcard characters with text, such as EM% to select all owners whose names begin in EM.) This attribute is mandatory for all SQL statements that operate on objects within a specific schema. See "SQL Statements That Can Be Protected by Command Rules" for a list of supported SQL statements.
  
  Note that the SELECT, INSERT, UPDATE, DELETE, and EXECUTE statements are not allowed for a selection of all (%) or the SYS and DVSYS schemas.
- Applicable Object Name: Enter the name of the database object that the command rule affects, or specify % to select all database objects. This attribute is mandatory, if you selected an object owner from the Object Owner list.
  
  You can run Oracle Database Vault reports on objects that the command rule affects. See the "Related Reports and Data Dictionary View" for more information.
- Evaluating Rule Set: From the list, select the rule set that you want to associate with the command rule. This attribute is mandatory.
  
  If the rule set evaluates to true, then the SQL statement succeeds. If it evaluates to false, the statement fails, and then Oracle Database Vault raises a command rule violation. (You can track rule violations by using the Command Rule Configuration Issues Report, discussed in Chapter 24.) Any auditing and custom event handling associated with the rule set occurs as a part of the command rule processing.
  
  See Chapter 6, "Configuring Rule Sets," for more information about rule sets.
Click OK.

Deleting a Command Rule

Before you delete a command rule, you can locate the various references to it by querying the command rule-related Oracle Database Vault views.

From Cloud Control, log into Oracle Database Vault Administrator as a user who has been granted the DV_OWNER or DV_ADMIN role.

"Logging into Oracle Database Vault" explains how to log in.
In the Oracle Database Vault Administration page, select Command Rules.
In the Command Rules page, select the command rule that you want to remove.
Click Delete.
In the Confirmation window, click Yes.

See Also:

Appendix 22, "Oracle Database Vault Data Dictionary Views" for more information about the command rule-related Oracle Data Vault data dictionary views

How Command Rules Work

"How Realms Work" describes what happens when a database account issues a SELECT, DDL, or DML statement that affects objects within a realm.

The following actions take place when SELECT, DDL, or DML statement is issued:

Oracle Database Vault queries all the command rules that need to be applied.

For SELECT, DDL, and DML statements, multiple command rules may apply because the object owner and object name support wildcard notation.

You can associate rule sets with both command rules and realm authorizations. Oracle Database Vault evaluates the realm authorization rule set first, and then it evaluates the rule sets that apply to the command type being evaluated.
For each command rule that applies, Oracle Database Vault evaluates its associated rule set.
If the associated rule set of any of the applicable command rules returns false or errors, Oracle Database Vault prevents the command from executing. Otherwise, the command is authorized for further processing. The configuration of the rule set with respect to auditing and event handlers dictates the auditing or custom processing that occurs.

Command rules override object privileges. That is, even the owner of an object cannot access the object if the object is protected by a command rule. You can disable either a command rule or the rule set of a command. If you disable a command rule, then the command rule does not perform the check it is designed to handle. If you disable a rule set, then the rule set always evaluates to TRUE. However, if you want to disable a command rule for a particular command, then you should disable the command rule because the rule set may be associated with other command rules or realm authorizations.

Tutorial: Using a Command Rule to Control Table Creations by a User

In this tutorial, you create a simple command rule to control whether or not users can create tables in the SCOTT schema.

Topics:

Step 1: Connect as User SCOTT and Create a Table
Step 2: Connect Using the DVOWNER or DV_ADMIN Role and Create a Command Rule
Step 3: Test the Command Rule
Step 4: Remove the Components for this Tutorial

See Also:

"Tutorial: Creating an Email Alert for Security Violations" for another example of how a command rule can work with a rule set to send an email alert when a violation occurs

Step 1: Connect as User SCOTT and Create a Table

First, user SCOTT must create a table.

Log into the database instance as user SCOTT.
```
sqlplus scott
Enter password: password
```
In a multitenant environment, enter a command similar to the following:
```
sqlplus scott@hrpdb
Enter password: password
```
To find the available pluggable databases (PDBs), query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

If the SCOTT account is locked and expired, then log in as the Database Vault Account Manager and unlock SCOTT and create a new password. For example:
```
sqlplus bea_dvacctmgr --Or, sqlplus bea_dvacctmgr@hrpdb
Enter password: password

ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;
```
Replace password with a password that is secure. See Oracle Database Security Guide for the minimum requirements for creating passwords.
```
CONNECT SCOTT --Or, sqlplus SCOTT@hrpdb
Enter password: password
```
As user SCOTT, create a table.
```
CREATE TABLE t1 (num NUMBER);
```
Now drop the table.
```
DROP TABLE t1;
```

At this stage, user SCOTT can create and drop tables. Do not exit SQL*Plus yet, and remain connected as SCOTT. You must use it later on when SCOTT tries to create another table.

Step 2: Connect Using the DVOWNER or DV_ADMIN Role and Create a Command Rule

After the table has been created in the SCOTT schema, you can create a command rule.

From Cloud Control, log into Oracle Database Vault Administrator as a user who has been granted the DV_OWNER or DV_ADMIN role and the SELECT ANY DICTIONARY system privilege.

"Logging into Oracle Database Vault" explains how to log in.
In the Oracle Database Vault Administrator Administration page, click Command Rules.

The Command Rules page appears.
Click Create.

The Create Command Rule page appears.
Enter the following settings:
- Command: Select CREATE TABLE
- Status: Set to Enabled so that the command rule is active.
- Applicable Object Owner: Select SCOTT.
- Applicable Object Name: Set to % so that it applies to all objects in the SCOTT schema.
- Evaluating Rule Set: Select Disabled so that no one can create tables in the SCOTT schema.
Click OK.

Do not exit Database Vault Administrator

Command rules take effect immediately. Right away, user SCOTT is prevented from creating tables, even though he is still in the same user session he was in a moment ago, before you created the CREATE TABLE command rule.

Step 3: Test the Command Rule

Next, you are ready to test the CREATE TABLE command rule.

In SQL*Plus, ensure that you are logged on as user SCOTT.

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

Try to create a table.
```
CREATE TABLE t1 (num NUMBER);
```
The following output should appear:
```
ORA-47400: Command Rule violation for create table on SCOTT.T1
```
As you can see, SCOTT is no longer allowed to create tables, even in his own schema.
In Oracle Database Vault Administrator, do the following:
1. In the Command Rules page, select the CREATE TABLE command rule and then click Edit.
2. In the Edit Command Rule page, select Enabled from the Rule Set list.
3. Click OK.
In SQL*Plus, as user SCOTT, try creating the table again.
```
CREATE TABLE t1 (num NUMBER);

Table created.
```

Now that the CREATE TABLE command rule is set to Enabled, user SCOTT is once again permitted to create tables. (Do not exit SQL*Plus.)

Step 4: Remove the Components for this Tutorial

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

In Oracle Database Vault Administrator, remove the CREATE TABLE command rule as follows:
1. Return to the Command Rules page.
2. Select the CREATE TABLE command rule and then click Delete.
3. In the Confirmation window, click Yes.
Log into the database instance as user SCOTT and remove the t1 table.
```
DROP TABLE t1;
```
If you no longer need the SCOTT account to be available, then connect as the Database Vault Account Manager and enter the following ALTER USER statement:
```
CONNECT bea_dvacctmgr --Or, CONNECT bea_dvacctmgr@hrpdb
Enter password: password

ALTER USER SCOTT ACCOUNT LOCK PASSWORD EXPIRE;
```

Guidelines for Designing Command Rules

Oracle provides a set of guidelines for designing command rules.

Create finer-grained command rules, because they are far easier to maintain.

For example, if you want to prevent SELECT statements from occurring on specific schema objects, then design multiple command rules to stop the SELECT statements on those specific schema objects, rather than creating a general command rule to prevent SELECT statements in the schema level.
When designing rules for the CONNECT event, be careful to include logic that does not inadvertently lock out any required user connections. If any account has been locked out accidentally, ask a user who has been granted the DV_ADMIN or DV_OWNER role to log in and correct the rule that is causing the lock-out problem. The CONNECT command rule does not apply to users with the DV_OWNER and DV_ADMIN roles. This prevents improperly configured CONNECT command rules from causing a complete lock-out.

If the account has been locked out, you can disable Oracle Database Vault, correct the rule that is causing the lock-out problem, and then reenable Oracle Database Vault. Even when Oracle Database Vault is disabled, you still can use Database Vault Administrator and the Database Vault PL/SQL packages. See Appendix B, "Disabling and Enabling Oracle Database Vault," for instructions on disabling and reenabling Database Vault.
Sometimes you must temporarily relax an enabled command rule for an administrative task. Rather than disabling the command rule, have the Security Manager (the account with the DV_ADMIN or DV_OWNER role) log in, set the rule set to Enabled, turn on Auditing on Success or Failure for the default rule set named Enabled, and then set the command rule back to its original rule set when the task is complete. (Be aware that in a unified auditing environment, this setting does not work. Instead, you must create a unified audit policy. Oracle Database Security Guide describes how to create unified audit policies for Database Vault.)
When designing command rules, be careful to consider automated processes such as backup where these procedures may be inadvertently disabled. You can account for these tasks by creating rules that allow the command when a series of Oracle Database Vault factors is known to be true (for example, the program being used), and the account being used or the computer or network on which the client program is running.

How Command Rules Affect Performance

The performance of a command rule depends on the complexity of the rules in the rule set associated with the command rule.

For example, suppose a rule set invokes a PL/SQL function that takes 5 seconds to run. In this case, a command rule that uses that rule set would take 5 seconds to grant access for the command statement to run.

You can check the system performance by running tools such as Oracle Enterprise Manager (including Oracle Enterprise Manager Cloud Control, which is installed by default with Oracle Database), Automatic Workload Repository (AWR), and TKPROF. For more information about Oracle Enterprise Manager, see the Oracle Enterprise Manager documentation set. For information about Cloud Control, refer to its online Help. See Oracle Database Performance Tuning Guide to learn how to monitor database performance, and Oracle Database SQL Tuning Guide to monitor the performance of individual SQL and PL/SQL statements.

Related Reports and Data Dictionary View

Table 7-2 lists Oracle Database Vault reports that are useful for analyzing command rules. See Chapter 24, "Oracle Database Vault Reports," for information about how to run these reports.

Table 7-2 Reports Related to Command Rules

Report	Description
"Command Rule Audit Report"	Lists audit records generated by command rule processing operations
"Command Rule Configuration Issues Report"	Tracks rule violations, in addition to other configuration issues the command rule may have
"Object Privilege Reports"	Lists object privileges that the command rule affects
"Sensitive Objects Reports"	Lists objects that the command rule affects
"Rule Set Configuration Issues Report"	Lists rules sets that have no rules defined or enabled, which may affect the command rules that use them

You can use the DVSYS.DBA_DV_COMMAND_RULE data dictionary view to find the SQL statements that are protected by command rules. See "DVSYS.DBA_DV_COMMAND_RULE View" for more information.