Securing sensitive data from DBA Access

If we have the privileges to become SYS or SYSTEM, then we can view the data from any schema. Database vault can help us to restrict the DBA access to sensitive information.

Following example shows the default behaviour (i.e. without database vault)

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Thu May 1 14:34:01 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT ENAME,SAL FROM SCOTT.EMP WHERE ROWNUM < 5;

ENAME SAL
———- ———-
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975

Solution to this problem is to create REALM in the database vault

We can now see the REALM created. At this time, REALM is not protecting any objects. We also did not configure any user, who can use this REALM.

Next step is to add the objects, to be secured by this REALM. So edit the SCOTT ACCESS REALM

In the Realm Secured objects section, press CREATE button


By doing this, we have now protected the schema objects. Only users with REALM Authorizations can access this object.
Some more interesting points

1. SYS can not grant access on this object

SQL> show user
USER is “SYS”

SQL> grant select on scott.emp to testuser;
grant select on scott.emp to testuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: Realm violation for grant object privilege on SCOTT.EMP
ORA-06512: at “DVSYS.AUTHORIZE_EVENT”, line 55
ORA-06512: at line 31

2. SCOTT himself can not grant access on this object

SQL> show user
USER is “SCOTT”

SQL> grant select on emp to testuser;
grant select on emp to testuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: Realm violation for grant object privilege on SCOTT.EMP
ORA-06512: at “DVSYS.AUTHORIZE_EVENT”, line 55
ORA-06512: at line 31

Next step is to add the database users, who will have the access to this REALM. For the testing purpose, we’ll add a user “TESTUSER”

SQL> show user
USER is “SYS”

SQL> create user testuser identified by password;
create user testuser identified by password
*
ERROR at line 1:
ORA-01031: insufficient privileges

What happened? Why SYS is not able to create the user?

When database vault is enabled, DVSYS.AUTHORIZE_EVENT will remove privileges like CREATE USER, DROP USER from SYS & SYSTEM account. If you disable Oracle Database Vault, users SYS and SYSTEM have get these privileges back.

So what is the solution?

Create the user using Vault administrator user (in this case vowner)

SQL> show user
USER is “VOWNER”

SQL> create user testuser identified by password;

User created.

SQL> grant create session to testuser;
grant create session to testuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47401: Realm violation for grant system privilege on CREATE SESSION.
ORA-06512: at “DVSYS.AUTHORIZE_EVENT”, line 55
ORA-06512: at line 31

User is created, but vowner could not grant the create session. Here we need to go back to SYS or SYSTEM user to grant this privilege

SQL> show user
USER is “SYS”

SQL> grant create session, select any table to testuser;

Grant succeeded.

Now the user is created. It’s time to grant the access of SCOTT ACCESS REALM to TESTUSER.

So in the REALM Authorizations section, press CREATE button

So now we have secured objects and added designated users, who can use the REALM.

Now it’s time to validate the REALM

SQL> show user
USER is “SYSTEM”

SQL> select * from scott.emp where rownum <5;

select * from scott.emp where rownum <5

ERROR at line 1:
ORA-01031: insufficient privileges

SQL> show user
USER is “TESTUSER”

SQL> SELECT ENAME,SAL FROM SCOTT.EMP WHERE ROWNUM < 5;

ENAME SAL
———- ———-
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975

This proves that only TESTUSER can access the secured object, apart from object owner.

We can also monitor the REALM violation by checking the reports

Now to drop the TESTUSER, we again need to login as DV_OWNER and drop the user.

Advertisements
This entry was posted in Oracle Database Vault, Oracle Security and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s