14 Oracle Database Vault Realm APIs

The DBMS_MACADM PL/SQL package enables you to configure Oracle Database Vault realms.

Only users who have been granted the DV_OWNER or DV_ADMIN role can use these procedures. For constants that you can use with these procedures, see Table 20-1 for more information.

• ADD_AUTH_TO_REALM Procedure
  The ADD_AUTH_TO_REALM procedure authorizes a user or role to access a realm as an owner or a participant. You can authenticate both common and local realms.
• ADD_OBJECT_TO_REALM Procedure
  The ADD_OBJECT_TO_REALM procedure registers a set of objects for realm protection.
• CREATE_REALM Procedure
  The CREATE_REALM procedure creates both common and local realms.
• DELETE_AUTH_FROM_REALM Procedure
  The DELETE_AUTH_FROM_REALM procedure removes the authorization of a user or role to access a realm.
• DELETE_OBJECT_FROM_REALM Procedure
  The DELETE_OBJECT_FROM_REALM procedure removes a set of objects from realm protection.
• DELETE_REALM Procedure
  The DELETE_REALM procedure deletes a realm, including its related configuration information that specifies who is authorized and what objects are protected.
• DELETE_REALM_CASCADE Procedure
  The DELETE_REALM_CASCADE procedure deletes a realm, including its related Database Vault configuration information that specifies who is authorized and the objects that are protected.
• RENAME_REALM Procedure
  The RENAME_REALM procedure renames a realm; the name change takes effect everywhere the realm is used.
• UPDATE_REALM Procedure
  The UPDATE_REALM procedure updates a realm.
• UPDATE_REALM_AUTH Procedure
  The UPDATE_REALM_AUTH procedure updates the authorization of a user or role to access a realm.

14.1 ADD_AUTH_TO_REALM Procedure

The ADD_AUTH_TO_REALM procedure authorizes a user or role to access a realm as an owner or a participant. You can authenticate both common and local realms.

Optionally, you can specify a rule set that must be checked before allowing the authorization to be enabled.

Syntax

DBMS_MACADM.ADD_AUTH_TO_REALM(
 realm_name    IN VARCHAR2, 
 grantee       IN VARCHAR2, 
 rule_set_name IN VARCHAR2, 
 auth_options  IN NUMBER
 auth_scope    IN NUMBER DEFAULT); 

Parameters

Table 14-1 ADD_AUTH_TO_REALM Parameters

Parameter	Description
realm_name	Realm name, which can be 128 characters in mixed case. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
grantee	User or role name to authorize as an owner or a participant. To find the existing users and roles in the current database instance, query the DBA_USERS and DBA_ROLES views. To find the authorization of a particular user or role, query the DVA_DV_REALM_AUTH view. To find existing secure application roles used in privilege management, query the DBA_DV_ROLE view.
rule_set_name	Optional. The rule set to check during runtime. The realm authorization is enabled only if the rule set evaluates to TRUE. To find the available rule sets, query the DBA_DV_RULE_SET view.
auth_options	Optional. Specify one of the following options to authorize the realm: DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT: Participant. This account or role allows system or direct privileges to access, manipulate, and create objects protected by the realm, provided these rights have been granted using the standard Oracle Database privilege grant process. (Default) DBMS_MACUTL.G_REALM_AUTH_OWNER: Owner. This account or role has the same authorization as the realm participant, plus the authorization to grant or revoke realm-secured roles and privileges on realm-protected objects. A realm can have multiple owners or participants. See Related Topics for more information about participants and owners.
auth_scope	Determines how to execute this procedure. The default is DBMS_MACUTL.G_SCOPE_LOCAL. DBMS_MACUTL.G_SCOPE_LOCAL (or 1) authorizes the realm locally in the current PDB DBMS_MACUTL.G_SCOPE_COMMON (or 2) authorizes the realm in the application root

Examples

The following example authorizes user PSMITH as a participant in the HR Apps realm. Because the default is to authorize the user as a participant, the auth_options parameter can be omitted.

BEGIN
 DBMS_MACADM.ADD_AUTH_TO_REALM(
  realm_name  => 'HR Apps', 
  grantee     => 'PSMITH'); 
END;
/

This example sets user PSMITH as the owner of the HR Apps realm.

BEGIN
 DBMS_MACADM.ADD_AUTH_TO_REALM(
  realm_name   => 'HR Apps', 
  grantee      => 'PSMITH', 
  auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
END;
/

The next example triggers the Check Conf Access rule set before allowing user PSMITH to act as the owner of the HR Apps realm.

BEGIN
 DBMS_MACADM.ADD_AUTH_TO_REALM(
  realm_name    => 'HR Apps', 
  grantee       => 'PSMITH', 
  rule_set_name => 'Check Conf Access',
  auth_options  => DBMS_MACUTL.G_REALM_AUTH_OWNER);
END;
/

This example shows how to commonly grant the common user C##HR_ADMIN access to the common realm HR Statistics Realm. The user running this procedure must be in the CDB root, and the rule set must be a common rule set residing in the application root.

BEGIN
 DBMS_MACADM.ADD_AUTH_TO_REALM(
  realm_name    => 'HR Statistics Realm', 
  grantee       => 'C##HR_ADMIN', 
  rule_set_name => 'Check Access',
  auth_options  => DBMS_MACUTL.G_REALM_AUTH_OWNER,
 auth_scope     => DBMS_MACUTL.G_SCOPE_COMMON);
END;
/

This example shows how to locally grant the common user C##HR_CLERK access to the common realm HR Statistics Realm. The user running this procedure must be in the same PDB in which the authorization applies. To find the existing PDBs query the DBA_PDBS data dictionary view. The rule set must be a local rule set.

BEGIN
 DBMS_MACADM.ADD_AUTH_TO_REALM(
  realm_name    => 'HR Statistics Realm', 
  grantee       => 'C##HR_CLERK', 
  rule_set_name => 'Check Access',
  auth_options  => DBMS_MACUTL.G_REALM_AUTH_OWNER,
  auth_scope    => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/

14.2 ADD_OBJECT_TO_REALM Procedure

The ADD_OBJECT_TO_REALM procedure registers a set of objects for realm protection.

Syntax

DBMS_MACADM.ADD_OBJECT_TO_REALM(
  realm_name   IN VARCHAR2, 
  object_owner IN VARCHAR2, 
  object_name  IN VARCHAR2, 
  object_type  IN VARCHAR2); 

Parameters

Table 14-2 ADD_OBJECT_TO_REALM Parameters

Parameter	Description
realm_name	Realm name, which can be 128 characters in mixed case. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
object_owner	The owner of the object that is being added to the realm. If you add a role to a realm, the object owner of the role is shown as % (for all), because roles do not have owners. To find the available users, query the DBA_USERS view. To find the authorization of a particular user or role, query the DVA_DV_REALM_AUTH view.
object_name	Object name. The wildcard % is allowed, to specify all objects (except roles) for the object owner that you have specified. If you enter %, then it can encompass all objects in the schema if % is also used for the object_type parameter. But if object_type is set to TABLE, then using % for the object_name refers to all tables in the schema. Note that the % wildcard character applies to objects that do not yet exist and currently existing objects. You can also use the DBMS_MACUTL.G_ALL_OBJECT constant. To find the available objects, query the ALL_OBJECTS view. To find objects that are secured by existing realms, query the DBA_DV_REALM_OBJECT view.
object_type	Object type, such as TABLE, INDEX, or ROLE. (The wildcard % is allowed.) You can also use the DBMS_MACUTL.G_ALL_OBJECT constant. You can add as many objects of any type as you want to the realm.

Example

BEGIN
 DBMS_MACADM.ADD_OBJECT_TO_REALM(
  realm_name    => 'HR Apps',
  object_owner  => '%',
  object_name   => 'HR_SELECT_ROLE',
  object_type   => 'ROLE');
END;
/

14.3 CREATE_REALM Procedure

The CREATE_REALM procedure creates both common and local realms.

After you create the realm, use the following procedures to complete the realm definition:

• ADD_OBJECT_TO_REALM procedure registers one or more objects for the realm.

• ADD_AUTH_TO_REALM procedure authorizes users or roles for the realm.

Syntax

DBMS_MACADM.CREATE_REALM(
 realm_name    IN VARCHAR2, 
 description   IN VARCHAR2 DEFAULT, 
 enabled       IN VARCHAR2 DEFAULT, 
 audit_options IN NUMBER DEFAULT,
 realm_type    IN NUMBER DEFAULT,
 realm_scope   IN NUMBER DEFAULT,
 pl_sql_stack  IN BOOLEAN DEFAULT); 

Parameters

Table 14-3 CREATE_REALM Parameters

Parameter	Description
realm_name	Realm name, up to 128 characters in mixed-case. Oracle suggests that you use the name of the protected application as the realm name (for example, hr_app for an human resources application). This parameter is mandatory. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
description	Description of the purpose of the realm, up to 1024 characters in mixed-case. If you omit this setting, then it defaults to an empty string. You may want to include a description for the business objective of the given application protection and document all other security policies that compliment the realm's protection. Also document who is authorized to the realm, for what purpose, and any possible emergency authorizations.
enabled	Controls realm checking and the ability of SQL statements to capture simulation log violations. If you omit this setting, then it defaults to DBMS_MACUTL.G_YES. DBMS_MACUTL.G_YES (or 'y') enables realm checking. (Default) DBMS_MACUTL.G_NO (or 'n') disables all realm checking, including the capture of violations in the simulation log. DBMS_MACUTL.G_SIMULATION (or 's') enables SQL statements to execute but capture violations in the simulation log.
audit_options	Specify DBMS_MACUTL.G_REALM_AUDIT_OFF (or NULL). (Default) Starting with Oracle Database release 23ai, traditional auditing is desupported. Only the DBMS_MACUTL.G_REALM_AUDIT_OFF setting is available for the audit_options parameter. To audit a realm, you must use unified auditing. See Oracle Database Security Guide for an example of how to create a unified audit policy for a realm.
realm_type	Controls whether the realm is regular or mandatory. If you omit this setting, then it defaults to DBMS_MACADM.REGULAR_REALM. DBMS_MACADM.REGULAR_REALM (or 0) disables mandatory realm checking. (Default) DBMS_MACADM.MANDATORY_REALM (or 1) enables mandatory realm checking for realm objects. Only realm owners or realm participants will have access to objects in a realm. Object owners and object-privileged users who are not realm owners or participants will have no access.
realm_scope	Determines how to execute this procedure. The default is DBMS_MACUTL.G_SCOPE_LOCAL. DBMS_MACUTL.G_SCOPE_LOCAL (or 1) means the realm is local in the current PDB. (Default) DBMS_MACUTL.G_SCOPE_COMMON (or 2) means the realm must be in the application root. This setting duplicates the realm in all of the associated PDBs. If you create the common realm in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example: ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;
pl_sql_stack	When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. If you omit this setting, then it defaults to FALSE. TRUE records the PL/SQL stack. FALSE does not record the PL/SQL stack. (Default)

Examples

The following example shows how to create a realm that is enabled and uses mandatory realm checking, and records the PL/SQL stack. Auditing is disabled because NULL defaults to DBMS_MACUTL.G_REALM_AUDIT_OFF.

BEGIN
 DBMS_MACADM.CREATE_REALM(
  realm_name     => 'HR Apps',
  description    => 'Realm to protect the HR schema',
  enabled        => DBMS_MACUTL.G_YES,
  audit_options  => NULL,
  realm_type     => DBMS_MACADM.MANDATORY_REALM,
  pl_sql_stack   => TRUE);
END;
/

This example shows how to create a variation of the preceding example, but as a common realm located in the application root. The user who creates this realm must be a common user and must run the procedure in the application root. The enabled setting is omitted and so defaults to DBMS_MACUTL.G_YES.

BEGIN
 DBMS_MACADM.CREATE_REALM(
  realm_name     => 'HR Apps',
  description    => 'Realm to protect the HR schema',
  audit_options  => NULL,
  realm_type     => DBMS_MACADM.MANDATORY_REALM,
  realm_scope    => DBMS_MACUTL.G_SCOPE_COMMON);
END;
/

This example shows how to create a local version of the preceding example. The user who creates this realm must be in the PDB in which the realm will reside. To find existing PDBs, query the DBA_PDBS data dictionary view.

BEGIN
 DBMS_MACADM.CREATE_REALM(
  realm_name     => 'HR Apps',
  description    => 'Realm to protect the HR schema',
  audit_options  => DBMS_MACUTL.G_REALM_AUDIT_OFF,
  realm_type     => DBMS_MACADM.MANDATORY_REALM,
  realm_scope    => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/

14.4 DELETE_AUTH_FROM_REALM Procedure

The DELETE_AUTH_FROM_REALM procedure removes the authorization of a user or role to access a realm.

Syntax

DBMS_MACADM.DELETE_AUTH_FROM_REALM(
 realm_name    IN VARCHAR2,
 grantee       IN VARCHAR2,
 auth_scope    IN NUMBER DEFAULT);  

Parameters

Table 14-4 DELETE_AUTH_FROM_REALM Parameters

Parameter	Description
realm_name	Realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
grantee	User or role name. To find the authorization of a particular user or role, query the DVA_DV_REALM_AUTH view.
auth_scope	Determines how to execute this procedure. The default is local. Options are as follows: DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the realm was authorized locally in the current PDB DBMS_MACUTL.G_SCOPE_COMMON (or 2 if the realm was authorized in the application root

Example

BEGIN
DBMS_MACADM.DELETE_AUTH_FROM_REALM(
 realm_name     => 'HR Apps',
 grantee        => 'PSMITH',
 auth_scope     => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/

14.5 DELETE_OBJECT_FROM_REALM Procedure

The DELETE_OBJECT_FROM_REALM procedure removes a set of objects from realm protection.

Syntax

DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
  realm_name   IN VARCHAR2, 
  object_owner IN VARCHAR2, 
  object_name  IN VARCHAR2, 
  object_type  IN VARCHAR2);

Parameters

Table 14-5 DELETE_OBJECT_FROM_REALM Parameters

Parameter	Description
realm_name	Realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
object_owner	The owner of the object that was added to the realm. To find the available users, query the DBA_USERS view.
object_name	Object name. (The wildcard % is allowed.) You can also use the DBMS_MACUTL.G_ALL_OBJECT constant. To find objects that are secured by existing realms, query the DBA_DV_REALM_OBJECT view. See also Related Topics.
object_type	Object type, such as TABLE, INDEX, or ROLE. (The wildcard % is allowed.) You can also use the DBMS_MACUTL.G_ALL_OBJECT constant. See also Related Topics.

Example

BEGIN
 DBMS_MACADM.DELETE_OBJECT_FROM_REALM(
  realm_name     => 'HR Apps',
  object_owner   => '%',
  object_name    => 'HR_SELECT_ROLE',
  object_type    => '%');
END;
/

14.6 DELETE_REALM Procedure

The DELETE_REALM procedure deletes a realm, including its related configuration information that specifies who is authorized and what objects are protected.

This procedure does not delete the actual database objects or users.

To find users who are authorized for the realm, query the DBA_DV_REALM_AUTH view. To find the objects that are protected by the realm, query the DBA_DV_REALM_OBJECT view.

Syntax

DBMS_MACADM.DELETE_REALM(
  realm_name IN VARCHAR2); 

Parameters

Table 14-6 DELETE_REALM Parameter

Parameter	Description
realm_name	Realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.

Example

EXEC DBMS_MACADM.DELETE_REALM ('HR Apps');

14.7 DELETE_REALM_CASCADE Procedure

The DELETE_REALM_CASCADE procedure deletes a realm, including its related Database Vault configuration information that specifies who is authorized and the objects that are protected.

The DBA_DV_REALM_AUTH view lists who is authorized in the realm and the DBA_DV_REALM_OBJECT view lists the protected objects.

It does not delete the actual database objects or users. This procedure works the same as the DELETE_REALM procedure. (In previous releases, these procedures were different, but now they are the same. Both are retained for earlier compatibility.) To find a listing of the realm-related objects, query the DBA_DV_REALM view. To find its authorizations, query DBA_DV_REALM_AUTH.

Syntax

DBMS_MACADM.DELETE_REALM_CASCADE(
  realm_name IN VARCHAR2); 

Parameters

Table 14-7 DELETE_REALM_CASCADE Parameter

Parameter	Description
realm_name	Realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.

Example

BEGIN
 DBMS_MACADM.RENAME_REALM(
  realm_name  => 'HR Apps',
  new_name    => 'HR and HCM Apps');
END;
/

14.8 RENAME_REALM Procedure

The RENAME_REALM procedure renames a realm; the name change takes effect everywhere the realm is used.

Syntax

DBMS_MACADM.RENAME_REALM(
 realm_name  IN VARCHAR2, 
 new_name    IN VARCHAR2); 

Parameters

Table 14-8 RENAME_REALM Parameters

Parameter	Description
realm_name	Current realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
new_name	New realm name, up to 128 characters in mixed-case.

Example

BEGIN
 DBMS_MACADM.RENAME_REALM(
  realm_name => 'HR Apps', 
  new_name   => 'HR and FIN Apps');
END; 
/

14.9 UPDATE_REALM Procedure

The UPDATE_REALM procedure updates a realm.

To find information about the current settings for a realm, query the DVSYS.DV$REALM or DBA_DV_REALM view.

When you update the audit options in a realm, the existing traditional audit records are disabled. You must create a unified audit policy to capture new audit records.

Syntax

DBMS_MACADM.UPDATE_REALM(
 realm_name    IN VARCHAR2, 
 description   IN VARCHAR2 DEFAULT, 
 enabled       IN VARCHAR2 DEFAULT, 
 audit_options IN NUMBER DEFAULT,
 realm_type    IN NUMBER DEFAULT,
 pl_sql_stack  IN BOOLEAN DEFAULT); 

Parameters

Table 14-9 UPDATE_REALM Parameters

Parameter	Description
realm_name	Realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
description	Description of the purpose of the realm, up to 1024 characters in mixed-case. If you do not want to change this setting, then omit it or set it to NULL.
enabled	Controls the enablement of the realm. If you do not want to change this setting, then omit it or set it to NULL. DBMS_MACUTL.G_YES (or 'y') enables realm checking. DBMS_MACUTL.G_NO (or 'n') disables all realm checking, including the capture of violations in the simulation log. DBMS_MACUTL.G_SIMULATION (or 's') enables SQL statements to execute but capture violations in the simulation log. NULL leaves the current setting unchanged. (Default)
audit_options	Perform one of the following actions: Set DBMS_MACUTL.G_REALM_AUDIT_OFF to disable traditional auditing for this realm policy. (Default) Leave the current audit_options setting as is if you want to continue using this traditional auditing setting. To do so, you can specify the same setting as the current existing audit_options for this realm, or do not specify audit_options (NULL). Starting with Oracle Database 23ai, traditional auditing is desupported. To audit a realm, you must use unified auditing. See Oracle Database Security Guide for an example of how to create a unified audit policy for a realm.
realm_type	Determines if a realm is regular of mandatory. If you do not want to change this setting, then omit it or set it to NULL. DBMS_MACADM.REGULAR_REALM (or 0): Disables mandatory realm checking. DBMS_MACADM.MANDATORY_REALM (or 1): Enables mandatory realm checking for realm objects. Only realm owners or realm participants will have access to objects in a realm. Object owners and object-privileged users who are not realm owners or participants will have no access. NULL leaves the current setting unchanged. (Default)
pl_sql_stack	When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. If you do not want to change this setting, then omit it or set it to NULL. TRUE records the PL/SQL stack. FALSE does not record the PL/SQL stack. NULL leaves the current setting unchanged. (Default)

Example

In the following example, the description, enabled, and audit_options settings are omitted because their values did not need to change.

BEGIN
 DBMS_MACADM.UPDATE_REALM(
  realm_name      => 'HR Apps',
  realm_type      => DBMS_MACADM.MANDATORY_REALM);
END;
/

If you did not make any modifications to the audit_options parameter of your realm, then the existing traditional audit policy will still be in place. If you updated the audit_options parameter of the realm, then this auditing will be disabled because traditional auditing is desupported starting in release 23ai. To create and enable a new unified audit policy, a user must be granted the AUDIT_ADMIN Oracle Database role and AUDIT_ADMIN Oracle Database Vault authorization. See the below example for creating a new unified audit policy:

CREATE AUDIT POLICY hr_app_aud_pol
 ACTIONS COMPONENT=DV Realm Violation ON "HR Apps";

AUDIT POLICY hr_app_aud_pol;

You can view the audit records by querying the UNIFIED_AUDIT_TRAIL data dictionary view. See How Oracle Oracle Database Security Guide for how this works.

14.10 UPDATE_REALM_AUTH Procedure

The UPDATE_REALM_AUTH procedure updates the authorization of a user or role to access a realm.

Syntax

DBMS_MACADM.UPDATE_REALM_AUTH(
 realm_name    IN VARCHAR2, 
 grantee       IN VARCHAR2, 
 rule_set_name IN VARCHAR2, 
 auth_options  IN NUMBER,
 auth_scope    IN NUMBER DEFAULT); 

Parameters

Table 14-10 UPDATE_REALM_AUTH Parameters

Parameter	Description
realm_name	Realm name. To find the existing realms in the current database instance, query the DBA_DV_REALM view.
grantee	User or role name. To find the available users and roles in the current database instance, query the DBA_USERS and DBA_ROLES data dictionary views. To find the authorization of a particular user or role, query the DVA_DV_REALM_AUTH view. To find existing secure application roles used in privilege management, query the DBA_DV_ROLE view.
rule_set_name	Optional. A rule set to check during runtime. The realm authorization is enabled only if the rule set evaluates to TRUE. To find the available rule sets, query the DBA_DV_RULE_SET view. To find rules that are associated with the rule sets, query the DBA_DB_RULE_SET_RULE view.
auth_options	Optional. Specify one of the following options to authorize the realm: DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT: Participant. This account or role provides system or direct privileges to access, manipulate, and create objects protected by the realm, provided these rights have been granted using the standard Oracle Database privilege grant process. DBMS_MACUTL.G_REALM_AUTH_OWNER: Owner. This account or role has the same authorization as the realm participant, plus the authorization to grant or revoke realm-secured roles and privileges on realm-protected objects. A realm can have multiple owners. The default for auth_options value is the previously set value, which you can find by querying the DBA_DV_REALM_AUTH data dictionary view.
realm_auth	Determines how to execute this procedure. The default is local. Options are as follows: DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the realm is authorized locally in the current PDB DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the realm is authorized in the application root

Example

BEGIN
 DBMS_MACADM.UPDATE_REALM_AUTH(
  realm_name    => 'HR Apps',
  grantee       => 'HR_SELECT_ROLE',
  rule_set_name => 'Check Conf Access',
  auth_options  => DBMS_MACUTL.G_REALM_AUTH_OWNER);
END;
/