Oracle Database Vault provides a set of utility APIs in the DBMS_MACUTL PL/SQL package.
Topics:
The 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 users who have been granted the DV_OWNER and DV_ADMIN roles.
You can use a set of constants, available in the DBMS_MACUTL PL/SQL package.
Topics:
Table 19-1 summarizes constant (that is, fields) descriptions for the 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 19-1 DBMS_MACUTL Listing of Constants
Example 19-1 shows how to use the G_YES and G_REALM_AUDIT_FAIL DBMS_MACUTL constants when creating a realm.
Example 19-2 shows how to use several DBMS_MACUTL constants when creating a rule set.
Example 19-2 Creating a Rule Set Using DBMS_MACUTL Constants
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_ALL, audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, fail_options => 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 19-3 shows how to use constants when creating a factor.
Example 19-3 Creating a Factor Using DBMS_MACUTL Constants
BEGIN
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 => DBMS_MACUTL.G_LABELED_BY_SELF,
eval_options => DBMS_MACUTL.G_EVAL_ON_SESSION,
audit_options => DBMS_MACUTL.G_AUDIT_ALWAYS,
fail_options => DBMS_MACUTL.G_FAIL_SILENTLY);
END;
/
You can use the DBMS_MACUTL PL/SQL package to perform tasks such as finding a time value or whether a user has the DVOWNER role or the appropriate privileges.
Table 19-2 lists the procedures and functions in the DBMS_MACUTL PL/SQL 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 19-2 DBMS_MACUTL Utility Functions
The CHECK_DVSYS_DML_ALLOWED procedure checks whether the given user can issue Data Modification Language (DML) commands to access the DVSYS objects
DBMS_MACUTL.CHECK_DVSYS_DML_ALLOWED( p_user IN VARCHAR2 DEFAULT USER);
Table 19-3 CHECK_DVSYS_DML_ALLOWED Parameter
| Parameter | Description |
|---|---|
|
|
User to check. To find existing users in the current database instance, query the following views:
|
User SYSTEM fails the check:
EXEC 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 "DBMS_MACUTL", line 23
ORA-06512: at "DBMS_MACUTL", line 372
ORA-06512: at "DBMS_MACUTL", line 508
ORA-06512: at "DBMS_MACUTL", line 572
ORA-06512: at line 1
User leo_dvowner, who has the DV_OWNER role, passes the check:
EXEC DBMS_MACUTL.CHECK_DVSYS_DML_ALLOWED('leo_dvowner');
PL/SQL procedure successfully completed.
The GET_CODE_VALUE function finds the value for a code within a code group, and then returns a VARCHAR2 value.
DBMS_MACUTL.GET_CODE_VALUE( p_code_group IN VARCHAR2, p_code IN VARCHAR2) RETURN VARCHAR2;
Table 19-4 GET_CODE_VALUE Parameters
| Parameter | Description |
|---|---|
|
|
Code group (for example, To find available code groups in the current database instance, query the |
|
|
ID of the code. This ID is listed when you run the |
BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Get Label Algorithm for Maximum Level/Union/Null',
rule_expr => 'DBMS_MACUTL.GET_CODE_VALUE(''LABEL_ALG'', ''HUN'') = ''Union''');
END;
/
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.
DBMS_MACUTL.GET_SECOND( p_date IN DATE DEFAULT SYSDATE) RETURN NUMBER;
Table 19-5 GET_SECOND Parameter
| Parameter | Description |
|---|---|
|
|
Date in SS format (for example, If you do not specify a date, then Oracle Database Vault uses the Oracle Database |
SET SERVEROUTPUT ON
DECLARE
seconds number;
BEGIN
seconds := 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
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.
DBMS_MACUTL.GET_MINUTE( p_date IN DATE DEFAULT SYSDATE) RETURN NUMBER;
Table 19-6 GET_MINUTE Parameter
| Parameter | Description |
|---|---|
|
|
Date in MI format (for example, If you do not specify a date, then Oracle Database Vault uses the Oracle Database |
SET SERVEROUTPUT ON
DECLARE
minute number;
BEGIN
minute := DBMS_MACUTL.GET_MINUTE(SYSDATE);
DBMS_OUTPUT.PUT_LINE('Minute: '||minute);
END;
/
Output similar to the following appears:
Minute: 17
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.
DBMS_MACUTL.GET_HOUR( p_date IN DATE DEFAULT SYSDATE) RETURN NUMBER;
| Parameter | Description |
|---|---|
|
|
Date in HH24 format (for example, If you do not specify a date, then Oracle Database Vault uses the Oracle Database |
SET SERVEROUTPUT ON
DECLARE
hours number;
BEGIN
hours := DBMS_MACUTL.GET_HOUR(SYSDATE);
DBMS_OUTPUT.PUT_LINE('Hour: '||hours);
END;
/
Output similar to the following appears:
Hour: 12
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.
DBMS_MACUTL.GET_DAY( p_date IN DATE DEFAULT SYSDATE) RETURN NUMBER;
| Parameter | Description |
|---|---|
|
|
Date in DD format (for example, If you do not specify a date, then Oracle Database Vault uses the Oracle Database |
SET SERVEROUTPUT ON
DECLARE
day number;
BEGIN
day := DBMS_MACUTL.GET_DAY(SYSDATE);
DBMS_OUTPUT.PUT_LINE('Day: '||day);
END;
/
Output similar to the following appears:
Day: 3
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.
DBMS_MACUTL.GET_MONTH( p_date IN DATE DEFAULT SYSDATE) RETURN NUMBER;
Table 19-9 GET_MONTH Parameter
| Parameter | Description |
|---|---|
|
|
Date in MM format (for example, If you do not specify a date, then Oracle Database Vault uses the Oracle Database |
SET SERVEROUTPUT ON
DECLARE
month number;
BEGIN
month := DBMS_MACUTL.GET_MONTH(SYSDATE);
DBMS_OUTPUT.PUT_LINE('Month: '||month);
END;
/
Output similar to the following appears:
Month: 4
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.
DBMS_MACUTL.GET_YEAR( p_date IN DATE DEFAULT SYSDATE) RETURN NUMBER;
Table 19-10 GET_YEAR Parameter
| Parameter | Description |
|---|---|
|
|
Date in YYYY format (for example, If you do not specify a date, then Oracle Database Vault uses the |
SET SERVEROUTPUT ON
DECLARE
year number;
BEGIN
year := DBMS_MACUTL.GET_YEAR(SYSDATE);
DBMS_OUTPUT.PUT_LINE('Year: '||year);
END;
/
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.
DBMS_MACUTL.IS_ALPHA( c IN VARCHAR2) RETURN BOOLEAN;
SET SERVEROUTPUT ON
BEGIN
IF 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;
/
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.
DBMS_MACUTL.IS_DIGIT( c IN VARCHAR2) RETURN BOOLEAN;
SET SERVEROUTPUT ON
BEGIN
IF 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;
/
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.
DBMS_MACUTL.IS_DVSYS_OWNER( p_user IN VARCHAR2 DEFAULT USER) RETURN BOOLEAN;
Table 19-13 IS_DVSYS_OWNER Parameter
| Parameter | Description |
|---|---|
|
|
User to check. To find existing users, query the following views:
|
SET SERVEROUTPUT ON
BEGIN
IF 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;
/
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.
DBMS_MACUTL.IS_OLS_INSTALLED() RETURN BOOLEAN;
None.
SET SERVEROUTPUT ON
BEGIN
IF 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;
/
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.
DBMS_MACUTL.IS_OLS_INSTALLED_VARCHAR() RETURN VARCHAR2;
None.
See "IS_OLS_INSTALLED Function" for an example.
The USER_HAS_OBJECT_PRIVILEGE function checks whether a user or role can access an object through a single specified object privilege grant, and then returns a BOOLEAN value. If the user or role has the object privilege, then USER_HAS_OBJECT_PRIVILEGE returns TRUE.
DBMS_MACUTL.USER_HAS_OBJECT_PRIVILEGE( p_user VARCHAR2, p_object_owner VARCHAR2, p_object_name VARCHAR2, p_privilege VARCHAR2) RETURNS BOOLEAN;
Table 19-14 USER_HAS_OBJECT_PRIVILEGE Parameters
| Parameter | Description |
|---|---|
|
|
User or role to check. To find existing users, query they following views:
|
|
|
Object owner, such as a schema. To find the available users, query they To find the authorization of a particular user, query they |
|
|
Object name, such as a table within the schema specified in the To find the available objects, query they To find objects that are secured by existing realms, query they |
|
|
Object privilege, such as, To find privileges for a database account excluding To find all privileges for a database account, query the |
SET SERVEROUTPUT ON
BEGIN
IF DBMS_MACUTL.USER_HAS_OBJECT_PRIVILEGE(
'SECTOR2_APP_MGR', 'OE', 'ORDERS', 'UPDATE')
THEN DBMS_OUTPUT.PUT_LINE('SECTOR2_APP_MGR has the UPDATE privilege for the OE.ORDERS table');
ELSE
DBMS_OUTPUT.PUT_LINE('SECTOR2_APP_MGR does not have the UPDATE privilege for the OE.ORDERS table.');
END IF;
END;
/
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.
DBMS_MACUTL.USER_HAS_ROLE( p_role IN VARCHAR2, p_user IN VARCHAR2 DEFAULT USER) RETURN BOOLEAN;
Table 19-15 USER_HAS_ROLE Parameters
| Parameter | Description |
|---|---|
|
|
Role privilege to check. To find existing roles, query the following views:
|
|
|
User to check. To find existing users, query the following views:
|
SET SERVEROUTPUT ON
BEGIN
IF 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;
/
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.
DBMS_MACUTL.USER_HAS_ROLE_VARCHAR( p_role IN VARCHAR2, p_user IN VARCHAR2 DEFAULT USER) RETURN VARCHAR2;
Table 19-16 USER_HAS_ROLE_VARCHAR Parameters
| Parameter | Description |
|---|---|
|
|
Role to check. To find existing roles, query the following views:
|
|
|
User to check. To find existing users, query the following views:
|
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.
DBMS_MACUTL.USER_HAS_SYSTEM_PRIVILEGE( p_privilege IN VARCHAR2, p_user IN VARCHAR2 DEFAULT USER) RETURN BOOLEAN;
Table 19-17 USER_HAS_SYSTEM_PRIVILEGE Parameters
| Parameter | Description |
|---|---|
|
|
System privilege to check for. To find privileges for a database account excluding To find all privileges for a database account, use |
|
|
User to check. To find existing users, query the following views:
|
SET SERVEROUTPUT ON
BEGIN
IF 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;
/