5 Configuring Realms

You can create a realm around database objects to protect them, and then set authorizations to control user access to this data.

5.1 What Are Realms?

Realms enable you to protect database objects, including specific object types.

5.1.1 About Realms

A realm is a grouping of database schemas, database objects, and database roles that must be secured for a given application.

Think of a realm as zone of protection for your database objects. A schema is a logical collection of database objects such as tables, views, and packages, and a role is a collection of privileges. By arranging schemas and roles into functional groups, you can control the ability of users to use system privileges against these groups and prevent unauthorized data access by the database administrator or other powerful users with system privileges. Oracle Database Vault does not replace the discretionary access control model in the existing Oracle database. It functions as a layer on top of this model for both realms and command rules.

Oracle Database Vault provides two types of realms: regular and mandatory. Both realm types can protect either an entire schema or crucial objects within a schema selectively, such as tables and indexes. With a regular realm, an object owner or users who has been granted object privileges can perform queries or DML operations without realm authorization but must have realm authorization to perform DDL operations. A mandatory realm provides stronger protection for objects within a realm. Mandatory realms block both object privilege-based and system privilege-based access and will not allow users with object privileges to perform queries, DML, or DDL operations without realm authorization. In other words, even an object owner cannot access his or her own objects without proper realm authorization if the objects are protected by mandatory realms.

For databases that use Oracle Flashback Technology, then both regular and mandatory realms will enforce the same behavior for a flashback table. Users can execute a FLASHBACK TABLE SQL statement on a realm-protected table if the user is authorized to the realm.

For databases that use Information Lifecycle Management (ILM), a Database Vault administrator can use the DBMS_MACADM.AUTHORIZE_MAINTENANCE_USER and DBMS_MACADM.UNAUTHORIZE_MAINTENANCE_USER procedure to control who can perform ILM operations on realm-protected objects.

You can register schemas, all objects of a certain type in a schema, or individual objects within a schema into a realm. After you create a realm, you can register a set of schema objects or roles (secured objects) for realm protection and authorize a set of users or roles to access the secured objects. Objects that are protected by a regular realm allow DML access to users who have direct object grants.

For example, you can create a realm to protect all existing database schemas that are used in an accounting department. The realm prohibits any user who is not authorized to the realm to use system privileges to access the secured accounting data. When an entire schema is protected, all objects in the schema are protected, including tables, indexes, procedures and other objects.

You can run reports on realms that you create in Oracle Database Vault. You can use simulation mode during development, test, and even production phases to log only realm violations instead of blocking access. This enables you to quickly test applications using Database Vault realms.

You can configure realms by using the Oracle Database Vault Administrator pages in Oracle Enterprise Manager Cloud Control. Alternatively, you can configure realms by using the PL/SQL interfaces and packages provided by Oracle Database Vault.

5.1.2 Mandatory Realms to Restrict User Access to Objects within a Realm

By default, users who own or have object privileges are allowed to access realm-protected objects without explicit realm authorization.

You optionally can configure the realm to prevent these users' access by configuring it to be a mandatory realm. Mandatory realms block system privilege-based access as well as object privilege-based access. This means that even the object owner cannot have access if he or she is not authorized to access the realm. Users can access secured objects in the mandatory realm only if the user or role is authorized to do so.

Mandatory realms have the following additional characteristics:

  • If there are multiple mandatory realms on the same object, then you must authorize the user or role on all the mandatory realms before they can access the protected object.

  • If a role is protected by a mandatory realm, then no privileges can be granted to or revoked from the protected role except by the realm owner.

  • You can update regular realms that you created in earlier releases to be mandatory realms. This way, you can block owner access and object-privileged users from accessing the realm-protected objects.

Mandatory realms have the following benefits:

  • Mandatory realms can block object owners and object privileged users. In previous releases, blocking these users could only be done by defining complicated command rules.

  • Mandatory realms provide more flexible configurations for access control. For example, suppose you want to enable a user to access an object with certain conditions, such as in a specific time range during the day. You cannot grant object privileges to that user because realms do not block object privileges. You only can grant system privileges to the user and then authorize this user to the realm with a rule, or make a command rule on the command directly. These solutions are either very expensive in terms of computational cost or undesirable because they entail the excessive granting of privileges such as system privileges to the user. With a mandatory realm, you only need to grant object privileges to the user, with a rule for specific conditions, and then authorize this user to be a realm owner or participant. Thus, with mandatory realms, Oracle Database Vault policies have more flexibility without granting users excessive privileges.

  • Mandatory realms add a layer of protection during patch upgrades. During a patch upgrade, a database administrator may need to have direct access to a realm-protected object in order to perform a patch on the object. If there are tables that contain sensitive data, such as social security numbers, you can protect these tables from the administrator's access with mandatory realms during the patch upgrade. When patching is complete, and the database administrator no long needs access to the objects, you can disable mandatory realm protection and then re-enable the normal application realm protection so that the application protection can return to its normal state.

  • You can use mandatory realms to secure tables during runtime. During runtime, application data can be stored in many tables. It is better to have a single user such as a runtime schema to access these tables so that you can maintain the integrity and correctness of the data. If the application data is scattered in many different schemas, then schema owners and users with object privileges can change the data if they log into the database directly. To insure that users cannot update these tables without going through the runtime schema's procedures, you can use mandatory realms to protect the tables so that only the authorized user's procedures can access them. Because a regular realm does not block object owners and object-privileged users, you can use mandatory realms to block them. This way, only authorized users can access these tables during runtime.

  • You can freeze security settings by preventing changes to configured roles.

5.1.3 Realms in a Multitenant Environment

In a multitenant environment, you can create a realm to protect common objects in the application root.

The advantage of creating a realm in the application root instead of creating a large number objects and realms around these objects within individual pluggable databases (PDBs) is that you can create them in one place, the application root. This way, you can manage them centrally.

You cannot create a common realm in the CDB root.

A Database Vault common realm can be either a regular realm or a mandatory realm. The realm protects only objects within the application root, not local objects in a PDB. The CDB root, application root, and any affected PDBs all must be Database Vault enabled.

To configure a common realm, you must be commonly granted the DV_OWNER or DV_ADMIN role. To grant common authorizations for a common realm, you must be in the application root. To propagate the realm to the PDBs that are associated with the application root, you must synchronize the application root. For example, to synchronize an application called saas_sales_app

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

Related Topics

5.1.4 Object Types That Realms Can Protect

You can create realms around all objects in a schema of certain object types.

These object types are as follows:

Object Types C-J Object Types L-P Object Types R-V

CLUSTER

LIBRARY

ROLE

DIMENSION

MATERIALIZED VIEW

SEQUENCE

FUNCTION

MATERIALIZED VIEW LOG

SYNONYM

INDEX

OPERATOR

TABLE

INDEX PARTITION

PACKAGE

TRIGGER

INDEXTYPE

PROCEDURE

TYPE

JOB

PROGRAM

VIEW

5.2 Default Realms

Oracle Database Vault provides default realms, which are regular realms, not mandatory realms.

5.2.1 Oracle Database Vault Realm

The Oracle Database Vault realm protects configuration and role information in the Oracle Database Vault DVSYS, DVF, and LBACSYS schemas.

The owners of all three of the DVSYS, DVF, and LBACSYS schemas are owners of this realm.

This realm protects the following objects:

  • Entire schemas that are protected: DVSYS, DVF, LABACSYS

  • Roles that are protected:

    Roles DV_A to DV_G Roles DV_M to DV-S Roles DV_S to L

    DV_ADMIN

    DV_MONITOR

    DV_STREAMS_ADMIN

    DV_AUDIT_CLEANUP

    DV_OWNER

    DV_XSTREAM_ADMIN

    DV_DATAPUMP_NETWORK_LINK

    DV_PATCH_ADMIN

    LBAC_DBA

    DV_GOLDENGATE_ADMIN

    DV_PUBLIC

    -

    DV_GOLDENGATE_REDO_ACCESS

    DV_SECANALYST

    -

  • PL/SQL package that is protected: SYS.DBMS_RLS

5.2.2 Database Vault Account Management Realm

The Database Vault Account Management realm defines the realm for the administrators who manage and create database accounts and database profiles.

This realm protects the DV_ACCTMGR and CONNECT roles. The owner of this realm can grant or revoke the CREATE SESSION privilege to or from a user.

5.2.3 Oracle Enterprise Manager Realm

Oracle Database Vault provides a realm specifically for Oracle Enterprise Manager accounts.

The Oracle Enterprise Manager realm protects Oracle Enterprise Manager accounts that are used for monitoring and management (DBSNMP user and the OEM_MONITOR role).

5.2.4 Oracle Default Schema Protection Realm

Oracle Default Schema Protection Realm protects roles and schemas that are used with Oracle features such as Oracle Text.

The advantage of this grouping is that Oracle Spatial schemas (MDSYS, MDDATA) are used extensively with Oracle Text (CTXSYS), and Oracle OLAP is an application rather than a core Oracle Database kernel feature.

Oracle Default Schema Protection Realm Protected Roles and Schemas

Oracle Default Schema Protection Realm protects several roles and schemas.

  • Roles that are protected by default: CTXAPP, OLAP_DBA, EJBCLIENT, OLAP_USER

  • Schemas that are protected by default: CTXSYS, EXFSYS, MDDATA, MDSYS

  • Roles that are recommended for protection: APEX_ADMINISTRATOR_ROLE, SPATIAL_CSW_ADMIN, WFS_USR_ROLE, CSW_USR_ROLE, SPATIAL_WFS_ADMIN, WM_ADMIN_ROLE

  • Schemas that are recommended for protection: APEX_030200, OWBSYS, WMSYS

Oracle Default Schema Protection Realm Owners

The SYS, CTXSYS, and EXFSYS users are the default owners of Oracle Default Schema Protection Realm. These users can grant the roles protected by this realm to other users, and grant permissions on its schemas to other users as well.

5.2.5 Oracle System Privilege and Role Management Realm

Oracle System Privilege and Role Management Realm protects all Oracle-supplied roles in an Oracle database.

This realm also contains authorizations for users who must grant system privileges.

User SYS is the only default owner of this realm. Any user who is responsible for managing system privileges should be authorized as an owner to this realm. These users can grant the roles that are protected by this realm to other users.

Examples of roles that the Oracle System Privilege and Role Management Realm protects are DBA, IMP_FULL_DATABASE, SELECT_CATALOG_ROLE, and SCHEDULER_ADMIN.

You can find the full list of roles that the Oracle System Privilege and Role Management Realm protects by executing the following query:

SELECT OBJECT_NAME FROM DBA_DV_REALM_OBJECT 
WHERE realm_name = 'Oracle System Privilege and Role Management Realm'
AND OBJECT_TYPE = 'ROLE'
ORDER BY OBJECT_NAME;

5.2.6 Oracle Default Component Protection Realm

Oracle Default Component Protection Realm protects the SYSTEM and OUTLN schemas.

The authorized users of this realm are users SYS and SYSTEM.

5.3 Creating a Realm

To enable realm protection, you create the realm and configure it to include realm-secured objects, roles, and authorizations.

  1. Log in to Oracle Database Vault Administrator from Cloud Control as a user who has been granted the DV_OWNER or DV_ADMIN role and the SELECT ANY DICTIONARY privilege. Logging into Oracle Database Vault explains how to log in.
  2. In the Administration page, under Database Vault Components, click Realms.
  3. In the Realms page, click Create to display the Create Realm page.
  4. In the Create Realm page, enter the following settings:
    • Name: Enter a name for the realm. It can contain up to 90 characters in mixed-case. This attribute is mandatory.

      Oracle suggests that you use the name of the protected application as the realm name (for example, hr_app for an human resources application).

    • Description: Enter a brief description of the realm. The description can contain up to 1024 characters in mixed-case. This attribute is optional.

      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.

    • Mandatory Realm: Select this check box to create the realm as a mandatory realm. See Mandatory Realms to Restrict User Access to Objects within a Realm for more information about mandatory realms.

    • Status: Select either Enabled, Disabled, or Simulation. This attribute is mandatory.

    • Audit Options: Select one of the following:

      • Audit Disabled: Does not create an audit record.

      • Audit on Success: Creates an audit record for authorized activities.

      • Audit on Failure: Creates an audit record when a realm violation occurs (for example, when an unauthorized user tries to modify an object that is protected by the realm).

      • Audit on Success or Failure: Creates an audit record for any activity that occurs in the realm, including both authorized and unauthorized activities.

      In a non-unified auditing environment, Oracle Database Vault writes the audit trail to the DVSYS.AUDIT_TRAIL$ table. See Auditing Oracle Database Vault for more information. If you have enabled unified auditing, then this setting does not capture audit records. Instead, you must create audit policies to capture this information, as described in Oracle Database Security Guide. Oracle Database also provides a default policy, ORA_DV_AUDPOL, that audits all actions that are performed on the Oracle Database Vault DVSYS and DVF schema objects and the Oracle Label Security LBACSYS schema objects.

  5. Click Next to display the Realm secured objects page.

    See About Realm-Secured Objects for conceptual information about the settings for this page.

  6. Click the Add button, and in the Add Secured Object dialog box, enter the following information:
    • Object Owner: From the list, select the name of the database schema owner. You can enter the % character if the object you want to secure with the realm is a role. This attribute is mandatory.

    • Object Type: From the list, select the type of the database object, such as TABLE, INDEX, or ROLE. This attribute is mandatory.

      You can add as many objects of any type as you want to the realm.

      By default, the Object Type box contains the % wildcard character to include all object types for the specified Object Owner. However, it does not include roles, which do not have specific schema owners in the database and must be specified explicitly.

    • Object Name: Enter the name of the object in the database that the realm must protect, or enter % 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 setting. But if Object Type is set to Tables, then using % for the Object Name refers to all tables in the schema. This attribute is mandatory.

      By default, the Object Name field contains the % wildcard character to encompass the entire schema specified for Object Type and Object Owner. Note that the % wildcard character applies to objects that do not yet exist and currently existing objects.

      If you enter % in the Object Name field, then it encompasses all objects in the schema if you also enter % in the Object Type field. However, if you set Object Type to a specific object type, such as Tables, then the setting of % in Object Type refers to all objects of that type (in this case, tables) in the schema.

  7. Click Next to display the Realm authorizations page.

    See About Realm Authorization for conceptual information about the settings for this page.

  8. Click the Add button, and in the Add Authorizations dialog box, enter the following information:
    • Realm Authorization Grantee: From the list, select the database account or role to whom you want to grant the realm authorization.

      This list shows all accounts and roles in the system, not just accounts with system privileges.

    • Realm Authorization Type: Select either of the following settings. This attribute is mandatory.

      • Participant: This account or role can exercise system privileges to access, manipulate, and create objects protected by the realm, provided that these privileges have been granted using the standard Oracle Database privilege grant process. A realm can have multiple participants.

      • Owner: This account or role has the same rights as the realm participant, plus the authorization to grant or revoke realm-secured database roles. The realm owner can grant or revoke privileges on realm-protected objects to other users. A realm can have multiple owners.

    • Realm Authorization Rule Set: Select from the available rule sets that have been created for your site. You can select only one rule set, but the rule set can have multiple rules.

      See Creating a Rule to Add to a Rule Set for more information about defining rules to govern the realm authorization.

      Any auditing and custom event handling associated with the rule set occurs as part of the realm authorization processing.

  9. Click Next to display the Review page.
  10. In the Review page, check the settings you have created.
  11. Click Finish to complete the realm creation.

5.4 About Realm-Secured Objects

Realm-secured objects define the territory—a set of schema and database objects and roles—that a realm protects.

You can create the following types of protections:

  • Objects from multiple database accounts or schemas can be under the same realm.

  • One object can belong to multiple realms.

    If an object belongs to multiple realms, then Oracle Database Vault checks the realms for the proper authorization. For SELECT, DDL, and DML statements, as long as a user is a participant in one of the realms, and if the command rules permit it, then the commands that the user enters are allowed. For GRANT and REVOKE operations of a database role in multiple realms, the person performing the GRANT or REVOKE operation must be the realm owner. Schema owners can perform DML operations on objects that are protected by multiple regular realms.

    If one of the realms is a mandatory realm, then the user who wants to access the object must be a realm owner or participant in the mandatory realm. During the authorization checking process, the non-mandatory realms are ignored. If there are multiple mandatory realms that protect the object, then the user who wants to access the object must be authorized in all of the mandatory realms.

5.5 About Realm Authorization

Realm authorizations establish the set of database accounts and roles that manage or access objects protected in realms.

You can grant a realm authorization to an account or role to allow the use of its system privileges in the following situations:

  • When the user must create or access realm-secured objects

  • When a user must grant or revoke realm-secured roles

A user who has been granted realm authorization as either a realm owner or a realm participant can use its system privileges to access secured objects in the realm.

Note the following:

  • Realm owners cannot add other users to their realms as owners or participants. Only users who have the DV_OWNER or DV_ADMIN role are allowed to add users as owners or participants to a realm.

  • Users who have been granted the DV_OWNER role can add themselves to a realm authorization.

  • A realm owner, but not a realm participant, can grant or revoke realm secured roles or grant or revoke object privileges on realm secured objects to anyone.

  • A user can be granted either as a realm owner or a realm participant, but not both. However, you can update the authorization types of existing realm authorizations.

Use the Edit Realm page to manage realm authorizations. You can create, edit, and remove realm authorizations.

5.6 Realm Authorizations in a Multitenant Environment

In a multitenant environment, the rules and behavior for common realm authorizations are similar to the authorizations for other common objects.

Local Authorization for a Common Realm

The local authorization for a common realm refers to the authorization a user has for the PDB that this user is accessing.

The rules for the local authorization for a common realm are as follows:

  • A user who has been commonly granted the DV_OWNER or DV_ADMIN role can grant local authorization to common users, common roles, local users, and local roles. The common DV_OWNER or DV_ADMIN user can also remove local authorization from a common realm in a PDB.

  • A local Database Vault administrator can authorize locally (that is, grant local authorizations to both local and common users) within the PDB. A common Database Vault administrator can also grant authorizations in each PDB. A common realm authorization can only be granted by a common Database Vault administrator in the application root.

  • The common Database Vault administrator can both add or remove local authorization to and from a common realm from within the PDB.

  • If a common user has only local authorization for a common realm, then this user cannot access the common realm in any other PDB than this local authorization.

  • A common user or a common role can have both the local authorization and the common authorization to a common realm at the same time. Removing a common user’s local authorization from a common realm does not affect the common user’s common authorization. Removing a common user’s common authorization from a common realm does not affect the common user’s local authorization.

Common Authorization for a Common Realm

The common authorization for a common realm refers to the authorization a common user or a common role has in the application root while the authorization takes effect in every container that is Database Vault enabled.

The rules for the local authorization for a common realm are as follows:

  • A user who has been commonly granted the DV_OWNER or DV_ADMIN role can grant common realm authorization to common users or roles in the application root. This common Database Vault administrator can perform the removal of common authorizations while in the application root.

  • This common authorization applies to the containers that have been Database Vault enabled in the CDB.

  • If a common user is authorized to a common realm in the application root, then this user has access to the objects protected by the common realm in the application root and any application PDBs.

  • Any rule sets that are associated with a common realm must be common rule sets. The rules that are added to a common rule set that is associated with common authorization cannot involve any local objects.

How the Authorization of a Realm Works in Both the Application Root and in an Individual PDB

During the Database Vault enforcement in a container, a common realm performs the same enforcement behaviors as the same realm when it is used locally in a PDB.

5.7 Modifying the Enablement Status of a Realm

You can disable or enable a realm, or set the realm to use simulation mode, from Enterprise Manager Cloud Control.

If the realm is managed by a policy, and if the policy status is set to partial, then you can modify the enablement status of the realm. If the policy is set to enabled, disabled, or simulation mode, then you cannot modify the enablement status of the realm.
  1. In the Oracle Database Vault Administration page, select Realms.
  2. In the Realms page, select the realm you want to disable or enable, and then select Edit.
  3. In the Edit Realm page, under Status in the General section, select either Disabled, Enabled, or Simulation.
  4. Click Done, and then click Finished.

5.8 Deleting a Realm

You can use Enterprise Manager Cloud Control to delete realms.

  1. Locate the various references to the realm that you want to delete by querying the realm-related Oracle Database Vault data dictionary views.
  2. If the Realm is part of a policy, then remove the realm from the policy.
    1. In the Oracle Database Vault Administration page, select Policies.
    2. Select the policy that contains the realm and then click Edit.
    3. Expand the Realms area.
    4. Select the realm and then click Remove.
    5. Click Next, then Finish.
  3. In the Administration page, under Database Vault Components, select Realms.
  4. In the Realms page, select the realm you want to delete, and then select Remove.
  5. In the Confirmation window, click Yes.

    Oracle Database Vault deletes the configuration for the realm, including realm authorizations. It does not delete the rule sets used for realm authorizations.

5.9 How Realms Work

When an appropriately privileged database account issues a SQL statement that affects an object within a realm, a special set of activities occur.

These privileges include DDL, DML, EXECUTE, GRANT, REVOKE, or SELECT privileges.

  1. Does the SQL statement affect objects secured by a realm?

    If yes, then go to Step 2. If no, then realms do not affect the SQL statement. Go to Step 7. If the object affected by the command is not secured in any realms, then realms do not affect the SQL statement being attempted.

  2. Is the realm a mandatory realm or regular realm?

    If yes, then go to Step 4. If it is regular realm, then go to Step 3.

  3. Is the database account using a system privilege to execute the SQL statement?

    If yes, then go to Step 4. If no, then go to Step 6. If the session has object privileges on the object in question for SELECT, EXECUTE, and DML statements only, then the realm protection is not enforced. Realms protect against the use of any system privilege on objects or roles protected by the realm. Even users with object privileges for objects that are protected by regular realms are prevented from performing DDL operations.

    Remember that if the O7_DICTIONARY_ACCESSIBILITY initialization parameter has been set to TRUE, then non-SYS users have access to SYS schema objects. For better security, ensure that O7_DICTIONARY_ACCESSIBILITY is set to FALSE.

  4. Is the database account a realm owner or realm participant?

    If yes, then go to Step 5. Otherwise, a realm violation occurs and the statement is not allowed to succeed. If the command is a GRANT or REVOKE of a role that is protected by the realm, or the GRANT or REVOKE of an object privilege on an object protected by the realm, then the session must be authorized as the realm owner directly or indirectly through roles.

  5. Is the realm authorization for the database account conditionally based on a rule set?

    If yes, then go to Step 6. If no, then go to Step 7.

  6. Does the rule set evaluate to TRUE?

    If yes, then go to Step 7. If no, then there is a realm violation, so the SQL statement is not allowed to succeed.

  7. Does a command rule prevent the command from executing?

    If yes, then there is a command rule violation and the SQL statement fails. If no, then there is no realm or command rule violation, so the command succeeds.

    For example, the HR account may have the DROP ANY TABLE privilege and may be the owner of the HR realm, but a command rule can prevent HR from dropping any tables in the HR schema unless it is during its monthly maintenance window. Command rules apply to the use of the ANY system privileges and object privileges and are evaluated after the realm checks.

In addition, because a session is authorized in a realm, it does not mean the account has full control on objects protected by the realm. Realm authorization does not implicitly grant extra privileges to the account. The account still must have system privileges or object privileges to access the objects. For example, an account or role may have the SELECT ANY table privilege and be a participant in the HR realm. This means the account or the account granted the role could query the HR.EMPLOYEES table. Being a participant in the realm does not mean the account or role can DROP the HR.EMPLOYEES table. Oracle Database Vault does not replace the discretionary access control model in the existing Oracle database. It functions as a layer on top of this model for both realms and command rules.

Note the following:

  • Protecting a table in a realm does not protect the view by default. Any view that must be protected should be added to the realm regardless of whether the view was created before or after the table was added to the realm.

  • For invoker's right procedures that access realm protected objects, the invoker of the procedure must be authorized to the realm.

  • Be aware that realm protection does not protect a table if access to the table has been granted to PUBLIC. For example, if SELECT ON table_name is granted to PUBLIC, then every user has access to table_name (unless the table is protected by a mandatory realm), even if this table is protected by a realm. As a best practice, revoke unnecessary privileges from PUBLIC.

5.10 How Authorizations Work in a Realm

Realm authorizations prevent users from performing activities if the users do not have the correct privileges.

5.10.1 About Authorizations in a Realm

Realms protect data from access through system privileges.

Realms do not give additional privileges to the data owner or participants.

The realm authorization provides a run-time mechanism to check logically if a user's command should be allowed or denied to access objects specified in the command and to proceed with its execution.

System privileges are sweeping database privileges such as CREATE ANY TABLE and DELETE ANY TABLE. These privileges typically apply across schemas and bypass the need for object privileges. Data dictionary views such as DBA_SYS_PRIVS, USER_SYS_PRIVS, and ROLE_SYS_PRIVS list the system privileges for database accounts and roles. Database authorizations work normally for objects not protected by a realm. However, a user must be authorized as a realm owner or participant to successfully use his or her system privileges on objects secured by the realm. A realm violation prevents the use of system privileges and can be audited.

Mandatory realms block both object privileged-based access and system privilege-based access. This means that even the object owner cannot have access if he or she is not authorized to access the realm. Users can access secured objects in the mandatory realm only if the user or role is authorized to do so.

5.10.2 Examples of Realm Authorizations

You can create realms that protect objects from users who have system privileges and other powerful privileges, for example.

5.10.2.1 Example: Unauthorized User Trying to Create a Table

The ORA-47401 error appears when unauthorized users try to create tables.

Example 5-1 shows what happens when an unauthorized user who has the CREATE ANY TABLE system privilege tries to create a table in a realm where the HR schema is protected by a realm.

Example 5-1 Unauthorized User Trying to Create a Table

CREATE TABLE HR.demo2 (col1 NUMBER(1));

The following output should appear

ORA-47401: Realm violation for CREATE TABLE on HR.DEMO2

As you can see, the attempt by the unauthorized user fails. Unauthorized use of system privileges such as SELECT ANY TABLE, CREATE ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, CREATE ANY INDEX, and others results in failure.

5.10.2.2 Example: Unauthorized User Trying to Use the DELETE ANY TABLE Privilege

An ORA-01031: insufficient privileges error appears for unauthorized user access.

Example 5-2 shows what happens when an unauthorized database account tries to use his DELETE ANY TABLE system privilege to delete an existing record, the database session returns the following error.

Example 5-2 Unauthorized User Trying to Use the DELETE ANY TABLE Privilege

DELETE FROM HR.EMPLOYEES WHERE EMPNO = 8002;

The following output should appear:

ERROR at line 1:
ORA-01031: insufficient privileges

Realms do not affect direct privileges on objects. For example, a user granted delete privileges to the HR.EMPLOYEES table can successfully delete records without requiring realm authorizations. Therefore, realms should minimally affect normal business application usage for database accounts.

5.10.2.3 Example: Authorized User Performing DELETE Operation

Authorized users are allowed to perform the activities for which they are authorized.

Example 5-3 shows how an authorized user can perform standard tasks allowed within the realm.

Example 5-3 Authorized User Performing DELETE Operation

DELETE FROM HR.EMPLOYEES WHERE EMPNO = 8002;

1 row deleted.

5.11 Access to Objects That Are Protected by a Realm

You can protect an object by a realm, but still enable access to objects that are part of this realm-protected object.

For example, suppose you create a realm around a specific table. However, you want users to be able to create an index on this table. You can accomplish this as follows, depending on the following scenarios.

  • The user does not have the CREATE ANY INDEX privilege. As the realm owner of the table, grant the CREATE INDEX ON table privilege to the user who must create the index.

  • The user has the CREATE ANY INDEX privilege. In this case, create another realm and make all index types as the secured objects and grant that user participant authorization to the realm. (Remember that having the CREATE ANY INDEX privilege alone is not sufficient for a non-realm participant to create an index in a realm-protected table.)

  • You want all of your database administrators to be able to create an index and they have the CREATE ANY INDEX privilege. In your data protection realm, specify all object types to be protected except the index types. This permits all of your administrators to create indexes for the protected table.

5.12 Example of How Realms Work

Realms can provide protection in which two users who each have the same privileges must have separate access levels for an object.

Figure 5-1 illustrates how data within a realm is protected.

In this scenario, two users, each in charge of a different realm, have the same system privileges. The owner of a realm can be either a database account or a database role. As such, each of the two roles, OE_ADMIN and HR_ADMIN, can be protected by a realm as a secured object and be configured as the owner of a realm.

Further, only a realm owner, such as OE_ADMIN, can grant or revoke database roles that are protected by the realm. The realm owner cannot manage roles protected by other realms such as the DBA role created by SYS in the Oracle System Privilege and Role Management realm. Any unauthorized attempt to use a system privilege to access realm-protected objects raises a realm violation, which can be audited. The powers of each realm owner are limited within the realm itself. For example, OE_ADMIN has no access to the Human Resources realm, and HR_ADMIN has no access to the Order Entry realm.

Figure 5-1 How Authorizations Work for Realms and Realm Owners

Description of Figure 5-1 follows
Description of "Figure 5-1 How Authorizations Work for Realms and Realm Owners"

5.13 How Realms Affect Other Oracle Database Vault Components

Realms have no effect on factors, identities, or rule sets, but they do affect command rules.

With command rules, Oracle Database Vault evaluates the realm authorization first when processing SQL statements.

How Realms Work explains the steps that Oracle Database Vault takes to process SQL statements that affect objects in a realm. How Command Rules Work describes how command rules are processed.

5.14 Guidelines for Designing Realms

Oracle provides a set of guidelines for designing realms.

  • Create realms based on the schemas and roles that form a database application.

    Define database roles with the minimum and specific roles and system privileges required to maintain the application objects and grant the role to named accounts. You then can add the role as an authorized member of the realm. For object-level privileges on objects protected by the realm and required by an application, create a role and grant these minimum and specific object-level privileges to the role, and then grant named accounts this role. In most cases, these types of roles do not need to be authorized in the realm unless ANY-style system privileges are already in use. A model using the principle of least privilege is ideal for any database application.

  • A database object can belong to multiple realms and an account or role can be authorized in multiple realms.

    To provide limited access to a subset of a database schema (for example, just the EMPLOYEES table in the HR schema), or roles protected by a realm, create a new realm with just the minimum required objects and authorizations.

  • If you want to add a role to a realm as a grantee, create a realm to protect the role. Doing so prevents users who have been granted the GRANT ANY ROLE system privilege, such as the SYSTEM user account, from granting the role to themselves.

  • If you want to add the SYS user account to a realm authorization, you must add user SYS explicitly and not through a role (such as the DBA role).

  • Be mindful of the privileges currently allowed to a role that you plan to add as a realm authorization.

    Realm authorization of a role can be accidentally granted and not readily apparent if an account such as SYS or SYSTEM creates a role for the first time and the Oracle Database Vault administrator adds this role as a realm authorization. This is because the account that creates a role is implicitly granted the role when it is created.

  • Sometimes you must temporarily relax realm protections for an administrative task. Rather than disabling the realm, have the Security Manager (DV_ADMIN or DV_OWNER) log in, add the named account to the authorized accounts for the realm, and set the authorization rule set to Enabled. Then in the enabled rule set, turn on all auditing for the rule set. You can remove the realm authorization when the administrative task is complete.

  • If you want to grant ANY privileges to new users, Oracle recommends that you add a database administrative user to the Oracle System Privilege and Role Management realm so that this user can grant other users ANY privileges, if they need them. For example, using a named account to perform the GRANT of the ANY operations enables you to audit these operations, which creates an audit trail for accountability.

  • If you drop a table, index, or role that has been protected by a realm and then recreate it using the same name, the realm protection is not restored. You must re-create the realm protection for the new table, index, or role. However, you can automatically enforce protection for all future tables, indexes, and roles within a specified schema. For example, to enforce protection for all future tables:

    BEGIN
     DBMS_MACADM.ADD_OBJECT_TO_REALM('realm_name', 'schema_name', '%', 'TABLE');
    END;
    /
  • You can test the development phase of a realm by using simulation mode, which enables the realm but writes detailed information about violations to a log file.

5.15 How Realms Affect Performance

Realms can affect database performance in a variety situations, such as with DDL and DML operations.

  • DDL and DML operations on realm-protected objects do not have a measurable effect on Oracle Database. Oracle recommends that you create the realm around the entire schema, and then authorize specific users to perform only specific operations related to their assigned tasks. For finer-grained control, you can define realms around individual tables and authorize users to perform certain operations on them, and also have a realm around the entire schema to protect the entire application. Note that this type of configuration (that is, multiple realms protecting the same objects) does not result in significant performance degradation, and it does enable you to grant realm authorization to some of the objects in a schema.

  • Auditing affects performance. To achieve the best performance, Oracle recommends that you use fine-grained auditing rather than auditing all operations.

  • Periodically check the system performance. You can do so by running tools such as Oracle Enterprise Manager (including Oracle Enterprise Manager Cloud Control, which is installed by default with Oracle Database), Automatic Workload Repository (AWR), and TKPROF.

5.16 Realm Related Reports and Data Dictionary Views

Oracle Database Vault provides reports and data dictionary views that are useful for analyzing realms.

Table 5-1 lists the Oracle Database Vault reports. See Oracle Database Vault Reports, for information about how to run these reports.

Table 5-1 Reports Related to Realms

Report Purpose

Realm Audit Report

Audits records generated by the realm protection and realm authorization operations

Realm Authorization Configuration Issues Report

Lists authorization configuration information, such as incomplete or disabled rule sets, or nonexistent grantees or owners that may affect the realm

Rule Set Configuration Issues Report

Lists rule sets that do not have rules defined or enabled, which may affect the realms that use them

Object Privilege Reports

Lists object privileges that the realm affects

Privilege Management - Summary Reports

Provides information about grantees and owners for a realm

Sensitive Objects Reports

Lists objects that the command rule affects

Table 5-2 lists data dictionary views that provide information about existing realms.

Table 5-2 Data Dictionary Views Used for Realms

Data Dictionary View Description

DBA_DV_REALM View

Lists the realms created in the current database instance.

DBA_DV_REALM_AUTH View

lists the authorization of a named database user account or database role (GRANTEE) to access realm objects in a particular realm

DBA_DV_REALM_OBJECT View

Lists the database schemas, or subsets of schemas with specific database objects contained therein, that are secured by the realms