82
DBMS_RULE_ADM

The DBMS_RULE_ADM package provides the administrative interfaces for creating and managing rules, rule sets, and rule evaluation contexts.

See Also:

Chapter 181, "Rule TYPEs" for more information about the types used with the DBMS_RULE_ADM package
Chapter 81, "DBMS_RULE" and Oracle Streams Concepts and Administration for more information about this package and rules

This chapter contains the following topics:

Using DBMS_RULE_ADM

Security Model

Security Model

PUBLIC is granted execute privilege on this package.

Summary of DBMS_RULE_ADM Subprograms

Table 82-1 DBMS_RULE_ADM Package Subprograms

Subprogram	Description
ADD_RULE Procedure	Adds the specified rule to the specified rule set
ALTER_RULE Procedure	Changes one or more aspects of the specified rule
CREATE_EVALUATION_CONTEXT Procedure	Creates a rule evaluation context
CREATE_RULE Procedure	Creates a rule with the specified name
CREATE_RULE_SET Procedure	Creates a rule set with the specified name
DROP_EVALUATION_CONTEXT Procedure	Drops the rule evaluation context with the specified name
DROP_RULE Procedure	Drops the rule with the specified name
DROP_RULE_SET Procedure	Drops the rule set with the specified name
GRANT_OBJECT_PRIVILEGE Procedure	Grants the specified object privilege on the specified object to the specified user or role
GRANT_SYSTEM_PRIVILEGE Procedure	Grants the specified system privilege to the specified user or role
REMOVE_RULE Procedure	Removes the specified rule from the specified rule set
REVOKE_OBJECT_PRIVILEGE Procedure	Revokes the specified object privilege on the specified object from the specified user or role
REVOKE_SYSTEM_PRIVILEGE Procedure	Revokes the specified system privilege from the specified user or role

Note:

All procedures commit unless specified otherwise.

ADD_RULE Procedure

This procedure adds the specified rule to the specified rule set.

Syntax

DBMS_RULE_ADM.ADD_RULE(
   rule_name           IN  VARCHAR2,
   rule_set_name       IN  VARCHAR2,
   evaluation_context  IN  VARCHAR2   DEFAULT NULL,
   rule_comment        IN  VARCHAR2   DEFAULT NULL);

Parameters

Table 82-2 ADD_RULE Procedure Parameters

Parameter	Description
`rule_name`	The name of the rule you are adding to the rule set, specified as `[``schema_name``.]``rule_name`. For example, to add a rule named `all_a` in the `hr` schema, enter `hr.all_a` for this parameter. If the schema is not specified, then the current user is the default.
`rule_set_name`	The name of the rule set to which you are adding the rule, specified as `[``schema_name``.]``rule_set_name`. For example, to add the rule to a rule set named `apply_rules` in the `hr` schema, enter `hr.apply_rules` for this parameter. If the schema is not specified, then the current user is the default.
`evaluation_context`	An evaluation context name in the form `[``schema_name``.]``evaluation_context_name`. If the schema is not specified, then the current user is the default. Only specify an evaluation context if the rule itself does not have an evaluation context and you do not want to use the rule set's evaluation context for the rule.
`rule_comment`	Optional description, which may contain the reason for adding the rule to the rule set

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Have ALTER_ON_RULE_SET privilege on the rule set
Have ALTER_ANY_RULE_SET system privilege
Be the owner of the rule set

Also, the rule set owner must meet at least one of the following requirements:

Have EXECUTE_ON_RULE privilege on the rule
Have EXECUTE_ANY_RULE system privilege
Be the rule owner

If the rule has no evaluation context and no evaluation context is specified when you run this procedure, then rule uses the evaluation context associated with the rule set. In such a case, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

If an evaluation context is specified, then the rule set owner must meet at least one of the following requirements:

Have EXECUTE_ON_EVALUATION_CONTEXT privilege on the evaluation context
Have EXECUTE_ANY_EVALUATION_CONTEXT system privilege, and the owner of the evaluation context must not be SYS
Be the evaluation context owner

Also, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

ALTER_RULE Procedure

This procedure changes one or more aspects of the specified rule.

Syntax

DBMS_RULE_ADM.ALTER_RULE(
   rule_name                  IN  VARCHAR2,
   condition                  IN  VARCHAR2        DEFAULT NULL,
   evaluation_context         IN  VARCHAR2        DEFAULT NULL,
   remove_evaluation_context  IN  BOOLEAN         DEFAULT false,
   action_context             IN  SYS.RE$NV_LIST  DEFAULT NULL,
   remove_action_context      IN  BOOLEAN         DEFAULT false,
   rule_comment               IN  VARCHAR2        DEFAULT NULL,
   remove_rule_comment        IN  BOOLEAN         DEFAULT false);

Parameters

Table 82-3 ALTER_RULE Procedure Parameters

Parameter	Description
`rule_name`	The name of the rule you are altering, specified as `[``schema_name``.]``rule_name`. For example, to alter a rule named `all_a` in the `hr` schema, enter `hr.all_a` for this parameter. If the schema is not specified, then the current user is the default.
`condition`	The condition to be associated with the rule. If non-`NULL`, then the rule's condition is changed.
`evaluation_context`	An evaluation context name in the form `[``schema_name``.]``evaluation_context_name`. If the schema is not specified, then the current user is the default. If non-`NULL`, then the rule's evaluation context is changed.
`remove_evaluation_context`	If `true`, then sets the evaluation context for the rule to `NULL`, which effectively removes the evaluation context from the rule. If `false`, then retains any evaluation context for the specified rule. If the `evaluation_context` parameter is non-`NULL`, then this parameter should be set to `false`.
`action_context`	If non-`NULL`, then changes the action context associated with the rule. A rule action context is information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated.
`remove_action_context`	If `true`, then sets the action context for the rule to `NULL`, which effectively removes the action context from the rule. If `false`, then retains any action context for the specified rule. If the `action_context` parameter is non-`NULL`, then this parameter should be set to `false`.
`rule_comment`	If non-`NULL`, then changes the description of the rule
`remove_rule_comment`	If `true`, then sets the comment for the rule to `NULL`, which effectively removes the comment from the rule. If `false`, then retains any comment for the specified rule. If the `rule_comment` parameter is non-`NULL`, then this parameter should be set to `false`.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Have ALTER_ON_RULE privilege on the rule
Have ALTER_ANY_RULE system privilege
Be the owner of the rule being altered

If an evaluation context is specified, then the rule owner must meet at least one of the following requirements:

Have EXECUTE_ON_EVALUATION_CONTEXT privilege on the evaluation context
Have EXECUTE_ANY_EVALUATION_CONTEXT system privilege, and the owner of the evaluation context must not be SYS
Be the evaluation context owner

Also, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

See Also:

Chapter 181, "Rule TYPEs" for more information about the types used with the DBMS_RULE_ADM package

CREATE_EVALUATION_CONTEXT Procedure

This procedure creates a rule evaluation context. A rule evaluation context defines external data that can be referenced in rule conditions. The external data can either exist as variables or as table data.

Syntax

DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
  evaluation_context_name      IN  VARCHAR2,
  table_aliases                IN  SYS.RE$TABLE_ALIAS_LIST    DEFAULT NULL,
  variable_types               IN  SYS.RE$VARIABLE_TYPE_LIST  DEFAULT NULL,
  evaluation_function          IN  VARCHAR2                   DEFAULT NULL,
  evaluation_context_comment   IN  VARCHAR2                   DEFAULT NULL);

Parameters

Table 82-4 CREATE_EVALUATION_CONTEXT Procedure Parameters

Parameter	Description
`evaluation_context_name`	The name of the evaluation context you are creating, specified as `[``schema_name``.]``evaluation_context_name`. For example, to create an evaluation context named `dept_eval_context` in the `hr` schema, enter `hr.dept_eval_context` for this parameter. If the schema is not specified, then the current user is the default.
`table_aliases`	Table aliases that specify the tables in an evaluation context. The table aliases can be used to reference tables in rule conditions.
`variable_types`	A list of variables for the evaluation context
`evaluation_function`	An optional function that will be called to evaluate rules using the evaluation context. It must have the same form as the `DBMS_RULE.EVALUATE` procedure. If the schema is not specified, then the current user is the default. See "Usage Notes" for more information about the evaluation function.
`evaluation_context_comment`	An optional description of the rule evaluation context.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Be the owner of the evaluation context being created and have CREATE_EVALUATION_CONTEXT_OBJ system privilege
Have CREATE_ANY_EVALUATION_CONTEXT system privilege

See Also:
Chapter 181, "Rule TYPEs" for more information about the types used with the DBMS_RULE_ADM package

The evaluation function must have the following signature:

FUNCTION evaluation_function_name(
  rule_set_name       IN   VARCHAR2,
  evaluation_context  IN   VARCHAR2,
  event_context       IN   SYS.RE$NV_LIST               DEFAULT NULL,
  table_values        IN   SYS.RE$TABLE_VALUE_LIST      DEFAULT NULL,
  column_values       IN   SYS.RE$COLUMN_VALUE_LIST     DEFAULT NULL,
  variable_values     IN   SYS.RE$VARIABLE_VALUE_LIST   DEFAULT NULL,
  attribute_values    IN   SYS.RE$ATTRIBUTE_VALUE_LIST  DEFAULT NULL,
  stop_on_first_hit   IN   BOOLEAN                      DEFAULT false,
  simple_rules_only   IN   BOOLEAN                      DEFAULT false,
  true_rules          OUT  SYS.RE$RULE_HIT_LIST,
  maybe_rules         OUT  SYS.RE$RULE_HIT_LIST);
RETURN BINARY_INTEGER;

Note:

Each parameter is required and must have the specified datatype. However, you can change the names of the parameters.

The return value of the function must be one of the following:

DBMS_RULE_ADM.EVALUATION_SUCCESS: The user specified evaluation function completed the rule set evaluation successfully. The rules engine returns the results of the evaluation obtained by the evaluation function to the rules engine client using the DBMS_RULE.EVALUATE procedure.
DBMS_RULE_ADM.EVALUATION_CONTINUE: The rules engine evaluates the rule set as if there were no evaluation function. The evaluation function is not used, and any results returned by the evaluation function are ignored.
DBMS_RULE_ADM.EVALUATION_FAILURE: The user specified evaluation function failed. Rule set evaluation stops, and an error is raised.

CREATE_RULE Procedure

This procedure creates a rule.

Syntax

DBMS_RULE_ADM.CREATE_RULE(
   rule_name           IN  VARCHAR2,
   condition           IN  VARCHAR2,
   evaluation_context  IN  VARCHAR2        DEFAULT NULL,
   action_context      IN  SYS.RE$NV_LIST  DEFAULT NULL,
   rule_comment        IN  VARCHAR2        DEFAULT NULL);

Parameters

Table 82-5 CREATE_RULE Procedure Parameters

Parameter	Description
`rule_name`	The name of the rule you are creating, specified as `[``schema_name``.]``rule_name`. For example, to create a rule named `all_a` in the `hr` schema, enter `hr.all_a` for this parameter. If the schema is not specified, then the current user is the default.
`condition`	The condition to be associated with the rule. A condition evaluates to `TRUE` or `FALSE` and can be any condition allowed in the `WHERE` clause of a `SELECT` statement. For example, the following is a valid rule condition: department_id = 30 Note: Do not include the word `"WHERE"` in the condition.
`evaluation_context`	An optional evaluation context name in the form `[``schema_name``.]``evaluation_context_name`, which is associated with the rule. If the schema is not specified, then the current user is the default. If `evaluation_context` is not specified, then the rule inherits the evaluation context from its rule set.
`action_context`	The action context associated with the rule. A rule action context is information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated.
`rule_comment`	An optional description of the rule

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Be the owner of the rule being created and have the CREATE_RULE_OBJ system privilege
Have CREATE_ANY_RULE system privilege

If an evaluation context is specified, then the rule owner must meet at least one of the following requirements:

Have EXECUTE_ON_EVALUATION_CONTEXT privilege on the evaluation context
Have EXECUTE_ANY_EVALUATION_CONTEXT system privilege, and the owner of the evaluation context must not be SYS.
Be the evaluation context owner

Also, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

See Also:

Chapter 181, "Rule TYPEs" for more information about the types used with the DBMS_RULE_ADM package

CREATE_RULE_SET Procedure

This procedure creates a rule set.

Syntax

DBMS_RULE_ADM.CREATE_RULE_SET(
   rule_set_name       IN  VARCHAR2,
   evaluation_context  IN  VARCHAR2  DEFAULT NULL,
   rule_set_comment    IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 82-6 CREATE_RULE_SET Procedure Parameters

Parameter	Description
`rule_set_name`	The name of the rule set you are creating, specified as `[``schema_name``.]``rule_set_name`. For example, to create a rule set named `apply_rules` in the `hr` schema, enter `hr.apply_rules` for this parameter. If the schema is not specified, then the current user is the default.
`evaluation_context`	An optional evaluation context name in the form `[``schema_name``.]``evaluation_context_name`, which applies to all rules in the rule set that are not associated with an evaluation context explicitly. If the schema is not specified, then the current user is the default.
`rule_set_comment`	An optional description of the rule set

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Be the owner of the rule set being created and have CREATE_RULE_SET_OBJ system privilege
Have CREATE_ANY_RULE_SET system privilege

If an evaluation context is specified, then the rule set owner must meet at least one of the following requirements:

Have EXECUTE_ON_EVALUATION_CONTEXT privilege on the evaluation context
Have EXECUTE_ANY_EVALUATION_CONTEXT system privilege, and the owner of the evaluation context must not be SYS
Be the evaluation context owner

DROP_EVALUATION_CONTEXT Procedure

This procedure drops a rule evaluation context.

Syntax

DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT(
   evaluation_context_name  IN  VARCHAR2,
   force                    IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-7 DROP_EVALUATION_CONTEXT Procedure Parameters

Parameter Description

Parameter	Description
`evaluation_context_name`	The name of the evaluation context you are dropping, specified as `[``schema_name``.]``evaluation_context_name`. For example, to drop an evaluation context named `dept_eval_context` in the `hr` schema, enter `hr.dept_eval_context` for this parameter. If the schema is not specified, then the current user is the default.
`force`	If `true`, then removes the rule evaluation context from all rules and rule sets that use it. If `false` and no rules or rule sets use the rule evaluation context, then drops the rule evaluation context. If `false` and one or more rules or rule sets use the rule evaluation context, then raises an exception. Caution: Setting `force` to `true` can result in rules and rule sets that do not have an evaluation context. If neither a rule nor the rule set it is in has an evaluation context, and no evaluation context was specified for the rule by the `ADD_RULE` procedure, then the rule cannot be evaluated.

evaluation_context_name

The name of the evaluation context you are dropping, specified as [schema_name.]evaluation_context_name.

For example, to drop an evaluation context named dept_eval_context in the hr schema, enter hr.dept_eval_context for this parameter. If the schema is not specified, then the current user is the default.

force

If true, then removes the rule evaluation context from all rules and rule sets that use it.

If false and no rules or rule sets use the rule evaluation context, then drops the rule evaluation context.

If false and one or more rules or rule sets use the rule evaluation context, then raises an exception.

Caution: Setting force to true can result in rules and rule sets that do not have an evaluation context. If neither a rule nor the rule set it is in has an evaluation context, and no evaluation context was specified for the rule by the ADD_RULE procedure, then the rule cannot be evaluated.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Be the owner of the evaluation context
Have DROP_ANY_EVALUATION_CONTEXT system privilege

DROP_RULE Procedure

This procedure drops a rule.

Syntax

DBMS_RULE_ADM.DROP_RULE(
   rule_name  IN  VARCHAR2,
   force      IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-8 DROP_RULE Procedure Parameters

Parameter Description

Parameter	Description
`rule_name`	The name of the rule you are dropping, specified as `[``schema_name``.]``rule_name`. For example, to drop a rule named `all_a` in the `hr` schema, enter `hr.all_a` for this parameter. If the schema is not specified, then the current user is the default.
`force`	If `true`, then removes the rule from all rule sets that contain it. If `false` and no rule sets contain the rule, then drops the rule. If `false` and one or more rule sets contain the rule, then raises an exception.

rule_name

The name of the rule you are dropping, specified as [schema_name.]rule_name. For example, to drop a rule named all_a in the hr schema, enter hr.all_a for this parameter. If the schema is not specified, then the current user is the default.

force

If true, then removes the rule from all rule sets that contain it.

If false and no rule sets contain the rule, then drops the rule.

If false and one or more rule sets contain the rule, then raises an exception.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Be the owner of the rule

Have DROP_ANY_RULE system privilege

Note:

To remove a rule from a rule set without dropping the rule from the database, use the REMOVE_RULE procedure.
The rule evaluation context associated with the rule, if any, is not dropped when you run this procedure.

DROP_RULE_SET Procedure

This procedure drops a rule set.

Syntax

DBMS_RULE_ADM.DROP_RULE_SET(
   rule_set_name  IN  VARCHAR2,
   delete_rules   IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-9 DROP_RULE_SET Procedure Parameters

Parameter Description

Parameter	Description
`rule_set_name`	The name of the rule set you are dropping, specified as `[``schema_name``.]``rule_set_name`. For example, to drop a rule set named `apply_rules` in the `hr` schema, enter `hr.apply_rules` for this parameter. If the schema is not specified, then the current user is the default.
`delete_rules`	If `true`, then also drops any rules that are in the rule set. If any of the rules in the rule set are also in another rule set, then these rules are not dropped. If `false`, then the rules in the rule set are retained.

rule_set_name

The name of the rule set you are dropping, specified as [schema_name.]rule_set_name. For example, to drop a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default.

delete_rules

If true, then also drops any rules that are in the rule set. If any of the rules in the rule set are also in another rule set, then these rules are not dropped.

If false, then the rules in the rule set are retained.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Have DROP_ANY_RULE_SET system privilege
Be the owner of the rule set

Note:
The rule evaluation context associated with the rule set, if any, is not dropped when you run this procedure.

GRANT_OBJECT_PRIVILEGE Procedure

This procedure grants the specified object privilege on the specified object to the specified user or role. If a user owns the object, then the user automatically is granted all privileges on the object, with grant option.

Syntax

DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
   privilege     IN  BINARY_INTEGER,
   object_name   IN  VARCHAR2,
   grantee       IN  VARCHAR2,
   grant_option  IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-10 GRANT_OBJECT_PRIVILEGE Procedure Parameters

Parameter	Description
`privilege`	The name of the object privilege to grant to the grantee on the object. See "Usage Notes" for the available object privileges.
`object_name`	The name of the object for which you are granting the privilege to the grantee, specified as `[``schema_name``.]``object_name`. For example, to grant the privilege on a rule set named `apply_rules` in the `hr` schema, enter `hr.apply_rules` for this parameter. If the schema is not specified, then the current user is the default. The object must be an existing rule, rule set, or evaluation context.
`grantee`	The name of the user or role for which the privilege is granted. The specified user cannot be the owner of the object.
`grant_option`	If `true`, then the specified user or users granted the specified privilege can grant this privilege to others. If `false`, then the specified user or users granted the specified privilege cannot grant this privilege to others.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Be the owner of the object on which the privilege is granted
Have the same privilege as the privilege being granted with the grant option

In addition, if the object is a rule set, then the user must have EXECUTE privilege on all the rules in the rule set with grant option or must own the rules in the rule set.

Table 82-11 lists the object privileges.

Table 82-11 Object Privileges for Evaluation Contexts, Rules, and Rule Sets

Privilege	Description
`SYS.DBMS_RULE_ADM.ALL_ON_EVALUATION_CONTEXT`	Alter and execute a particular evaluation context in another user's schema
`SYS.DBMS_RULE_ADM.ALL_ON_RULE`	Alter and execute a particular rule in another user's schema
`SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET`	Alter and execute a particular rule set in another user's schema
`SYS.DBMS_RULE_ADM.ALTER_ON_EVALUATION_CONTEXT`	Alter a particular evaluation context in another user's schema
`SYS.DBMS_RULE_ADM.ALTER_ON_RULE`	Alter a particular rule in another user's schema
`SYS.DBMS_RULE_ADM.ALTER_ON_RULE_SET`	Alter a particular rule set in another user's schema
`SYS.DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT`	Execute a particular evaluation context in another user's schema
`SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE`	Execute a particular rule in another user's schema
`SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET`	Execute a particular rule set in another user's schema

Examples

For example, to grant the HR user the privilege to alter a rule named hr_dml in the strmadmin schema, enter the following:

BEGIN 
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.ALTER_ON_RULE,
    object_name  => 'strmadmin.hr_dml',
    grantee      => 'hr', 
    grant_option => false);
END;
/

GRANT_SYSTEM_PRIVILEGE Procedure

This procedure grant the specified system privilege to the specified user or role.

Syntax

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     IN  BINARY_INTEGER,
   grantee       IN  VARCHAR2,
   grant_option  IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters

Parameter Description

Parameter	Description
`privilege`	The name of the system privilege to grant to the grantee.
`grantee`	The name of the user or role for which the privilege is granted
`grant_option`	If `true`, then the specified user or users granted the specified privilege can grant the system privilege to others. If `false`, then the specified user or users granted the specified privilege cannot grant the system privilege to others.

privilege

The name of the system privilege to grant to the grantee.

grantee

The name of the user or role for which the privilege is granted

grant_option

If true, then the specified user or users granted the specified privilege can grant the system privilege to others.

If false, then the specified user or users granted the specified privilege cannot grant the system privilege to others.

Usage Notes

Table 82-13 lists the system privileges.

Table 82-13 System Privileges for Evaluation Contexts, Rules, and Rule Sets

Privilege	Description
`SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUATION_CONTEXT`	Alter any evaluation context owned by any user
`SYS.DBMS_RULE_ADM.ALTER_ANY_RULE`	Alter any rule owned by any user
`SYS.DBMS_RULE_ADM.ALTER_ANY_RULE_SET`	Alter any rule set owned by any user
`SYS.DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT`	Create a new evaluation context in any schema
`SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ`	Create a new evaluation context in the grantee's schema
`SYS.DBMS_RULE_ADM.CREATE_ANY_RULE`	Create a new rule in any schema
`SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ`	Create a new rule in the grantee's schema
`SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET`	Create a new rule set in any schema
`SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ`	Create a new rule set in the grantee's schema
`SYS.DBMS_RULE_ADM.DROP_ANY_EVALUATION_CONTEXT`	Drop any evaluation context in any schema
`SYS.DBMS_RULE_ADM.DROP_ANY_RULE`	Drop any rule in any schema
`SYS.DBMS_RULE_ADM.DROP_ANY_RULE_SET`	Drop any rule set in any schema
`SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT`	Execute any evaluation context owned by any user
`SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE`	Execute any rule owned by any user
`SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET`	Execute any rule set owned by any user

For example, to grant the strmadmin user the privilege to create a rule set in any schema, enter the following:

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
    grantee      => 'strmadmin', 
    grant_option => false);
END;
/

Note:

When you grant a privilege on "ANY" object (for example, ALTER_ANY_RULE), and the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to false, you give the user access to that type of object in all schemas, except the SYS schema. By default, the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to false.

If you want to grant access to an object in the SYS schema, then you can grant object privileges explicitly on the object. Alternatively, you can set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to true. Then privileges granted on "ANY" object will allow access to any schema, including SYS.

REMOVE_RULE Procedure

This procedure removes the specified rule from the specified rule set.

Syntax

DBMS_RULE_ADM.REMOVE_RULE(
   rule_name                IN  VARCHAR2,
   rule_set_name            IN  VARCHAR2,
   evaluation_context       IN  VARCHAR2  DEFAULT NULL,
   all_evaluation_contexts  IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-14 REMOVE_RULE Procedure Parameters

Parameter	Description
`rule_name`	The name of the rule you are removing from the rule set, specified as `[``schema_name``.]``rule_name`. For example, to remove a rule named `all_a` in the `hr` schema, enter `hr.all_a` for this parameter. If the schema is not specified, then the current user is the default.
`rule_set_name`	The name of the rule set from which you are removing the rule, specified as `[``schema_name``.]``rule_set_name`. For example, to remove the rule from a rule set named `apply_rules` in the `hr` schema, enter `hr.apply_rules` for this parameter. If the schema is not specified, then the current user is the default.
`evaluation_context_name`	The name of the evaluation context associated with the rule you are removing, specified as `[``schema_name``.]``evaluation_context_name`. For example, to specify an evaluation context named `dept_eval_context` in the `hr` schema, enter `hr.dept_eval_context` for this parameter. If the schema is not specified, then the current user is the default. If an evaluation context was specified for the rule you are removing when you added the rule to the rule set using the `ADD_RULE` procedure, then specify the same evaluation context. If you added the same rule more than once with different evaluation contexts, then specify the rule with the evaluation context you want to remove. If you specify an evaluation context that is not associated with the rule, then an error is raised. Specify `NULL` if you did not specify an evaluation context when you added the rule to the rule set. If you specify `NULL` and there are one or more evaluation contexts associated with the rule, then an error is raised.
`all_evaluation_contexts`	If `true`, then the rule is removed from the rule set with all of its associated evaluation contexts. If `false`, then only the rule with the specified evaluation context is removed. This parameter is relevant only if the same rule is added more than once to the rule set with different evaluation contexts.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Have ALTER_ON_RULE_SET privilege on the rule set
Have ALTER_ANY_RULE_SET system privilege
Be the owner of the rule set

Note:
This procedure does not drop a rule from the database. To drop a rule from the database, use the DROP_RULE procedure.

REVOKE_OBJECT_PRIVILEGE Procedure

This procedure revokes the specified object privilege on the specified object from the specified user or role.

Syntax

DBMS_RULE_ADM.REVOKE_OBJECT_PRIVILEGE(
   privilege    IN  BINARY_INTEGER,
   object_name  IN  VARCHAR2,
   revokee      IN  VARCHAR2);

Parameters

Table 82-15 REVOKE_OBJECT_PRIVILEGE Procedure Parameters

Parameter	Description
`privilege`	The name of the object privilege on the object to revoke from the revokee. See "GRANT_OBJECT_PRIVILEGE Procedure" for a list of the object privileges.
`object_name`	The name of the object for which you are revoking the privilege from the revokee, specified as `[``schema_name``.]``object_name`. For example, to revoke an object privilege on a rule set named `apply_rules` in the `hr` schema, enter `hr.apply_rules` for this parameter. If the schema is not specified, then the current user is the default. The object must be an existing rule, rule set, or evaluation context.
`revokee`	The name of the user or role from which the privilege is revoked. The user who owns the object cannot be specified.

REVOKE_SYSTEM_PRIVILEGE Procedure

Revokes the specified system privilege from the specified user or role.

Syntax

DBMS_RULE_ADM.REVOKE_SYSTEM_PRIVILEGE(
   privilege  IN  BINARY_INTEGER,
   revokee    IN  VARCHAR2);

Parameters

Table 82-16 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters

Parameter	Description
`privilege`	The name of the system privilege to revoke from the revokee. See "GRANT_SYSTEM_PRIVILEGE Procedure" for a list of the system privileges.
`revokee`	The name of the user or role from which the privilege is revoked

82 DBMS_RULE_ADM

Using DBMS_RULE_ADM

Security Model

Table 82-1 DBMS_RULE_ADM Package Subprograms

Syntax

Parameters

Table 82-2 ADD_RULE Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-3 ALTER_RULE Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-4 CREATE_EVALUATION_CONTEXT Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-5 CREATE_RULE Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-6 CREATE_RULE_SET Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-7 DROP_EVALUATION_CONTEXT Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-8 DROP_RULE Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-9 DROP_RULE_SET Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-10 GRANT_OBJECT_PRIVILEGE Procedure Parameters

Usage Notes

Table 82-11 Object Privileges for Evaluation Contexts, Rules, and Rule Sets

Examples

Syntax

Parameters

Table 82-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters

Usage Notes

Table 82-13 System Privileges for Evaluation Contexts, Rules, and Rule Sets

Syntax

Parameters

Table 82-14 REMOVE_RULE Procedure Parameters

Usage Notes

Syntax

Parameters

Table 82-15 REVOKE_OBJECT_PRIVILEGE Procedure Parameters

Syntax

Parameters

Table 82-16 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters

82
DBMS_RULE_ADM