ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
1
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_ADM
DBMS_XSTREAM_UTL_IVK
DBMS_ASSERT
DBMS_RULE_EXIMP
RE$NV_LIST
DBMS_CHAIN_INVOKER
DBMS_RULE_IMP_OBJ
RE$TABLE_ALIAS_LIST
DBMS_LOGREP_UTIL
DBMS_STREAMS_ADM_IVK
RE$VARIABLE_TYPE_LIST
DBMS_PRVTAQIS
DBMS_STREAMS_ADM_UTL
Documented
Yes
Exceptions
Error Code
Reason
ORA-24161
INVALID_NV_NAME
First Available
20c
Security Model
Owned by SYS with EXECUTE granted to PUBLIC
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.add_rule(
rule_name IN VARCHAR2,
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2 := NULL,
rule_comment IN VARCHAR2 := NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(add_rule, NONE);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_evaluation_context, NONE);
-- 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);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_rule, NONE);
Drops the rule evaluation context with the specified name
dbms_rule_adm.drop_evaluation_context(
evaluation_context_name IN VARCHAR2,
force IN BOOLEAN := FALSE);
PRAGMA SUPPLEMENTAL_LOG_DATA(alter_evaluation_context, NONE);
dbms_rule_adm.drop_rule(
rule_name IN VARCHAR2,
force IN BOOLEAN := FALSE);
PRAGMA SUPPLEMENTAL_LOG_DATA(drop_rule, NONE);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(drop_rule_set, NONE);
-- 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;
/
Grants the specified object privilege on the specified object to the specified user or role
dbms_rule_adm.grant_object_privilege(
privilege IN BINARY_INTEGER,
object_name IN VARCHAR2,
grantee IN VARCHAR2,
grant_option IN BOOLEAN := FALSE);
PRAGMA SUPPLEMENTAL_LOG_DATA(grant_object_privilege, AUTO);
Grants the specified system privilege to the specified user or role
dbms_rule_adm.grant_system_privilege(
privilege IN BINARY_INTEGER,
grantee IN VARCHAR2,
grant_option IN BOOLEAN := FALSE);
PRAGMA SUPPLEMENTAL_LOG_DATA(grant_system_privilege, AUTO);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(remove_rule, NONE);
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"
Revokes the specified object privilege on the specified object from the specified user or role
dbms_rule_adm.revoke_object_privilege(
privilege IN BINARY_INTEGER,
object_name IN VARCHAR2,
revokee IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(revoke_object_privilege, AUTO);