Skip Headers
Oracle® Database Vault Administrator's Guide
10g Release 2 (10.2)

Part Number B25166-23
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Configuring Command Rules

This chapter contains:

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 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 "Defining Realm Authorization".

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

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

Command rules can be categorized as follows:

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 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 PL/SQL interfaces and packages provided by Oracle Database Vault, refer to the following chapters:

Default Command Rules

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

Table 6-1 Default Command Rules

SQL Statement Object Name Rule Set Name

ALTER PROFILE

-

Can Maintain Accounts/Profiles

ALTER SYSTEM

-

Allow System Parameters

ALTER USER

-

Can Maintain Own Account

CREATE PROFILE

-

Can Maintain Accounts/Profiles

CREATE USER

-

Can Maintain Accounts/Profiles

DROP PROFILE

-

Can Maintain Accounts/Profiles

DROP USER

-

Can Maintain Accounts/Profiles

GRANT

SYS.DBMS_RLS package

Can Grant VPD Administration

REVOKE

SYS.DBMS_RLS package

Can Grant VPD Administration


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

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.

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.

SQL Statements That Can Be Protected by Command Rules

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

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

Creating and Editing a Command Rule

Follow these steps:

  1. Log in to Oracle Database Vault Administrator as a user who has been granted the DV_OWNER or DV_ADMIN role.

    "Starting Oracle Database Vault" 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

Enter the following settings:

Applicability

Enter the following settings:

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 such rule violations by using the Command Rule Configuration Issues Report, discussed in Chapter 17.) 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.

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 Data Dictionary 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.

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:

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

  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 rule is disabled, then the command rule is not allowed to run regardless of its associated rule set. That is, the command rule itself is disabled. However, if you disable a rule set of a command rule, then the rule set is not checked. In that case, the command rule is allowed to run without any condition. If the command rule 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 rule itself.)

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

This section contains:

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

About This Tutorial

In this tutorial, you create a simple command rule that disables and then enables the ability of user SCOTT to create tables.

Step 1: Connect as User SCOTT and Create a Table

  1. Log in to SQL*Plus as user SCOTT.

    sqlplus scott
    Enter password: password
    

    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 amalcolm_dvacctmgr
    Enter password: password
    
    ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;
    

    Replace password with a password that is secure.

    CONNECT SCOTT
    Enter password: password
    
  2. As user SCOTT, create a table.

    CREATE TABLE t1 (num NUMBER);
    
  3. 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 Role and Create a Command Rule

  1. Log in to Oracle Database Vault Administrator as a user who has been granted the DV_OWNER or DV_ADMIN role.

    "Starting Oracle Database Vault" explains how to log in.

  2. In the Oracle Database Vault Administrator Administration page, click Command Rules.

    The Command Rules page appears.

  3. Click Create.

    The Create Command Rule page appears.

  4. Enter the following settings:

    • Command list: Select CREATE TABLE

    • Status: Set to Enabled so that the command rule is active.

    • Object Owner: Select SCOTT.

    • Object Name: Set to % so that it applies to all objects in the SCOTT schema.

    • Rule Set: Select Disabled so that user SCOTT is prevented from creating tables.

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

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

    CONNECT SCOTT
    Enter password: password
    
  2. Try to create a table.

    CREATE TABLE t1 (num NUMBER);
    

    The following output should appear:

    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-47400: Command Rule violation for create table on SCOTT.T1
    ORA-06512: at "DVSYS.AUTHORIZE_EVENT", line 55
    ORA-06512: at line 31
    

    As you can see, SCOTT is no longer allowed to create tables, even in his own schema.

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

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

  1. In Oracle Database Vault Administrator, remove the CREATE TABLE command rule as follows:

    • Return to the Command Rules page.

    • Select the CREATE TABLE command rule and then click Remove.

    • In the Confirmation page, click Yes.

  2. Log in to SQL*Plus as user SCOTT and remove the t1 table.

    SQL> DROP TABLE t1;
    
  3. 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 amalcolm_dvacctmgr
    Enter password: password
    
    ALTER USER SCOTT ACCOUNT LOCK PASSWORD EXPIRE;
    

Guidelines for Designing Command Rules

Follow these guidelines to design command rules:

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

Related Reports and Data Dictionary View

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

Table 6-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 DBA_DV_COMMAND_RULE data dictionary view to find the SQL statements that are protected by command rules. See "DBA_DV_COMMAND_RULE View" for more information.