14 Oracle Database Vault Rule Set APIs

You can use the DBMS_MACADM PL/SQL package and a set of Oracle Database Vault rule functions to manage rule sets.

Topics:

DBMS_MACADM Rule Set Procedures

The DBMS_MACADM rule set procedures enable you to configure both rule sets and individual rules that go within these rule sets.

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
ADD_RULE_TO_RULE_SET Procedure	Adds a rule to a rule set
CREATE_RULE Procedure	Creates a rule
CREATE_RULE_SET Procedure	Creates a rule set
DELETE_RULE Procedure	Deletes a rule
DELETE_RULE_FROM_RULE_SET Procedure	Deletes a rule from a rule set
DELETE_RULE_SET Procedure	Deletes a rule set
RENAME_RULE Procedure	Renames a rule. The name change takes effect everywhere the rule is used.
RENAME_RULE_SET Procedure	Renames a rule set. The name change takes effect everywhere the rule set is used.
UPDATE_RULE Procedure	Updates a rule
UPDATE_RULE_SET Procedure	Updates a rule set

ADD_RULE_TO_RULE_SET Procedure

The ADD_RULE_TO_RULE_SET procedure adds rule to a rule set; you can enable having the rule checked when the rule set is evaluated.

Syntax

DBMS_MACADM.ADD_RULE_TO_RULE_SET(
  rule_set_name  IN VARCHAR2, 
  rule_name      IN VARCHAR2, 
  rule_order     IN NUMBER, 
  enabled        IN VARCHAR2);

Parameters

Table 14-2 ADD_RULE_TO_RULE_SET Parameters

Parameter	Description
`rule_set_name`	Rule set name. To find existing rule sets in the current database instance, query the `DVSYS.DBA_DV_RULE_SET` view, described in DVSYS.DBA_DV_RULE_SET View.
`rule_name`	Rule to add to the rule set. To find existing rules, query the `DVSYS.DBA_DV_RULE` view, described in DVSYS.DBA_DV_RULE View. To find rules that have been associated with rule sets, use `DVSYS.DBA_DV_RULE_SET_RULE`, described in DVSYS.DBA_DV_RULE View.
`rule_order`	Does not apply to this release, but you must include a value for the `ADD_RULE_TO_RULE_SET` procedure to work. Enter `1`.
`enabled`	Optional. Determines whether the rule should be checked when the rule set is evaluated. Possible values are: `DBMS_MACUTL.G_YES` (Yes; default). Enables the rule to be checked during the rule set evaluation. `DBMS_MACUTL.G_NO` (No). Prevents the rule from being checked during the rule set evaluation. See Table 19-1 for more information.

Examples

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;
/

CREATE_RULE Procedure

The CREATE_RULE procedure creates a rule, which afterwards, can be added to a rule set.

Syntax

DBMS_MACADM.CREATE_RULE(
  rule_name  IN VARCHAR2, 
  rule_expr  IN VARCHAR2);

Parameters

Table 14-3 CREATE_RULE Parameters

Parameter Description

Parameter	Description
`rule_name`	Rule name, up to 128 characters in mixed-case. Spaces are allowed. To find existing rules in the current database instance, query the `DVSYS.DBA_DV_RULE` view, described in DVSYS.DBA_DV_RULE View. To find rules that have been associated with rule sets, query `DVSYS.DBA_DV_RULE_SET_RULE`, described in DVSYS.DBA_DV_RULE_SET_RULE View.
`rule_expr`	PL/SQL `BOOLEAN` expression. 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.

rule_name

Rule name, up to 128 characters in mixed-case. Spaces are allowed.

To find existing rules in the current database instance, query the DVSYS.DBA_DV_RULE view, described in DVSYS.DBA_DV_RULE View.

To find rules that have been associated with rule sets, query DVSYS.DBA_DV_RULE_SET_RULE, described in DVSYS.DBA_DV_RULE_SET_RULE View.

rule_expr

PL/SQL BOOLEAN expression.

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.

Examples

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;
/

CREATE_RULE_SET Procedure

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.

Syntax

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);

Parameters

Table 14-4 CREATE_RULE_SET Parameters

Parameter	Description
`rule_set_name`	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 `DVSYS.DBA_DV_RULE_SET` view, described in DVSYS.DBA_DV_RULE_SET View.
`description`	Description of the purpose of the rule set, up to 1024 characters in mixed-case.
`enabled`	`DBMS_MACUTL.G_YES` (Yes) enables the rule set; `DBMS_MACUTL.G_NO` (No) disables it. The default is `DBMS_MACUTL.G_YES`.
`eval_options`	If you plan to assign multiple rules to the rule set, enter one of the following settings: `DBMS_MACUTL.G_RULESET_EVAL_ALL`: All rules in the rule set must evaluate to true for the rule set itself to evaluate to true (default). `DBMS_MACUTL.G_RULESET_EVAL_ANY`: At least one rule in the rule set must evaluate to true for the rule set itself to evaluate to true.
`audit_options`	Select one of the following settings: `DBMS_MACUTL.G_RULESET_AUDIT_OFF`: Disables auditing for the rule set (default) `DBMS_MACUTL.G_RULESET_AUDIT_FAIL`: Creates an audit record when a rule set violation occurs `DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS`: Creates an audit record for a successful rule set evaluation `DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS`: Creates an audit record for both successful and failed rule set evaluations
`fail_options`	Options for reporting errors: `DBMS_MACUTL.G_RULESET_FAIL_SHOW`: Shows an error message (default) `DBMS_MACUTL.G_RULESET_FAIL_SILENT`: Does not show an error message
`fail_message`	Enter an error message for failure, up to 80 characters in mixed-case, to associate with the fail code you specify for `fail_code`.
`fail_code`	Enter a number in the range of -20000 to -20999 or 20000 to 20999 to associate with the `fail_message` parameter.
`handler_options`	Select one of the following settings: `DBMS_MACUTL.G_RULESET_HANDLER_OFF`: Disables error handling (default) `DBMS_MACUTL.G_RULESET_HANDLER_FAIL`: Calls handler on rule set failure `DBMS_MACUTL.G_RULESET_HANDLER_SUCCESS`: Calls handler on rule set success
`handler`	Name of the PL/SQL function or procedure that defines the custom event handler logic.
`is_static`	Optional. Determines how often a rule set is evaluated when it is accessed. The default is `FALSE`. `TRUE`: The rule set is evaluated once during the user session. After that, the value is re-used. `FALSE`: The rule set is evaluated every time.

Example

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;
/

DELETE_RULE Procedure

The DELETE_RULE procedure deletes a rule.

Syntax

DBMS_MACADM.DELETE_RULE(
  rule_name IN VARCHAR2);

Parameter

Table 14-5 DELETE_RULE Parameter

Parameter Description

Parameter	Description
`rule_name`	Rule name. To find existing rules in the current database instance, query the `DVSYS.DBA_DV_RULE` view, described in DVSYS.DBA_DV_RULE View. To find rules that have been associated with rule sets, query `DVSYS.DBA_DV_RULE_SET_RULE`, described in DVSYS.DBA_DV_RULE_SET_RULE View.

rule_name

Rule name.

To find existing rules in the current database instance, query the DVSYS.DBA_DV_RULE view, described in DVSYS.DBA_DV_RULE View.

To find rules that have been associated with rule sets, query DVSYS.DBA_DV_RULE_SET_RULE, described in DVSYS.DBA_DV_RULE_SET_RULE View.

Example

EXEC DBMS_MACADM.DELETE_RULE('Check UPDATE operations');

DELETE_RULE_FROM_RULE_SET Procedure

The DELETE_RULE_FROM_RULE_SET procedure deletes a rule from a rule set.

Syntax

DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
  rule_set_name IN VARCHAR2, 
  rule_name     IN VARCHAR2);

Parameters

Table 14-6 DELETE_RULE_FROM_RULE_SET Parameters

Parameter Description

rule_set_name

Rule set name.

To find existing rule sets in the current database instance, query the DVSYS.DBA_DV_RULE_SET view, described in DVSYS.DBA_DV_RULE_SET View.

rule_name

Rule to remove from the rule set.

To find existing rules in the current database instance, query the DVSYS.DBA_DV_RULE view, described in DVSYS.DBA_DV_RULE View.

To find rules that have been associated with rule sets, query DVSYS.DBA_DV_RULE_SET_RULE, described in DVSYS.DBA_DV_RULE_SET_RULE View.

Example

BEGIN
 DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
  rule_set_name => 'Limit_DBA_Access', 
  rule_name     => 'Check UPDATE operations');
END;
/

DELETE_RULE_SET Procedure

The DELETE_RULE_SET procedure deletes a rule set.

Syntax

DBMS_MACADM.DELETE_RULE_SET(
  rule_set_name IN VARCHAR2);

Parameters

Table 14-7 DELETE_RULE_SET Parameter

Parameter Description

rule_set_name

Rule set name.

To find existing rule sets in the current database instance, query the DVSYS.DBA_DV_RULE_SET view, described in DVSYS.DBA_DV_RULE_SET View.

Example

EXEC DBMS_MACADM.DELETE_RULE_SET('Limit_DBA_Access');

RENAME_RULE Procedure

The RENAME_RULE procedure renames a rule and causes the name change to take effect everywhere the rule is used.

Syntax

DBMS_MACADM.RENAME_RULE(
  rule_name  IN VARCHAR2, 
  new_name   IN VARCHAR2);

Parameters

Table 14-8 RENAME_RULE Parameters

Parameter Description

Parameter	Description
`rule_name`	Current rule name. To find existing rules in the current database instance, query the `DVSYS.DBA_DV_RULE` view, described in DVSYS.DBA_DV_RULE View. To find rules that have been associated with rule sets, query `DVSYS.DBA_DV_RULE_SET_RULE`, described in DVSYS.DBA_DV_RULE_SET_RULE View.
`new_name`	New rule name, up to 128 characters in mixed-case.

rule_name

Current rule name.

To find existing rules in the current database instance, query the DVSYS.DBA_DV_RULE view, described in DVSYS.DBA_DV_RULE View.

To find rules that have been associated with rule sets, query DVSYS.DBA_DV_RULE_SET_RULE, described in DVSYS.DBA_DV_RULE_SET_RULE View.

new_name

New rule name, up to 128 characters in mixed-case.

Example

BEGIN
 DBMS_MACADM.RENAME_RULE(
  rule_name  => 'Check UPDATE operations', 
  new_name   => 'Check Sector 2 Processes');
END; 
/

RENAME_RULE_SET Procedure

The RENAME_RULE_SET procedure renames a rule set and causes the name change to take effect everywhere the rule set is used.

Syntax

DBMS_MACADM.RENAME_RULE_SET(
  rule_set_name IN VARCHAR2, 
  new_name      IN VARCHAR2);

Parameters

Table 14-9 RENAME_RULE_SET Parameters

Parameter Description

Parameter	Description
`rule_set_name`	Current rule set name. To find existing rule sets in the current database instance, query the `DVSYS.DBA_DV_RULE_SET` view, described in DVSYS.DBA_DV_RULE_SET View.
`new_name`	New rule set name, up to 128 characters in mixed-case. Spaces are allowed.

rule_set_name

Current rule set name.

To find existing rule sets in the current database instance, query the DVSYS.DBA_DV_RULE_SET view, described in DVSYS.DBA_DV_RULE_SET View.

new_name

New rule set name, up to 128 characters in mixed-case. Spaces are allowed.

Example

BEGIN
 DBMS_MACADM.RENAME_RULE_SET(
  rule_set_name => 'Limit_DBA_Access', 
  new_name      => 'Limit Sector 2 Access'); 
END;
/

UPDATE_RULE Procedure

The UPDATE_RULE procedure updates a rule.

Syntax

DBMS_MACADM.UPDATE_RULE(
  rule_name  IN VARCHAR2, 
  rule_expr  IN VARCHAR2);

Parameters

Table 14-10 UPDATE_RULE Parameters

Parameter Description

Parameter	Description
`rule_name`	Rule name. To find existing rules in the current database instance, query the `DVSYS.DBA_DV_RULE` view, described in DVSYS.DBA_DV_RULE View. To find rules that have been associated with rule sets, query `DVSYS.DBA_DV_RULE_SET_RULE`, described in DVSYS.DBA_DV_RULE_SET_RULE View.
`rule_expr`	PL/SQL `BOOLEAN` expression. 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 `DVSYS.DBA_DV_RULE` view.

rule_name

Rule name.

To find existing rules in the current database instance, query the DVSYS.DBA_DV_RULE view, described in DVSYS.DBA_DV_RULE View.

To find rules that have been associated with rule sets, query DVSYS.DBA_DV_RULE_SET_RULE, described in DVSYS.DBA_DV_RULE_SET_RULE View.

rule_expr

PL/SQL BOOLEAN expression.

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 DVSYS.DBA_DV_RULE view.

Example

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;
/

UPDATE_RULE_SET Procedure

The UPDATE_RULE_SET procedure updates a rule set.

Syntax

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);

Parameters

Table 14-11 UPDATE_RULE_SET Parameters

Parameter	Description
`rule_set_name`	Rule set name. To find existing rule sets in the current database instance, query the `DVSYS.DBA_DV_RULE_SET` view, described in DVSYS.DBA_DV_RULE_SET View.
`description`	Description of the purpose of the rule set, up to 1024 characters in mixed-case.
`enabled`	`DBMS_MACUTL.G_YES` (Yes) enables rule set checking; `DBMS_MACUTL.G_NO` (No) disables it. The default for the `enabled` setting is the previously set value, which you can find by querying the `DVSYS.DBA_DV_RULE_SET` data dictionary view.
`eval_options`	If you plan to assign multiple rules to the rule set, enter one of the following settings: `DBMS_MACUTL.G_RULESET_EVAL_ALL`: All rules in the rule set must evaluate to true for the rule set itself to evaluate to true. `DBMS_MACUTL.G_RULESET_EVAL_ANY`: At least one rule in the rule set must evaluate to true for the rule set itself to evaluate to true. The default for `eval_options` is the previously set value, which you can find by querying the `DVSYS.DBA_DV_RULE_SET` data dictionary view.
`audit_options`	Select one of the following settings: `DBMS_MACUTL.G_RULESET_AUDIT_OFF`: Disables auditing for the rule set `DBMS_MACUTL.G_RULESET_AUDIT_FAIL`: Creates an audit record when a rule set violation occurs `DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS`: Creates an audit record for a successful rule set evaluation `DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS`: Creates an audit record for both successful and failed rule set evaluations The default for `audit_options` is the previously set value, which you can find by querying the `DVSYS.DBA_DV_RULE_SET` data dictionary view.
`fail_options`	Options for reporting errors: `DBMS_MACUTL.G_RULESET_FAIL_SHOW`: Shows an error message. `DBMS_MACUTL.G_RULESET_FAIL_SILENT`: Does not show an error message. The default for `fail_options` is the previously set value, which you can find by querying the `DVSYS.DBA_DV_RULE_SET` data dictionary view.
`fail_message`	Error message for failure, up to 80 characters in mixed-case, to associate with the fail code you specify for `fail_code`.
`fail_code`	Enter a number in the range of -20000 to -20999 or 20000 to 20999 to associate with the `fail_message` parameter.
`handler_options`	Select one of the following settings: `DBMS_MACUTL.G_RULESET_HANDLER_OFF`: Disables error handling. `DBMS_MACUTL.G_RULESET_HANDLER_FAIL`: Call handler on rule set failure. `DBMS_MACUTL.G_RULESET_HANDLER_SUCCESS`: Call handler on rule set success. The default for `handler_options` is the previously set value, which you can find by querying the `DVSYS.DBA_DV_RULE_SET` data dictionary view.
`handler`	Name of the PL/SQL function or procedure that defines the custom event handler logic.
`is_static`	Optional. Determines how often a rule set is evaluated when it is accessed by a SQL statement. `TRUE`: The rule set is evaluated once during the user session. After that, the value is re-used. `FALSE`: The rule set evaluated each time a SQL statement accesses it (default).

Example

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;
/

Oracle Database Vault PL/SQL Rule Set Functions

Oracle Database Vault provides functions to use in rule sets to inspect the SQL statement that the rule set protects.

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
DVSYS.DV_SYSEVENT Function	Returns the system event firing the rule set
DVSYS.DV_LOGIN_USER Function	Returns the login user name
DVSYS.DV_INSTANCE_NUM Function	Returns the database instance number
DVSYS.DV_DATABASE_NAME Function	Returns the database name
DVSYS.DV_DICT_OBJ_TYPE Function	Returns the type of the dictionary object on which the database operation occurred (for example, table, procedure, view)
DVSYS.DV_DICT_OBJ_OWNER Function	Returns the owner of the dictionary object on which the database operation occurred
DVSYS.DV_DICT_OBJ_NAME Function	Returns the name of the dictionary object on which the database operation occurred
DVSYS.DV_SQL_TEXT Function	Returns the first 4000 characters of SQL text of the database statement used in the operation

DVSYS.DV_SYSEVENT Function

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.

Syntax

DVSYS.DV_SYSEVENT ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get System Event Firing the Maintenance Rule Set',
  rule_expr => 'DVSYS.DV_SYSEVENT = ''CREATE''');
END;
/

DVSYS.DV_LOGIN_USER Function

The DV_LOGIN_USER function returns the login user name, in VARCHAR2 data type.

Syntax

DVSYS.DV_LOGIN_USER ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check System Login User Name',
  rule_expr => 'DVSYS.DV_LOGIN_USER = ''SEBASTIAN''');
END;
/

DVSYS.DV_INSTANCE_NUM Function

The DV_INSTANCE_NUM function returns the database instance number, in NUMBER data type.

Syntax

DVSYS.DV_INSTANCE_NUM ()
RETURN NUMBER;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check Database Instance Number',
  rule_expr => 'DVSYS.DV_INSTANCE_NUM BETWEEN 6 AND 9');
END;
/

DVSYS.DV_DATABASE_NAME Function

The DV_DATABASE_NAME function returns the database name, in VARCHAR2 data type.

Syntax

DVSYS.DV_DATABASE_NAME ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check Database Name',
  rule_expr => 'DVSYS.DV_DATABASE_NAME = ''ORCL''');
END;
/

DVSYS.DV_DICT_OBJ_TYPE Function

The DV_DICT_OBJ_TYPE function returns the type of the dictionary object on which the database operation occurred.

For example, dictionary objects it returns are table, procedure, or view. The return type is VARCHAR2.

Syntax

DVSYS.DV_DICT_OBJ_TYPE ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check Dictionary Object Type',
  rule_expr => 'DVSYS.DV_DICT_OBJ_TYPE IN (''TABLE'', ''VIEW'')');
END;
/

DVSYS.DV_DICT_OBJ_OWNER Function

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.

Syntax

DVSYS.DV_DICT_OBJ_OWNER ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check Dictionary Object Owner',
  rule_expr => 'DVSYS.DV_DICT_OBJ_OWNER = ''JSMITH''');
END;
/

DVSYS.DV_DICT_OBJ_NAME Function

The DV_DICT_OBJ_NAME function returns the name of the dictionary object on which the database operation occurred.

The return type is VARCHAR2.

Syntax

DVSYS.DV_DICT_OBJ_NAME ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check Dictionary Object Name',
  rule_expr => 'DVSYS.DV_DICT_OBJ_NAME = ''SALES''');
END;
/

DVSYS.DV_SQL_TEXT Function

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.

Syntax

DVSYS.DV_SQL_TEXT ()
RETURN VARCHAR2;

Parameters

None

Example

BEGIN 
 DBMS_MACADM.CREATE_RULE(
  rule_name => 'Check SQL Text',
  rule_expr => 'DVSYS.DV_SQL_TEXT = ''SELECT SALARY FROM HR.EMPLOYEES''');
END;
/