4 Configuring Realms
You can create a realm around database objects to protect them, and then set authorizations to control user access to this data.
- What Are Realms?
Realms enable you to protect database objects, including specific object types. - Default Realms
Oracle Database Vault provides default realms to protect Database Vault andSYS
-related schemas, system and object privileges, roles, and audit-related objects. - Creating a Realm
The first step in enabling realm protection is to create the realm itself, and then add realm-secured objects, roles, and authorizations. - Modifying a Realm
You can use theDBMS_MACADM.UPDATE_REALM
procedure to modify the definition of a realm. - Deleting a Realm
You can use theDBMS_MACADM.DELETE_REALM
procedure to delete a realm. - About Realm-Secured Objects
Realm-secured objects define the territory—a set of schema and database objects and roles—that a realm protects. - About Realm Authorization
Realm authorizations establish the set of database accounts and roles that manage or access objects protected in realms. - Realm Authorizations in a Multitenant Environment
The rules and behavior for common realm authorizations are similar to the authorizations for other common objects. - 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. - How Authorizations Work in a Realm
Realm authorizations prevent users from performing activities if the users do not have the correct privileges. - 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. - 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. - How Realms Affect Other Oracle Database Vault Components
Realms have no effect on factors, identities, or rule sets, but they do affect command rules. - Guidelines for Designing Realms
Oracle provides a set of guidelines for designing realms. - How Realms Affect Performance
Realms can affect database performance in a variety situations, such as with DDL and DML operations. - Realm Related Reports and Data Dictionary Views
Oracle Database Vault provides reports and data dictionary views that are useful for analyzing realms.
4.1 What Are Realms?
Realms enable you to protect database objects, including specific object types.
- About Realms
A realm is a grouping of database schemas, database objects, and database roles that must be secured for a given application. - 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. - Realms in a Multitenant Environment
You can create a realm to protect common objects in the application root. - Object Types That Realms Can Protect
You can create realms around all objects in a schema of certain object types.
Parent topic: Configuring Realms
4.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, individual database roles 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 and system privilege access and will not allow users with object privileges to perform queries, DML, or DDL operations without realm authorization. In other words, if the objects are protected by mandatory realms, even the object owner cannot access their own objects without proper realm authorization.
For example, you can create a realm to protect the database schemas that are used by an accounting department's application. The realm will prohibit any user from using their system privileges (for example, SELECT ANY TABLE
) from accessing the realm-protected schema objects. When an entire schema is realm protected, all existing and new objects are protected. This includes tables, indexes, procedures, views, packages, and more. Users with direct object grant on objects protected by a regular realm are still allowed to use their grants. For example, if you are granted SELECT
on HR.EMPLOYEES
, you can perform the SELECT
command on objects protected by a regular realm. However, if that object is protected by a mandatory realm, you will not be allowed to perform the SELECT
command unless you are a member of the realm authorization list. Mandatory realms require the granted user to be an authorized participant in the mandatory realm.
Note the following:
-
You can run reports on realms that you create in Oracle Database Vault. Realms can be enabled, disabled, or placed in simulation mode where violations of the realm will be logged but the action will not be blocked. 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.
Parent topic: What Are Realms?
4.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 the are 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 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.
-
SYS
-owned objects are already protected by data dictionary protection and are not protected separately by Oracle Database Vault.
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 in to 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.
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.
Related Topics
Parent topic: What Are Realms?
4.1.3 Realms 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
Parent topic: What Are Realms?
4.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 |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Parent topic: What Are Realms?
4.2 Default Realms
Oracle Database Vault provides default realms to protect Database Vault and SYS
-related schemas, system and object privileges, roles, and audit-related objects.
You can add users to realms so that the user can perform tasks that are protected by the default realms.
- Oracle Database Vault Realm
TheOracle Database Vault
realm protects configuration and role information in the Oracle Database VaultDVSYS
,DVF
, andLBACSYS
schemas. - Database Vault Account Management Realm
TheDatabase Vault Account Management
realm defines the realm for the administrators who manage and create database accounts and database profiles. - Oracle Enterprise Manager Realm
Oracle Database Vault provides a realm specifically for Oracle Enterprise Manager monitoring accounts. - Oracle Default Schema Protection Realm
TheOracle Default Schema Protection Realm
protects roles and schemas that are used with Oracle features such as Oracle Text. - Oracle System Privilege and Role Management Realm
TheOracle System Privilege and Role Management Realm
protects all Oracle-supplied roles in an Oracle database. - Oracle Default Component Protection Realm
TheOracle Default Component Protection Realm
protects theSYSTEM
andOUTLN
schemas.
Parent topic: Configuring Realms
4.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.
To find the objects that this realm protects, perform the following query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_DV_REALM_OBJECT
WHERE REALM_NAME = 'Oracle Database Vault Realm'
ORDER BY OWNER, OBJECT_NAME;
To find the realm-authorized users, their role as participant or owner, and if an Oracle Database Vault rule set is applied to the authorized user, perform the following query:
SELECT GRANTEE, AUTH_OPTIONS, AUTH_RULE_SET_NAME
FROM DBA_DV_REALM_AUTH
WHERE REALM_NAME = 'Oracle Database Vault Realm'
ORDER BY GRANTEE;
Related Topics
Parent topic: Default Realms
4.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.
The owner of this realm can grant or revoke the CREATE SESSION
privilege to or from a user.
To find the objects that this realm protects, perform the following query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_DV_REALM_OBJECT
WHERE REALM_NAME = 'Database Vault Account Management'
ORDER BY OWNER, OBJECT_NAME;
To find the realm-authorized users, their role as participant or owner, and if an Oracle Database Vault rule set is applied to the authorized user, perform the following query:
SELECT GRANTEE, AUTH_OPTIONS, AUTH_RULE_SET_NAME
FROM DBA_DV_REALM_AUTH
WHERE REALM_NAME = 'Database Vault Account Management'
ORDER BY GRANTEE;
Related Topics
Parent topic: Default Realms
4.2.3 Oracle Enterprise Manager Realm
Oracle Database Vault provides a realm specifically for Oracle Enterprise Manager monitoring 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).
To find the objects that this realm protects, perform the following query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_DV_REALM_OBJECT
WHERE REALM_NAME = 'Oracle Enterprise Manager'
ORDER BY OWNER, OBJECT_NAME;
To find the realm-authorized users, their role as participant or owner, and if an Oracle Database Vault rule set is applied to the authorized user, perform the following query:
SELECT GRANTEE, AUTH_OPTIONS, AUTH_RULE_SET_NAME
FROM DBA_DV_REALM_AUTH
WHERE REALM_NAME = 'Oracle Enterprise Manager'
ORDER BY GRANTEE;
Related Topics
Parent topic: Default Realms
4.2.4 Oracle Default Schema Protection Realm
The 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
protects several roles and schemas.
-
To find the objects that this realm protects, perform the following query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_DV_REALM_OBJECT WHERE REALM_NAME = 'Oracle Default Schema Protection Realm' ORDER BY OWNER, OBJECT_NAME;
-
To find the realm-authorized users, their role as participant or owner, and if an Oracle Database Vault rule set is applied to the authorized user, perform the following query:
SELECT GRANTEE, AUTH_OPTIONS, AUTH_RULE_SET_NAME FROM DBA_DV_REALM_AUTH WHERE REALM_NAME = 'Oracle Default Schema Protection Realm' ORDER BY GRANTEE;
-
Roles that are protected by default:
CTXAPP
,OLAP_DBA
,EJBCLIENT
,OLAP_USER
-
Schemas that are protected by default:
CTXSYS
,EXFSYS
,MDDATA
,MDSYS
-
Schemas that are recommended for protection:
APEX_030200
,OWBSYS
,WMSYS
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.
Parent topic: Default Realms
4.2.5 Oracle System Privilege and Role Management Realm
The 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
.
To find the objects that this realm protects, perform the following query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_DV_REALM_OBJECT
WHERE REALM_NAME = 'Oracle System Privilege and Role Management Realm'
ORDER BY OWNER, OBJECT_NAME;
To find the realm-authorized users, their role as participant or owner, and if an Oracle Database Vault rule set is applied to the authorized user, perform the following query:
SELECT GRANTEE, AUTH_OPTIONS, AUTH_RULE_SET_NAME
FROM DBA_DV_REALM_AUTH
WHERE REALM_NAME = 'Oracle System Privilege and Role Management Realm'
ORDER BY GRANTEE;
Parent topic: Default Realms
4.2.6 Oracle Default Component Protection Realm
The Oracle Default Component Protection Realm
protects the SYSTEM
and OUTLN
schemas.
The authorized users of this realm are users SYS
and SYSTEM
.
To find the objects that this realm protects, perform the following query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_DV_REALM_OBJECT
WHERE REALM_NAME = 'Oracle Default Component Protection Realm'
ORDER BY OWNER, OBJECT_NAME;
To find the realm-authorized users, their role as participant or owner, and if an Oracle Database Vault rule set is applied to the authorized user, perform the following query:
SELECT GRANTEE, AUTH_OPTIONS, AUTH_RULE_SET_NAME
FROM DBA_DV_REALM_AUTH
WHERE REALM_NAME = 'Oracle Default Component Protection Realm'
ORDER BY GRANTEE;
Parent topic: Default Realms
4.3 Creating a Realm
The first step in enabling realm protection is to create the realm itself, and then add realm-secured objects, roles, and authorizations.
4.4 Modifying a Realm
You can use the DBMS_MACADM.UPDATE_REALM
procedure to modify the definition of a realm.
Parent topic: Configuring Realms
4.5 Deleting a Realm
You can use the DBMS_MACADM.DELETE_REALM
procedure to delete a realm.
Related Topics
Parent topic: Configuring Realms
4.6 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. ForGRANT
andREVOKE
operations of a database role in multiple realms, the person performing theGRANT
orREVOKE
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.
-
SYS
-owned objects are already protected by data dictionary protection and are not protected separately by Oracle Database Vault.
Parent topic: Configuring Realms
4.7 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
orDV_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.
Related Topics
Parent topic: Configuring Realms
4.8 Realm Authorizations 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
orDV_ADMIN
role can grant local authorization to common users, common roles, local users, and local roles. The commonDV_OWNER
orDV_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
orDV_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.
Parent topic: Configuring Realms
4.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.
- Are the user's object privileges correct?
Oracle Database Vault first checks the user's privileges before allowing the user to continue. If the user does not have the correct privileges, then grant these to the user. If the user's privileges are correct, then go to Step 2. Realm authorization does not implicitly grant additional privileges to the user.
-
Does the SQL statement affect objects secured by a realm?
If yes, then go to Step 3. If no, then realms do not affect the SQL statement. Go to Step 8. If the object affected by the command is not secured in any realms, then realms do not affect the SQL statement being attempted.
-
Is the realm a mandatory realm or regular realm?
If yes, then go to Step 5. If it is regular realm, then go to Step 4.
-
Is the database account using a system privilege to run the SQL statement?
If yes, then go to Step 5. If no, then go to Step 7. 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. -
Is the database account a realm owner or realm participant?
If yes, then go to Step 6. Otherwise, a realm violation occurs and the statement is not allowed to succeed. If the command is a
GRANT
orREVOKE
of a role that is protected by the realm, or theGRANT
orREVOKE
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. -
Is the realm authorization for the database account conditionally based on a rule set?
-
Does the rule set evaluate to
TRUE
?If yes, then go to Step 8. If no, then there is a realm violation, so the SQL statement is not allowed to succeed.
-
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 theDROP ANY TABLE
privilege and may be the owner of theHR
realm, but a command rule can preventHR
from dropping any tables in theHR
schema unless it is during its monthly maintenance window. Command rules apply to the use of theANY
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, ifSELECT ON
table_name
is granted toPUBLIC
, then every user has access totable_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 fromPUBLIC
.
Parent topic: Configuring Realms
4.10 How Authorizations Work in a Realm
Realm authorizations prevent users from performing activities if the users do not have the correct privileges.
- About Authorizations in a Realm
Realms protect data from access through system privileges. - Examples of Realm Authorizations
You can create realms that protect objects from users who have system privileges and other powerful privileges, for example.
Parent topic: Configuring Realms
4.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 their 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 they are 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.
Parent topic: How Authorizations Work in a Realm
4.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.
- Example: Unauthorized User Trying to Create a Table
TheORA-47401
error appears when unauthorized users try to create tables. - Example: Unauthorized User Trying to Use the DELETE ANY TABLE Privilege
AnORA-01031: insufficient privileges
error appears for unauthorized user access. - Example: Authorized User Performing DELETE Operation
Authorized users are allowed to perform the activities for which they are authorized.
Parent topic: How Authorizations Work in a Realm
4.10.2.1 Example: Unauthorized User Trying to Create a Table
The ORA-47401
error appears when unauthorized users try to create tables.
Example 4-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 4-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.
Parent topic: Examples of Realm Authorizations
4.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 4-2 shows what happens when an unauthorized database account tries to use their DELETE ANY TABLE
system privilege to delete an existing record, the database session returns the following error.
Example 4-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.
Parent topic: Examples of Realm Authorizations
4.10.2.3 Example: Authorized User Performing DELETE Operation
Authorized users are allowed to perform the activities for which they are authorized.
Example 4-3 shows how an authorized user can perform standard tasks allowed within the realm.
Example 4-3 Authorized User Performing DELETE Operation
DELETE FROM HR.EMPLOYEES WHERE EMPNO = 8002; 1 row deleted.
Parent topic: Examples of Realm Authorizations
4.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.
Parent topic: Configuring Realms
4.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 4-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 4-1 How Authorizations Work for Realms and Realm Owners
Description of "Figure 4-1 How Authorizations Work for Realms and Realm Owners"
Related Topics
Parent topic: Configuring Realms
4.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.
Parent topic: Configuring Realms
4.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 theHR
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 theSYSTEM
user account, from granting the role to themselves. -
If you want to add the
SYS
user account to a realm authorization, you must add userSYS
explicitly and not through a role (such as theDBA
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
orSYSTEM
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
orDV_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 usersANY
privileges, if they need them. For example, using a named account to perform theGRANT
of theANY
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 without enforcing the restrictions. Simulation mode writes detailed information about violations, allowing you to see the activities that have been enforced. A user who has the
DV_OWNER
orDV_ADMIN
role can view the simulation log by querying theDBA_DV_SIMULATION_LOG
data dictionary view.
Parent topic: Configuring Realms
4.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
.
Parent topic: Configuring Realms
4.16 Realm Related Reports and Data Dictionary Views
Oracle Database Vault provides reports and data dictionary views that are useful for analyzing realms.
Table 4-1 lists the Oracle Database Vault reports.
Table 4-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 |
All object privilege reports |
List object privileges that the realm affects |
Privilege management summary reports |
Provide information about grantees and owners for a realm |
Sensitive objects reports |
Lists objects that the command rule affects |
Table 4-2 lists data dictionary views that provide information about existing realms.
Table 4-2 Data Dictionary Views Used for Realms
Data Dictionary View | Description |
---|---|
|
Lists the realms created in the current database instance. |
|
lists the authorization of a named database user account or database role ( |
|
Lists the database schemas, or subsets of schemas with specific database objects contained therein, that are secured by the realms |
Parent topic: Configuring Realms