1. Administrator's Guide
  2. Configuring Rule Sets

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.

Related Topics

Parent topic: Configuring Rule Sets

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.

Related Topics

Parent topic: Configuring Rule Sets

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

Parent topic: Configuring Rule Sets

5.4 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. Run 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_RULESET_AUDIT_OFF, DBMS_MACUTL.G_RULESET_AUDIT_FAIL, DBMS_MACUTL.G_RULESET_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 they do 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;
/

Related Topics

Parent topic: Configuring Rule Sets

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

  • What Are Rules?
    A rule is an expression that checks if a particular condition is true or false.
  • Default Rules
    Default rules are rules that have commonly used behavior, such as checking if an action evaluates to true or false.
  • Creating a New Rule
    You can create a new rule or use the default Oracle Database Vault rules.
  • Adding Existing Rules to a Rule Set
    After you have created one or more rules, you can add them to a rule set.
  • Modifying a Rule
    You can use the DBMS_MACADM.UPDATE_RULE procedure to modify the definition of a rule.
  • 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.

Parent topic: Configuring Rule Sets

5.5.1 What Are Rules?

A rule is an expression that checks if a particular condition is true or false.

This expression enables Oracle Database Vault to perform an action based on the result of the rule evaluation during runtime. For example, the Is Database Administrator default rule checks if the session user is SYS or has the DBA role enabled or granted. You can create an Oracle Database Vault rule set that includes the rule to prevent users who fail this rule check from accessing a realm that should only be accessed by users with the DBA role. In addition, you can create Oracle Database Vault command rules that prevent users who fail a rule check from executing critical SQL commands that should only be used by users with the DBA role or SYS.

The expression that you use to define the rule must be a PL/SQL Boolean expression. You can use standard Oracle Database functions to build the expression, such as the SYS_CONTEXT function. An example of a rule expression using the SYS_CONTEXT function is as follows: 

SYS_CONTEXT('USERENV',SESSION_USER') = 'RLAYTON'

This rule expression translates to "Check if the currently logged in user is user RLAYTON."

When you create a rule, you can define a scope for it, that is, whether the rule is created in a PDB or in an application root.

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. Oracle provides guidelines for designing rule sets.

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, then Oracle Database Vault evaluates the rule set to true without evaluating its rules.

Related Topics

Parent topic: Creating a Rule to Add to a Rule Set

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

Parent topic: Creating a Rule to Add to a Rule Set

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

Related Topics

Parent topic: Creating a Rule to Add to a Rule Set

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

Related Topics

Parent topic: Creating a Rule to Add to a Rule Set

5.5.5 Modifying a Rule

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.
  2. Find the rule and check its definition.
    For example:
    SELECT * FROM DBA_DV_RULE ORDER BY NAME;
  3. Run 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;
/

Related Topics

Parent topic: Creating a Rule to Add to a Rule Set

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

Related Topics

Parent topic: Creating a Rule to Add to a Rule Set

5.6 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. Run 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;
/

Related Topics

Parent topic: Configuring Rule Sets

5.7 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, run 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. Run the DBMS_MACADM.DELETE_RULE_SET procedure to delete the rule set.
    For example: 
    EXEC DBMS_MACADM.DELETE_RULE_SET('Limit_DBA_Access');

Related Topics

Parent topic: Configuring Rule Sets

5.8 How Rule Sets Work

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

Parent topic: Configuring Rule Sets

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

Parent topic: How Rule Sets Work

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

Related Topics

Parent topic: How Rule Sets Work

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

Parent topic: How Rule Sets Work

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

Parent topic: Configuring Rule Sets

5.9.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 they can do this is if their 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.

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

5.9.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. This user can only attempt 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 accts_admin_ace@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 meets the password complexity requirements of the user's profile.

  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 sec_admin_owen;
GRANT SELECT ON V_$SESSION TO sec_admin_owen;

    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.

Related Topics

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

5.9.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 sec_admin_owen@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;

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

5.9.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 sec_admin_owen@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 sec_admin_owen 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 sec_admin_owen.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;

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

5.9.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 patch_user can log in.

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

5.9.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 accts_admin_ace@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 sec_admin_owen;
REVOKE SELECT ON V_$SESSION FROM sec_admin_owen;
  4. Connect as the DV_OWNER or DV_ADMIN user and drop the rules, rule set, and command rule, in the order shown.
    CONNECT sec_admin_owen@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;

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

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

Parent topic: Configuring Rule Sets

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

Related Topics

Parent topic: Configuring Rule Sets

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

Parent topic: Configuring Rule Sets

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

Related Topics

Parent topic: Configuring Rule Sets