| Oracle® Database Vault Administrator's Guide 11g Release 2 (11.2) Part Number E10576-01 |
|
|
View PDF |
| Oracle® Database Vault Administrator's Guide 11g Release 2 (11.2) Part Number E10576-01 |
|
|
View PDF |
This chapter contains:
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.
This section contains:
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
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;
/
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
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 |
|---|---|
|
|
User performing the operation. To find existing users in the current database instance, query they 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.
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 |
|---|---|
|
|
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 |
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;
/
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 |
|---|---|
|
|
Date in SS format, for example: If you do not specify a date, Oracle Database Vault uses the Oracle Database |
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
The GET_MINUTE function returns the minute in Oracle MI (minute) format (00–59), in a NUMBER value. 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 |
|---|---|
|
|
Date in MI format, for example, If you do not specify a date, Oracle Database Vault uses the Oracle Database |
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
The GET_HOUR function returns the hour in Oracle HH24 (hour) format (00–23), in a NUMBER value. 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 |
|---|---|
|
|
Date in HH24 format, for example, If you do not specify a date, Oracle Database Vault uses the Oracle Database |
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
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 |
|---|---|
|
|
Date in DD format, for example, If you do not specify a date, Oracle Database Vault uses the Oracle Database |
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
The GET_MONTH function returns the month in Oracle MM (month) format (01–12), in a NUMBER value. 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 |
|---|---|
|
|
Date in MM format, for example, If you do not specify a date, Oracle Database Vault uses the Oracle Database |
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
The GET_YEAR function returns the year in Oracle YYYY (year) format (0001–9999), in a NUMBER value. 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 |
|---|---|
|
|
Date in YYYY format, for example, If you do not specify a date, Oracle Database Vault uses the |
Example
SET SERVEROUTPUT ON
DECLARE
year number;
BEGIN
year := DVSYS.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.
Syntax
DVSYS.DBMS_MACUTL.IS_ALPHA( c IN VARCHAR2) RETURN BOOLEAN;
Parameter
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;
/
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
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;
/
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 |
|---|---|
|
|
User to check. To find existing users, query they 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;
/
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;
/
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, 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.
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 |
|---|---|
|
|
Role privilege to check. To find existing roles, query they following views:
|
|
|
User to check. To find existing users, query they 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;
/
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 |
|---|---|
|
|
Role to check. To find existing roles, query they following views:
|
|
|
User to check. To find existing users, query they 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.
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 |
|---|---|
|
|
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 they 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;
/
|
 Copyright © 2006, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|