Virtual Private Database
Virtual Private Database which is also known as Fine Grained Access Control (FGAC) is a new feature available with Oracle for providing row level restrictions on tables. Normally data restrictions are provided with views. The complexity grows exponentially with the number of views present in a database. Maintenance becomes a big task with these multiple views.
Oracle came with a package DBMS_RLS for providing a fine grained access control. Using this we can provide a row level restriction to all the users accessing the tables. This reduces the maintenance effort for user restrictions as we need not create multiple views for providing user restrictions.
How it works
VPD can be easily explained with a select query. In a VPD enabled table if a user issue a select query, the oracle automatically appends a predicate to the query depending upon some VPD policies issued to the tables. The predicate is appended in runtime.
Say the user issue a select query
select * from table1;
The VPD will modify the query based on its policies.
select * from table1 where supervisor = ‘scott’
All the transactions done on the particular table will be controlled by VPD.
How to Implement VPD on tables
Let us assume the following situation for explaining the steps to implement VPD on tables.
Let there be two schemas sec_tbl_sch and main_tbl_sch, where sec_tbl_sch is the admin.
The main_tbl_sch schema has two key tables which will be used by another web application for displaying data pulled from the key tables. The users using the web application use their own username/password to access the tables.
The two key tables are EMPLOYEES and PROJECTS
create table employees
( emplid varchar2(10) NOT NULL,
s_id varchar2(10) NOT NULL );
EMPLID S_ID
------- -----
0200145 IND01
0200146 USA02
0200147 IND07
0200148 USA02
The schema sec_tbl_sch has a table ACCESS_TBL which identifies the supervisors and their corresponding S_ID’s.
SUPERVISOR S_ID ACCESS_TYPE ---------- ----- ----------- JOHN IND01 S JOHN IND01 I JOHN IND01 D JOHN IND01 U JOHN IND07 S JAMES IND07 S JAMES IND07 I JAMES IND07 U JAMES IND07 D PETER USA02 S PETER USA02 I PETER USA02 U PETER USA02 D S – SELECT I – INSERT U – UPDATE D – DELETE privileges.
You can see the user JOHN has all the privileges on IND01 and SELECT only privilege on IND07. Similarly you can find the other privileges for other supervisors.
The three tables are enough for explaining the concept of ROW LEVEL RESTRICTIONS that are applied dynamically to the tables whenever a supervisor is accessing the tables.
The steps involved in VPD are
- Creating a Policy Function
- Applying the Policy
1) Creating a Policy Functions
In order to apply the policy to the tables we have to create two functions one for SELECT and the other for DELETE, UPDATE and INSERT. The functions are created by the user SEC_TBL_SCH for security reasons.
create or replace function sel_policy_s_id
(
obj_schema in varchar2,
obj_table in varchar2
)
return varchar2
as
ret_predicate varchar2(1000);
begin
if (obj_schema = user) then
ret_predicate := null;
else
for s_rec in
(
select s_id
from access_tbl
where supervisor = USER
and access_type = 'S'
) loop
ret_predicate := ret_predicate||','||cust_rec.s_id;
end loop;
ret_predicate := ltrim(ret_predicate,',');
ret_predicate := 'S_ID IN ('||ret_predicate||')';
end if;
return ret_predicate;
end;
The function accepts two parameters obj_schema dn obj_table and it will return the predicate that should be appended to the select query for the logged in USER.
Insert, Update and Delete Policy Function
create or replace function del_ins_upd_policy_s_id
(
obj_schema in varchar2,
obj_table in varchar2
)
return varchar2
as
ret_predicate varchar2(1000);
begin
if (obj_schema = user) then
ret_predicate := null;
else
for s_rec in
(
select s_id
from access_tbl
where supervisor = USER
and access_type in ('I','U','D')
) loop
ret_predicate := ret_predicate ||','||s_rec.s_id;
end loop;
ret_predicate := ltrim(ret_predicate ,',');
ret_predicate := 'S_ID IN ('||ret_predicate ||')';
end if;
return ret_predicate ;
end;
2) Applying the Policy
Before applying the policies the user SEC_TBL_SCH should be granted EXECUTE privilege on the package DBMS_RLS. Then the policies can be applied by the below steps.
begin
dbms_rls.add_policy (
object_schema => 'MAIN_TBL_SCH',
object_name => 'EMPLOYEES',
policy_name => 'SEL_POLICY',
function_schema => 'SEC_TBL_SCH',
policy_function => 'sel_policy_s_id',
statement_types => 'SELECT',
update_check => TRUE
);
end;
begin
dbms_rls.add_policy (
object_schema => 'MAIN_TBL_SCH',
object_name => 'EMPLOYEES',
policy_name => 'INS_DEL_UPD_POLICY',
function_schema => 'SEC_TBL_SCH',
policy_function => 'del_ins_upd_policy_s_id',
statement_types => 'INSERT, UPDATE, DELETE',
update_check => TRUE
);
end;
After applying the policies a fine grained access control will be applied to all the users accessing the key tables.
If the user JOHN is accessing the table employees by a select query
select * from employees;
The VPD will automatically append the predicate to the select query and change it to
select * from employees where s_id in ( ‘IND01’, ‘IND07’ )
EMPLID S_ID
------- -----
0200145 IND01
0200147 IND07
Similarly the restrictions will also be imposed on the user based upon the policy. If the user JOHN is trying to insert a record into employees with the below query.
INSERT INTO EMPLOYEES VALUES (‘0200146’, ‘IND07’);
Oracle will throw the following error
ERROR at line 1:
ORA-28115: policy with check option
violation
Conclusion
Thus by using Virtual Private Database we can implement a row level security on Tables.
Related Posts
- Oracle PL/SQL: Oracle System Tables
- Oracle PL/SQL: Primary keys
- Migrating Oracle Database from HP UNIX to LINUX
- Inbuilt Functions in Oracle
- Oracle PL/SQL: Loops and Conditional Statements
Tags: oracle virtual private table, Row Level Scurity on Oracle Tables, Virtual Private Database
It’s difficult to make this work with modern object/relational mappers. It’s also difficult to get connection pooling working with this.
There are other issues as well. The decision to use VPD should not be taken lightly.