5 Configuring Rule Sets

5.5.1 Creating a New Rule

To create and add a rule to a rule set:

1. In the Oracle Database Vault Administration page, select Rule Sets.

2. In the Rule Sets page, select the rule set to which you want to create and add a rule, and then select Edit.

3. In the Edit Rule Set Page, scroll down to Rules Associated To The Rule Set and select Create.

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

     • "Oracle Database Vault PL/SQL Rule Functions"

     • Chapter 11, "Using the DVSYS.DBMS_MACADM Package"

     • Chapter 13, "Using the DVSYS.DBMS_MACUTL Package"

     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.

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

1. In the Edit Rule Set page, scroll to Rules Associated To The Rule Set.

2. Select the rule you want to edit and click Edit.

3. In the Edit Rule page, modify the rule as necessary.

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

1. In the Edit Rule Set page, scroll to Rules Associated To The Rule Set.

2. Select the rule you want to delete and click Remove.

3. 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');

5.5.2 Adding Existing Rules to a Rule Set

To add existing rules to a rule set:

1. In the Rule Sets page, select the rule set that you want to add rules to, and then select Edit.

2. Under Rules Associated To The Rule Set, select Add Existing Rules.

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

4. Click OK.

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

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

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

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

5.8.1 Step 1: Install and Configure the UTL_MAIL PL/SQL Package

1. Log on to SQL*Plus as SYS using the SYSDBA privilege, and then install the UTL_MAIL package.

   sqlplus "sys/as sysdba"
   Enter password: password
   
   SQL> @?/rdbms/admin/utlmail.sql
   SQL> @?/rdbms/admin/prvtmail.plb
   

   The UTL_MAIL package enables you to manage e-mail. See Oracle Database PL/SQL Packages and Types Reference for more information about UTL_MAIL.

2. Check the current value of the SMTP_OUT_SERVER parameter, and make a note of this value so that you can restore it when you complete this tutorial.

   For example:

   SQL> SHOW PARAMETER SMTP_OUT_SERVER
   
   NAME                    TYPE              VALUE
   ----------------------- ----------------- ----------------------------------
   SMTP_OUT_SERVER         string            some_value.my_company.com
   

3. Issue the following ALTER SYSTEM statement:

   SQL> ALTER SYSTEM SET SMTP_OUT_SERVER="imap_mail_server.my_company.com";
   

   Replace imap_mail_server with the name of your SMTP server, which you can find in the account settings in your e-mail tool. Enclose these settings in quotation marks. For example:

   ALTER SYSTEM SET SMTP_OUT_SERVER="my_imap_mail_server.my_company.com"
   

4. Connect as SYS using the SYSOPER privilege and then restart the database.

   SQL> CONNECT SYS/AS SYSOPER
   Enter password: password
   
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP
   

5. Ensure that the SMTP_OUT_SERVER parameter setting is correct.

   SQL> CONNECT SYS/AS SYSDBA
   Enter password: password
   
   SQL> SHOW PARAMETER SMTP_OUT_SERVER
   
   NAME                    TYPE              VALUE
   ----------------------- ----------------- ----------------------------------
   SMTP_OUT_SERVER         string            my_imap_mail_server.my_company.com
   

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

1. Ensure that you are connected as SYS using the SYSDBA privilege, and then grant the following privileges to the Database Vault Owner account.

   For example:

   SQL> CONNECT SYS/AS SYSDBA
   Enter password: password
   
   SQL> GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO dbvowner;
   SQL> GRANT EXECUTE ON UTL_TCP TO dbvowner;
   SQL> GRANT EXECUTE ON UTL_SMTP TO dbvowner;
   SQL> GRANT EXECUTE ON UTL_MAIL TO dbvowner;
   

   The UTL_TCP, UTL_SMTP, and UTL_MAIL PL/SQL packages are used by the e-mail security alert that you will create.

2. Connect to SQL*Plus as the Oracle Database Vault Owner account.

   For example:

   SQL> CONNECT dbvowner
   Enter password: password
   

3. Create the following procedure:

   CREATE OR REPLACE PROCEDURE email_alert AS
   msg varchar2(20000) := 'Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: '; 
   BEGIN
     msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS'); 
   UTL_MAIL.SEND (
       sender      => 'youremail@yourcompany.com',
       recipients  => 'recipientemail@company.com',
       subject     => 'Table modification attempted outside maintenance!',
       message     => msg); 
   END email_alert;
   /
   

   Replace youremail@yourcompany.com with your e-mail address, and recipientemail@company.com with the e-mail address of the person you want to receive the notification.

4. Grant the EXECUTE permission on this procedure to DVSYS.

   SQL> GRANT EXECUTE ON email_alert TO DVSYS; 
   

5. Test the settings that you have created so far.

   SQL> EXEC EMAIL_ALERT;
   SQL> COMMIT;
   

   SQL*Plus should display a PL/SQL procedure successfully completed message, and in a moment, depending on the speed of your mail server, you should receive the e-mail alert.

5.8.3 Step 3: Create a Rule Set and a Command Rule to Use the E-mail Security Alert

1. As the Oracle Database Vault Owner, create the following rule set:

   BEGIN
    DVSYS.DBMS_MACADM.CREATE_RULE_SET(
     rule_set_name    => 'ALTER TABLE Command Security Policy', 
     description      => 'This rule set allows ALTER TABLE only during the 
                          maintenance period.', 
     enabled          => 'Y',
     eval_options     => 1,
     audit_options    => POWER(2,0),
     fail_options     => 2,
     fail_message     => '',
     fail_code        => NULL,
     handler_options  => POWER(2,0), 
     handler          => 'dbavowner.email_alert');
    END;
   /
   

2. Create a rule similar to the following.

   For now, set the rule expression to be during the time you will test it. For example, if you want to 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 it to 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;
   /
   

3. Add the Restrict Access to Maintenance Period rule to the ALTER TABLE Command Security Policy 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;
    /
   

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

5. Commit these updates to the database.

   SQL> COMMIT;
   

5.8.4 Step 4: Test the E-mail Security Alert

1. Connect to SQL*Plus as user SCOTT.

   For example:

   CONNECT SCOTT
   Enter password: password
   

   If the SCOTT account is locked and expired, then a user with the DV_ACCTMGR role can unlock this account and create a new password as follows:

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

2. As the user SCOTT, create a test table.

   SQL> CREATE TABLE mytest (col1 number);
   

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

   UNIX systems: Log in as root and use the date command to set the time. For example, assuming the date today is December 13, 2007, you would enter the following:

   su root
   Password: password
   
   date 12131409
   

   Windows: Double-click the clock icon, which is typically at the lower right corner of the screen. In the Date and Time Properties window, set the time to 2 p.m., and then click OK.

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

5. Connect as the Oracle Database Vault Owner account.

   SQL> CONNECT dbvowner
   Enter password: password
   

6. Reset the system time to a time outside the Restrict Access to Maintenance Period time.

7. Connect as SCOTT and try altering the my_test table again.

   SQL> CONNECT SCOTT
   Enter password: password
   
   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 the mytest table. In a moment, you should receive an e-mail with the subject header Table modification attempted outside maintenance! and with a message similar to the following:

   Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: Wednesday 28 NOV, 2007 14:24:25
   

8. Reset the system time to the correct time.

5.8.5 Step 5: Remove the Components for This Tutorial

1. Connect to SQL*Plus as the Oracle Database Vault Owner account.

   For example:

   SQL> CONNECT dbvowner
   Enter password: password
   

2. 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');
   

3. Drop the email_alert PL/SQL procedure.

   SQL> DROP PROCEDURE email_alert;
   

4. Connect as user SCOTT and remove the mytest table.

   SQL> CONNECT SCOTT
   Enter password: password
   
   SQL> DROP TABLE mytest;
   

5. Connect as SYS using the SYSDBA privilege and then revoke the EXECUTE privilege on the UTL_TCP, UTL_SMTP, and UTL_MAIL PL/SQL packages from the Oracle Database Vault Owner account.

   For example:

   SQL> REVOKE EXECUTE ON UTL_TCP FROM dbvowner;
   SQL> REVOKE EXECUTE ON UTL_SMTP FROM dbvowner;
   SQL> REVOKE EXECUTE ON UTL_MAIL FROM dbvowner;
   

6. Set the SMTP_OUT_SERVER parameter to its original value.

   For example:

   SQL> ALTER SYSTEM SET SMTP_OUT_SERVER="some_value.my_company.com";
   

7. Restart the database. For example:

   SQL> CONNECT SYS/AS SYSOPER
   Enter password: password
   
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP