A realm is a core Oracle Database Vault structure that provides logical protection against users with any type of object-level privilege. A realm can be defined on any object in any schema. In this recipe, we will use both the PL/SQL interface and Oracle Database Vault Administrator (DVA) for defining realms.
In this chapter, we will create a realm named HR_TABLES_REALM by using the PL/SQL interface. This realm will include all the tables from the HR schema. Next, we will create a realm named HR_VIEWS_REALM by using DVA Console. This realm will include all the views from the HR schema. The user HR will be defined as the realm owner and the users vw_america and vw_europe will be defined as the realm participants.
Note
Before you start, you must have Oracle Database Vault installed. Details on installation can be found in the documentation page http://docs.oracle.com/cd/E11882_01/server.112/e23090/dvca.htm#CIAIHIDA and on deinstallation can be found at http://docs.oracle.com/cd/E11882_01/server.112/e23090/dvca.htm#CIAJGEBI. Details on enabling/disabling can be found at the Oracle Database Vault documentation link http://docs.oracle.com/cd/E11882_01/server.112/e23090/dvdisabl.htm#BJEDGGGA , http://docs.oracle.com/cd/B28359_01/network.111/b28529/getstrtd.htm#CIHBBJFA. Another detailed description about enabling and disabling Oracle Database Vault can be found in Oracle Support doc: How to Install/Deinstall Oracle Database Vault [ID 171155.1]. During installation you should create the Database Vault Owner user named odva_owner, and the Database Vault Account Manager odva_manager. All steps will be performed on the HACKDB database.
All realms and realm authorization will be created and granted using Oracle Database Vault owner user odva_owner. This can be done by
using the PL/SQL interface:
- Connect as the Oracle Vault Owner user.
SQL> conn odva_owner Enter password: Connected. SQL>
- Create
HR_TABLES_REALMby using thedbms_macadmpackage, as follows:SQL> BEGIN DBMS_MACADM.CREATE_REALM(REALM_NAME => 'HR_TABLES_REALM', DESCRIPTION =>'PROTECTS HR SCHEMA TABLES', ENABLED=> DBMS_MACUTL.G_YES, AUDIT_OPTIONS=>DBMS_MACUTL.G_REALM_AUDIT_OFF); END; 2 / Pl/sql procedure successfully completed. SQL>
- Add the table objects owned by the
HRuser toHR_TABLES_REALM, as follows:SQL> begin dbms_macadm.add_object_to_realm(realm_name=>'HR_TABLES_REALM',object_owner=>'HR',object_name=>'%',object_type =>'%' ); end; 2 / PL/SQL procedure successfully completed. SQL>
- Connect as the user
systemand issue aSELECTstatement against theemployeestable:SQL> conn system Enter password: Connected. SQL> select salary from hr.employees; select salary from hr.employees * ERROR at line 1: ORA-01031: insufficient privileges SQL>
Now all the tables from the
HRschema are protected by the realm. - Connect as the user
HRand issue aSELECTstatement against theemployeestable, as follows:SQL> conn HR Enter password: Connected. SQL> select salary from hr.employees where first_name like 'B%'; SALARY ---------- 6000 3900 SQL>
The user
HRbeing the schema owner has DML rights against all the tables within the schema.
Using Oracle Vault Administration Conso
- Type
https://yourhost:yourport/dvain the browser address bar. - Add the host, listener port, and SID or service name for your database. Log in as the user
odva_owner. - In the Administration tab, go to the Database Vault Features Administration panel and choose Realms.
- In the Realms administration page, click on the Create button.
- Then create
HR_VIEWS_REALMby specifying Name asHR_VIEWS_REALM, Description type asProtects HR Schema Views, and then click on Audit Disabled in the Audit Options section and finally click on the OK button.
- If the creation was successful, you will get back to the Realms administration panel. Here check HR_VIEWS_REALM and click on the Edit button, as follows:
- In the Create Realm Secured Object panel, click on the Create button. Choose HR as Object Owner, VIEW as Object Type, and enter
%for Object Name to add all the views, as follows:
- You will get back to the Edit Realm page. Click on the Create button from the Realms Authorization panel. Now in the Create Realm Authorization panel in the Grantee combobox, choose HR [USER]. As Authorization Type choose Owner, as follows:
- Next, create an additional realm authorization for the user
vw_america. For this choose VW_AMERICA [USER] in the Grantee section. Choose Authorization Type as Participant:
- Repeat the same steps for the user
vw_europe, and click on the OK button from the Edit Realm page. - In sqlplus, connect as the user
HR, grantselect onthe viewemp_details_viewtovm_americaandvw_europeusers, as follows:SQL> conn HR Enter password: Connected. SQL> grant select on emp_details_view to vw_america,vw_europe; Grant succeeded. SQL>
- As the user
vw_europeissue aSELECTagainstemp_details_view, as follows:SQL> conn vw_america Enter password: Connected. SQL> select first_name,last_name from hr.emp_details_view where employee_id=100; FIRST_NAME LAST_NAME -------------------- ------------------------- Steven King SQL>
The user
vw_america, having select rights and being a realm participant, can select fromemp_details_view.
Objects can be protected in a realm by following logical steps. (An example of how realms work can be found at http://docs.oracle.com/cd/E11882_01/server.112/e23090/cfrealms.htm#CHDBFEHJ.) By defining a realm, all privileges on objects will be revoked from all the users except the schema owner. A realm also has a set of authorizations; they could be realm owner and participants. A realm owner can grant and revoke realm-protected roles and privileges on and from the protected objects. A realm participant can access objects from a realm after it has received privileges on those objects from a realm owner.
It is important to remember that realms generally protect objects in conjunction with rulesets, factors, and command rules.
During the installation of Oracle Vault, a number of default realms are created around sensitive objects, as follows:
- Oracle Database Vault realm: This realm defines the realm around
DVSYSandDVFschemas, which are Oracle Database Vault functional schemas, and theLBACSYSschema, which is the functional schema for Oracle Label Security - Database Vault Account Management realm: A realm defined for the administrators (
odva_ownerandodva_manager) who manage and create database accounts and database profiles - Oracle Enterprise Manager realm: A realm defined around the Oracle Enterprise Manager (OEM) accounts
SYSMANandDBSNMP - Oracle Data Dictionary realm: A realm defined from the Oracle catalog schemas, such as
ANONYMOUS,DBSNMP,MDSYS,SYS,SYSMAN,OUTLN,MDDATA,BI,CTXSYS, andMGMT_VIEW
More information about realms can be found by running the Oracle Vault-related reports and by querying the DBA_DV_REALM, DBA_DV_REALM_AUTH, and DBA_DV_REALM_OBJECT system views. Realm violations can be a sign of an attack directed against protected objects. You can catch realm violations into a trace file by using event 47998 at session or system level (for example, ALTER SYSTEM SET EVENTS '47998 trace name context forever, level 12') or by setting audit on realm and use audit reports as we will see in the recipe Creating and using Oracle Database Vault reports from this chapter.