Virtual Private Database (VPD) 10g

The VPD is used to restrict the user from viewing/selecting certain rows/columns. The DBMS_rls package manage the Row level security in the database.

When a user directly or indirectly accesses a table view, or synonym associated with a vpd security policy. The oracle database serer automatically modifies the users sql statement to filter the rows. The modifications is banes on the where clause returned by a function , which implements the security policy.

The following are the new features for VPD in 10g

* column level privacy / column masking
* Static, context-sensitive and shared policies
* Support for paralled queries.

Column Level VPD:-

Using sec_relevant_cols to the dbms_rls.add_policy procedure. If this parameter is omitted then the policy behaves as in oracle 9i, when the policy is applied to all columns.

The column level privacy enforces row-leve-access control only when a statement accesses security relevant columns.

Eg.

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1000 Raja 1 40000
1001 Jameel 2 42000
1002 Ahamed 3 44000
1004 Kannan 2 45000

Here no we want to restrict the access of columns if the department no is not 2 , means it will list out only the rows if the department is 2.

We can accomplish this task using below.

I) Create the function:-

SQL> create or replace function listonly2 
(v_owner varchar2, v_objname varchar2) return varchar2 as
where_clause varchar2(200);
begin
where_clause := 'deptid=2';
return where_clause;
end;
/

II) Create the policy:-

SQL> begin
sys.dbms_rls.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTDEPT2',
function_schema => 'MANZOOR',
policy_function => 'LISTONLY2',
sec_relevant_cols => 'SALARY'
);
end;
/

Here we have created a policy in such a way that whenever the salary column is selected it needs to retrun only the rows for which the department id is 2 and if the salary column is not included in the selection then it will list all the rows.

See below..

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1001 Jameel 2 42000
1004 Kannan 2 45000

The above returns the rows for which the deptid is 2 since the salary column has been included in the selection list.

SQL> select empid, ename, deptid from emp;

EMPID ENAME DEPTID
---------- ------------------------------ ----------
1000 Raja 1
1001 Jameel 2
1002 Ahamed 3
1004 Kannan 2

The above returns all the rows in the emp table since we have not included the salary column in the selection list. 

Another eg.

In this only the data pertaining to the current user should list if the salary column is included in the selection list, and if the user is selecting all the columns excluding the salary column then it will list all the rows.

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1000 Raja 1 40000
1001 Jameel 2 42000
1002 Ahamed 3 44000
1004 Kannan 2 45000

SQL> create or replace function listcuruser (v_owner varchar2, v_objnam varchar2) return varchar2 as
where_clause varchar2(50);
begin
where_clause := 'ename=sys_context(''USERENV'',''SESSION_USER'')';
return where_clause;
end;
/

SQL> begin
sys.dbms_rls.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTONLYUSER',
function_Schema => 'MANZOOR',
policy_function => 'LISTCURUSER',
sec_relevant_cols => 'SALARY'
);
end;
/

SQL> show user;
USER is "MANZOOR"

SQL> select * from emp;

no rows selected


--The above returns no rows since the user manzoor is not existing the the emp table and he is selected all the columns i.e. including the salary colunm.

SQL> select empid, ename, deptid from emp;

EMPID ENAME DEPTID
---------- ------------------------------ ----------
1000 RAJA 1
1001 JAMEEL 2
1002 AHAMED 3
1004 KANNAN 2

The above retruns all the rows since the user does not included the salary column in the selection list;

SQL> conn ahamed/ahamed;
Connected.
SQL> show user;
USER is "AHAMED"

SQL> select * from manzoor.emp;

no rows selected

--The above returns no since currently the emp table has two policy the 1st is to select only the deptid is 2 and the 2nd is to select only the records pertaining to that particular user. Since the deptid of AHAMED is 3 it does not return any rows.

SQL> conn manzoor/ahamed;
Connected.

SQL> update emp set deptid = 2 where ename ='AHAMED';

1 row updated.

SQL> commit;

Commit complete.

SQL> conn ahamed/ahamed;
Connected.

SQL> select * from manzoor.emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1002 AHAMED 2 44000


To drop the policies-

SQL> begin
dbms_rls.drop_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTONLYUSER'
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_rls.drop_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTDEPT2'
);
end;
/


Columns Masking

Here it will list all the rows but it will hide or mask the column that we have mentioned in the policy. This can be accomplished using dbms_rls.add_policy package/procedure and packing by parameter sec_relevant_cols_opt => dbms_rls_all_rows.

Eg:-

SQL> begin
dbms_rls.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'COLMASKING',
function_schema => 'MANZOOR',
policy_function => 'LISTONLY2',
sec_relevant_cols => 'SALARY',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
);
END;
/

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1000 RAJA 1
1001 JAMEEL 2 42000
1002 AHAMED 2 44000
1004 KANNAN 2 45000


Here all the rows in the emp tables will get dispaly but the salary column will list only if the depit is 2 and for rest of the deptid the salary column will be masked i.e. the value will not be shown.

To drop the policy:-

SQL> begin
dbms_rls.drop_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'COLMASKING'
);
END;
/