Installing Oracle Database Vault - Part 2

In Part 1 I went over installing Oracle Database Vault and re-enabling SYS privileges like creating a user.

In this post I'll explain how to use Database Vault to restrict and enable access to individual tables and schemas, which is probably why someone wanted to install database vault in the first place.

Managing Schema Access

In order to restrict privileged user access to specific schemas or objects, the following method is used.

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.

For example, the DBA can view salary details in an employee table by using the SYS id. To prevent this, follow this process to create a REALM:

As sys, before adding a REALM –

$ sqlplus “/ as sysdba”
SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
Alexander            Hunold                          9000
Bruce                Ernst                           6000
David                Austin                          4800
Valli                Pataballa                       4800
Diana                Lorentz                         4200
Nancy                Greenberg                      12008

9 rows selected.

After adding a realm and specifying the EMPLOYEES table as the protected object:

$ sqlplus “/ as sysdba”
SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10;
SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10
                                                 *
ERROR at line 1:
ORA-01031: insufficient privileges

Creating a Realm

Navigate to the dva home page and select “Realms”.

Click on “Create” and create the realm with a suitable name and description:

Click on “OK”
Select the new Realm and click on “Edit”
Under “Realm Secured Objects”, click on “Create”

Use the drop-down lists to select the owner, object type and enter the object name – note, to restrict access to an entire schema, leave the “%” in the object name field.

Click on “OK”
Click on “OK” again.
If the query is attempted again by the SYS user, they are denied access:

$ sqlplus “/ as sysdba”
SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10;
SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10
                                                 *
ERROR at line 1:
ORA-01031: insufficient privileges

Allowing a specific user access to an object

To allow a specific user access to this table:

Create a user, sebastian, and grant select any table.

    $ sqlplus “/ as sysdba”
    SQL> create user sebastian identified by dhdgfgf;
    SQL> grant create session to sebastian;
    SQL> grant select any table to sebastian;

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.

$ sqlplus sebastian/dhdgfgf
SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10;
SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE ROWNUM < 10
                                                 *
ERROR at line 1:
ORA-01031: insufficient privileges

To authorize user SEBASTIAN to have access to the HR Realm as follows, using the Database Vault web pages:

Click on the “Realms” item:

Edit the HR_EMPLOYEES Realm and click ‘create’ in authorizations

Select SEBASTIAN from the grantee drop down
Leave the rest to default, and click OK, then OK again.

Log into the database as sebastian:

$sqlplus sebastian/dhdgfgf 

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

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
Alexander            Hunold                          9000
Bruce                Ernst                           6000
David                Austin                          4800
Valli                Pataballa                       4800
Diana                Lorentz                         4200
Nancy                Greenberg                      12008

9 rows selected.

So, the sebastian user now has access to the table.

You should be able to use this to specify other access.

One of my colleagues pointed out that the access is controlled by database grants. So for example, if you allow SYS access to the table realm, then SYS can do everything to that table - drop, truncate etc, because SYS can do that.

So a realm is used to restrict access to an object specifically, but if a user is allowed into that realm, the access is based on the grants in the database.

Also, to grant access to export:

# sqlplus dbvault_owner/password

SQL> EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SEBASTIAN');