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');
