3 Getting Started with Oracle Database Vault

3.2.1 Step 1: Adding the SYSTEM User to the Data Dictionary Realm

In this tutorial, the SYSTEM user will grant ANY privileges to a new user account, SEBASTIAN. In order to do this, you will need to include SYSTEM in the Oracle Data Dictionary realm.

To include SYSTEM in the Oracle Data Dictionary realm:

1. Log in to Oracle Database Vault Administrator using a database account that has been granted the Database Vault Owner (DV_OWNER) role.

   "Starting Oracle Database Vault Administrator" explains how to log in.

2. In the Administration page, under Database Vault Feature Administration, click Realms.

3. In the Realms page, select Oracle Data Dictionary from the list and then click Edit.

4. In the Edit Realm: Oracle Data Dictionary page, under Realm Authorizations, click Create.

5. In the Create Realm Authorization Page, from the Grantee list, select SYSTEM [USER].

6. For Authorization Type, select Owner.

7. Leave Authorization Rule Set at <Non Selected>.

8. Click OK.

   In the Edit Realm: Oracle Data Dictionary page, SYSTEM should be listed as an owner under the Realm Authorizations.

9. Click OK to return to the Realms page.

10. To return to the Administration page, click the Database Instance instance_name link over Realms.

3.2.2 Step 2: Log On as SYSTEM to Access the HR Schema

Log in to SQL*Plus as user SYSTEM and access the HR schema.

sqlplus system
SQL> Enter password: password

SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Donald               OConnell                        2600
Douglas              Grant                           2600
Jennifer             Whalen                          4400
Michael              Hartstein                      13000
Pat                  Fay                             6000
Susan                Mavris                          6500
Hermann              Baer                           10000
Shelley              Higgins                        12000
William              Gietz                           8300
 
9 rows selected.

If the HR schema is locked and expired, log in to SQL*Plus as the Database Vault Account Manager and unlock and unexpire the account. For example:

sqlplus dbvacctmgr
Enter password: password

SQL> ALTER USER ACCOUNT UNLOCK IDENTIFIED BY password

Replace password with a password that is secure. See Oracle Database Security Guide for the minimum requirements for creating passwords.

As you can see, SYSTEM has access to the salary information in the EMPLOYEES table of the HR schema. This is because SYSTEM is automatically granted the DBA role, which includes the SELECT ANY TABLE system privilege.

3.2.3 Step 3: Create a Realm

Realms can protect one or more schemas, individual schema objects, and database roles. Once you create a realm, you can create security restrictions that apply to the schemas and their schema objects within the realm. Your first step is to create a realm for the HR schema.

1. In the Realms page of Oracle Database Vault Administrator, click Create.

2. In the Create Realm page, under General, enter HR Realm after Name.

3. After Status, ensure that Enabled is selected so that the realm can be used.

4. Under Audit Options, ensure that Audit On Failure is selected so that you can create an audit trial later on.

5. Click OK.

   The Realms Summary page appears, with HR Realm in the list of realms.

3.2.4 Step 4: Secure the EMPLOYEES Table in the HR Schema

At this stage, you are ready to add the EMPLOYEES table in the HR schema to the secured objects of the HR realm.

1. In the Realms page, select HR Realm from the list and then click Edit.

2. In the Edit Realm: HR Realm page, scroll to Realm Secured Objects and then click Create.

3. In the Create Realm Secured Object page, enter the following settings:

   • Object Owner: Select HR from the list.

   • Object Type: Select %.

   • Object Name: Enter EMPLOYEES.

4. Click OK.

5. In the Edit Realm: HR Realm page, click OK.

3.2.5 Step 5: Create an Authorization for the Realm

At this stage, there are no database accounts or roles authorized to access or otherwise manipulate the database objects the realm will protect. So, the next step is to authorize database accounts or database roles so that they can have access to the schemas within the realm. You will create the SEBASTIAN user account. After you authorize him for the realm, SEBASTIAN will be able to view and modify the EMPLOYEES table.

1. In SQL*Plus, connect as the Database Vault Account Manager, who has the DV_ACCTMGR role, and create user SEBASTIAN.

   For example:

   SQL> CONNECT dbvacctmgr
   Enter password: password
   
   SQL> CREATE USER SEBASTIAN IDENTIFIED BY password;
   

   Replace password with a password that is secure. See Oracle Database Security Guide for the minimum requirements for creating passwords.

2. Connect as SYSTEM privilege, and then grant SEBASTIAN the following additional privileges.

   SQL> CONNECT SYSTEM
   Enter password: password
   
   SQL> GRANT CREATE SESSION, SELECT ANY TABLE TO SEBASTIAN;
   

Do not exit SQL*Plus; you will need it for Step 6: Test the Realm, when you test the realm.

At this stage, even though SEBASTIAN has the SELECT ANY TABLE privilege, he cannot select from the HR.EMPLOYEES table because it is protected by a realm.

Next, authorize user SEBASTIAN to have access to the HR Realm as follows:

1. In the Realms page of Database Vault Administrator, select the HR Realm in the list of realms, and then click Edit.

2. In the Edit Realm: HR Realm page, scroll down to Realm Authorizations and then click Create.

3. In the Create Realm Authorization page, under Grantee, select SEBASTIAN[USER] from the list.

   If SEBASTIAN does not appear in the list, select the Refresh button in your browser.

   SEBASTIAN will be the only user who will have access to the EMPLOYEES table in the HR schema.

4. Under Authorization Type, select Owner.

   The Owner authorization allows the user SEBASTIAN in the HR realm to manage the database roles protected by HR, as well as create, access, and manipulate objects within the realm. In this case, the HR user and SEBASTIAN will be the only persons allowed to view the EMPLOYEES table.

5. Under Authorization Rule Set, select <Not Assigned>, because rule sets are not needed to govern this realm.

6. Click OK.

3.2.6 Step 6: Test the Realm

To test the realm, try accessing the EMPLOYEES table as a user other than HR. The SYSTEM account normally has access to all objects in the HR schema, but now that you have safeguarded the EMPLOYEES table with Oracle Database Vault, this is no longer the case.

In SQL*Plus, connect as SYSTEM, and then try accessing the salary information in the EMPLOYEES table again:

sqlplus system
Enter password: password

SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM <10;

Error at line 1:
ORA-01031: insufficient privileges

SYSTEM no longer has access to the salary information in the EMPLOYEES table. However, user SEBASTIAN does have access to this information. Try the following:

SQL> CONNECT SEBASTIAN
Enter password: password

SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM <10;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Donald               OConnell                        2600
Douglas              Grant                           2600
Jennifer             Whalen                          4400
Michael              Hartstein                      13000
Pat                  Fay                             6000
Susan                Mavris                          6500
Hermann              Baer                           10000
Shelley              Higgins                        12000
William              Gietz                           8300
 
9 rows selected.

3.2.7 Step 7: Run a Report

Because you enabled auditing on failure for the HR Realm, you can generate a report to find any security violations such as the one you attempted in Step 6: Test the Realm.

1. In the Oracle Database Vault Administrator home page, click Database Vault Reports.

   To run the report, you need to have logged in using an account that has the DV_OWNER, DV_ADMIN, or DV_SECANALYST role. Note that user SEBASTIAN cannot run the report, even if it affects his own realm. "Oracle Database Vault Roles" describes these roles in detail. Currently, you should be logged in as the the Database Vault Owner (DV_OWNER) account.

2. In the Database Vault Reports page, scroll down to Database Vault Auditing Reports and select Realm Audit.

3. Click Run Report.

Oracle Database Vault generates a report listing the type of violation (in this case, the SELECT statement entered in the previous section), when and where it occurred, the login account who tried the violation, and what the violation was.

3.2.8 Step 8: Remove the Components for This Tutorial

1. Remove the SYSTEM account from the Data Dictionary Realm.

   1. Ensure that you are logged on to Oracle Database Vault Administrator using a database account that has been granted the DV_OWNER role.

   2. From the Administration page, select Realms.

   3. From the list of realms, select Oracle Data Dictionary, and then click Edit.

   4. Under Realm Authorizations, select SYSTEM.

   5. Click Remove, and in the Confirmation window, click Yes.

2. Delete the HR Realm.

   1. In the Realms page, select HR Realm from the list of realms.

   2. Click Remove, and in the Confirmation window, click Yes.

3. Drop user SEBASTIAN.

   In SQL*Plus, log on as the Oracle Database Vault account manager (for example, DBVACCTMGR) you created when you installed Oracle Database Vault, and then drop SEBASTIAN as follows:

   sqlplus dbvacctmgr
   Enter password: password
   
   SQL> DROP USER SEBASTIAN;
   User dropped.
   

4. If necessary, lock and expire the HR account.

   SQL> ALTER USER HR ACCOUNT LOCK PASSWORD EXPIRE;