13 Using the DVSYS.DBMS_MACUTL Package

In this chapter:

About the DVSYS.DBMS_MACUTL Package
DVSYS.DBMS_MACUTL Constants
Procedures and Functions Within the DVSYS.DBMS_MACUTL Package

13.1 About the DVSYS.DBMS_MACUTL Package

The DVSYS.DBMS_MACUTL package provides a set of general purpose utility procedures and functions that you can use throughout the application code you write for Oracle Database Vault. This package is available to all users.

13.2 DVSYS.DBMS_MACUTL Constants

In this section:

DVSYS.DBMS_MACUTL Listing of Constants
Examples of Using the DVSYS.DBMS_MACUTL Constants

13.2.1 DVSYS.DBMS_MACUTL Listing of Constants

Table 13-1 summarizes constant (that is, fields) descriptions for the DVSYS.DBMS_MACUTL package. You can use these constants with any of the Oracle Database Vault PL/SQL packages. Many of these constants have equivalents in the Oracle Database Vault package. For example, the enabled parameter, which is available in several procedures, can accept either Y (for Yes) or the constant G_YES. Choosing one over the other is a matter of personal preference. They both have the same end result.

Table 13-1 DVSYS.DBMS_MACUTL Listing of Constants

Constant Name	Data Type	Description
`G_ALL_OBJECT`	`VARCHAR2(1)`	Used with the realm API `object_name` and `object_type` parameters as a wildcard to indicate all object names or all object types.
`G_AUDIT_ALWAYS`	`NUMBER`	Used with the factor API `audit_options` parameter to enable an audit.
`G_AUDIT_OFF`	`NUMBER`	Used with the factor API `audit_options` parameter to disable auditing.
`G_AUDIT_ON_GET_ERROR`	`NUMBER`	Used with the factor API `audit_options` parameter to audit if the expression specified in the `get_expr` parameter returns an error.
`G_AUDIT_ON_GET_NULL`	`NUMBER`	Used with the factor API `audit_options` parameter to audit if the expression in the `get_expr` field is null.
`G_AUDIT_ON_TRUST_LEVEL_NEG`	`NUMBER`	Used with the factor API `audit_options` parameter to audit if the trust level is negative.
`G_AUDIT_ON_TRUST_LEVEL_NULL`	`NUMBER`	Used with the factor API `audit_options` parameter to audit if no trust level exists.
`G_AUDIT_ON_VALIDATE_ERROR`	`NUMBER`	Used with the factor API `audit_options` parameter to audit if the validation function returns an error.
`G_AUDIT_ON_VALIDATE_FALSE`	`NUMBER`	Used with the factor API `audit_options` parameter to audit if validation function is false.
`G_EVAL_ON_ACCESS`	`NUMBER`	Used with the factor API `eval_options` parameter to re-evaluate the factor each time it is accessed.
`G_EVAL_ON_SESSION`	`NUMBER`	Used with the factor API `eval_options` parameter to evaluate the factor only once, when the user logs in to the session.
`G_FAIL_SILENTLY`	`NUMBER`	Used with the `fail_options` parameter to fail and show no error message.
`G_FAIL_WITH_MESSAGE`	`NUMBER`	Used with the `fail_options` parameter to fail and show an error message.
`G_IDENTIFY_BY_CONSTANT`	`NUMBER`	Used with the factor API `identify_by` parameter: Fixed value in PL/SQL expression defined in the `get_expr` parameter.
`G_IDENTIFY_BY_CONTEXT`	`NUMBER`	Used with the factor API `identify_by` parameter to indicate context.
`G_IDENTIFY_BY_FACTOR`	`NUMBER`	Used with the factor API `identify_by` parameter for subfactors through the `factor_link$` table.
`G_IDENTIFY_BY_METHOD`	`NUMBER`	Used with the factor API `identify_by` parameter: Expression in `get_expr` field
`G_IDENTIFY_BY_RULESET`	`NUMBER`	Used with the factor API `identify_by` parameter: Expression and Rule Set with the `factor_expr$` table
`G_LABELED_BY_FACTORS`	`NUMBER`	Used with the factor API `labeled_by` parameter to derive the label from subfactor and merge algorithm.
`G_LABELED_BY_SELF`	`NUMBER`	Used with the factor API `labeled_by` parameter to label the factor identities.
`G_MAX_SESSION_LABEL`	`VARCHAR2(30)`	This is the highest label a user could set based on the factors. It does not take into account the label for a user.
`G_MIN_POLICY_LABEL`	`VARCHAR2(30)`	The label to which a factor with a null label defaults.
`G_NO`	`VARCHAR2(1)`	Used with the following APIs: The factor API `label_indicator` parameter to indicate that a child factor linked to a parent factor does not contribute to the label of the parent factor in an Oracle Label Security integration. Any API that uses the `enabled` parameter.
`G_OLS_SESSION_LABEL`	`VARCHAR2(30)`	The Oracle Label Security session label for a user at the time `init_session` is run.
`G_REALM_AUDIT_FAIL`	`NUMBER`	Used with the realm API `audit_options` parameter to audit when the realm is violated.
`G_REALM_AUDIT_OFF`	`NUMBER`	Used with the realm API `audit_options` parameter to disable auditing.
`G_REALM_AUDIT_SUCCESS`	`NUMBER`	Used with the realm API `audit_options parameter`: Audit on successful realm access
`G_REALM_AUTH_OWNER`	`NUMBER`	Used with the realm API `auth_options` parameter to set the realm authorization to Owner.
`G_REALM_AUTH_PARTICIPANT`	`NUMBER`	Used with the realm API `auth_options` parameter to set the realm authorization to Participant.
`G_RULESET_AUDIT_FAIL`	`NUMBER`	Used with the rule set API `audit_options` parameter to audit on rule set failure.
`G_RULESET_AUDIT_OFF`	`NUMBER`	Used with the rule set API `audit_options` parameter to disable auditing.
`G_RULESET_AUDIT_SUCCESS`	`NUMBER`	Used with the rule set API `audit_options` parameter to audit on rule set success.
`G_RULESET_EVAL_ALL`	`NUMBER`	Used with the rule set API `eval_options` parameter to enable the rule set to succeed if all rules evaluate to true.
`G_RULESET_EVAL_ANY`	`NUMBER`	Used with the rule set API `eval_options` parameter to succeed if any of the rules evaluate to true.
`G_RULESET_FAIL_SHOW`	`NUMBER`	Used with the rule set API `fail_options` parameter to show an error message if the rule set fails.
`G_RULESET_FAIL_SILENT`	`NUMBER`	Used with the rule set API `fail_options` parameter to not show an error message if the rule set fails.
`G_RULESET_HANDLER_FAIL`	`NUMBER`	Used with the rule set API `handler_options` parameter to call a handler (specified by the `handler` parameter) if the rule set fails.
`G_RULESET_HANDLER_OFF`	`NUMBER`	Used with the rule set API `handler_options` parameter to disable calls to a handler or if no handler is used.
`G_RULESET_HANDLER_SUCCESS`	`NUMBER`	Used with the rule set API `handler_options` parameter to call a handler if the rule set succeeds.
`G_USER_POLICY_LABEL`	`VARCHAR2(30)`	This is what Oracle Label Security has decided the user's label should be set to after factoring in the preceding values.
`G_YES`	`VARCHAR2(1)`	Used with the following APIs: The factor API `label_indicator` parameter to indicate that a child factor linked to a parent factor contributes to the label of the parent factor in an Oracle Label Security integration. Any API that uses the `enabled` parameter.

13.2.2 Examples of Using the DVSYS.DBMS_MACUTL Constants

Example 13-1 shows how to use the G_YES and G_REALM_AUDIT_FAIL DBMS_MACUTL constants when creating a realm.

Example 13-1 Creating a Realm Using DVSYS.DBMS_MACUTL Constants

BEGIN
 DVSYS.DBMS_MACADM.CREATE_REALM(
  realm_name    => 'Performance Statistics Realm',
  description   => 'Realm to measure performance',
  enabled       =>  DVSYS.DBMS_MACUTL.G_YES,
  audit_options =>  DVSYS.DBMS_MACUTL.G_REALM_AUDIT_FAIL);
END;

Example 13-2 shows how to use several DVSYS.DBMS_MACUTL constants when creating a rule set.

Example 13-2 Creating a Rule Set Using DVSYS.DBMS_MACUTL Constants

BEGIN
 DVSYS.DBMS_MACADM.CREATE_RULE_SET(
  rule_set_name    => 'Limit_DBA_Access', 
  description      => 'DBA access through predefined processes', 
  enabled          => 'Y',
  eval_options     => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ALL,
  audit_options    => POWER(2,0),
  fail_options     => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,
  fail_message     => 'Rule Set Limit_DBA_Access has failed.',
  fail_code        => -22220,
  handler_options  => POWER(2,0), 
  handler          => 'dbavowner.email_alert');
END;

Example 13-3 shows how to use constants when creating a factor.

Example 13-3 Creating a Factor Using DVSYS.DBMS_MACUTL Constants

BEGIN
 DVSYS.DBMS_MACADM.CREATE_FACTOR(
  factor_name       => 'Sector2_DB', 
  factor_type_name  => 'Instance', 
  description       => ' ', 
  rule_set_name     => 'DB_access', 
  get_expr          => 'UPPER(SYS_CONTEXT('USERENV','DB_NAME'))', 
  validate_expr     => 'dbavowner.check_db_access', 
  identify_by       => 2, 
  labeled_by        => DVSYS.DBMS_MACUTL.G_LABELED_BY_SELF, 
  eval_options      => DVSYS.DBMS_MACUTL.G_EVAL_ON_SESSION, 
  audit_options     => 0, 
  fail_options      => DVSYS.DBMS_MACUTL.G_FAIL_SILENTLY; 
END;

13.3 Procedures and Functions Within the DVSYS.DBMS_MACUTL Package

Table 13-2 lists the procedures and functions in the DVSYS.DBMS_MACUTL package. You can use these procedures or functions as stand-alone code, or within rule expressions. The examples in this section show a mixture of using both.

Table 13-2 DVSYS.DBMS_MACUTL Utility Functions

Procedure or Function	Descriptions
CHECK_DVSYS_DML_ALLOWED Procedure	Verifies that public-packages are not being bypassed by users updating the Oracle Database Vault configuration.
GET_CODE_VALUE Function	Looks up the value for a code within a code group; returns a `VARCHAR2` value.
GET_SECOND Function	Returns the seconds in Oracle SS format (00-59); returns a `NUMBER` value. Useful for rule expressions based on time data.
GET_MINUTE Function	Returns the minute in Oracle MI format (00–59); returns a `NUMBER` value. Useful for rule expressions based on time data.
GET_HOUR Function	Returns the month in Oracle HH24 format (00–23); returns a `NUMBER` value. Useful for rule expressions based on time data.
GET_DAY Function	Returns the day in Oracle DD format (01–31); returns a `NUMBER` value. Useful for rule expressions based on time data.
GET_MONTH Function	Returns the month in Oracle MM format (01–12); returns a `NUMBER` value. Useful for rule expressions based on time data.
GET_YEAR Function	Returns the year in Oracle YYYY format (0001–9999); returns a `NUMBER` value. Useful for rule expressions based on time data.
GET_SQL_TEXT Function	Concatenates the elements of `ora_name_list_t` into a single `VARCHAR2` value; returns a `VARCHAR2` value.
IS_ALPHA Function	Checks whether the character is alphabetic; returns a `BOOLEAN` value.
IS_DIGIT Function	Checks whether the character is numeric; returns a `BOOLEAN` value.
IS_DVSYS_OWNER Function	Determines whether a user is authorized to manage the Oracle Database Vault configuration; returns a `BOOLEAN` value.
IS_OLS_INSTALLED Function	Returns an indicator as to whether or not Oracle Label Security is installed; returns a `BOOLEAN` value.
IS_OLS_INSTALLED_VARCHAR Function	Returns an indicator as to whether or not Oracle Label Security is installed; returns a `VARCHAR2` value.
USER_HAS_OBJECT_PRIVILEGE Function	Checks whether a user or role may access an object through an object privilege grant; returns a `BOOLEAN` value.
USER_HAS_ROLE Function	Checks whether a user has a role privilege, directly or indirectly (through another role); returns a `BOOLEAN` value.
USER_HAS_ROLE_VARCHAR Function	Checks whether a user has a role privilege, directly or indirectly (through another role); returns a `VARCHAR2` value.
USER_HAS_SYSTEM_PRIVILEGE Function	Checks whether a user has a system privilege, directly or indirectly (through a role ); returns a `BOOLEAN` value.

13.3.1 CHECK_DVSYS_DML_ALLOWED Procedure

This procedure verifies that public packages are not being bypassed by users updating the Oracle Database Vault configuration.

Syntax

CHECK_DVSYS_DML_ALLOWED(
  p_user VARCHAR2 DEFAULT USER);

Parameter

Table 13-3 CHECK_DVSYS_DML_ALLOWED Parameter

Parameter Description

Parameter	Description
`p_user`	User performing the operation. To find existing users in the current database instance, query they following views: `DBA_USERS`: Finds available users for the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View". `DVSYS.DBA_DV_ROLE`: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".

p_user

User performing the operation.

To find existing users in the current database instance, query they following views:

DBA_USERS: Finds available users for the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".
DVSYS.DBA_DV_ROLE: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".

Example

User SYSTEM fails the check:

SQL> EXEC DVSYS.DBMS_MACUTL.CHECK_DVSYS_DML_ALLOWED('system'); 

ERROR at line 1:
ORA-47920: Authorization failed for user system to perform this operation
ORA-06512: at "DVSYS.DBMS_MACUTL", line 23
ORA-06512: at "DVSYS.DBMS_MACUTL", line 372
ORA-06512: at "DVSYS.DBMS_MACUTL", line 508
ORA-06512: at "DVSYS.DBMS_MACUTL", line 572
ORA-06512: at line 1

User dbvowner, who has the DV_OWNER role, passes the check:

SQL> EXEC DVSYS.DBMS_MACUTL.CHECK_DVSYS_DML_ALLOWED('dbvowner');

PL/SQL procedure successfully completed.

13.3.2 GET_CODE_VALUE Function

This function looks up the value for a code within a code group, and then returns a VARCHAR2 value.

Syntax

GET_CODE_VALUE(
  p_code_group VARCHAR2, 
  p_code       VARCHAR2)
RETURNS VARCHAR2;

Parameters

Table 13-4 GET_CODE_VALUE Parameters

Parameter Description

Parameter	Description
`p_code_group`	Code group, for example, `AUDIT_EVENTS` or `BOOLEAN`. To find available code groups in the current database instance, query the `DVSYS.DBA_DV_CODE` view, described in "DBA_DV_CODE View".
`p_code`	ID of the code. This ID is listed when you run the `DVSYS.DBA_DV_CODE` view.

p_code_group

Code group, for example, AUDIT_EVENTS or BOOLEAN.

To find available code groups in the current database instance, query the DVSYS.DBA_DV_CODE view, described in "DBA_DV_CODE View".

p_code

ID of the code.

This ID is listed when you run the DVSYS.DBA_DV_CODE view.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get Label Algorithm for Maximum Level/Union/Null',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_CODE_VALUE(''LABEL_ALG'', ''HUN'')');
END;

13.3.3 GET_SECOND Function

This function returns the seconds in Oracle SS (seconds) format (00–59), and then returns a NUMBER value. It is useful for rule expressions based on time data.

Syntax

GET_SECOND(
  p_date DATE DEFAULT SYSDATE)
RETURNS NUMBER;

Parameter

Table 13-5 GET_SECOND Parameter

Parameter Description

Parameter	Description
`p_date`	Date in SS format, for example: `59`. If you do not specify a date, Oracle Database Vault uses the Oracle Database `SYSDATE` function to retrieve the current date and time set for the operating system on which the database resides.

p_date

Date in SS format, for example: 59.

If you do not specify a date, Oracle Database Vault uses the Oracle Database SYSDATE function to retrieve the current date and time set for the operating system on which the database resides.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get the Second',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_SECOND(59);
END;

13.3.4 GET_MINUTE Function

This function returns the minute in Oracle MI (minute) format (00–59); returns a NUMBER value. Useful for rule expressions based on time data.

Syntax

GET_MINUTE(
  p_date DATE DEFAULT SYSDATE)
RETURNS NUMBER;

Parameter

Table 13-6 GET_MINUTE Parameter

Parameter Description

Parameter	Description
`p_date`	Date in MI format, for example, `30` (as is 2:30). If you do not specify a date, Oracle Database Vault uses the Oracle Database `SYSDATE` function to retrieve the current date and time set for the operating system on which the database resides.

p_date

Date in MI format, for example, 30 (as is 2:30).

If you do not specify a date, Oracle Database Vault uses the Oracle Database SYSDATE function to retrieve the current date and time set for the operating system on which the database resides.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get the Minute',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_MINUTE(30);
END;

13.3.5 GET_HOUR Function

This function returns the hour in Oracle HH24 (hour) format (00–23); returns a NUMBER value. Useful for rule expressions based on time data.

Syntax

GET_HOUR(
  p_date DATE DEFAULT SYSDATE)
RETURNS NUMBER;

Parameter

Table 13-7 GET_HOUR Parameter

Parameter Description

Parameter	Description
`p_date`	Date in HH24 format, for example, `14` for 2:00 p.m. If you do not specify a date, Oracle Database Vault uses the Oracle Database `SYSDATE` function to retrieve the current date and time set for the operating system on which the database resides.

p_date

Date in HH24 format, for example, 14 for 2:00 p.m.

If you do not specify a date, Oracle Database Vault uses the Oracle Database SYSDATE function to retrieve the current date and time set for the operating system on which the database resides.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get the Hour',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_HOUR(14);
END;

13.3.6 GET_DAY Function

This function returns the day in Oracle DD (day) format (01–31); returns a NUMBER value. It is useful for rule expressions based on time data.

Syntax

GET_DAY(
  p_date DATE DEFAULT SYSDATE)
RETURNS NUMBER;

Parameter

Table 13-8 GET_DAY Parameter

Parameter Description

Parameter	Description
`p_date`	Date in DD format, for example, `01` for the first day of the month. If you do not specify a date, Oracle Database Vault uses the Oracle Database `SYSDATE` function to retrieve the current date and time set for the operating system on which the database resides.

p_date

Date in DD format, for example, 01 for the first day of the month.

If you do not specify a date, Oracle Database Vault uses the Oracle Database SYSDATE function to retrieve the current date and time set for the operating system on which the database resides.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get the Day',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_DAY();
END;

13.3.7 GET_MONTH Function

This function returns the month in Oracle MM (month) format (01–12); returns a NUMBER value. Useful for rule expressions based on time data.

Syntax

GET_MONTH(
  p_date DATE DEFAULT SYSDATE)
RETURNS NUMBER;

Parameter

Table 13-9 GET_MONTH Parameter

Parameter Description

Parameter	Description
`p_date`	Date in MM format, for example, `08` for August. If you do not specify a date, Oracle Database Vault uses the Oracle Database `SYSDATE` function to retrieve the current date and time set for the operating system on which the database resides.

p_date

Date in MM format, for example, 08 for August.

If you do not specify a date, Oracle Database Vault uses the Oracle Database SYSDATE function to retrieve the current date and time set for the operating system on which the database resides.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get the Month',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_MONTH(08);
END;

13.3.8 GET_YEAR Function

This function returns the year in Oracle YYYY (year) format (0001–9999); returns a NUMBER value. Useful for rule expressions based on time data.

Syntax

GET_YEAR(
  p_date DATE DEFAULT SYSDATE)
RETURNS NUMBER;

Parameter

Table 13-10 GET_YEAR Parameter

Parameter Description

Parameter	Description
`p_date`	Date in YYYY format, for example, `1984`. If you do not specify a date, Oracle Database Vault uses the `SYSDATE` function to retrieve the current date and time set for the operating system on which the database resides.

p_date

Date in YYYY format, for example, 1984.

If you do not specify a date, Oracle Database Vault uses the SYSDATE function to retrieve the current date and time set for the operating system on which the database resides.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get the Year',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_YEAR(1984);
END;

13.3.9 GET_SQL_TEXT Function

This function concatenates the elements of ora_name_list_t into a single VARCHAR2 value, and then returns a VARCHAR2 value.

Syntax

GET_SQL_TEXT(
  p_sql_text ora_name_list_t)
RETURNS VARCHAR2;

Parameters

Table 13-11 GET_SQL_TEXT Parameter

Parameter	Description
`p_sql_text`	Table of `VARCHAR2` strings representing SQL text, for example, `SELECT`, `DROP TABLE`, and so on.

Example

BEGIN 
 DVSYS.DBMS_MACADM.CREATE_RULE(
  rule_name => 'Get SQL strings',
  rule_expr => 'DVSYS.DBMS_MACUTL.GET_SQL_TEXT(sec_mgr.sql_strings);
END;

13.3.10 IS_ALPHA Function

This function checks whether the character is alphabetic, and then returns a BOOLEAN value. IS_ALPHA returns TRUE if the character is alphabetic.

Syntax

IS_ALPHA(
  c VARCHAR2)
RETURNS BOOLEAN;

Parameter

Table 13-12 IS_ALPHA Parameter

Parameter	Description
`c`	String with one character

Example

SET SERVEROUTPUT ON
BEGIN 
 IF DVSYS.DBMS_MACUTL.IS_ALPHA('z')  
  THEN DBMS_OUTPUT.PUT_LINE('The alphabetic character was found');
 ELSE 
  DBMS_OUTPUT.PUT_LINE('No alphbetic characters today.'); 
 END IF; 
END;

13.3.11 IS_DIGIT Function

This function checks whether the character is numeric, and then returns a BOOLEAN value. IS_DIGIT returns TRUE if the character is a digit.

Syntax

IS_DIGIT(
  c VARCHAR2)
RETURNS BOOLEAN;

Parameter

Table 13-13 IS_DIGIT Parameter

Parameter	Description
`c`	String with one character

Example

SET SERVEROUTPUT ON
BEGIN 
 IF DVSYS.DBMS_MACUTL.IS_DIGIT('7')  
  THEN DBMS_OUTPUT.PUT_LINE('The numeric character was found');
 ELSE 
  DBMS_OUTPUT.PUT_LINE('No numeric characters today.'); 
 END IF; 
END;

13.3.12 IS_DVSYS_OWNER Function

This function determines whether a user is authorized to manage the Oracle Database Vault configuration, and then returns a BOOLEAN value. IS_DVSYS_OWNER returns TRUE if the user is authorized.

Syntax

IS_DVSYS_OWNER(
  p_user VARCHAR2 DEFAULT USER)
RETURNS BOOLEAN;

Parameter

Table 13-14 IS_DVSYS_OWNER Parameter

Parameter Description

Parameter	Description
`p_user`	User to check. To find existing users, query they following views: `DBA_USERS`: Finds available users for the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View". `DVSYS.DBA_DV_ROLE`: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".

p_user

User to check.

To find existing users, query they following views:

DBA_USERS: Finds available users for the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".
DVSYS.DBA_DV_ROLE: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".

Example

SET SERVEROUTPUT ON
BEGIN 
 IF DVSYS.DBMS_MACUTL.IS_DVSYS_OWNER('PSMITH')  
  THEN DBMS_OUTPUT.PUT_LINE('PSMITH is authorized to manage Database Vault.');
 ELSE 
  DBMS_OUTPUT.PUT_LINE('PSMITH is not authorized to manage Database Vault.'); 
 END IF; 
END;

13.3.13 IS_OLS_INSTALLED Function

This function returns an indicator as to whether or not Oracle Label Security is installed, and then returns a TRUE or FALSE BOOLEAN value. If Oracle Label Security is installed, IS_OLS_INSTALLED returns TRUE.

Syntax

IS_OLS_INSTALLED()
RETURNS BOOLEAN;

Parameters

None.

Example

SET SERVEROUTPUT ON
BEGIN 
 IF DVSYS.DBMS_MACUTL.IS_OLS_INSTALLED()  
  THEN DBMS_OUTPUT.PUT_LINE('OLS is installed');
 ELSE 
  DBMS_OUTPUT.PUT_LINE('OLS is not installed'); 
 END IF; 
END;

13.3.14 IS_OLS_INSTALLED_VARCHAR Function

This function returns an indicator as to whether or not Oracle Label Security is installed, and then returns a Y or N VARCHAR2 value. If Oracle Label Security is installed, IS_OLS_INSTALLED_VARCHAR returns Y.

Syntax

IS_OLS_INSTALLED_VARCHAR()
RETURNS VARCHAR2;

Parameters

None.

13.3.15 USER_HAS_OBJECT_PRIVILEGE Function

This function checks whether a user or role may access an object through an object privilege grant, and then returns a BOOLEAN value. If the user or role has object privileges, then USER_HAS_OBJECT_PRIVILEGE returns TRUE.

Syntax

USER_HAS_OBJECT_PRIVILEGE(
  p_user          VARCHAR2, 
  p_object_owner  VARCHAR2, 
  p_object_name   VARCHAR2, 
  p_privilege     VARCHAR2)
RETURNS BOOLEAN;

Parameters

Table 13-15 USER_HAS_OBJECT_PRIVILEGE Parameters

Parameter	Description
`p_user`	User or role to check. To find existing users, query they following views: `DBA_USERS`: Finds available users for the current database instance. See Oracle Database Reference. `DBA_ROLES`: Finds available roles in the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View". `DVSYS.DBA_DV_ROLE`: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".
`p_object_owner`	Object owner. To find the available users, query they `DBA_USERS` view, described in Oracle Database Reference. To find the authorization of a particular user, query they `DVA_DV_REALM_AUTH` view, described in "DBA_DV_REALM_AUTH View".
`p_object_name`	Object name. To find the available objects, query they `ALL_OBJECTS` view, described in Oracle Database Reference. To find objects that are secured by existing realms, query they `DVSYS.DBA_DV_REALM_OBJECT` view, described in "DBA_DV_REALM_OBJECT View".
`p_privilege`	Object privilege, for example, `SELECT`, `UPDATE`, `INSERT`, and so on. To find privileges for a database account excluding `PUBLIC` privileges, query they `DVSYS.DBA_DV_USER_PRIVS` view, described in "DBA_DV_USER_PRIVS View". To find all privileges for a database account, use `DVSYS.DBA_DV_USER_PRIVS_ALL`, described in "DBA_DV_USER_PRIVS_ALL View".

Example

SET SERVEROUTPUT ON
BEGIN
 IF DVSYS.DBMS_MACUTL.USER_HAS_OBJECT_PRIVILEGE(
   'SECTOR2_APP_MGR', 'OE', 'ORDERS', 'SELECT, UPDATE')
  THEN DBMS_OUTPUT.PUT_LINE('SECTOR2_APP_MGR has privileges.');
   ELSE 
  DBMS_OUTPUT.PUT_LINE('SECTOR2_APP_MGR does not have privileges.'); 
 END IF;
END;

13.3.16 USER_HAS_ROLE Function

This function checks whether a user has a role privilege, directly or indirectly (through another role), and then returns a BOOLEAN value. If the user has a role privilege, then USER_HAS_ROLE returns TRUE.

Syntax

USER_HAS_ROLE(
  p_role VARCHAR2, 
  p_user VARCHAR2 DEFAULT USER)
RETURNS BOOLEAN;

Parameters

Table 13-16 USER_HAS_ROLE Parameters

Parameter Description

Parameter	Description
`p_role`	Role privilege to check. To find existing roles, query they following views: `DBA_ROLES`: Finds available roles in the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View". `DVSYS.DBA_DV_ROLE`: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".
`p_user`	User to check. To find existing users, query they following views: `DBA_USERS`: Finds available users for the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "Oracle Database Vault Data Dictionary Views".

p_role

Role privilege to check.

To find existing roles, query they following views:

DBA_ROLES: Finds available roles in the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".
DVSYS.DBA_DV_ROLE: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".

p_user

User to check.

To find existing users, query they following views:

DBA_USERS: Finds available users for the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "Oracle Database Vault Data Dictionary Views".

Example

SET SERVEROUTPUT ON
BEGIN
 IF DVSYS.DBMS_MACUTL.USER_HAS_ROLE('PSMITH', 'SECTOR2_APP_MGR')
  THEN DBMS_OUTPUT.PUT_LINE('User PSMITH has the SECTOR2_APP_MGR role');
   ELSE 
  DBMS_OUTPUT.PUT_LINE('User PSMITH does not have the SECTOR2_APP_MGR role.'); 
 END IF;
END;

13.3.17 USER_HAS_ROLE_VARCHAR Function

This function checks whether a user has a role privilege, directly or indirectly (through another role), and then returns a VARCHAR2 value. If the user has the role privilege specified, then USER_HAS_ROLE_VARCHAR returns Y.

Syntax

USER_HAS_ROLE_VARCHAR(
  p_role VARCHAR2, 
  p_user VARCHAR2 DEFAULT USER)
RETURNS VARCHAR2;

Parameters

Table 13-17 USER_HAS_ROLE_VARCHAR Parameters

Parameter Description

Parameter	Description
`p_role`	Role to check. To find existing roles, query they following views: `DBA_ROLES`: Finds available roles in the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View". `DVSYS.DBA_DV_ROLE`: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".
`p_user`	User to check. To find existing users, query they following views: `DBA_USERS`: Finds available users for the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".

p_role

Role to check.

To find existing roles, query they following views:

DBA_ROLES: Finds available roles in the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".
DVSYS.DBA_DV_ROLE: Finds existing secure application roles used in privilege management. See "DBA_DV_ROLE View".

p_user

User to check.

To find existing users, query they following views:

DBA_USERS: Finds available users for the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".

13.3.18 USER_HAS_SYSTEM_PRIVILEGE Function

This function checks whether a user has a system privilege, directly or indirectly (through a role), and then returns a BOOLEAN value. If the user has the system privilege specified, then USER_HAS_SYSTEM_PRIVILEGE returns TRUE.

Syntax

USER_HAS_SYSTEM_PRIVILEGE(
  p_privilege  VARCHAR2, 
  p_user       VARCHAR2 DEFAULT USER)
RETURNS BOOLEAN;

Parameters

Table 13-18 USER_HAS_SYSTEM_PRIVILEGE Parameters

Parameter Description

Parameter	Description
`p_privilege`	System privilege to check for. To find privileges for a database account excluding `PUBLIC` privileges, query the `DVSYS.DBA_DV_USER_PRIVS` view, described in "DBA_DV_USER_PRIVS View". To find all privileges for a database account, use `DVSYS.DBA_DV_USER_PRIVS_ALL`, described in "DBA_DV_USER_PRIVS_ALL View".
`p_user`	User to check. To find existing users, query they following views: `DBA_USERS`: Finds available users for the current database instance. See Oracle Database Reference. `DVA_DV_REALM_AUTH`: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".

p_privilege

System privilege to check for.

To find privileges for a database account excluding PUBLIC privileges, query the DVSYS.DBA_DV_USER_PRIVS view, described in "DBA_DV_USER_PRIVS View".

To find all privileges for a database account, use DVSYS.DBA_DV_USER_PRIVS_ALL, described in "DBA_DV_USER_PRIVS_ALL View".

p_user

User to check.

To find existing users, query they following views:

DBA_USERS: Finds available users for the current database instance. See Oracle Database Reference.
DVA_DV_REALM_AUTH: Finds the authorization of a particular user or role. See "DBA_DV_REALM_AUTH View".

Example

SET SERVEROUTPUT ON
BEGIN
 IF DVSYS.DBMS_MACUTL.USER_HAS_SYSTEM_PRIVILEGE('EXECUTE', 'PSMITH')
  THEN DBMS_OUTPUT.PUT_LINE('User PSMITH has the EXECUTE system privilege.');
   ELSE 
  DBMS_OUTPUT.PUT_LINE('User PSMITH does not have the EXECUTE system privilege.'); 
 END IF;
END;