Using Database Vault Secure Application Roles to restrict the data access from designated application

Problem statement: Users should be allowed the access to the data from the customized application.

Solution: To achieve this we will create the secured application role and associate it with the rule set. This will allow the access to data from the designated application, when all conditions in rule sets are satisfied.

First create the user, to be used

$sqlplus vowner

SQL> create user testuser identified by password;
User created.

SQL> create user ytest identified by password;
User created.

$ sqlplus / as sysdba

SQL> grant create session to testuser;
SQL> grant create session to ytest;

Create rule set, with two conditions.

1.Initiated connection should be coming from a designated host (application server)
2.Database session user should be a application user (application user)


Now create the secured application role with rule set


This operation will create a database role

SQL> select role from dba_roles where role=’TEST_SECURE_ROLE’;

ROLE
——————————
TEST_SECURE_ROLE

Grant the required privileges to the ROLE

$ sqlplus scott

SQL> grant select on dept to TEST_SECURE_ROLE;
Grant succeeded.

To make use of the application role, we’ll have to set it in the current session, using DVSYS.DBMS_MACSEC_ROLES.SET_ROLE procedure. In any application, this will the first statement, which will control the access to the restricted data.

Now it’s time to test the setup

Scenario 1 – Test from wrong host, but right user

C:\>sqlplus testuser@devdb

SQL> SELECT DVF.F$SESSION_USER FROM DUAL
/

F$SESSION_USER
——————————————————————————–

TESTUSER

SQL> SELECT DVF.F$MACHINE FROM DUAL
/

F$MACHINE
——————————————————————————–

C471

SQL> SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE(‘TEST_SECURE_ROLE’);
BEGIN DVSYS.DBMS_MACSEC_ROLES.SET_ROLE(‘TEST_SECURE_ROLE’); END;

*
ERROR at line 1:
ORA-47305: Rule Set violation on SET ROLE (MACHINE BASED RESTRICTION)
ORA-06512: at “DVSYS.DBMS_MACUTL”, line 38
ORA-06512: at “DVSYS.DBMS_MACUTL”, line 381
ORA-06512: at “DVSYS.DBMS_MACSEC”, line 242
ORA-06512: at “DVSYS.ROLE_IS_ENABLED”, line 4
ORA-06512: at “DVSYS.DBMS_MACSEC_ROLES”, line 24
ORA-06512: at line 1

Scenario 2 – TEST FROM CORRECT HOST BUT WRONG USER
 
$ sqlplus ytest

SQL> SELECT DVF.F$SESSION_USER FROM DUAL
/
F$SESSION_USER
——————————————————————————–
YTEST

SQL> SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT DVF.F$MACHINE FROM DUAL
/

F$MACHINE
——————————————————————————–
dev1

SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE(‘TEST_SECURE_ROLE’);
BEGIN DVSYS.DBMS_MACSEC_ROLES.SET_ROLE(‘TEST_SECURE_ROLE’); END;

*
ERROR at line 1:
ORA-47305: Rule Set violation on SET ROLE (MACHINE BASED RESTRICTION)
ORA-06512: at “DVSYS.DBMS_MACUTL”, line 38
ORA-06512: at “DVSYS.DBMS_MACUTL”, line 381
ORA-06512: at “DVSYS.DBMS_MACSEC”, line 242
ORA-06512: at “DVSYS.ROLE_IS_ENABLED”, line 4
ORA-06512: at “DVSYS.DBMS_MACSEC_ROLES”, line 24
ORA-06512: at line 1

Scenario 3 – TEST FROM CORRECT HOST & USER

$ sqlplus testuser@devdb

SQL> SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE(‘TEST_SECURE_ROLE’);

PL/SQL procedure successfully completed.

SQL> select DVF.F$SESSION_USER from dual
/

F$SESSION_USER
——————————————————————————–
TESTUSER

SQL> SELECT DVF.F$MACHINE FROM DUAL
/

F$MACHINE
——————————————————————————–
dev1

SQL> SELECT * FROM SCOTT.DEPT;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

So when both the conditions are satisfied, user was allowed to access the data.

Another thing to be noticed here, that the security protection happens when user try to set the role using SET_ROLE procedure and not when he tries to access the data.

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

2 Responses to Using Database Vault Secure Application Roles to restrict the data access from designated application

  1. vadim says:

    May I ask you if you tested a case where the objective is this: user SCOTT should NOT be allowed to grant any new role ? How to enforce this with ODV?

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