Oracle DBMS_RULE_ADM
Version 21c

General Information
Library Note Morgan's Library Page Header
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.
Source {ORACLE_HOME}/rdbms/admin/dbmsread.sql
Subprograms
ADD_RULE CREATE_RULE_SET GRANT_SYSTEM_PRIVILEGE
ALTER_EVALUATION_CONTEXT DROP_EVALUATION_CONTEXT REMOVE_RULE
ALTER_RULE DROP_RULE REVOKE_OBJECT_PRIVILEGE
CREATE_EVALUATION_CONTEXT DROP_RULE_SET REVOKE_SYSTEM_PRIVILEGE
CREATE_RULE GRANT_OBJECT_PRIVILEGE  
 
ADD_RULE
Adds the specified rule to the specified rule set 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);
TBD
 
ALTER_EVALUATION_CONTEXT
Alters a rule evaluation context 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);
-- from $ORACLE_HOME/rdbms/admin/f1102000.sql

DECLARE
 vt sys.re$variable_type_list;
BEGIN
  vt := sys.re$variable_type_list(
          sys.re$variable_type(
            'DML',
            'sys.lcr$_row_record',
            'sys.dbms_streams_internal.row_variable_value_function',
            'sys.dbms_streams_internal.row_fast_evaluation_function'),
          sys.re$variable_type(
            'DDL',
            'sys.lcr$_ddl_record',
            'sys.dbms_streams_internal.ddl_variable_value_function',
            'sys.dbms_streams_internal.ddl_fast_evaluation_function'),
          sys.re$variable_type(
            NULL,
            'sys.anydata',
            NULL,
            'sys.dbms_streams_internal.anydata_fast_eval_funciton')
        );

  dbms_rule_adm.alter_evaluation_context(
    evaluation_context_name => 'sys.streams$_evaluatoin_context',
    variable_types          => vt);
END;
/
 
ALTER_RULE
Changes one or more aspects of the specified rule 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);
TBD
 
CREATE_EVALUATION_CONTEXT
Creates a rule evaluation context 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;
/
 
CREATE_RULE
Creates a rule with the specified name 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);
TBD
 
CREATE_RULE_SET
Creates a rule set with the specified name 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);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_rule_set, NONE);
TBD
 
DROP_EVALUATION_CONTEXT
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);
exec dbms_rule_adm.drop_evaluation_context('DV$RULE_EVALCTX', TRUE);
 
DROP_RULE
Drops the rule with the specified name 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;
/
 
DROP_RULE_SET
Drops the rule set with the specified name 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;
/
 
GRANT_OBJECT_PRIVILEGE
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);
-- from $ORACLE_HOME/rdbms/admin/execstr.sql

BEGIN
  dbms_rule_adm.grant_object_privilege(
    privilege    => dbms_rule_adm.execute_on_evaluation_context,
    object_name  => 'sys.streams$_evaluation_context',
    grantee      => 'PUBLIC',
    grant_option => FALSE);
END;
/
 
GRANT_SYSTEM_PRIVILEGE
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);
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege    => sys.dbms_rule_adm.create_any_rule_set,
    grantee      => 'UWCLASS',
    grant_option => FALSE);
END;
/
 
REMOVE_RULE
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;

RULE_SET_NAME        RULE_NAME
-------------------- -----------------
ALERT_QUE_R          ALERT_QUE$1
ALERT_QUE$1          ALERT_QUE$1

-- 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"
 
REVOKE_OBJECT_PRIVILEGE
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);
exec dbms_rule_adm.revoke_object_privilege(
  dbms_rule_adm.execute_on_evaluation_context, 'PUBLIC');
 
REVOKE_SYSTEM_PRIVILEGE
Revokes the specified system privilege from the specified user or role dbms_rule_adm.revoke_system_privilege(
privilege IN BINARY_INTEGER,
revokee   IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(revoke_system_privilege, AUTO);
exec dbms_rule_adm.revoke_system_privilege(
  sys.dbms_rule_adm.create_any_rule_set, 'UWCLASS');

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_RULE
DBMS_RULE_EXPIMP
DBMS_RULE_EXP_EC_INTERNAL
DBMS_RULE_EXP_EV_CTXS
DBMS_RULE_EXP_RL_INTERNAL
DBMS_RULE_EXP_RS_INTERNAL
DBMS_RULE_EXP_RULE_SETS
DBMS_RULE_EXP_UTL
DBMS_RULE_EXP_UTLI
DBMS_RULE_IMP_OBJ
DBMS_RULE_INTERNAL
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx