5 Configuring Rule Sets

This chapter contains:

What Are Rule Sets?
Default Rule Sets
Creating a Rule Set
Configuring or Editing a Rule Set
Creating a Rule to Add to a Rule Set
Deleting a Rule Set
How Rule Sets Work
Tutorial: Creating an E-mail Alert for Security Violations
Guidelines for Designing Rule Sets
How Rule Sets Affect Performance
Related Reports and Data Dictionary Views

What Are Rule Sets?

A rule set is a collection of one or more rules that you can associate with a realm authorization, factor assignment, command rule, or secure application role. The rule set evaluates to true or false based on the evaluation of each rule it contains and the evaluation type (All True or Any True). A rule within a rule set is a PL/SQL expression that evaluates to true or false. You can create a rule and add the rule to multiple rule sets.

You can use rule sets to accomplish the following activities:

As a further restriction to realm authorization, to define the conditions under which realm authorization is active
To define when to allow a command rule
To enable a secure application role
To define when to assign the identity of a factor

When you create a rule set, Oracle Database Vault makes it available for selection when you configure the authorization for a realm, command rule, factor, or secure application role.

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

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

Default Rule Sets

By default, Oracle Database Vault provides the following selections for rule sets:

Allow Sessions: Controls the ability to create a session in the database. This rule set enables you to add rules to control database logins using the CONNECT command rule. The CONNECT command rule is useful to control or limit SYSDBA access to programs that require its use. This rule set is not populated.
Can Grant VPD Administration: Controls the ability to grant the GRANT EXECUTE or REVOKE EXECUTE privileges on the Oracle Virtual Private Database DBMS_RLS package, with the GRANT and REVOKE statements.
Can Maintain Accounts/Profiles: Controls the roles that manage user accounts and profiles, through the CREATE USER, DROP USER, CREATE PROFILE, ALTER PROFILE, or DROP PROFILE statements.
Can Maintain Own Account: Allows the accounts with the DV_ACCTMGR role to manage user accounts and profiles with the ALTER USER statement. Also allows individual accounts to change their own password using the ALTER USER statement.
Disabled: Convenience rule set to quickly disable security configurations like realms, command rules, factors, and secure application roles.
Enabled: Convenience rule set to quickly enable system features.

Creating a Rule Set

In general, to create a rule set, you first create the rule set itself, and then you edit the rule set to associate it with one or more rules. You can associate a new rule with the rule set, add existing rules to the rule set, or delete a rule association from the rule set.

Configuring or Editing a Rule Set

To configure or edit a rule set:

In the Oracle Database Vault Administration page, select Rule Sets.
In the Rule Set page, select the rule set that you want to edit.
Click Edit.
Modify the rule set as necessary, and then click OK.

Creating a Rule to Add to a Rule Set

After you create a new rule set, you can associate it with one or more rules. When you create a new rule, it is automatically added to the current rule set. You also can add existing rules to the rule set. Alternatively, you can omit adding rules to the rule set and use it as a template for rule sets you may want to create in the future.

The rule set evaluation depends on the evaluation of its rules using the Evaluation Options (All True or Any True). If a rule set is disabled, Oracle Database Vault evaluates the rule set to true without evaluating its rules.

See "How Rule Sets Work" for information on how rules are evaluated, how to nest rules, and how to create rules that exclude a particular user, such as a super system administrator.

Creating a New Rule

To create and add a rule to a rule set:

In the Oracle Database Vault Administration page, select Rule Sets.
In the Rule Sets page, select the rule set to which you want to create and add a rule, and then select Edit.
In the Edit Rule Set Page, scroll down to Rules Associated To The Rule Set and select Create.
In the Create Rule page, enter the following settings:
- Name: Enter a name for the rule. Use up to 90 characters in mixed-case.
- Rule Expression: Enter a PL/SQL expression that fits the following requirements:
  - It is valid in a SQL WHERE clause.
  - It can be a freestanding and valid PL/SQL Boolean expression such as the following:
```
TO_CHAR(SYSDATE,'HH24') = '12'
```
  - It must evaluate to a Boolean (TRUE or FALSE) value.
  - It must be no more than 255 characters long.
  - It can contain existing and compiled PL/SQL functions from the current database instance. Ensure that these are fully qualified functions (such as schema. function_name). Do not include complete SQL statements.
    
    If you want to use application package functions or standalone functions, you must grant the DVSYS account the GRANT EXECUTE privilege on the function. Doing so reduces the chances of errors when you add new rules.
  - Ensure that the rule works. You can test the syntax by running the following statement in SQL*Plus:
```
SELECT rule_expression FROM DUAL;
```
    For example, suppose you have created the following the rule expression:
```
SYS_CONTEXT('USERENV','SESSION_USER') != 'SQL*Plus'
```
    You could test this expression as follows:
```
SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL;
```
  See the following sections for functions that you can use in the rule set expression:
  For additional examples of expressions, see the rule defined in the rule sets provided with Oracle Database Vault. "Default Rule Sets" lists these rule sets.
Click OK.

The Edit Rule Set page appears. By default, the new rule is added to the rule set.

Editing a Rule

The changes you make to a rule apply to all rule sets that include the rule.

To edit a rule:

In the Edit Rule Set page, scroll to Rules Associated To The Rule Set.
Select the rule you want to edit and click Edit.
In the Edit Rule page, modify the rule as necessary.
Click OK.

Removing a Rule from a Rule Set

Before you remove a rule from a rule set, you can locate the various references to it by querying the rules-related Oracle Database Vault views. See "Oracle Database Vault Data Dictionary Views" for more information.

To remove a rule from a rule set:

In the Edit Rule Set page, scroll to Rules Associated To The Rule Set.
Select the rule you want to delete and click Remove.
In the Confirmation page, click Yes.

After you remove the rule from the rule set, it still exists. If you want, you can associate it with other rule sets. If you want to delete the rule, use the DVSYS.DBMS_MACADM.DELETE_RULE function, described in "Rule Set Procedures Within DVSYS.DBMS_MACADM". For example, to delete the rule Night Shift, log in to SQL*Plus as the Database Vault Owner and enter the following statement:

EXEC DVSYS.DBMS_MACADM.DELETE_RULE('Night Shift');

Adding Existing Rules to a Rule Set

To add existing rules to a rule set:

In the Rule Sets page, select the rule set that you want to add rules to, and then select Edit.
Under Rules Associated To The Rule Set, select Add Existing Rules.
In the Add Existing Rules page, select the rules you want, and then click Move (or Move All, if you want all of them) to move them to the Selected Rules list.

You can select multiple rules by holding down the Ctrl key as you click each rule.
Click OK.

Deleting a Rule Set

Before you delete a rule set, you can locate the various references to it by querying the rules-related Oracle Database Vault views. See "Oracle Database Vault Data Dictionary Views" for more information.

To delete a rule set:

If other Database Vault objects, such as command rules, reference the rule set, then remove the reference.

You can delete a rule set only if no other Database Vault objects are referencing it.
In the Oracle Database Vault Administration page, select Rule Sets.
In the Rule Set page, select the rule set that you want to remove.
Click Remove.
In the Confirmation page, click Yes.

The rule set is deleted. However, the rules associated with the rule set are not deleted.

How Rule Sets Work

This section describes how rule sets work in the following ways:

How Oracle Database Vault Evaluates Rules
Improving Performance by Setting the Order in Which Rules Appear in a Rule Set
Nesting Rules Within a Rule Set
Creating Rules to Apply to Everyone Except One User

How Oracle Database Vault Evaluates Rules

Oracle Database Vault evaluates the rules within a rule set as a collection of expressions. If you have set Evaluation Options to All True and if a rule fails the evaluation, then the evaluation stops at that point, instead of attempting to evaluate the rest of the rules in the rule set. Similarly, if Evaluation Options is set to Any True and if a rule evaluates to true, the evaluation stops at that point. If a rule set is disabled, Oracle Database Vault evaluates it to true without evaluating its rules.

Improving Performance by Setting the Order in Which Rules Appear in a Rule Set

Generally speaking, the order in which rules appear within a rule set does not affect the final outcome: the rule set either permits or prevents an action. However, the order can affect performance. You can place multiple rules within a single rule and prioritize them by using the AND or OR operator to improve the performance of the rule.

Nesting Rules Within a Rule Set

You can nest one or more rules within the rule set. For example, suppose you want to create a nested rule, Is Corporate Network During Maintenance, that performs the following two tasks:

It limits table modifications only when the database session originates within the corporate network.
It restricts table modifications during the system maintenance window scheduled between 10:00 p.m. and 10:59 p.m.

The rule definition would be as follows:

DVF.F$NETWORK = 'Corporate' AND TO_CHAR(SYSDATE,'HH24')  '22' AND '23'

You can create it using a factor function. See "Oracle Database Vault PL/SQL Factor Functions" for more information. Chapter 7 explains how to create factors.

Creating Rules to Apply to Everyone Except One User

You can also create rules to apply to everyone except one user, for example, the super system administrator. The rule definition for this type of rule can be as follows:

SYS_CONTEXT('USERENV','SESSION_USER') = 'SUPERADMIN_USER' OR additional_rule

If the current user is the super system administrator, then the system evaluates the rule to true without evaluating additional_rule. If the current user is not the super system administrator, then the evaluation of the rule depends on the evaluation of additional_rule.

Tutorial: Creating an E-mail Alert for Security Violations

In the following tutorial, you will create an e-mail alert that is triggered when a user attempts to alter a table outside a maintenance period. In your rule set, In your rule set, you will a PL/SQL procedure that sends an e-mail security alert if the rule is violated. To create the e-mail security alert, you need to use the UTL_SMTP PL/SQL package.

In this tutorial:

Step 1: Create an E-mail Security Alert PL/SQL Procedure
Step 2: Create an Oracle Database Vault Rule Set That Uses the E-mail Security Alert
Step 3: Test the E-mail Security Alert
Step 4: Remove the Components for This Tutorial

Step 1: Create an E-mail Security Alert PL/SQL Procedure

Log in to SQL*Plus as SYS using the SYSDBA privilege, and then grant EXECUTE permissions on the UTL_SMTP PL/SQL package to the Oracle Database Vault Owner(DV_OWNER) account.

For example:
```
sqlplus SYS/AS SYSDBA
Enter password: password

SQL> GRANT EXECUTE ON UTL_SMTP TO dbvowner;
```
Connect as the Oracle Database Owner (DV_OWNER) account.

For example:
```
CONNECT dbvowner
Enter password: password
```

Create the following procedure:

CREATE OR REPLACE PROCEDURE email_alert AS
DECLARE
  c utl_smtp.connection;
msg varchar2(20000) := 'Realm violation for the ALTER TABLE Command Security Policy. The time is: ';
  PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
  BEGIN
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
  END;
 
BEGIN
  msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
  c := utl_smtp.open_connection('smtp-server.example.com');
  utl_smtp.helo(c, 'example.com');
  utl_smtp.mail(c, 'sender@example.com');
  utl_smtp.rcpt(c, 'recipient@example.com');
  utl_smtp.open_data(c);
  send_header('From',    '"Sender" <sender@example.com>');
  send_header('To',      '"Recipient" <recipient@example.com>');
  send_header('Subject', 'Tables are being modified!');
  utl_smtp.write_data(c, utl_tcp.CRLF || msg);
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      utl_smtp.quit(c);
    EXCEPTION
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        NULL; -- When the SMTP server is down or unavailable, we don't have
              -- a connection to the server. The quit call will raise an
              -- exception that we can ignore.
END email_alert;

Replace the following values:

example.com: Replace with the domain of the local (sending) host.
sender@example.com: Replace with your e-mail address.
recipient@example.com: Replace with the e-mail address of your recipient.

Grant EXECUTE permissions on this procedure to DVSYS.
```
GRANT EXECUTE ON email_alert TO DVSYS; 
```

Step 2: Create an Oracle Database Vault Rule Set That Uses the E-mail Security Alert

As the DV_OWNER account, create a rule set similar to the following:

BEGIN
 DVSYS.DBMS_MACADM.CREATE_RULE_SET(
  rule_set_name    => 'ALTER TABLE Command Security Policy', 
  description      => 'Allows table updates only during maintenance, 
  enabled          => 'y', 
  eval_options     => 1, 
  audit_options    => POWER(2,0), 
  fail_options     => 2, 
  fail_message     => '', 
  fail_code        => NULL, 
  handler_options  => POWER(2,0), 
  handler          => 'dbvowner.email_alert');
END;
/

For the handler setting, replace dbvowner with your DV_OWNER account name.

Create a rule similar to the following.

For now, create the rule during the time you will test it. For example, if you will test it between 2 p.m. and 3 p.m., create the rule as follows:
```
BEGIN
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name  => 'Restrict Access to Maintenance Period', 
  rule_expr  => 'TO_CHAR(SYSDATE,''HH24'') BETWEEN ''14'' AND ''15''');
END;
/
```
Ensure that you use two single quotation marks instead of double quotation marks for HH24, 14, and 15. You can double-check the system time on your computer by issuing the following SQL statement:
```
SQL> SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;

TO
--
14
```
Later on, when you are satisfied that the rule works, you can update for a time when your site typically performs maintenance work, for example, between 7 p.m. and 10 p.m, as follows:
```
BEGIN
 DVSYS.DBMS_MACADM.UPDATE_RULE(
  rule_name  => 'Restrict Access to Maintenance Period', 
  rule_expr  => 'TO_CHAR(SYSDATE,''HH24'') BETWEEN ''19'' AND ''22''');
END;
/
```

Now add the rule to the rule set:

BEGIN
 DBMS_MACADM.ADD_RULE_TO_RULE_SET(
  rule_set_name => 'ALTER TABLE Command Security Policy', 
  rule_name     => 'Restrict Access to Maintenance Period'); 
END;
/

Create the following command rule:

BEGIN
 DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
   command         => 'ALTER TABLE', 
   rule_set_name   => 'ALTER TABLE Command Security Policy', 
   object_owner    => 'SCOTT', 
   object_name     => '%', 
   enabled         => 'Y');
 END; 
/

Commit these updates to the database.
```
SQL> COMMIT;
```

Step 3: Test the E-mail Security Alert

Log on to SQL*Plus as a regular user, SCOTT.

For example:
```
sqlplus scott
Enter password: password
```
If the SCOTT account is locked and expired, a system administrator (for example, SYS or SYSTEM) can unlock this account and create a new password as follows:
```
ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY password;
```

As the user SCOTT, create a test table.

SQL> CREATE TABLE my_test (col1 varchar2);

Change the system time on your computer to a time when the ALTER TABLE Command Security Policy rule set takes place, for example, between 2 p.m. and 3 p.m. In a shell window, enter the following:
```
$ su root
Password: password

$ date 12131409
```
As user SCOTT, try altering the my_test table.
```
SQL> ALTER TABLE mytest ADD (col2 number);

Table altered.
```
SCOTT should be able to alter the mytest table during this time.
Reset the system time to a time outside the Restrict Access to Maintenance Period time.

As user SCOTT, try altering the my_test table again.

SQL> ALTER TABLE mytest ADD (col3 number);

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

SCOTT cannot alter this table, even though he owns it.

Reset the system time to the correct time.

Step 4: Remove the Components for This Tutorial

Connect to SQL*Plus as the Oracle Database Owner (DV_OWNER) account. For example:
```
CONNECT dbvowner
Enter password: password
```

Enter the following commands in the order shown to delete the rule set components.

SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE_FROM_RULE_SET('ALTER TABLE Command Security Policy', 'Restrict Access to Maintenance Period');
 
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE('Restrict Access to Maintenance Period');
 
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE('ALTER TABLE', 'SCOTT', '%');
 
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE_SET('ALTER TABLE Command Security Policy');

Drop the email_alert PL/SQL procedure.
```
DROP PROCEDURE email_alert;
```
Connect as SYS using the SYSDBA privilege and then revoke the EXECUTE privilege on the UTL_SMTP PL/SQL package from the Oracle Database Vault Owner account.
```
SQL> CONNECT SYS/AS SYSDBA
Enter password: password

SQL> REVOKE EXECUTE ON UTL_SMTP FROM dbvowner;
```

Guidelines for Designing Rule Sets

Follow these guidelines for designing rule sets:

You can share rules among multiple rule sets. This lets you develop a library of reusable rule expressions. Oracle recommends that you design such rules to be discrete, single-purpose expressions.
Leverage Oracle Database Vault factors in your rule expressions to provide reusability and trust in the values used by your rule expressions. Factors can provide contextual information to use in your rules expressions.
You can use custom event handlers to extend Oracle Database Vault security policies to integrate external systems for error handling or alerting. Using Oracle utility packages such as UTL_TCP, UTL_HTTP, UTL_SMTP, or DBMS_AQ can help you to achieve this type of integration.
Test rule sets thoroughly for various accounts and scenarios either on a test database or on a test realm or command rule for nonsensitive data before you apply them to realms and command rules that protect sensitive data. You can test rule expressions directly with the following SQL statement:
```
SQL> SELECT SYSDATE from DUAL where rule expression
```
You can nest rule expressions inside a single rule. This helps to achieve more complex situations where you would need a logical AND for a subset of rules and a logical OR with the rest of the rules. See the definition for the Is Corporate Network During Maintenance rule set under "Tutorial: Creating an E-mail Alert for Security Violations" for an example.

How Rule Sets Affect Performance

In general, the more rules and more complex the rules, the more performance overhead the performance for execution of certain operations governed by these rule sets. For example, if you have a very large number of rules in a rule set governing a SELECT statement, performance could degrade significantly.

If you have rule sets that require many rules, performance improves if you move all the rules to logic defined in a single PL/SQL standalone or package function.

However, if a rule is used by other rule sets, there is little performance effect on your system.

You can check 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 Views

Table 5-1 lists Oracle Database Vault reports that are useful for analyzing rule sets and the rules within them. See Chapter 16, "Oracle Database Vault Reports" for information about how to run these reports.

Table 5-1 Reports Related to Rule Sets

Report	Description
"Rule Set Configuration Issues Report"	Lists rule sets that have no rules defined or enabled
"Secure Application Configuration Issues Report"	Lists secure application roles that have incomplete or disabled rule sets
"Command Rule Configuration Issues Report"	Lists rule sets that are incomplete or disabled

Table 5-2 lists data dictionary views that provide information about existing rules and rule sets.

Table 5-2 Data Dictionary Views Used for Rules and Rule Sets

Data Dictionary View	Description
"DBA_DV_RULE View"	Lists the rules that have been defined
"DBA_DV_RULE_SET View"	Lists the rules sets that have been created
"DBA_DV_RULE_SET_RULE View"	Lists rules that are associated with existing rule sets