6 Configuring Command Rules

6.1 What Are 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 executes 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 "Defining Realm Authorization".

A command rule has the following attributes, in addition to its bonding operations and authorization functionality:

• SQL statement the command rule will protect

• Owner of the object the command rule will affect

• Database object the command rule will affect

• Whether the command rule is enabled or not

• 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 authorized and a command rule violation is created. Chapter 5, "Configuring Rule Sets" describes rule sets in detail.

You can define a command rule for a CONNECT event that can determine whether a session is allowed after the normal authentication process, Oracle Label Security initialization, factor initialization, and the Oracle Label Security integration complete. In addition, you can disable or enable a command rule when necessary, and apply the same rule to realms and command rules.

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" for more information.

This chapter explains how to configure command rules by using Oracle Database Vault Administrator. To configure command rules by using the PL/SQL interfaces and packages provided by Oracle Database Vault, refer to the following appendixes:

• Appendix H, "PL/SQL Interfaces to Oracle Database Vault"

• Appendix E, "Oracle Database Vault DVSYS.DBMS_MACADM Package"

6.2 Commands with Default Rules

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

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.

The following default command rules on an Oracle Virtual Private Database (VPD) prevent the database administrator from giving VPD capabilities to an account.

• GRANT

• REVOKE

Only the accounts with the DV_OWNER role can use the GRANT and REVOKE statements pertaining to the SYS.DBMS_RLS object and the EXECUTE privilege.

6.3 SQL Statements That Can Be Protected by Command Rules

You can protect the following SQL statements by using command rules:

6.4 Creating and Editing a Command Rule

Follow these steps:

1. Log in to Oracle Database Vault Administrator using a database account that has been granted the DV_OWNER role.

   At a minimum, you must have the DV_ADMIN role. "Starting Oracle Database Vault Administrator" explains how to log in.

2. In the Administration page, under Database Vault Feature Administration, click Command Rules.

3. In the Command Rules page:

   • To create a new command rule, click Create.

   • To edit an existing command rule, select it from the list and then click Edit.

4. In the Create (or Edit) Command Rule page, enter the following settings, and then click OK.

   • General

   • Applicability

   • Rule Set

General

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. The default is Enabled. This attribute is mandatory.

Applicability

Enter the following settings:

• Object Owner: From the list, select the owner of the object the command rule will affect. You can use wildcard characters such as %. (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.

• Object Name: Enter the name of the database object that the command rule will affect, or specify % to select all database objects. However, you cannot use wildcard characters with text, for example, EMP_% to specify all tables beginning with the characters EMP_. 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" for more information.

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 creates a command rule violation. (You can track such rule violations by using the Command Rule Configuration Issues Report, discussed in Chapter 11.) Any auditing and custom event handling associated with the rule set occurs as a part of the command rule processing.

See Chapter 5, "Configuring Rule Sets" for more information about rule sets.

6.5 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. See "Oracle Database Vault Public Views" for more information.

To delete a command rule:

1. In the Oracle Database Vault Administration page, select Command Rules.

2. In the Command Rules page, select the command rule that you want to remove.

3. Click Remove.

4. In the Confirmation page, click Yes.

6.6 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 a command rule is executed:

1. Oracle Database Vault queries for all rules that the account is attempting to use.

   For SELECT, DDL, and DML statements, more than one command rule 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.

2. For each command rule that applies, Oracle Database Vault evaluates its associated rule set.

3. 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. You can disable (or set the Disabled setting for) either a command or a rule set of a command. If a command is disabled, then the command is not allowed to run regardless of its associated rule set. That is, the command itself is disabled. However, if you disable a rule set of a command, then the rule set is not checked. In that case, the command is allowed to run without any condition. If the command has been set to the Disabled setting, then you are prevented from querying the table even though you have the SELECT privilege on the table. (Only the rule set has been disabled, not the command itself.)

6.7 Example of How Command Rules Work

Suppose you want to allow only the accounts with a role called FIN_DBA to create new tables. You could create a command rule with the following settings:

• Command: Enter CREATE TABLE

• Object Owner: Enter % to select all object owners.

• Object Name: Enter % to select all objects.

• Rule Set: Enter Can Create Tables

  The Can Create Tables rule set includes a rule that checks to see if the account has the FIN_DBA role.

If a database account with CREATE TABLE privileges issues the CREATE TABLE statement but does not have the FIN_DBA role, then Oracle Database Vault prevents the account from creating the new table and generates an error.

SQL> CREATE TABLE accounts_western (
 acct_num NUMBER(10),
 acct_name VARCHAR(10));

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20400: Command Rule Violation for CREATE TABLE
ORA-06512: at "DVSYS.AUTHORIZE_EVENT", line 35
ORA-06512: at line 13

See also "Example of How Rule Sets Work" for another example of how a command rule can work with a rule set.

6.8 Guidelines for Configuring Command Rules for SQL Statements

Follow these guidelines to configure 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 schemas, design the command rule to stop the SELECT statement on those specific schemas, rather than creating a general command rule to prevent SELECT statements in all cases.

• When designing rules for the CONNECT event, be careful to include logic that does not inadvertently lock out the Oracle Database Vault Owner or Administrator.

  If the account has been locked out, you can disable Oracle Database Vault, unlock the account, and then reenable Oracle Database Vault. See Appendix B, "Enabling and Disabling Oracle Database Vault" for more information.

• Sometimes you need to 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.

• 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.

6.9 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 second 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 Database Control, which is installed by default with Oracle Database), Statspack, and TKPROF. For more information about Oracle Enterprise Manager, see the Oracle Enterprise Manager documentation set. For information about Database Control, refer to its online Help. Oracle Database Performance Tuning Guide describes the Statspack and TKPROF utilities.

6.10 Related Reports

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