Virtual Private Database and Implementation

*This concept comes up with Oracle Database 8i and it is the simplest data security solution provided by Oracle. Besides of its simplicity, it is free, flexible and application indepedent.

VPD is a row level security mechanism that adds predefined constraints to the queries on predefined tables or views. It works on the database side and any application or user who want to reach the forbidden data are blocked by VPD.

What are the benefits of VPD ?

Management Side

Time, Cost and Money Saving : Once this security is built on database server, there is no need to implement the same security to all other applications which access to the database.

Application transparent : Since the security is on the databse side, you do not need to change any code on the application side.

Eliminating the security problem : You do not need to take any other security precautions because VPD offers a base level (granular) security solution.

Operational Side

Dynamic Security : The usage of VPD offers a dynamic solution wihtout defining user specific criteria or grants or roles. It gets informations automatically from user session and implements the policy immediately on the predefined critical datas.

More than one Security Option : There is not any obligation in number of security policy applied to an object. You can apply countless security policy to an object according your server performance.

No back doors : There is no clear way to bypass the security, it is more efficient than granting roles and privileges. (SQL injection is out of topic)

Why we don’t use views instead of VPD ?

One of the reasons to create views instead of reaching to the base table is to enhance security on critical datas. For instance; consider you have an employee table which holds name, address, phone and salary informations on your employees. When you grant your developer to reach to this table, it is not possible to hide the salary information. At this point, you come up with view solution and you create a view which does not hold salary information and grant the developer to reach only to this view. Thus, you solved the problem, you think..

How about this problem?

Each department manager wants to see their own employees information including salary informations. Well, you can solve this problem by creating thousand of views according to the number of departments and changing application considering these views. Do you think that this solution is effective of easy to implement?

How VPD works technically ?

VPD basically relies on dynamic query modification concept. The function returns a WHERE condition (predicate), appends to the user’ s DML statement and shows the result to the user. It works fully transparently and dynamically based on the function which implements the security policy.

Since VPD is very flexible, you can use any static or dynamic predicate in your policy functions. For this purpose, Oracle provides built-in context namespace, USERENV, which holds informations about connected users’ sessions. For instance; you can get the user’ s IP ADDRESS or COMPUTER NAME informations from USERENV context in your function and returns 1 or 0 in an if clause according to these informations.

*Keep in mind that; without changing your SQL statements, you implement a security option and provide same statements returns different results for each user.

Implementation of VPD

In this part, I am going to implement VPD through an example. I think this will be the easiest way to comprehend the VPD usage.

Example 1

This example is going to secure the salary information of employees who earns more than $200.000.

Platform: Oracle Database 10.2.0.3.0

Steps of implementing VPD

1. Create a table which holds critical information

2. Create the policy function

3. Add the function to the created table by giving a security policy name using DBMS_RLS package.

First, we create an example table which holds employee informations, insert some datas and query the result.

CREATE TABLE SCOTT.employees

(

ID NUMBER NOT NULL,

fist_name VARCHAR2(20) NOT NULL,

last_name VARCHAR2(20) NOT NULL,

phone_number NUMBER,

salary NUMBER NOT NULL

);

ALTER TABLE SCOTT.employees ADD (

CONSTRAINT employees_pk

PRIMARY KEY

(ID));

INSERT INTO SCOTT.employees

VALUES (1, 'James', 'Brown', '2121121', '40000');

INSERT INTO SCOTT.employees

VALUES (2, 'Charlie', 'Connery', '3234323', '100000');

INSERT INTO SCOTT.employees

VALUES (3, 'Vanessa', 'Silver', '2232326', '500000');

SQL> select * from SCOTT.employees;

ID	FIST_NAME	LAST_NAME	PHONE_NUMBER	SALARY
1	James	Brown	2.121.121	40.000
2	Charlie	Connery	3.234.323	100.000
3	Vanessa	Silver	2.232.326	500.000

Next, we create the security function which exclude the informations of employees who earns more than $200.000.

CREATE OR REPLACE FUNCTION SCOTT.function_example1 (

obj_schema IN VARCHAR2,

obj_name IN VARCHAR2

)

RETURN VARCHAR2

AS

BEGIN

RETURN 'SALARY <200000';

END;

/

Then, we add the policy function to the employees table using DBMS_RLS package. (Database Management System_Row Level Security) In this section, i am not going through the procedures of DMBS_RLS package but DBMS_RLS.ADD_POLICY and DROP_POLICY procedures will be important for us.

BEGIN

SYS.DBMS_RLS.ADD_POLICY (

object_schema => 'SCOTT'

,object_name => 'employees'

,policy_name => 'policy_example1'

,function_schema => 'SCOTT'

,policy_function => 'function_example1'

,statement_types => 'SELECT'

,sec_relevant_cols => 'SALARY'

,sec_relevant_cols_opt => dbms_rls.ALL_ROWS);

END;

/

Object_schema - Schema containing the employees table

Object_name - The table name, “employees”

Policy_name - Name of the policy to be added.

Function_schema - Schema contaning the policy function

Policy_function - Name of the function which will generate the predicate

Statement_types - types that policy will apply.

Sec_relevant_cols - specifies the relevant colums that policy will apply.(*)

Sec_relevant_cols_opt - used for column masking

Finally, when we query the employees table from any database user (except SYS or EXEMPT ACCESS POLICY privileged users), we will get the result shown below:

SQL> select * from SCOTT.employees;

ID	FIST_NAME	LAST_NAME	PHONE_NUMBER	SALARY
1	James	Brown	2.121.121	40.000
2	Charlie	Connery	3.234.323	100.000
3	Vanessa	Silver	2.232.326

If you don’ t want to see Vanessa’ s row, simply drop the policy, remove sec_relevant_cols_opt option and re add the policy to the table.

Just as in the case of that example, VPD can be used to hide the same informatin dynamically regarding the session information of the user.

(*) this may appear little unnecessary, but it is not. When you specify the column, people who don’ t query the salary column won’ t execute the policy, so result wil get faster.

Before passing to the second example, it is good to drop the applied policy to the employees table. To do this, we use DBMS_RLS.DROP_POLICY procedure.

BEGIN

SYS.DBMS_RLS.DROP_POLICY (

object_schema => 'SCOTT'

,object_name => 'employees'

,policy_name => 'policy_example1');

END;

/

Example 2

In this example, I am going to take the database username as the credential and allow only “SCOTT” user to see all datas in employees table. When other users queries the employees table, they won’ t see any data in the table.

I am going to use the same employees table created in example 1.

SQL> select * from SCOTT.employees;

ID	FIST_NAME	LAST_NAME	PHONE_NUMBER	SALARY
1	James	Brown	2.121.121	40.000
2	Charlie	Connery	3.234.323	100.000
3	Vanessa	Silver	2.232.326	500.000

Now, I create another policy function policy_function2 which will take username of the database user from its session and prevent all users except “SCOTT” user.

CREATE OR REPLACE FUNCTION SCOTT.function_example2 (

obj_schema IN VARCHAR2,

obj_name IN VARCHAR2

)

RETURN VARCHAR2

IS

f_predicate VARCHAR2 (15);

v_username VARCHAR2 (15);

BEGIN

SELECT UPPER (SYS_CONTEXT ('USERENV', 'SESSION_USER'))

INTO v_username

FROM DUAL;

IF (v_username LIKE 'SCOTT')

THEN

f_predicate := '1=1';

ELSE

f_predicate := '1=0';

END IF;

RETURN f_predicate;

END;

/

Then, we add the function to the table with add_policy procedure as in the example 1.

BEGIN

SYS.DBMS_RLS.ADD_POLICY (

object_schema => 'SCOTT'

,object_name => 'employees'

,policy_name => 'policy_example2'

,function_schema => 'SCOTT'

,policy_function => 'function_example2'

,statement_types => 'SELECT'

,sec_relevant_cols => 'SALARY'

-- ,sec_relevant_cols_opt => dbms_rls.ALL_ROWS(*)

);

END;

/

I commented the sec_relevant_cols_opt option in order to prevent user to see all datas in employees table. If you don’t, users will see the employees information with masked salary column.

Finally, when other users query the employees table, the result will look like this:

ID	FIST_NAME	LAST_NAME	PHONE_NUMBER	SALARY

On the other hand, when SCOTT user tries to reach the employees table, it will see all datas:

ID	FIST_NAME	LAST_NAME	PHONE_NUMBER	SALARY
1	James	Brown	2.121.121	40.000
2	Charlie	Connery	3.234.323	100.000
3	Vanessa	Silver	2.232.326	500.000

Conclusion

The Virtual Private Database (VPD) might be seen the key enabling technology for opening mission-critical systems to partners and customers. It ensures that no matter how users access to protected data, the policy will be run automatically. Also, it might be used in Telecommunication firms’ and Human Resources departments’ datas to lower the cost and complexity of development.

This is the base of row level granural access control provided by Oracle.

Hope this will be useful.