Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases
The concept of Virtual Private Database – aka Row Level Security or Fine Grained Access Control – is quite powerful. Without any impact on the SQL used in the applications and reports to access the database, very flexible, dynamic and complex security policies can be enforced, in a performance wise very efficient manner. Besides, VPD can be used for other things than just security. VPD can in general be used to dynamically append where clauses to queries fired by the applications and users of the database. These where clauses can of course enforce security policies, but also ensure that only records in the designated language or of the specified year or country are returned. One very powerful application of VPD is allowing several users or organisations to share the same set of tables while providing each of them with something akin to their own partition, a virtual sub-table, a subset of records from the shared table.
Virtual Private Database is implemented through the DBMS_RLS package, available in the Oracle Database since the 8i release, and only in the Enterprise Edition. That means that the wonders of VPD are not available to anyone on an older release than 8i and anyone not one an Enterprise Edition database.
In this article, I will show how we can achieve most of the benefits of VPD even in a Standard Edition or pre-8i database.
Step one: All data access through Views rather than tables
Key to this poor man’s VPD is that we lack the ability to append restrictions or policy predicates that real VPD allows us to set, right before the query is executed. So we need another way to add a where clause to the where clause defined by the user or the application. If all database access is done through for example an ADF Business Components tier, we can make use of facilities in ADF BC to add last minute where-clause manipulation. However, for a more robust solution, we want the where clause manipulation in the database itself. And the only way of implementing it there, is by building a layer of one-to-one views on our tables. For every table, there will be a view, created as:
create view schema2.table_name as select * from schema1.table_name.
In order to not impact any existing application or report, we want to use the same names for the views as previously we had for the tables. We can do that in one of thow ways: rename all tables and create views in the same schema using the old tablenames OR create the views in a second schema. This second schema needs all privileges on all tables in the primary (table)schema. Public synonyms used by applications to access the database, that currently refer to the tables in the primary schema should be redirected to refer to the secondary schema with all the views.
Step two: Implementing ‘policies’
A. Static Predicate
The easiest case is where the policy is to apply a static predicate. The real VPD policy function would be something like:
create or replace function EMP_STATIC_POLICY_FUN ( p_schema_name in varchar2 , p_table_name in varchar2 ) return varchar2 is begin return 'SAL < 4000 or JOB <>''MANAGER'''; end EMP_STATIC_POLICY_FUN; /
Implementing such a static policy in the poor man’s world is dead easy. We create the view with this predicate as its where-clause:
view emp as select * from scott.emp where SAL < 4000 OR JOB <>'MANAGER' /
B. Predicate with dynamic dependencies
In many cases the policy has some dynamic characteristic. It depends on the context – which user is executing the query, what time or day is it etc. For example, a real VPD Policy Function:
create or replace function EMP_DYNAMIC_POLICY_FUN ( p_schema_name in varchar2 , p_table_name in varchar2 ) return varchar2 is l_predicate varchar2(2000):= ''; begin if USER='SCOTT' then l_predicate:= 'job<>''MANAGER'''; -- SCOTT is not allowed to see MANAGER records else if to_char(sysdate, 'DAY') in ('SATURDAY','SUNDAY') then l_predicate:= 'sal < 2000'; -- during the weekend, other users than SCOTT may only see employee record for staff earning less than 2000 else if to_char(sysdate, 'HH24') < 9 then l_predicate:= 'job != ''CLERK'''; -- on a weekday before business hours, no peeking at CLERKs end if; end if; end if; return l_predicate; end EMP_DYNAMIC_POLICY_FUN; / </>
Implementing this in Poor Man’s VPD could be done in two ways. One would be to write function like
replace function is_record_allowed( p_sal in number, p_job in number) return number and invoke that function in the View’s Where-clause:
where is_record_allowed(emp.sal, emp.job) = 1. Inside this function, we would have logic similar to the function above, except that instead of returning a predicate string, it would perform evaluation of the record based on the values of USER, SYSDATE, p_sal and p_job. Needless to say that the cost incurred by this solution is quite high: for every record in EMP, we will invoke this PL/SQL function.
The other way to implement this logic is by moving the logic out of the Policy Function into the predicate itself. It gives us somewhat ugly where clauses, but it performs far better:
create or replace view emp as select * from scott.emp where 1 = case when user='SCOTT' then case when job!= 'MANAGER' then 1 end when to_char(sysdate, 'DAY') in ('SATURDAY','SUNDAY') then case when sal < 2000 then 1 end when to_char(sysdate, 'HH24') < 9 then case when job != 'CLERK' then 1 end else 1 end /
It might be tempting to write logic such as:
create or replace package my_context_package as function get_favorite_job return varchar2 ; procedure set_favorite_job ( p_job in varchar2 ); end; / create or replace package body my_context_package as g_job varchar2(30); function get_favorite_job return varchar2 is begin dbms_output.put_line('call to get_favorite_job'); return g_job; end; procedure set_favorite_job ( p_job in varchar2 ) is begin g_job := p_job; end; end; / create or replace view emp as select * from scott.emp where job = my_context_package.get_favorite_job /
to filter the Employee records based on a context setting or a user preference. You must realize however that this function will be called for every record being evaluated by the SQL engine:
SQL> exec my_context_package.set_favorite_job('CLERK') PL/SQL procedure successfully completed. SQL> select * 2 from emp 3 where job = my_context_package.get_favorite_job 4 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10 call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job call to get_favorite_job
It is not hard to see how such a call may seriously impact performance for queries on large tables! A much better solution is based on the concept of Application Contexts. See for example the following definitions of a Context and a Package to manipulate the Context’s contents:
create context my_context using my_context_package / create or replace package my_context_package as procedure set_favorite_job ( p_job in varchar2 ); end; / create or replace package body my_context_package as procedure set_favorite_job ( p_job in varchar2 ) is begin dbms_session.set_context ( namespace => 'MY_CONTEXT' , attribute => 'JOB' , value => p_job ); end; end; /
With these preparations in place, we can execute our query as follows:
exec my_context_package.set_favorite_job('CLERK') PL/SQL procedure successfully completed. select * from emp where job = sys_context( 'MY_CONTEXT' ,'JOB') / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10
In this case, only one evaluation is made of the value of sys_context( ‘MY_CONTEXT’ ,’JOB’) . The Optimizer regards this reference as bind variable. That also means that repeated execution of this select statement, even for different values of the JOB attribute in MY_CONTEXT, do not require hard (re-)parses of the query. Using Application Context is a very performance savvy solution to this category of challenges where context data influences the nature of the query.
C. Predicate with subqueries
If our policy function would be something like:
create or replace function EMP_NEW_YORK_ONLY_POLICY_FUN ( p_schema_name in varchar2 , p_table_name in varchar2 ) return varchar2 is l_predicate varchar2(2000):=''; -- which is just as good as ' 1=1' begin if USER!='SCOTT' then l_predicate:= '''NEW YORK'' = (select d.loc from dept d where emp.deptno = d.deptno)'; else l_predicate:= 'sal < 4000'; end if; dbms_output.put_line('Predicate for EMP_NEW_YORKERS_ONLY_POLICY_FUN '||l_predicate); return l_predicate; end EMP_NEW_YORK_ONLY_POLICY_FUN; /
something somewhat strange happens when we have specified our VPD policy:
begin dbms_rls.add_policy ( object_schema => 'SCOTT' , object_name => 'EMP' , policy_name => 'NEW_YORKERS_ONLY' , policy_function => 'EMP_NEW_YORK_ONLY_POLICY_FUN' , function_schema => 'SCOTT' , statement_types => 'SELECT' , update_check => false , enable => true ); end; /
When we connect as SCOTT, everything goes as expected:
SQL> connect scott/tiger SQL> set serveroutput on SQL> select * from emp 2 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 ... 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 13 rows selected. Predicate for EMP_NEW_YORKERS_ONLY_POLICY_FUN sal < 4000
Now let’s connect as another USER, called UP:
SQL> connect up/up Connected. SQL> desc dept ERROR: ORA-04043: object "SCOTT"."DEPT" does not exist SQL> select * from emp 2 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 Predicate for EMP_NEW_YORKERS_ONLY_POLICY_FUN 'NEW YORK' = (select d.loc from dept d where emp.deptno = d.deptno)
So while user UP does not have access to table DEPT, the where clause added by the Policy Function that clearly refers table DEPT can still be executed without any problem. We find the explanation in the Oracle Documentation:
If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy-protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users do not require EXECUTE privilege on the policy function, because the server makes the call with the function definer’s right.
Now if we want to implement this policy in our view, we only need to make sure that the schema containing all views has select access on the DEPT table. That is not a real problem.
D. Dynamic Predicate
Suppose the predicate is selected from a table with user specific predicates. Something like:
create table user_predicates ( for_user varchar2(30) , for_table varchar2(30) , predicate varchar2(4000) ) /
Now we have nothing solid to add to the where clause of our view, that is: we want to add the dynamic where clause, which of course is not possible in a static View definition. Solution, using a Table Function and Dynamic SQL to process the dynamic predicate:
create type num_tbl as table of number / create or replace function pre_selection return num_tbl is l_num_tbl num_tbl; l_predicate varchar2(2000):= 'job = ''CLERK'''; begin /* normally this statement could retrieve the dynamic predicate: select predicate into l_predicate from user_predicates where for_user = USER and table_name = 'EMP' ; */ execute immediate 'select empno from emp where '||l_predicate bulk collect into l_num_tbl ; return l_num_tbl; end pre_selection; / create view emp as select emp.* from emp , table ( pre_selection) allowed_records where emp.empno = allowed_records.column_value /
If the predicate is very selective or the underlying table does not contain a large number of records, the size of the collection returned by pre_selection will not be overwhelming. In that case, we can further simplify this code to the following:
create type emp_t as object ( ename varchar2(30) , sal number(8,2) , job varchar2(30) ) / create or replace type emp_tbl as table of emp_t / create or replace function pre_selection return emp_tbl is l_emp_tbl emp_tbl; l_predicate varchar2(2000):= 'job = ''CLERK'''; begin execute immediate 'select emp_t2(emp.ename, emp.sal, emp.job) from emp where '||l_predicate bulk collect into l_emp_tbl ; dbms_output.put_line(l_emp_tbl(1).ename); return l_emp_tbl; end pre_selection; / create view emp as select emp.* from table ( cast(pre_selection as emp_tbl)) emp /
Additional VPD Features
Real VPD offers several other features:
- Different predicates and policies for Select, Insert, Update and Delete statements – with VPD, we can define a policy for one or more of the statement types. Unfortunately, our poor man’s VPD cannot accomplish the same thing, other than by using several Views, one for each type of statement
- Real VPD allows us to switch on the ‘check option’ – The check option means that any Update operation on the data I can see i.e. data that passes the policy can only be performed if the resulting data also adheres to the predicate. This is behavior our Poor Man’s VPD implementation can provide too: by defining all views with the CHECK OPTION
- Real VPD let’s us indicate that the policy only has to be applied for queries that access specific columns. This fancy behavior is something we cannot provide with our View based solution.
Implementing Application Security Policies – Oracle9i Application Developer’s Guide – Fundamentals Release 2 (9.2) – Introduction to the concept of Application Context
- Virtual Private Database, securing your data
- Standard for Database Development – Getting rid of USER from PL/SQL and SQL – no longer is USER equivalent to End User
- Pop-quiz: VPD policy that depends on a table with a policy…
- Another Pop-Quiz: Whose VPD policy is used when executing SQL in a (definer rights) package?
- Good Citizenship – Have Client Applications register themselves with the database