You can create rules during the rule set creation process, or independently of it.

After you create the rule, you can associate a rule set with one or more additional rules.

If you create a new rule during the rule set creation process, the rule 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.

You can add as many rules that you want to a rule set, but for better design and performance, you should keep the rule sets simple. See Guidelines for Designing Rule Sets for additional advice.

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.

Default rules are rules that have commonly used behavior, such as checking if an action evaluates to true or false.

You can create a new rule or use the default Oracle Database Vault rules.

1. Connect to the PDB or the application root as a user who has been granted the DV_OWNER or DV_ADMIN role.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

2. Execute the DBMS_MACADM.CREATE_RULE statement to create the rule.
   For example:

   BEGIN
    DBMS_MACADM.CREATE_RULE(
     rule_name  => 'Is SYSADM Administrator', 
     rule_expr  =>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SYSADM''',
     scope      => DBMS_MACUTL.G_SCOPE_LOCAL);
   END;
   /

   In this specification:

   • rule_name up to 90 characters in mixed-case. Spaces are allowed. The DBA_DV_RULE data dictionary view lists existing rules. The DBA_DV_RULE_SET_RULE lists rule sets that are associated with rules. Oracle suggests that you start the name with a verb and complete the name with the purpose of the rule. For example: Prevent non-admin access to SQL*Plus. Because rules do not have a description parameter, make the name explicit but be sure to not exceed over 90 characters.
   • rule_expr is a PL/SQL Boolean expression. If the expression contains quotation marks, do not use double quotation marks. Instead, use two single quotation marks. Enclose the entire expression within single quotation marks. For example:

     'TO_CHAR(SYSDATE,''HH24'') = ''12'''

     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 1024 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 any other form of SQL statements.

       Be aware that you cannot use invoker's rights procedures with rule expressions. Doing so will cause the rule evaluation to fail unexpectedly. Only use definer's rights procedures with rule expressions.

       If you want to use application package functions or standalone functions, you must grant the DVSYS account the 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') != 'TSMITH'
       

       You could test this expression as follows:

       SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL;
       

       For the Boolean example listed earlier, you would enter the following:

       SELECT TO_CHAR(SYSDATE,'HH24')FROM DUAL;
       

   • scope defines whether the rule is created in a PDB (DBMS_MACUTL.G_SCOPE_LOCAL) or in an application root (DBMS_MACUTL.G_SCOPE_COMMON).

After you have created one or more rules, you can add them to a rule set.

1. Connect to the PDB or the application root as a user who has been granted the DV_OWNER or DV_ADMIN role.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

2. Query the DBA_DV_RULE data dictionary view to find the rule to add to a rule set.

   SELECT NAME FROM DBA_DV_RULE 
   ORDER BY NAME;

3. Query the DBA_DV_RULE_SET data dictionary view to find the rule set to which you want to add the rule.

   SELECT RULE_SET_NAME 
   FROM DBA_DV_RULE_SET 
   ORDER BY RULE_SET_NAME;

   You can also query the DBA_DV_RULE_SET_RULE data dictionary view to find if the rule has already been associated with a rule set.

4. Execute the DBMS_MACADM.ADD_RULE_TO_RULE_SET to add the rule to a rule set.
   For example:

   BEGIN
    DBMS_MACADM.ADD_RULE_TO_RULE_SET(
     rule_set_name => 'Limit_DBA_Access', 
     rule_name     => 'Is SYSADM Administrator',
     rule_order    => 1,
     enabled       => DBMS_MACUTL.G_NO,
     scope         => );
   END;
   /

   In this specification:

   • rule_order does not apply to this release, but you must include a value for the ADD_RULE_TO_RULE_SET procedure to work. Enter 1.
   • enabled determines whether the rule should be checked when the rule set is evaluated. DBMS_MACUTL.G_YES (default). Enables the rule to be checked during the rule set evaluation. DBMS_MACUTL.G_NO Prevents the rule from being checked during the rule set evaluation.
   • scope defines whether the rule is created in a PDB (DBMS_MACUTL.G_SCOPE_LOCAL) or in an application root (DBMS_MACUTL.G_SCOPE_COMMON).

You can use the DBMS_MACADM.UPDATE_RULE procedure to modify the definition of a rule.

1. Connect to the PDB or the application root as a user who has been granted the DV_OWNER or DV_ADMIN role.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

2. Find the rule and check its definition.
   For example:

   SELECT * FROM DBA_DV_RULE ORDER BY NAME; 

3. Execute the DBMS_MACADM.UPDATE_RULE statement.
   For example:

   BEGIN
    DBMS_MACADM.UPDATE_RULE(
     rule_name  => 'Check UPDATE operations', 
     rule_expr  =>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SYSADM'' AND
                  (
                    UPPER(SYS_CONTEXT(''USERENV'',''MODULE'')) LIKE ''APPSRVR%'' OR
                    UPPER(SYS_CONTEXT(''USERENV'',''MODULE'')) LIKE ''DBAPP%'' )'
                  );
   END;
   /

Before you remove a rule from a rule set, you must remove references to it from rule sets.

1. Connect to the PDB or the application root as a user who has been granted the DV_OWNER or DV_ADMIN role.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

2. Query the DBA_DV_RULE data dictionary view to find the rule that you want to remove from a rule set.

   SELECT NAME FROM DBA_DV_RULE 
   ORDER BY NAME;

3. Query the DBA_DV_RULE_SET_RULE data dictionary views to find rule sets that are associated with the rule.
   For example:

   SELECT RULE_SET_NAME 
   FROM DBA_DV_RULE_SET_RULE 
   WHERE RULE_NAME = 'Is SYSADM Administrator';

4. Execute the DBMS_MACADM.DELETE_RULE_FROM_RULE_SET procedure to remove the rule from the rule set.
   For example:

   BEGIN
    DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
     rule_set_name => 'Limit_DBA_Access', 
     rule_name     => 'Is SYSADM Administrator');
   END;
   /

EXEC DBMS_MACADM.DELETE_RULE('Is SYSADM Administrator');

Oracle Database Vault evaluates the rules within a rule set as a collection of expressions.

If you have set the eval_options parameter in the DBMS_MACADM.CREATE_RULE_SET or DBMS_MACADM.UPDATE_RULE_SET procedure to DBMS_MACUTL.G_RULESET_EVAL_ALL and if a rule evaluates to false, then the evaluation stops at that point, instead of attempting to evaluate the rest of the rules in the rule set. Similarly, if eval_options is set to DBMS_MACUTL.G_RULESET_EVAL_ANY and if a rule evaluates to true, the evaluation stops at that point. If a rule set is disabled, then Oracle Database Vault evaluates it to true without evaluating its rules.

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') between '22' AND '23'

You can also create rules to apply to everyone except one user (for example, a privileged user).

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

In this tutorial, you configure a rule set that defines two-person integrity (TPI).

This feature is also called dual key security, dual key connection, and two-man rule security. In this type of security, two users are required to authorize an action instead of one user.

The idea is that one user provides a safety check for the other user before that user can proceed with a task. Two-person integrity provides an additional layer of security for actions that potentially can be dangerous. This type of scenario is often used for tasks such as database patch updates, which is what this tutorial will demonstrate. One user, patch_user must log in to perform a database patch upgrade, but the only way that he can do this is if his manager, patch_boss is already logged in. You will create a function, rules, a rule set, and a command rule to control patch_user's ability to log in.

You must create two users for this tutorial, patch_boss and patch_user.

1. Log in to a PDB as a user who has been granted the DV_ACCTMGR role.

   For example:

   sqlplus bea_dvacctmgr@pdb_name
   Enter password: password
   

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

2. Create the following users and grant them the CREATE SESSION privilege.

   GRANT CREATE SESSION TO patch_boss IDENTIFIED BY password;
   GRANT CREATE SESSION TO patch_user IDENTIFIED BY password;
   

   Replace password with a password that is secure.

3. Connect as user SYS with the SYSDBA administrative privilege.

   CONNECT SYS@pdb_name AS SYSDBA
   Enter password: password
   

4. Grant the following privileges to the DV_OWNER or DV_ADMIN user.

   For example:

   GRANT CREATE PROCEDURE TO leo_dvowner;
   GRANT SELECT ON V_$SESSION TO leo_dvowner;
   

   The V_$SESSION table is the underlying table for the V$SESSION dynamic view.

The behavior of the Database Vault settings will be determined by the function.

1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.

   For example:

   CONNECT leo_dvowner@pdb_name
   Enter password: password
   

2. Create the check_boss_logged_in function as follows:

   CREATE OR REPLACE FUNCTION check_boss_logged_in
   return varchar2
   authid definer as 
    
   v_session_number number := 0;
   v_allow varchar2(10)    := 'TRUE';
   v_deny varchar2(10)     := 'FALSE';
    
   BEGIN
     SELECT COUNT(*) INTO v_session_number
     FROM SYS.V_$SESSION
     WHERE USERNAME = 'PATCH_BOSS'; -- Enter the user name in capital letters.
    
    IF v_session_number > 0
     THEN RETURN v_allow;
    ELSE
     RETURN v_deny;
    END IF;
   END check_boss_logged_in;
   /
   

3. Grant the EXECUTE privilege on the check_boss_logged_in function to the DVSYS schema.

   GRANT EXECUTE ON check_boss_logged_in to DVSYS;

Next, you must create two rules, a rule set to which you will add them, and a command rule.

1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.

   For example:

   CONNECT leo_dvowner@pdb_name
   Enter password: password
   

2. Create the Check if Boss Is Logged In rule, which checks that the patch_user user is logged in to the database. In the definition, replace leo_dvowner with the name of the DVOWNER or DV_ADMIN user who created the check_boss_logged_in function.

   If the check_boss_logged_in function returns TRUE (that is, patch_boss is logged in to another session), then patch_user can log in.

   BEGIN
     DBMS_MACADM.CREATE_RULE(
      rule_name => 'Check if Boss Is Logged In',
      rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''PATCH_USER'' and leo_dvowner.check_boss_logged_in =  ''TRUE'' ');
   END;
   /
   

   Enter the user name, PATCH_USER, in upper-case letters, which is how the SESSION_USER parameter stores it.

3. Create the Allow Connect for Other Database Users rule, which ensures that the user logged in (patch_user) is not user patch_boss. It also enables all other valid users to log in.

   BEGIN
    DBMS_MACADM.CREATE_RULE(
     rule_name => 'Allow Connect for Other Database Users',
     rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''PATCH_USER''');
   END;
   /
   COMMIT;
   

4. Create the Dual Connect for Boss and Patch rule set, and then add the two rules to it.

   BEGIN
       DBMS_MACADM.CREATE_RULE_SET(
        rule_set_name     => 'Dual Connect for Boss and Patch',
        description       => 'Checks if both boss and patch users are logged in.',
        enabled           => DBMS_MACUTL.G_YES,
        eval_options      => 2,
        audit_options     => DBMS_MACUTL.G_RULESET_AUDIT_OFF,
        fail_options      => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
        fail_message      =>'',
        fail_code         => NULL,
        handler_options   => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
        handler           => ''
        );
   END;
   /
   
   BEGIN
       DBMS_MACADM.ADD_RULE_TO_RULE_SET(
        rule_set_name     => 'Dual Connect for Boss and Patch',
        rule_name         => 'Check if Boss Is Logged In'
      );
   END;
   /
   
   BEGIN
       DBMS_MACADM.ADD_RULE_TO_RULE_SET(
        rule_set_name     => 'Dual Connect for Boss and Patch',
        rule_name         => 'Allow Connect for Other Database Users'
      );
   END;
   /
   

5. Create the following CONNECT command rule, which permits user patch_user to connect to the database only if patch_boss is already logged in.

   BEGIN
      DBMS_MACADM.CREATE_COMMAND_RULE(
       command            => 'CONNECT',
       rule_set_name      => 'Dual Connect for Boss and Patch',
       object_owner       => '%',
       object_name        => '%',
       enabled            => DBMS_MACUTL.G_YES);
   END;
   /
   COMMIT;

After the rules have been created, they are ready to be tested.

1. Exit SQL*Plus.

   EXIT
   

2. Create a second shell, for example:

   xterm &
   

3. In the first shell, try to log in as user patch_user.

   sqlplus patch_user@pdb_name
   Enter password: password
   
   ERROR:
   ORA-47400: Command Rule violation for CONNECT on LOGON
   
   Enter user-name:
   

   User patch_user cannot log in until user patch_boss is already logged in. (Do not try the Enter user-name prompt yet.)

4. In the second shell and then log in as user patch_boss.

   sqlplus patch_boss@pdb_name
   Enter password: password
   Connected. 
   

   User patch_boss can log in.

5. Go back to the first shell, and then try logging in as user patch_user again.

   Enter user_name: patch_user
   Enter password: password
   

   This time, user patch_user is deemed a valid user, so now he can log in.

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

1. In the session for the user patch_boss, exit SQL*Plus and then close the shell.

   EXIT
   

2. In the first shell, connect the DV_ACCTMGR user and remove the users you created.

   CONNECT bea_dvacctmgr@pdb_name
   Enter password: password
   
   DROP USER patch_boss;
   DROP USER patch_user;
   

3. Connect as a user SYS with the SYSDBA administrative privilege and revoke the privileges that you had granted to the DV_OWNER or DV_ADMIN user.

   CONNECT SYS@pdb_name AS SYSDBA
   Enter password: password
   
   REVOKE CREATE PROCEDURE FROM leo_dvowner;
   REVOKE SELECT ON V_$SESSION FROM leo_dvowner;
   

4. Connect as the DV_OWNER or DV_ADMIN user and drop the rules, rule set, and command rule, in the order shown.

   CONNECT leo_dvowner@pdb_name
   Enter password: password
   
   DROP FUNCTION check_boss_logged_in;
   EXEC DBMS_MACADM.DELETE_COMMAND_RULE('CONNECT', '%', '%');
   EXEC DBMS_MACADM.DELETE_RULE_FROM_RULE_SET('Dual Connect for Boss and Patch', 'Check if Boss Is Logged In');
   EXEC DBMS_MACADM.DELETE_RULE_FROM_RULE_SET('Dual Connect for Boss and Patch', 'Allow Connect for Other Database Users');
   EXEC DBMS_MACADM.DELETE_RULE('Check if Boss Is Logged In');
   EXEC DBMS_MACADM.DELETE_RULE('Allow Connect for Other Database Users');
   EXEC DBMS_MACADM.DELETE_RULE_SET('Dual Connect for Boss and Patch');
   COMMIT;