Skip Headers
Oracle® Database Vault Administrator's Guide
11g Release 2 (11.2)

Part Number E10576-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

13 Using the DVSYS.DBMS_MACUTL Package

This chapter contains:

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.

DVSYS.DBMS_MACUTL Constants

This section contains:

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 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 reevaluate 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 consider 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.


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     => DBMS_MACUTL.G_RULESET_EVAL_ALL,
  audit_options    => DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
  fail_options     => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,
  fail_message     => 'Rule Set Limit_DBA_Access has failed.',
  fail_code        => 20000,
  handler_options  => DBMS_MACUTL.G_RULESET_HANDLER_FAIL, 
  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       => DBMS_MACUTL.G_IDENTIFY_BY_FACTOR, 
  labeled_by        => DVSYS.DBMS_MACUTL.G_LABELED_BY_SELF, 
  eval_options      => DVSYS.DBMS_MACUTL.G_EVAL_ON_SESSION, 
  audit_options     => DBMS_MACUTL.G_AUDIT_ALWAYS, 
  fail_options      => DVSYS.DBMS_MACUTL.G_FAIL_SILENTLY); 
END;
/

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 standalone 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.

GET_SECOND Function

Returns the seconds in Oracle SS format (00-59). Useful for rule expressions based on time data

GET_MINUTE Function

Returns the minute in Oracle MI format (00–59). Useful for rule expressions based on time data

GET_HOUR Function

Returns the month in Oracle HH24 format (00–23). Useful for rule expressions based on time data

GET_DAY Function

Returns the day in Oracle DD format (01–31). Useful for rule expressions based on time data

GET_MONTH Function

Returns the month in Oracle MM format (01–12). Useful for rule expressions based on time data

GET_YEAR Function

Returns the year in Oracle YYYY format (0001–9999). Useful for rule expressions based on time data

IS_ALPHA Function

Checks whether the character is alphabetic

IS_DIGIT Function

Checks whether the character is numeric

IS_DVSYS_OWNER Function

Determines whether a user is authorized to manage the Oracle Database Vault configuration

IS_OLS_INSTALLED Function

Returns an indicator regarding whether Oracle Label Security is installed

IS_OLS_INSTALLED_VARCHAR Function

Returns an indicator regarding whether Oracle Label Security is installed

USER_HAS_ROLE Function

Checks whether a user has a role privilege, directly or indirectly (through another role)

USER_HAS_ROLE_VARCHAR Function

Checks whether a user has a role privilege, directly or indirectly (through another role)

USER_HAS_SYSTEM_PRIVILEGE Function

Checks whether a user has a system privilege, directly or indirectly (through a role)


CHECK_DVSYS_DML_ALLOWED Procedure

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

Syntax

DVSYS.DBMS_MACUTL.CHECK_DVSYS_DML_ALLOWED(
  p_user IN VARCHAR2 DEFAULT USER); 

Parameter

Table 13-3 CHECK_DVSYS_DML_ALLOWED Parameter

Parameter Description

p_user

User performing the operation.

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


Example

User SYSTEM fails the check:

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:

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

PL/SQL procedure successfully completed.

GET_CODE_VALUE Function

The GET_CODE_VALUE function finds the value for a code within a code group, and then returns a VARCHAR2 value.

Syntax

DVSYS.DBMS_MACUTL.GET_CODE_VALUE(
  p_code_group IN VARCHAR2, 
  p_code       IN VARCHAR2)
RETURN VARCHAR2; 

Parameters

Table 13-4 GET_CODE_VALUE Parameters

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.


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'') = ''Union''');
END;
/

GET_SECOND Function

The GET_SECOND 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

DVSYS.DBMS_MACUTL.GET_SECOND(
  p_date IN DATE DEFAULT SYSDATE)
RETURN NUMBER;

Parameter

Table 13-5 GET_SECOND Parameter

Parameter Description

p_date

Date in SS format, for example: 59.

If you do not specify a date, then 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

SET SERVEROUTPUT ON
DECLARE 
  seconds number; 
BEGIN 
  seconds := DVSYS.DBMS_MACUTL.GET_SECOND(TO_DATE('03-APR-2009 6:56 PM', 
  'dd-mon-yyyy hh:mi PM')); 
  DBMS_OUTPUT.PUT_LINE('Seconds: '||seconds); 
END; 
/ 

This example, which uses a fixed date and time, returns the following:

Seconds: 56

GET_MINUTE Function

The GET_MINUTE function returns the minute in Oracle MI (minute) format (00–59), in a NUMBER value. It is useful for rule expressions based on time data.

Syntax

DVSYS.DBMS_MACUTL.GET_MINUTE(
  p_date IN DATE DEFAULT SYSDATE)
RETURN NUMBER;

Parameter

Table 13-6 GET_MINUTE Parameter

Parameter Description

p_date

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

If you do not specify a date, then 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

SET SERVEROUTPUT ON
DECLARE 
  minute number; 
BEGIN 
  minute := DVSYS.DBMS_MACUTL.GET_MINUTE(SYSDATE); 
  DBMS_OUTPUT.PUT_LINE('Minute: '||minute); 
END; 
/

Output similar to the following appears:

Minute: 17

GET_HOUR Function

The GET_HOUR function returns the hour in Oracle HH24 (hour) format (00–23), in a NUMBER value. It is useful for rule expressions based on time data.

Syntax

DVSYS.DBMS_MACUTL.GET_HOUR(
  p_date IN DATE DEFAULT SYSDATE)
RETURN NUMBER; 

Parameter

Table 13-7 GET_HOUR Parameter

Parameter Description

p_date

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

If you do not specify a date, then 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

SET SERVEROUTPUT ON
DECLARE 
  hours number; 
BEGIN 
  hours := DVSYS.DBMS_MACUTL.GET_HOUR(SYSDATE); 
  DBMS_OUTPUT.PUT_LINE('Hour: '||hours); 
END; 
/

Output similar to the following appears:

Hour: 12

GET_DAY Function

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

Syntax

DVSYS.DBMS_MACUTL.GET_DAY(
  p_date IN DATE DEFAULT SYSDATE)
RETURN NUMBER; 

Parameter

Table 13-8 GET_DAY Parameter

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, then 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

SET SERVEROUTPUT ON
DECLARE 
  day number; 
BEGIN 
  day := DVSYS.DBMS_MACUTL.GET_DAY(SYSDATE); 
  DBMS_OUTPUT.PUT_LINE('Day: '||day); 
END; 
/

Output similar to the following appears:

Day: 3

GET_MONTH Function

The GET_MONTH function returns the month in Oracle MM (month) format (01–12), in a NUMBER value. It is useful for rule expressions based on time data.

Syntax

DVSYS.DBMS_MACUTL.GET_MONTH(
  p_date IN DATE DEFAULT SYSDATE)
RETURN NUMBER;

Parameter

Table 13-9 GET_MONTH Parameter

Parameter Description

p_date

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

If you do not specify a date, then 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

SET SERVEROUTPUT ON
DECLARE 
  month number; 
BEGIN 
  month := DVSYS.DBMS_MACUTL.GET_MONTH(SYSDATE); 
  DBMS_OUTPUT.PUT_LINE('Month: '||month); 
END; 
/

Output similar to the following appears:

Month: 4

GET_YEAR Function

The GET_YEAR function returns the year in Oracle YYYY (year) format (0001–9999), in a NUMBER value. It is useful for rule expressions based on time data.

Syntax

DVSYS.DBMS_MACUTL.GET_YEAR(
  p_date IN DATE DEFAULT SYSDATE)
RETURN NUMBER;

Parameter

Table 13-10 GET_YEAR Parameter

Parameter Description

p_date

Date in YYYY format, for example, 1984.

If you do not specify a date, then 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

SET SERVEROUTPUT ON
DECLARE 
  year number; 
BEGIN 
  year := DVSYS.DBMS_MACUTL.GET_YEAR(SYSDATE); 
  DBMS_OUTPUT.PUT_LINE('Year: '||year); 
END; 
/

IS_ALPHA Function

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

Syntax

DVSYS.DBMS_MACUTL.IS_ALPHA(
  c IN VARCHAR2)
RETURN BOOLEAN; 

Parameter

Table 13-11 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 alphabetic characters today.'); 
 END IF; 
END;
/

IS_DIGIT Function

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

Syntax

DVSYS.DBMS_MACUTL.IS_DIGIT(
  c IN VARCHAR2)
RETURN BOOLEAN;

Parameter

Table 13-12 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;
/

IS_DVSYS_OWNER Function

The IS_DVSYS_OWNER 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

DVSYS.DBMS_MACUTL.IS_DVSYS_OWNER(
  p_user IN VARCHAR2 DEFAULT USER)
RETURN BOOLEAN;

Parameter

Table 13-13 IS_DVSYS_OWNER Parameter

Parameter Description

p_user

User to check.

To find existing users, query the following views:


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

IS_OLS_INSTALLED Function

The IS_OLS_INSTALLED function returns an indicator regarding whether 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

DVSYS.DBMS_MACUTL.IS_OLS_INSTALLED()
RETURN 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;
/

IS_OLS_INSTALLED_VARCHAR Function

The IS_OLS_INSTALLED_VARCHAR function returns an indicator regarding whether Oracle Label Security is installed, and then returns a Y or N VARCHAR2 value. If Oracle Label Security is installed, then IS_OLS_INSTALLED_VARCHAR returns Y.

Syntax

DVSYS.DBMS_MACUTL.IS_OLS_INSTALLED_VARCHAR()
RETURN VARCHAR2;

Parameters

None.

Example

See "IS_OLS_INSTALLED Function" for an example.

USER_HAS_ROLE Function

The USER_HAS_ROLE 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

DVSYS.DBMS_MACUTL.USER_HAS_ROLE(
  p_role IN VARCHAR2, 
  p_user IN VARCHAR2 DEFAULT USER)
RETURN BOOLEAN;

Parameters

Table 13-14 USER_HAS_ROLE Parameters

Parameter Description

p_role

Role privilege to check.

To find existing roles, query the following views:

p_user

User to check.

To find existing users, query the following views:


Example

SET SERVEROUTPUT ON
BEGIN
 IF DVSYS.DBMS_MACUTL.USER_HAS_ROLE('SECTOR2_APP_MGR', 'PSMITH')
  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;
/

USER_HAS_ROLE_VARCHAR Function

The USER_HAS_ROLE_VARCHAR 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

DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(
  p_role IN VARCHAR2, 
  p_user IN VARCHAR2 DEFAULT USER)
RETURN VARCHAR2;

Parameters

Table 13-15 USER_HAS_ROLE_VARCHAR Parameters

Parameter Description

p_role

Role to check.

To find existing roles, query the following views:

p_user

User to check.

To find existing users, query the following views:


USER_HAS_SYSTEM_PRIVILEGE Function

The USER_HAS_SYSTEM_PRIVILEGE 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

DVSYS.DBMS_MACUTL.USER_HAS_SYSTEM_PRIVILEGE(
  p_privilege  IN VARCHAR2, 
  p_user       IN VARCHAR2 DEFAULT USER)
RETURN BOOLEAN;

Parameters

Table 13-16 USER_HAS_SYSTEM_PRIVILEGE Parameters

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 the following views:


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 ANY PRIVILEGE privilege.');
   ELSE 
  DBMS_OUTPUT.PUT_LINE('User PSMITH does not have the EXECUTE ANY PRIVILEGE privilege.'); 
 END IF;
END;
/