Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?
Purpose
This package provides subprograms for creating and managing rules, rule sets, and rule evaluation contexts.
It's use is often observed when reviewing Data Pump Import logs.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
System Privileges
CREATE_EVALUATION_CONTEXT_OBJ
BINARY_INTEGER
1
CREATE_ANY_EVALUATION_CONTEXT
BINARY_INTEGER
2
ALTER_ANY_EVALUATION_CONTEXT
BINARY_INTEGER
3
DROP_ANY_EVALUATION_CONTEXT
BINARY_INTEGER
4
EXECUTE_ANY_EVALUATION_CONTEXT
BINARY_INTEGER
5
CREATE_RULE_SET_OBJ
BINARY_INTEGER
6
CREATE_ANY_RULE_SET
BINARY_INTEGER
7
ALTER_ANY_RULE_SET
BINARY_INTEGER
8
DROP_ANY_RULE_SET
BINARY_INTEGER
9
EXECUTE_ANY_RULE_SET
BINARY_INTEGER
10
CREATE_RULE_OBJ
BINARY_INTEGER
11
CREATE_ANY_RULE
BINARY_INTEGER
12
ALTER_ANY_RULE
BINARY_INTEGER
13
DROP_ANY_RULE
BINARY_INTEGER
14
EXECUTE_ANY_RULE
BINARY_INTEGER
15
Object Privileges
EXECUTE_ON_EVALUATION_CONTEXT
BINARY_INTEGER
16
ALTER_ON_EVALUATION_CONTEXT
BINARY_INTEGER
17
ALL_ON_EVALUATION_CONTEXT
BINARY_INTEGER
18
EXECUTE_ON_RULE_SET
BINARY_INTEGER
19
ALTER_ON_RULE_SET
BINARY_INTEGER
20
ALL_ON_RULE_SET
BINARY_INTEGER
21
EXECUTE_ON_RULE
BINARY_INTEGER
22
ALTER_ON_RULE
BINARY_INTEGER
23
ALL_ON_RULE
BINARY_INTEGER
24
Evaluation Success
EVALUATION_SUCCESS
BINARY_INTEGER
0
EVALUATION_FAILURE
BINARY_INTEGER
1
EVALUATION_CONTINUE
BINARY_INTEGER
2
Dependencies
DBMS_APPLY_ADM
DBMS_PRVTSQDS
DBMS_STREAMS_ADM_UTL_INVOK
DBMS_AQADM_INV
DBMS_RULEADM_INTERNAL
DBMS_SYSTEM
DBMS_AQADM_SYS
DBMS_RULES_LIB
DBMS_XSTREAM_ADM_UTL
DBMS_AQADM_SYSCALLS
DBMS_RULE_EXIMP
DBMS_XSTREAM_UTL_IVK
DBMS_ASSERT
DBMS_RULE_IMP_OBJ
RE$NV_LIST
DBMS_CHAIN_INVOKER
DBMS_STREAMS_ADM_IVK
RE$TABLE_ALIAS_LIST
DBMS_LOGREP_UTIL
DBMS_STREAMS_ADM_UTL
RE$VARIABLE_TYPE_LIST
DBMS_PRVTAQIS
Documented
Yes
Exceptions
Error Code
Reason
ORA-24161
INVALID_NV_NAME
First Available
20c
Security Model
Owned by SYS with EXECUTE granted to PUBLIC and the OGG_APPLY, OGG_CAPTURE, OGG_SHARED_CAPTURE, XSTREAM_APPLY and XSTREAM_CAPTURE roles.
From a security standpoint this package is a nightmare. Why would anyone think granting execute to PUBLIC on a package that makes possible granting of object and system privileges is a good idea?
And, while it is true that these are not system privileges in the sense that "GRANT CREATE ANY ..." is a system privilege it is still irresponsible.
dbms_rule_adm.alter_evaluation_context(
evaluation_context_name IN VARCHAR2,
table_aliases IN sys.re$table_alias_list := NULL,
remove_table_aliases IN BOOLEAN := FALSE,
variable_types IN sys.re$variable_type_list := NULL,
remove_variable_types IN BOOLEAN := FALSE,
evaluation_function IN VARCHAR2 := NULL,
remove_evaluation_function IN BOOLEAN := FALSE,
evaluation_context_comment IN VARCHAR2 := NULL,
remove_eval_context_comment IN BOOLEAN := FALSE);
dbms_rule_adm.alter_rule(
rule_name IN VARCHAR2,
condition IN VARCHAR2 := NULL,
evaluation_context IN VARCHAR2 := NULL,
remove_evaluation_context IN BOOLEAN := FALSE,
action_context IN sys.re$nv_list := NULL,
remove_action_context IN BOOLEAN := FALSE,
rule_comment IN VARCHAR2 := NULL,
remove_rule_comment IN BOOLEAN := FALSE);
dbms_rule_adm.create_evaluation_context(
evaluation_context_name IN VARCHAR2,
table_aliases IN sys.re$table_alias_list := NULL,
variable_types IN sys.re$variable_type_list := NULL,
evaluation_function IN VARCHAR2 := NULL,
evaluation_context_comment IN VARCHAR2 := NULL);
-- from $ORACLE_HOME/rdbms/admin/catmacc.sql
-- another example is present in $ORACLE_HOME/rdbms/admin/execstr.sql
DECLARE
rmdvt sys.re$variable_type_list;
BEGIN
rmdvt:=sys.re$variable_type_list(sys.re$variable_type('dv$dummy','number',NULL,NULL));
dbms_rule_adm.create_evaluation_context('DV$RULE_EVALCTX', NULL, rmdvt);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE IN (-24145) THEN NULL; --evaluation context already created
ELSE
RAISE;
END IF;
END;
/
dbms_rule_adm.create_rule(
rule_name IN VARCHAR2,
condition IN VARCHAR2,
evaluation_context IN VARCHAR2 := NULL,
action_context IN sys.re$nv_list := NULL,
rule_comment IN VARCHAR2 := NULL);
dbms_rule_adm.create_rule_set(
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2 := NULL,
rule_set_comment IN VARCHAR2 := NULL,
result_cache IN BOOLEAN := FALSE);
dbms_rule_adm.drop_rule(
rule_name IN VARCHAR2,
force IN BOOLEAN := FALSE);
BEGIN
FOR rrec IN (SELECT rule_owner, rule_name FROM dba_rules) LOOP
dbms_rule_adm.drop_rule(rule_name => rrec.rule_owner || '.' || rrec.rule_name, TRUE);
END LOOP;
END;
/
dbms_rule_adm.drop_rule_set(
rule_set_name IN VARCHAR2,
delete_rules IN BOOLEAN := FALSE);
-- from $ORACLE_HOME/rdbms/admin/dvu121.sql
DECLARE
CURSOR find_aq_rules IS
SELECT object_name
FROM dba_objects
WHERE object_type = 'RULE'
AND owner = 'DVSYS';
CURSOR find_aq_rulesets IS
SELECT object_name
FROM dba_objects
WHERE object_type = 'RULE SET'
AND owner = 'DVSYS';
BEGIN
FOR c IN find_aq_rules LOOP
BEGIN
dbms_rule_adm.drop_rule('DVSYS.' || c.object_name, TRUE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
FOR c IN find_aq_rulesets LOOP
BEGIN
dbms_rule_adm.drop_rule_set('DVSYS.' || c.object_name, TRUE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
Removes the specified rule from the specified rule set
dbms_rule_adm.remove_rule(
rule_name IN VARCHAR2,
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2 := NULL,
all_evaluation_contexts IN BOOLEAN := FALSE);
SELECT rule_set_name, rule_name
FROM dba_rule_set_rules;
-- executing the following line is not recommended as it will damage the instance
exec dbms_rule_adm.remove_rule('ALERT_QUES$1', 'ALERT_QUE_R');
-- the fact that doing it is granted to PUBLIC
defines "unnecessary risk"
