149 DBMS_RULE_ADM
The DBMS_RULE_ADM
package provides the subprograms for creating and managing rules, rule sets, and rule evaluation contexts.
This chapter contains the following topics:
149.1 DBMS_RULE_ADM Overview
This package provides the subprograms for creating and managing rules, rule sets, and rule evaluation contexts.
See Also:
-
Rule TYPEs for more information about the types used with the
DBMS_RULE_ADM
package
149.2 DBMS_RULE_ADM Security Model
PUBLIC
is granted EXECUTE
privilege on this package.
See Also:
Oracle Database Security Guide for more information about user group PUBLIC
149.3 Summary of DBMS_RULE_ADM Subprograms
This table lists the DBMS_RULE_ADM
subprograms and briefly describes them.
Table 149-1 DBMS_RULE_ADM Package Subprograms
Subprogram | Description |
---|---|
Adds the specified rule to the specified rule set |
|
Alters a rule evaluation context |
|
Changes one or more aspects of the specified rule |
|
Creates a rule evaluation context |
|
Creates a rule with the specified name |
|
Creates a rule set with the specified name |
|
Drops the rule evaluation context with the specified name |
|
Drops the rule with the specified name |
|
Drops the rule set with the specified name |
|
Grants the specified object privilege on the specified object to the specified user or role |
|
Grants the specified system privilege to the specified user or role |
|
Removes the specified rule from the specified rule set |
|
Revokes the specified object privilege on the specified object from the specified user or role |
|
Revokes the specified system privilege from the specified user or role |
Note:
All subprograms commit unless specified otherwise.
149.3.1 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 149-2 ADD_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule you are adding to the rule set, specified as |
|
The name of the rule set to which you are adding the rule, specified as |
|
An evaluation context name in the form 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. |
|
Optional description, which can 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 the 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 beSYS
-
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.
149.3.2 ALTER_EVALUATION_CONTEXT Procedure
This procedure alters 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.ALTER_EVALUATION_CONTEXT( evaluation_context_name IN VARCHAR2, table_aliases IN SYS.RE$TABLE_ALIAS_LIST DEFAULT NULL, remove_table_aliases IN BOOLEAN DEFAULT FALSE, variable_types IN SYS.RE$VARIABLE_TYPE_LIST DEFAULT NULL, remove_variable_types IN BOOLEAN DEFAULT FALSE, evaluation_function IN VARCHAR2 DEFAULT NULL, remove_evaluation_function IN BOOLEAN DEFAULT FALSE, evaluation_context_comment IN VARCHAR2 DEFAULT NULL, remove_eval_context_comment IN BOOLEAN DEFAULT FALSE);
Parameters
Table 149-3 ALTER_EVALUATION_CONTEXT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the evaluation context you are altering, specified as For example, to alter an evaluation context named |
|
If If non- Table aliases specify the tables in an evaluation context. The table aliases can be used to reference tables in rule conditions. |
|
If If |
|
If If non- |
|
If If |
|
If If non- An evaluation function is an optional function that will be called to evaluate rules that use the evaluation context. It must have the same form as the See CREATE_EVALUATION_CONTEXT Procedurefor more information about evaluation functions. |
|
If If |
|
If If non- An evaluation context comment is an optional description of the rule evaluation context. |
|
If If |
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 altered
-
Have
ALL_ON_EVALUATION_CONTEXT
orALTER_ON_EVALUATION_CONTEXT
object privilege on an evaluation context owned by another user -
Have
ALTER_ANY_EVALUATION_CONTEXT
system privilegeSee Also:
Rule TYPEs for more information about the types used with the
DBMS_RULE_ADM
package
149.3.3 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 149-4 ALTER_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule you are altering, specified as |
|
The condition to be associated with the rule. If non- |
|
An evaluation context name in the form If non- |
|
If If If the |
|
If non- |
|
If If If the |
|
If non- |
|
If If If the |
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 beSYS
-
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:
Rule TYPEs for more information about the types used with the DBMS_RULE_ADM
package
149.3.4 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 149-5 CREATE_EVALUATION_CONTEXT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the evaluation context you are creating, specified as For example, to create an evaluation context named |
|
Table aliases that specify the tables in an evaluation context. The table aliases can be used to reference tables in rule conditions. |
|
A list of variables for the evaluation context |
|
An optional function that will be called to evaluate rules using the evaluation context. It must have the same form as the See "Usage Notes" for more information about the evaluation function. |
|
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 privilegeSee Also:
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 theDBMS_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.
149.3.5 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 149-6 CREATE_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule you are creating, specified as |
|
The condition to be associated with the rule. A condition evaluates to department_id = 30 Ensure that the proper case is used for text in rule conditions. Note: Do not include the word |
|
An optional evaluation context name in the form If |
|
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. |
|
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 beSYS
. -
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:
Rule TYPEs for more information about the types used with the DBMS_RULE_ADM
package
149.3.6 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 149-7 CREATE_RULE_SET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule set you are creating, specified as |
|
An optional evaluation context name in the form |
|
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 beSYS
-
Be the evaluation context owner
149.3.7 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 149-8 DROP_EVALUATION_CONTEXT Procedure Parameters
Parameter | Description |
---|---|
|
The name of the evaluation context you are dropping, specified as For example, to drop an evaluation context named |
|
If If If Caution: Setting |
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
149.3.8 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 149-9 DROP_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule you are dropping, specified as |
|
If If If |
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 privilegeNote:
-
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.
-
149.3.9 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 149-10 DROP_RULE_SET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule set you are dropping, specified as |
|
If If |
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.
149.3.10 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 149-11 GRANT_OBJECT_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the object privilege to grant to the grantee on the object. See "Usage Notes" for the available object privileges. |
|
The name of the object for which you are granting the privilege to the grantee, specified as |
|
The name of the user or role for which the privilege is granted. The specified user cannot be the owner of the object. |
|
If If |
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 149-12 lists the object privileges.
Table 149-12 Object Privileges for Evaluation Contexts, Rules, and Rule Sets
Privilege | Description |
---|---|
|
Alter and execute a particular evaluation context in another user's schema |
|
Alter and execute a particular rule in another user's schema |
|
Alter and execute a particular rule set in another user's schema |
|
Alter a particular evaluation context in another user's schema |
|
Alter a particular rule in another user's schema |
|
Alter a particular rule set in another user's schema |
|
Execute a particular evaluation context in another user's schema |
|
Execute a particular rule in another user's schema |
|
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; /
149.3.11 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 149-13 GRANT_SYSTEM_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the system privilege to grant to the grantee. |
|
The name of the user or role for which the privilege is granted |
|
If If |
Usage Notes
Table 149-14 lists the system privileges.
Table 149-14 System Privileges for Evaluation Contexts, Rules, and Rule Sets
Privilege | Description |
---|---|
|
Alter any evaluation context owned by any user |
|
Alter any rule owned by any user |
|
Alter any rule set owned by any user |
|
Create a new evaluation context in any schema |
|
Create a new evaluation context in the grantee's schema |
|
Create a new rule in any schema |
|
Create a new rule in the grantee's schema |
|
Create a new rule set in any schema |
|
Create a new rule set in the grantee's schema |
|
Drop any evaluation context in any schema |
|
Drop any rule in any schema |
|
Drop any rule set in any schema |
|
Execute any evaluation context owned by any user |
|
Execute any rule owned by any user |
|
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; /
149.3.12 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 149-15 REMOVE_RULE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the rule you are removing from the rule set, specified as |
|
The name of the rule set from which you are removing the rule, specified as |
|
The name of the evaluation context associated with the rule you are removing, specified as If an evaluation context was specified for the rule you are removing when you added the rule to the rule set using the Specify |
|
If If 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.
149.3.13 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 149-16 REVOKE_OBJECT_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
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. |
|
The name of the object for which you are revoking the privilege from the revokee, specified as |
|
The name of the user or role from which the privilege is revoked. The user who owns the object cannot be specified. |
149.3.14 REVOKE_SYSTEM_PRIVILEGE Procedure
This 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 149-17 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the system privilege to revoke from the revokee. See GRANT_SYSTEM_PRIVILEGE Procedure for a list of the system privileges. |
|
The name of the user or role from which the privilege is revoked |