You can use the DBMS_MACADM PL/SQL package and a set of Oracle Database Vault rule functions to manage rule sets.
Topics:
The DBMS_MACADM PL/SQL package provides procedures that enable you to manage both rule sets and rules.
Table 14-1 lists procedures within the DBMS_MACADM package that you can use to configure rule sets. Only users who have been granted the DV_OWNER or DV_ADMIN role can use these procedures.
Table 14-1 DBMS_MACADM Rule Set Configuration Procedures
| Procedure | Description |
|---|---|
|
Adds a rule to a rule set |
|
|
Creates a rule |
|
|
Creates a rule set |
|
|
Deletes a rule |
|
|
Deletes a rule from a rule set |
|
|
Deletes a rule set |
|
|
Renames a rule. The name change takes effect everywhere the rule is used. |
|
|
Renames a rule set. The name change takes effect everywhere the rule set is used. |
|
|
Updates a rule |
|
|
Updates a rule set |
See Also:
Chapter 6, "Configuring Rule Sets," for detailed information about rule sets
Chapter 19, "Oracle Database Vault Utility APIs," for a set of general-purpose utility procedures that you can use with the rule set procedures and functions
The ADD_RULE_TO_RULE_SET procedure adds rule to a rule set, and lets you specify whether to have the rule be checked when the rule set is evaluated.
DBMS_MACADM.ADD_RULE_TO_RULE_SET( rule_set_name IN VARCHAR2, rule_name IN VARCHAR2, rule_order IN NUMBER, enabled IN VARCHAR2);
Table 14-2 ADD_RULE_TO_RULE_SET Parameters
| Parameter | Description |
|---|---|
|
|
Rule set name. To find existing rule sets in the current database instance, query the |
|
|
Rule to add to the rule set. To find existing rules, query the To find rules that have been associated with rule sets, use |
|
|
Does not apply to this release, but you must include a value for the |
|
|
Optional. Determines whether the rule should be checked when the rule set is evaluated. Possible values are:
See Table 19-1 for more information. |
The following example adds a rule to a rule set, and by omitting the enabled parameter, automatically enables the rule to be checked when the rule set is evaluated.
BEGIN DBMS_MACADM.ADD_RULE_TO_RULE_SET( rule_set_name => 'Limit_DBA_Access', rule_name => 'Restrict DROP TABLE operations', rule_order => 1); END; /
This example adds the rule to the rule set but disables rule checking.
BEGIN DBMS_MACADM.ADD_RULE_TO_RULE_SET( rule_set_name => 'Limit_DBA_Access', rule_name => 'Check UPDATE operations', rule_order => 1, enabled => DBMS_MACUTL.G_NO); END; /
The CREATE_RULE procedure creates a rule. After you create a rule, you can add it to a rule set.
DBMS_MACADM.CREATE_RULE( rule_name IN VARCHAR2, rule_expr IN VARCHAR2);
Table 14-3 CREATE_RULE Parameters
| Parameter | Description |
|---|---|
|
|
Rule name, up to 90 characters in mixed-case. Spaces are allowed. To find existing rules in the current database instance, query the To find rules that have been associated with rule sets, query |
|
|
PL/SQL If the expression contains quotation marks, do not use double quotation marks. Instead, use two single quotation marks. Enclose the entire expression within single quotation marks. For example: 'TO_CHAR(SYSDATE,''HH24'') = ''12''' See "Creating a New Rule" for more information on rule expressions. |
The following example shows how to create a rule expression that checks if the current session user is SYSADM.
BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Check UPDATE operations',
rule_expr =>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SYSADM''');
END;
/
Note:
The following feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).The following example shows how to create a rule expression that uses the public standalone function OLS_LABEL_DOMINATES to find if the session label of the hr_ols_pol Oracle Label Security policy dominates or is equal to the hs label. The value 0 indicates if it is false. (To check if it is equal, you would specify 1.)
BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Check OLS Factor',
rule_expr => 'OLS_LABEL_DOMINATES(''hr_ols_pol'', ''hs'') = 1');
END;
/
The CREATE_RULE_SET procedure creates a rule set. After you create a rule set, you can use the CREATE_RULE and ADD_RULE_TO_RULE_SET procedures to create and add rules to the rule set.
DBMS_MACADM.CREATE_RULE_SET( rule_set_name IN VARCHAR2, description IN VARCHAR2, enabled IN VARCHAR2, eval_options IN NUMBER, audit_options IN NUMBER, fail_options IN NUMBER, fail_message IN VARCHAR2, fail_code IN NUMBER, handler_options IN NUMBER, handler IN VARCHAR2, is_static IN BOOLEAN DEFAULT FALSE);
Table 14-4 CREATE_RULE_SET Parameters
| Parameter | Description |
|---|---|
|
|
Rule set name, up to 90 characters in mixed-case. Spaces are allowed. To find existing rule sets in the current database instance, query the |
|
|
Description of the purpose of the rule set, up to 1024 characters in mixed-case. |
|
|
|
|
|
If you plan to assign multiple rules to the rule set, enter one of the following settings:
|
|
|
Select one of the following settings:
|
|
|
Options for reporting errors:
|
|
|
Enter an error message for failure, up to 80 characters in mixed-case, to associate with the fail code you specify for |
|
|
Enter a number in the range of -20000 to -20999 or 20000 to 20999 to associate with the |
|
|
Select one of the following settings:
|
|
|
Name of the PL/SQL function or procedure that defines the custom event handler logic. |
|
|
Optional. Determines how often a rule set is evaluated when it is accessed. The default is
|
BEGIN DBMS_MACADM.CREATE_RULE_SET( rule_set_name => 'Limit_DBA_Access', description => 'DBA access through predefined processes', enabled => DBMS_MACUTL.G_YES, eval_options => DBMS_MACUTL.G_RULESET_EVAL_ANY, audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS, fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT, fail_message => '', fail_code => 20461, handler_options => DBMS_MACUTL.G_RULESET_HANDLER_FAIL, handler => 'dbavowner.email_alert', is_static => TRUE); END; /
The DELETE_RULE procedure deletes a rule.
DBMS_MACADM.DELETE_RULE( rule_name IN VARCHAR2);
Table 14-5 DELETE_RULE Parameter
| Parameter | Description |
|---|---|
|
|
Rule name. To find existing rules in the current database instance, query the To find rules that have been associated with rule sets, query |
EXEC DBMS_MACADM.DELETE_RULE('Check UPDATE operations');
The DELETE_RULE_FROM_RULE_SET procedure deletes a rule from a rule set.
DBMS_MACADM.DELETE_RULE_FROM_RULE_SET( rule_set_name IN VARCHAR2, rule_name IN VARCHAR2);
Table 14-6 DELETE_RULE_FROM_RULE_SET Parameters
| Parameter | Description |
|---|---|
|
|
Rule set name. To find existing rule sets in the current database instance, query the |
|
|
Rule to remove from the rule set. To find existing rules in the current database instance, query the To find rules that have been associated with rule sets, query |
BEGIN DBMS_MACADM.DELETE_RULE_FROM_RULE_SET( rule_set_name => 'Limit_DBA_Access', rule_name => 'Check UPDATE operations'); END; /
The DELETE_RULE_SET procedure deletes a rule set.
DBMS_MACADM.DELETE_RULE_SET( rule_set_name IN VARCHAR2);
Table 14-7 DELETE_RULE_SET Parameter
| Parameter | Description |
|---|---|
|
|
Rule set name. To find existing rule sets in the current database instance, query the |
EXEC DBMS_MACADM.DELETE_RULE_SET('Limit_DBA_Access');
The RENAME_RULE procedure renames a rule. The name change takes effect everywhere the rule is used.
DBMS_MACADM.RENAME_RULE( rule_name IN VARCHAR2, new_name IN VARCHAR2);
Table 14-8 RENAME_RULE Parameters
| Parameter | Description |
|---|---|
|
|
Current rule name. To find existing rules in the current database instance, query the To find rules that have been associated with rule sets, query |
|
|
New rule name, up to 90 characters in mixed-case. |
BEGIN DBMS_MACADM.RENAME_RULE( rule_name => 'Check UPDATE operations', new_name => 'Check Sector 2 Processes'); END; /
The RENAME_RULE_SET procedure renames a rule set. The name change takes effect everywhere the rule set is used.
DBMS_MACADM.RENAME_RULE_SET( rule_set_name IN VARCHAR2, new_name IN VARCHAR2);
Table 14-9 RENAME_RULE_SET Parameters
| Parameter | Description |
|---|---|
|
|
Current rule set name. To find existing rule sets in the current database instance, query the |
|
|
New rule set name, up to 90 characters in mixed-case. Spaces are allowed. |
BEGIN DBMS_MACADM.RENAME_RULE_SET( rule_set_name => 'Limit_DBA_Access', new_name => 'Limit Sector 2 Access'); END; /
The UPDATE_RULE procedure updates a rule.
DBMS_MACADM.UPDATE_RULE( rule_name IN VARCHAR2, rule_expr IN VARCHAR2);
Table 14-10 UPDATE_RULE Parameters
| Parameter | Description |
|---|---|
|
|
Rule name. To find existing rules in the current database instance, query the To find rules that have been associated with rule sets, query |
|
|
PL/SQL If the expression contains quotation marks, do not use double quotation marks. Instead, use two single quotation marks. Enclose the entire expression within single quotation marks. For example: 'TO_CHAR(SYSDATE,''HH24'') = ''12''' See "Creating a New Rule" for more information on rule expressions. To find existing rule expressions, query the |
BEGIN
DBMS_MACADM.UPDATE_RULE(
rule_name => 'Check UPDATE operations',
rule_expr =>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SYSADM'' AND
(
UPPER(SYS_CONTEXT(''USERENV'',''MODULE'')) LIKE ''APPSRVR%'' OR
UPPER(SYS_CONTEXT(''USERENV'',''MODULE'')) LIKE ''DBAPP%'' )'
);
END;
/
The UPDATE_RULE_SET procedure updates a rule set.
DBMS_MACADM.UPDATE_RULE_SET( rule_set_name IN VARCHAR2, description IN VARCHAR2, enabled IN VARCHAR2, eval_options IN NUMBER, audit_options IN NUMBER, fail_options IN NUMBER, fail_message IN VARCHAR2, fail_code IN NUMBER, handler_options IN NUMBER, handler IN VARCHAR2, is_static IN BOOLEAN DEFAULT FALSE);
Table 14-11 UPDATE_RULE_SET Parameters
| Parameter | Description |
|---|---|
|
|
Rule set name. To find existing rule sets in the current database instance, query the |
|
|
Description of the purpose of the rule set, up to 1024 characters in mixed-case. |
|
|
The default for the |
|
|
If you plan to assign multiple rules to the rule set, enter one of the following settings:
The default for |
|
|
Select one of the following settings:
The default for |
|
|
Options for reporting errors:
The default for |
|
|
Error message for failure, up to 80 characters in mixed-case, to associate with the fail code you specify for |
|
|
Enter a number in the range of -20000 to -20999 or 20000 to 20999 to associate with the |
|
|
Select one of the following settings:
The default for |
|
|
Name of the PL/SQL function or procedure that defines the custom event handler logic. |
|
|
Optional. Determines how often a rule set is evaluated when it is accessed by a SQL statement.
|
BEGIN DBMS_MACADM.UPDATE_RULE_SET( rule_set_name => 'Limit_DBA_Access', description => 'DBA access through predefined processes', enabled => DBMS_MACUTL.G_YES, eval_options => DBMS_MACUTL.G_RULESET_EVAL_ANY, audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, fail_options => DBMS_MACUTL.G_RULESET_FAIL_SHOW, fail_message => 'Access denied!', fail_code => 20900, handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF, handler => '', is_static = TRUE); END; /
In addition to the rule set-specific procedures in the DBMS_MADADM PL/SQL package, you can use the standalone Oracle Database Vault PL/SQL rule set functions.
Oracle Database Vault provides a set of functions that you can use in rule sets to inspect the SQL statement that you want the rule set to protect.
For example, if a rule set protects SELECT ON HR.EMPLOYEES under a command rule, then you could use these functions to make more informed decisions in the rule expression.
Table 14-12 lists the default rule functions.
Table 14-12 Installed Oracle Database Vault PL/SQL Rule Set Functions
| Rule Set Function | Description |
|---|---|
|
Returns the system event firing the rule set |
|
|
Returns the login user name |
|
|
Returns the database instance number |
|
|
Returns the database name |
|
|
Returns the type of the dictionary object on which the database operation occurred (for example, table, procedure, view) |
|
|
Returns the owner of the dictionary object on which the database operation occurred |
|
|
Returns the name of the dictionary object on which the database operation occurred |
|
|
Returns the first 4000 characters of SQL text of the database statement used in the operation |
The DV_SYSEVENT function returns the system event firing the rule set. The event name is the same as that in the syntax of the SQL statement (for example, INSERT, CREATE.) The return type is VARCHAR2.
DVSYS.DV_SYSEVENT () RETURN VARCHAR2;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Get System Event Firing the Maintenance Rule Set', rule_expr => 'DVSYS.DV_SYSEVENT = ''CREATE'''); END; /
The DV_LOGIN_USER function returns the login user name, in VARCHAR2 data type.
DVSYS.DV_LOGIN_USER () RETURN VARCHAR2;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Check System Login User Name', rule_expr => 'DVSYS.DV_LOGIN_USER = ''SEBASTIAN'''); END; /
The DV_INSTANCE_NUM function returns the database instance number, in NUMBER data type.
DVSYS.DV_INSTANCE_NUM () RETURN NUMBER;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Check Database Instance Number', rule_expr => 'DVSYS.DV_INSTANCE_NUM BETWEEN 6 AND 9'); END; /
The DV_DATABASE_NAME function returns the database name, in VARCHAR2 data type.
DVSYS.DV_DATABASE_NAME () RETURN VARCHAR2;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Check Database Name', rule_expr => 'DVSYS.DV_DATABASE_NAME = ''ORCL'''); END; /
The DV_DICT_OBJ_TYPE function returns the type of the dictionary object on which the database operation occurred (for example, table, procedure, or view). The return type is VARCHAR2.
DVSYS.DV_DICT_OBJ_TYPE () RETURN VARCHAR2;
None.
BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Check Dictionary Object Type',
rule_expr => 'DVSYS.DV_DICT_OBJ_TYPE IN (''TABLE'', ''VIEW'')');
END;
/
The DV_DICT_OBJ_OWNER function returns the name of the owner of the dictionary object on which the database operation occurred. The return type is VARCHAR2.
DVSYS.DV_DICT_OBJ_OWNER () RETURN VARCHAR2;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Check Dictionary Object Owner', rule_expr => 'DVSYS.DV_DICT_OBJ_OWNER = ''JSMITH'''); END; /
The DV_DICT_OBJ_NAME function returns the name of the dictionary object on which the database operation occurred. The return type is VARCHAR2.
DVSYS.DV_DICT_OBJ_NAME () RETURN VARCHAR2;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Check Dictionary Object Name', rule_expr => 'DVSYS.DV_DICT_OBJ_NAME = ''SALES'''); END; /
The DV_SQL_TEXT function returns the first 4000 characters of SQL text of the database statement used in the operation The return type is VARCHAR2.
DVSYS.DV_SQL_TEXT () RETURN VARCHAR2;
None.
BEGIN DBMS_MACADM.CREATE_RULE( rule_name => 'Check SQL Text', rule_expr => 'DVSYS.DV_SQL_TEXT = ''SELECT SALARY FROM HR.EMPLOYEES'''); END; /