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.

Resources

Implementing Application Security Policies - Oracle9i Application Developer's Guide - Fundamentals Release 2 (9.2) - Introduction to the concept of Application Context