5 Configuring Rule Sets

Rule sets group one or more rules together; the rules determine whether a user can perform an action on an object.

5.1 What Are Rule Sets?

A rule set is a collection of one or more rules.

You can associate the rule set 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.

5.2 Rule Sets and Rules in a Multitenant Environment

You can create a rule set and its associated rules in a PDB or an application root.

A common realm must use a common rule set when the associated realm or command rule is evaluated by Database Vault. The common rule set and its rules can only be created in the application root. After the common rule set is created, it exists in every container that is associated with the root where the common rule set is created. The common rule set can only include common rules.

To configure a common rule set and its rules, you must be commonly granted the DV_OWNER or DV_ADMIN role.

5.3 Default Rules and Rule Sets from Releases Earlier Than Release 12.2

Many default rules and rule sets from earlier releases are no longer supported, but may be in use in your current Oracle Database installation.

If you use default rules and rule sets from releases earlier than Oracle Database release 12.2, Oracle Database does not remove them during an upgrade in case you have customized them for your own use. If you customized these rules and rule sets, or use these older default rule sets, Oracle recommends that you re-implement the customized rules and rule sets by using the ALTER SYSTEM and ALTER SESSION command rules, and then disable and drop the old rules and rule sets. If you have not customized these rules and rule sets, or otherwise use them, you should drop these earlier rules and rule sets because the same functionality is available in later default command rules.

Note:

See the release 12.2 version of Oracle Database Vault Administrator’s Guide for a full listing of the rules and rule sets that may be affected.

5.4 Default Rule Sets

Oracle Database Vault provides a set of default rule sets that you can customize for your needs.

You can find a full list of rule sets by querying the DBA_DV_RULE_SET data dictionary view. To find rules that are associated with a rule set, query the DBA_DV_RULE_SET_RULE data dictionary view.

The default rule sets are as follows:

  • Allow Dumping Datafile Header prevents the dumping of data blocks.

  • Allow Fine Grained Control for Alter System enables you to control the ability of users to set initialization parameters using the ALTER SYSTEM SQL statement.

  • 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/Profiled 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. See DV_ACCTMGR Database Vault Account Manager Role for more information about the DV_ACCTMGR role.

  • Disabled is a convenience rule set to quickly disable security configurations like realms, command rules, factors, and secure application roles.

  • Enabled is a convenience rule set to quickly enable system features.

  • Not allow to set AUDIT_SYS_OPERATIONS to False prevents the AUDIT_SYS_OPERATIONS initialization parameter from being set to FALSE. If unified auditing is enabled, then the AUDIT_SYS_OPERATIONS parameter has no effect.

  • Not allow to set OPTIMIZER_SECURE_VIEW_MERGING to True prevents the OPTIMIZER_SECURE_VIEW_MERGING initialization parameter from being set to TRUE.

  • Not allow to set OS_ROLES to True prevents the OS_ROLES initialization parameter from being set to TRUE.

  • Not allow to set PLSQL_DEBUG to True prevents the PLSQL_DEBUG initialization parameter from being set to TRUE.

  • Not allow to set REMOTE_OS_ROLES to True prevents the REMOTE_OS_ROLES initialization parameter from being set to TRUE.

  • Not allow to set SQL92_SECURITY to False prevents the SQL92_SECURITY from being set to FALSE.

  • Not allow to turn off AUDIT_TRAIL prevents the AUDIT_TRAIL initialization parameter from being turned off. If unified auditing is enabled, then the AUDIT_TRAIL parameter has no effect.

5.5 Creating a Rule Set

To create a rule set, you first create the rule set itself, and then you can 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.
  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_SET statement to create the rule set.
    For example:
    BEGIN
     DBMS_MACADM.CREATE_RULE_SET(
      rule_set_name    => 'Limit_DBA_Access', 
      description      => 'DBA access through predefined processes', 
      enabled          => DBMS_MACUTL.G_YES,
      eval_options     => DBMS_MACUTL.G_RULESET_EVAL_ANY,
      audit_options    => DBMS_MACUTL.G_RULESET_AUDIT_OFF,
      fail_options     => DBMS_MACUTL.G_RULESET_FAIL_SHOW,
      fail_message     => 'Evaluation failed',
      fail_code        => 20461,
      handler_options  => DBMS_MACUTL.G_RULESET_HANDLER_OFF, 
      handler          => '',
      is_static        => TRUE,
      scope            => DBMS_MACUTL.G_SCOPE_LOCAL);
    END;
    /

    In this specification:

    • rule_set_name can be up to 128 characters in mixed-case. Spaces are allowed. Oracle suggests that you start the name with a verb and complete it with the realm or command rule name to which the rule set is attached.The DBA_DV_RULE_SET data dictionary view lists existing rule sets.
    • description can be 1024 characters in mixed-case. You may want to document the business requirement of the rule set (for example, Rule set to limit access to SQL*Plus).
    • enabled controls whether the rule set is enabled or disabled. DBMS_MACUTL.G_YES enables the rule set; DBMS_MACUTL.G_NO disables it. The default is DBMS_MACUTL.G_YES.
    • eval_options is used if you plan to have multiple rules associated with the rule set. DBMS_MACUTL.G_RULESET_EVAL_ALL means all rules must evaluate to TRUE; DBMS_MACUTL.G_RULESET_EVAL_ANY means at least one rule must evaluate to TRUE.
    • audit_options applies only to traditional auditing, not unified auditing environments. Starting with Oracle Database release 21c, traditional auditing is deprecated. Oracle recommends that you create unified audit policies instead of using audit_options. applies only to traditional auditing, not unified auditing environments.

      Valid audit_options settings are DBMS_MACUTL.G_REALM_AUDIT_OFF, DBMS_MACUTL.G_REALM_AUDIT_FAIL, DBMS_MACUTL.G_REALM_AUDIT_SUCCESS, and DBMS_MACUTL.G_REALM_AUDIT_FAIL + DBMS_MACUTL.G_REALM_AUDIT_SUCCESS.

    • fail_options designates whether to show (DBMS_MACUTL.G_RULESET_FAIL_SHOW) to not show (DBMS_MACUTL.G_RULESET_FAIL_SILENT) error messages. An advantage of selecting DBMS_MACUTL.G_RULESET_FAIL_SILENT and then enabling auditing is that you can track the activities of a potential intruder. The audit report reveals the activities of the intruder, yet the intruder is unaware that you are doing this because he or she does not see any error messages.
    • fail_message is a text string error message up to 80 characters in mixed-case, to associate with the fail code you specify for fail_code. If you do not specify an error message, then Oracle Database Vault displays a generic error message.
    • fail_code is a number in the range of -20000 to -20999 or 20000 to 20999 to associate with the fail_message parameter. If you omit this setting, then Oracle Database Vault displays a generic error code.
    • handler_options enables you to include handler code to define custom event handler logic. DBMS_MACUTL.G_RULESET_HANDLER_OFF disables error handling (default), DBMS_MACUTL.G_RULESET_HANDLER_FAIL calls handler on rule set failure, and DBMS_MACUTL.G_RULESET_HANDLER_SUCCESS calls handler on rule set success.
    • handler is a PL/SQL function or procedure that defines the custom event handler logic. You can create a custom event method to provide special processing outside the standard Oracle Database Vault rule set auditing features. For example, you can use an event handler to initiate a workflow process or send event information to an external system.

      Write the expression as a fully qualified procedure (such as schema.procedure_name). Do not include any other form of SQL statements. If you are using application package procedures or standalone procedures, you must provide DVSYS with the EXECUTE privilege on the object. The procedure signature can be in one of the following two forms:

      • PROCEDURE my_ruleset_handler(p_ruleset_name IN VARCHAR2, p_ruleset_rules IN BOOLEAN): Use this form when the name of the rule set and its return value are required in the handler processing.

      • PROCEDURE my_ruleset_handler: Use this form when the name of the rule set and its return value are not required in the handler processing.

      Be aware that you cannot use invoker's rights procedures as event handlers. Doing so can cause the rule set evaluation to fail unexpectedly. Only use definer's rights procedures as event handlers.

      Use the following syntax:

      myschema.my_ruleset_handler
      
    • is_static determines how often a rule set is evaluated when it is accessed. TRUE evaluates the rule set once during the user session. After that, the value is re-used. FALSE evaluates the rule set each time the rule set is called. The default is FALSE.
    • scope defines whether the rule set is created in a PDB (DBMS_MACUTL.G_SCOPE_LOCAL) or in an application root (DBMS_MACUTL.G_SCOPE_COMMON). If you create the common rule set in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:
      ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

    At this stage the rule set creation is complete.

  3. Optionally, add one or more rules to the rule set.
    The DBA_DV_RULE data dictionary view lists existing rules.
    For example:
    BEGIN
     DBMS_MACADM.ADD_RULE_TO_RULE_SET(
      rule_set_name => 'Limit_DBA_Access',
      rule_name     => 'Is Database Administrator',
      rule_order    => 1,
      enabled       => DBMS_MACUTL.G_YES);
    END;
    /

5.6 Creating a Rule to Add to a Rule Set

A rule defines the behavior that you want to control; a rule set is a named collection of rules.

5.6.1 About Creating Rules

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.

Related Topics

5.6.2 Default Rules

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

You can find a full list of rules by querying the DBA_DV_RULE data dictionary view. The following table lists the current default Oracle Database rules.

Table 5-1 Current Default Oracle Database Vault Rules

Rule Description

Are Dest Parameters Allowed

Checks if the current SQL statement attempts to alter initialization parameters related to the size limit of a dump

Are Dump Parameters Allowed

Checks if the current SQL statement attempts to alter initialization parameters related to the destination of a dump

False

Evaluates to FALSE

Is Alter DVSYS Allowed

Note: This default rule has been deprecated.

Checks if the logged-in user can execute the ALTER USER statement on other users successfully

Is Database Administrator

Checks if a user has been granted the DBA role

Is Drop User Allowed

Checks if the logged in user can drop users

Is Dump of Block Allowed

Checks if the dumping of blocks is allowed

Is First Day of Month

Checks if the specified date is the first day of the month

Is Label Administrator

Checks if the user has been granted the LBAC_DBA role

Is Last Day of Month

Checks if the specified date is the last day of the month

Is Parameter Value False

Checks if a specified parameter value has been set to FALSE

Is Parameter Value None

Checks if a specified parameter value has been set to NONE

Is Parameter Value Not False

Checks if a specified parameter value has been set to <> FALSE

Is Parameter Value Not None

Checks if a specified parameter value has been set to <> NONE

Is Parameter Value Not Off

Checks if a specified parameter value has been set to <> OFF

Is Parameter Value Not On

Checks if a specified parameter value has been set to <> ON

Is Parameter Value Not True

Checks if a specified parameter value has been set to <> TRUE

Is Parameter Value Off

Checks if a specified parameter value has been set to OFF

Is Parameter Value On

Checks if a specified parameter value has been set to ON

Is Parameter Value True

Checks if a specified parameter value has been set to TRUE

Is SYS or SYSTEM User

Checks if the user is SYS or SYSTEM

Is Security Administrator

Checks if a user has been granted the DV_ADMIN role

Is Security Owner

Checks if a user has been granted the DV_OWNER role

Is User Manager

Checks if a user has been granted the DV_ACCTMGR role

Login User Is Object User

Checks if the logged in user is the same as the user about to be altered by the current SQL statement

No Exempt Access Policy Role

Checks if the user has been granted the EXEMPT ACCESS POLICY role or is user SYS

Not Export Session

Obsolete

True

Evaluates to TRUE

5.6.3 Creating a New Rule

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 create a rule, you can add it to a rule set.

5.6.4 Adding Existing Rules to a Rule Set

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

5.6.5 Modifying a Rule Set

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

5.6.6 Removing a Rule from a Rule Set

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

After you remove the rule from the rule set, the rule still exists. If you want, you can associate it with other rule sets. You can also delete the rule by executing the DBMS_MACADM.DELETE_RULE. For example:

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

5.7 Modifying a Rule Set

You can use the DBMS_MACADM.UPDATE_RULE_SET procedure to modify the definition of 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

    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 set and check its definition.
    For example:
    SELECT * FROM DBA_DV_RULE_SET ORDER BY RULE_SET_NAME; 
  3. Execute the DBMS_MACADM.UPDATE_RULE_SET statement.
    For example:
    BEGIN
     DBMS_MACADM.UPDATE_RULE_SET(
      rule_set_name    => 'Limit_DBA_Access', 
      description      => 'DBA access through predefined processes', 
      enabled          => DBMS_MACUTL.G_NO,
      eval_options     => DBMS_MACUTL.G_RULESET_EVAL_ANY,
      audit_options    => DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
      fail_options     => DBMS_MACUTL.G_RULESET_FAIL_SHOW,
      fail_message     => 'Access denied!',
      fail_code        => 20900,
      handler_options  => DBMS_MACUTL.G_RULESET_HANDLER_OFF, 
      handler          => '',
      is_static        =  TRUE);
    END;
    /

5.8 Deleting a Rule Set

Before you delete a rule set, you must remove any rules from the 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

    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_SET data dictionary view to find the rule set that you want to delete.
    SELECT RULE_SET_NAME 
    FROM DBA_DV_RULE_SET 
    ORDER BY RULE_SET_NAME;
  3. Query the DBA_DV_RULE_SET_RULE data dictionary view to ensure that no rules are associated with the rule set that you want to delete.
    For example:
    SELECT RULE_NAME 
    FROM DBA_DV_RULE_SET_RULE 
    WHERE RULE_SET_NAME = 'Limit_DBA_Access';
  4. If necessary, execute DBMS_MACADM.DELETE_RULE_FROM_RULE_SET remove the rules that are associated with 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;
    /
    
  5. Execute the DBMS_MACADM.DELETE_RULE_SET procedure to delete the rule set.
    For example:
    EXEC DBMS_MACADM.DELETE_RULE_SET('Limit_DBA_Access');

5.9 How Rule Sets Work

Understanding how rule sets work helps to create more effective rule sets.

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

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

5.9.3 Creating Rules to Apply to Everyone Except One User

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

  • To create a rule that excludes specific users, user the SYS_CONTEXT function.

For example:

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

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

5.10 Tutorial: Configuring Two-Person Integrity, or Dual Key Security

This tutorial demonstrates how to use Oracle Database Vault to control the authorization of two users.

5.10.1 About This Tutorial

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.

5.10.2 Step 1: Create Users for This Tutorial

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

  • patch_boss acts in a supervisory role: If patch_boss is not logged in, then the patch_user user cannot log in.

  • patch_user is the user who is assigned to perform the patch upgrade. However, for this tutorial, user patch_user does not actually perform a patch upgrade. He only attempts to log in.

To create the users:

  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.

In a real-world scenario, you also would log in as the DV_OWNER user and grant the DV_PATCH_ADMIN role to user patch_user (but not to patch_boss). But because you are not really going to perform a database patch upgrade in this tutorial, you do not need to grant this role to user patch_user.

5.10.3 Step 2: Create a Function to Check if User patch_boss Is Logged In

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

The function that you must create, check_boss_logged_in, does just that: When user patch_user tries to log in to the database instance, it checks if user patch_boss is already logged in by querying the V$SESSION data dictionary view.
  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;

5.10.4 Step 3: Create Rules, a Rule Set, and a Command Rule to Control User Access

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

The rule set triggers the check_boss_logged_in function when user patch_user tries to logs in to the database.
  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;

5.10.5 Step 4: Test the Users' Access

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.

5.10.6 Step 5: Remove the Components for This Tutorial

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;

5.11 Guidelines for Designing Rule Sets

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

  • You can design a rule set so that its evaluation is static, that is, it is evaluated only once during a user session. Alternatively, it can be evaluated each time the rule set is accessed. If the rule set is evaluated only once, then the evaluated value is reused throughout the user session each time the rule set is accessed. Using static evaluation is useful in cases where the rule set must be accessed multiple times but the conditions on which the rule set depend do not change during that session. An example would be a SELECT command rule associated with a rule set when the same SELECT statement occurs multiple times and if the evaluated value is acceptable to use again, rather than evaluating the rule set each time the SELECT occurs.

    To control the static evaluation of the rule set, set the is_static parameter of the CREATE_RULE_SET or UPDATE_RULE_SET procedures of the DBMS_MACADM PL/SQL package. See DBMS_MACADM Rule Set Procedures for more information.

  • Use 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_MAIL, 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. For example, suppose you want to create a nested rule that performs the following two tasks:

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

    A rule definition for this scenario could be as follows:

    DVF.F$NETWORK = 'Corporate' AND TO_CHAR(SYSDATE,'HH24') between '22' AND '23'
  • You cannot use invoker's rights procedures with rule expressions. Only use definer's rights procedures with rule expressions.

5.12 How Rule Sets Affect Performance

The number and complexity of rules can slow database performance.

Rule sets govern the performance for execution of certain operations. 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.

If possible, consider setting the rule set to use static evaluation, assuming this is compatible with the associated command rule's usage. See Guidelines for Designing Rule Sets for more information.

You can check system performance by running tools such as Oracle Enterprise Manager (including Oracle Enterprise Manager Cloud Control, which is installed by default with Oracle Database), Automatic Workload Repository (AWR), and TKPROF.

5.13 Rule Set and Rule Related Reports and Data Dictionary Views

Oracle Database Vault provides reports and data dictionary views that are useful for analyzing rule sets and the rules within them.

Table 5-2 lists the Oracle Database Vault reports.

Table 5-2 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-3 lists data dictionary views that provide information about existing rules and rule sets.

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

Data Dictionary View Description

DBA_DV_RULE

Lists the rules that have been defined

DBA_DV_RULE_SET

Lists the rule sets that have been created

DBA_DV_RULE_SET_RULE

Lists rules that are associated with existing rule sets