When a SQL statement is processed by Database Vault, it is checked for realm violations. The steps are as follows:
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.
2. Is the realm a mandatory realm? If yes, then go to Step 4. If it is a 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. 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 and 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 and the command succeeds.
Benefits of Using Realms
Use realms to protect a set of database objects or roles from otherwise privileged users.
- For instance, DBAs have sweeping privileges, such as SELECT ANY TABLE or DROP ANY TABLE. These users can read or destroy data that they may not necessarily require access to. It would be better to limit access to that data to application users and application DBAs.
- A realm can be defined to limit access. You can define the realm, add the objects to it that are protected, and add the users who are allowed to access the objects that are under the realm.
- Realms can also limit the set of users who can grant privileges on the objects inside the realm. This would apply to, for example, granting EXECUTE on packages, SELECT on views, and GRANT on roles.
- Realms also help with compliance requirements found in PCI, SOX, HIPAA, and others that require granting access to sensitive data based on a need-to-know basis.
- Mandatory realms can be used in response to a cyber threat, preventing all access until the threat has been analyzed.
Protecting Objects from DBAs
In this example, the SALES_DBA user has been set up as the database administrator for the Sales application. Accordingly, this user has been assigned the DBA role, which means that the role has many powerful privileges, including DROP ANY TABLE. Normally, SALES_DBA would be able to drop any table in the database, including those in other schemas, such as HR.
Step 1 shows how this application DBA is able to drop a table belonging to another application.
SQL> CONNECT sales_dba/password SQL> DROP TABLE hr.bonus_it; Table dropped.
In step 2, the leo_dvowner user uses Database Vault Administrator to create a realm and secure all the HR tables in that realm.
Then, in step 3, the same SALES_DBA user attempts to drop the same (now restored) HR table, but is unable to. Instead, a realm violation error is returned, and the DROP TABLE command fails.
SQL> DROP TABLE hr.bonus_it;
ORA-47401: Realm Violation for drop table
on HR.BONUS_IT
Effect of Realms on Nonmembers
If a user is not a member of a realm authorization, the only way that user can access the realm- secured objects is to have the relevant object privileges granted to him or her. A user cannot rely on system privileges, such as SELECT ANY TABLE to access objects, if those objects are secured in a realm.
Also, users cannot rely on the fact that they own the schema being accessed. Schema owners cannot drop, alter, or create objects in their own schemas if that schema is protected in a realm, and the schema owners are not members of that realm. To provide this access, the schema owner can be authorized to the realm. The other users can be granted either the DV_REALM_OWNER role or direct object-level privileges on the schema’s objects. The grant would have to be done by a user who is already a member (specifically an owner) in the realm.
Protecting Roles
The following steps illustrate how to protect a role by using a realm:
1. A user creates the BENNIES role.
SQL> CREATE ROLE bennies; Role created.
2. The leo_dvowner user uses Database Vault Administrator (DVA) to secure the BENNIES role in a realm.
3. The leo_dvowner user uses DVA to add HR as a participant in that realm.
4. The HR user attempts to grant the role, but fails with a realm violation. This is because the HR user is a participant and not an owner in the realm.
SQL> grant bennies to sh; ORA-47401: Realm Violation for grant role privilege on NULL.NULL
5. The leo_dvowner user uses DVA to change the HR user to be an owner in the realm rather than a participant.
6. The HR user is now able to grant the role.
SQL> GRANT bennies TO sh; Grant succeeded.
7. The HR user is also able to revoke the role.
SQL> REVOKE bennies FROM sh; Revoke succeeded.
Mandatory Realms and Object Privileges
By default, users who own or have object privileges are allowed to access realm-protected objects without explicit realm authorization. However, you can configure the realm to prevent these users from accessing objects, by configuring the realm to be a mandatory realm.
In Oracle Database 12c, if you need to prevent users from accessing realm-secured objects by using object privileges, you create a mandatory realm. For this realm, users can access the realm- secured objects only if they are members of the realm authorization. Consequently, the users granted object privileges on the realm-secured objects and the schema owner of realm-secured objects need to be members of the realm to be able to access the realm-secured objects.
You can also use mandatory realms in response to a cyber threat, preventing all access until the threat has been analyzed.
Protecting with a Mandatory Realm
A realm protects the HR schema. The HR user is able to select rows of the protected tables in the HR schema because the owner is always granted OBJECT privileges on his objects. Because this privilege is not always a desirable situation, you decide to disallow the HR user from selecting data from its own tables. You can either update the HR_REALM as mandatory or you can create a mandatory realm and protect the sensitive objects.
use case:
1. HR is able to select EMPLOYEES table rows protected in HR_REALM regular realm.
SQL> select last_name from hr.employees; LAST_NAME ---------- SMITH JONES
2. Leo_dvownwer makes the HR_REALM realm mandatory.
3. HR cannot view EMPLOYEES table rows.
SQL> select last_name from hr.employees; select * from hr.employees * ERROR at line 1: ORA-01031: insufficient privileges
Characteristics of Mandatory Realms
- When an object is protected by a regular and a mandatory realm, the more secure rules apply.
- If there are multiple mandatory realms on the same object, you must authorize the user or role on all the mandatory realms before the user 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.
Benefits of Mandatory Realms
The benefits of mandatory realms are listed below:
- Can block object owners and object privileged users.
- Provide more flexible configurations for access control.
- Add a layer of protection during patch upgrades.
- Secure tables during run time.
- Allow freezing of security settings by preventing changes to configured roles.
- Add an additional multifactor authorization check even for connections coming through the application account.
Protecting Sensitive Data During Patching
During a patch upgrade, a database administrator may need direct access to a realm-protected object in order to perform a patch on the object. If tables in the same realm contain sensitive data, such as Social Security numbers, you can use mandatory realms to protect these tables from the administrator’s access during the patch upgrade.
When the administrator completes the patching and no longer needs access to the objects, you can disable the mandatory realm protection so that the applications can work normally. In this way, mandatory realms can provide protection against DBAs during patching time, even if the DBAs can log in as application schema owners.
Protecting Sensitive Data During Run Time
During run time, application data is stored in many tables of different schemas. It is recommended that you have a single-user APPS, such as a runtime schema, to access these tables so that data integrity and accuracy are maintained. When application data scatter in many different schemas, schema owners, as well as users with object privileges, can also change the data if they log in to the database directly.
To make sure that no users can update the tables without running the runtime schemas procedures, use realms to protect the tables. This way, only the authorized user’s procedures can access them. Because a regular realm does not block object owners and object privileged users, use mandatory realms to block them, as in steps 1 and 2 in the slide. Only authorized users can access these tables.
Tasks Involving Realms
1. You have to create the realm before adding the objects. This can be done in different tools. All use the CREATE_REALM procedure of the DBMS_MACADM package in the DVSYS schema.
2. Editing a realm enables you to change anything that is defined for the realm. This is also the method for securing objects under the realm (RENAME_REALM, UPDATE_REALM).
3. When you create a realm-secured object, you put the object under the protection of the realm. You specify the owner of the object or objects to be added. Objects belonging to different users, of different types, can be under the same realm. You can use “%” for all object types, or a specific type, such as TABLE or CLUSTER. Similarly, you can use “%”for object names (ADD_OBJECT_TO_REALM, DELETE_OBJECT_FROM_REALM).
4. To add authorizations to a realm, define the grantee (the user or role name that is being authorized) and the type: participant or owner (which is the equivalent of the WITH ADMIN option) (ADD_AUTH_TO_REALM, UPDATE_REALM_AUTH, DELETE_AUTH_FROM_REALM).
5. When you add authorization to a realm, you can also specify an authorization rule set. This rule set must be satisfied to allow access to the realm-protected objects.
6. Database Vault deletes the configuration for the realm, including realm authorizations. It does not delete the rule sets used for realm authorizations (DELETE_REALM, DELETE_REALM_CASCADE).
Realm Attributes
The following are the attributes of a realm:
1. Name: The name of the realm. This is used to refer to it later. It is case-sensitive.
2. Description: A description of the realm.
3. Status: Either Enabled or Disabled. If it is Disabled, it has no effect. The Status is Enabled by default.
4. Audit Options: Audit options for a realm can be set to one of the following values:
- Audit Disabled
- Audit on Failure (default)
- Audit on Success or Failure
In a non-unified auditing environment, Database Vault writes the audit trail to the DVSYS.AUDIT_TRAIL$ table. If you have enabled unified auditing, this setting does not capture audit records. Instead, you must create and enable audit policies to capture this information.
5. Realm Secured Objects: The list of schema objects and roles that are protected by the schema
6. Realm Authorizations: The list of authorized users or roles. This defines which users are able to access the objects that are secured by the realm.
Realm Views
The views that contain realm information are:
1. DBA_DV_REALM: Each realm is represented here with one row.
- NAME: The name of the realm
- DESCRIPTION: The description of the realm
- AUDIT_OPTIONS: A number indicating when auditing is done:
- 0: Never audit
- 1: Audit on failure
- 3: Audit on success or failure
- ENABLED: Whether the realm is enabled or not. The value can be Y or N.
2. DBA_DV_REALM_OBJECT: This view contains the list of realm-secured objects.
- REALM_NAME: Name of the realm
- OWNER: Owner of the realm-secured object
- OBJECT_NAME: Name of the secured object
- OBJECT_TYPE: Type of the secured object
3. DBA_DV_REALM_AUTH: The realm authorizations are represented in this view.
- REALM_NAME: Name of the realm
- GRANTEE: User or role authorized to access the realm-secured objects
- AUTH_RULE_SET_NAME: Rule set that must evaluate to TRUE in order for the grantee to access the realm-secured objects
- AUTH_OPTIONS: Indicates whether the grantee is able to grant any roles that are secured in this realm:
- Participant: Not able to grant
- Owner: Able to grant
4. The DVSYS.DV$REALM view describes settings that were used to create Database Vault realms, such as which audit options have been assigned, whether the realm is a mandatory realm, and so on.
Oracle-Defined Realms
Default realms are enabled and audit on failure.
- Oracle Database Vault: Protects configuration and role information in the Database Vault DVSYS, DVF, and LBACSYS schemas.
- Database Vault Account Management: 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.
- Oracle Enterprise Manager: Protects Oracle Enterprise Manager accounts that are used for monitoring and management (DBSNMP user and the OEM_MONITOR role).
- Oracle Default Schema Protection Realm: Protects roles and schemas that are used with Oracle features such as Oracle OLAP, Oracle Spatial, and Oracle Text.
- Oracle System Privilege and Role Management Realm: Protects all sensitive roles that are used for exporting and importing data to and from 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. Only owners of this realm can grant the protected roles to other users.
- Oracle Default Component Protection Realm: Protects the SYSTEM and OUTLN schemas. The authorized users of this realm are users SYS and SYSTEM.
Predefined Reports
- Realm Audit Report Audits: Records generated by the realm protection and realm authorization operations. This is helpful in troubleshooting rule sets and monitoring failed authorization attempts. Realm violations are also displayed in this report. This report would show when a database account attempts to perform an action on a realm object on which it is not authorized to perform that action. When you configure a realm, you set the audit options for the realm operations.
- Realm Authorization Configuration: 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.