Views

When working with views you have several options to hide some of the data. In the article on views I have told you about the way to hide columns from the table. By adding a predicate (where clause) to the view, you can restrict the rows a user sees. This would imply creating different views for different (groups of) users. If you have an interface built to interact with the data, that would also mean you have to maintain multiple versions of these interfaces. Not a big problem if you only have a small number (like two or three) interfaces to maintain, but if the number of interfaces grows, chances are that you will forget to implement changes in one or more of them.

Another way of dealing with different (groups of) users is to implement Fine Grained Access Control (or Virtual Private Databases or Row Level Security).

Row Level Security

RLS or VPD or FGAC is one of the features of the Oracle database with lots of different names. Commonly it’s referred to as Virtual Private Database (VPD) but it is implemented as Row Level Security (RLS) hence the names of the supplied package DBMS_RLS. The marketing term for this feature was (or used to be) Fine Grained Accesss Control.

What RLS does is transparently add a predicate to every query issued against a table that has a policy defined on it. The package provides a couple of programs to (amongst others) add, remove, enable and disable policies. The actual implementation of the policy should be done in your own package.

Steps to take

First you create a (packaged) function that will result in the predicate to be added. This function has a specific signature. It has two in parameters which are the schema name and the object (table, view or synonym) name of the object. It must return a where predicate as a result. Attaching the function to the object you want to protect provides the names of the schema and object to which the policy will apply:

FUNCTION my_policy(
  schema_in IN VARCHAR2,
  object_in IN VARCHAR2
 )
RETURN VARCHAR2

In this function you execute anything you would normally be able to execute in a PL/SQL function. Be aware that this function is run every time you execute DML on the object, so if this one runs for a relatively long time, your queries will run considerably longer.

In the implementation of the function you construct the predicate you want attached to the object:

IS
  l_return_value VARCHAR2(32767);
BEGIN
  l_return_value := '1=1';
 RETURN l_return_value;
END my_policy;

After you have created the function, you must attach it to the object. You do this by executing a procedure in the DBMS_RLS package: DBMS_RLS.ADD_POLICY:

 BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'SCOTT',
    object_name      => 'EMP',
    policy_name      => 'EMP_POLICY',
    function_schema  => 'SCOTT',
    policy_function  => 'MY_POLICY',
    statement_types  => 'SELECT, INSERT, UPDATE, DELETE'
   );
 END;
object_schema Specifies the schema containing the object (table, view or synonym) you want to protect, in our case that is SCOTT. If no object_schema is specified, the current log-on user schema is assumed.
object_name Specifies the object within the schema to protect, in our case EMP.
policy_name Gives this policy a name so it can be referenced with the other programs in the DBMS_RLS package. It must be unique for the same object.
function_schema Specifies the schema in which the policy function was created. In our case it is also SCOTT but it can be any schema with the appropriate privileges. If NULL then the current schema is assumed.
policy_function Specifies the function to enforce the policy. In our case that is MY_POLICY. If the function is defined within package, then the name of the package must be present.
statement_types Specifies the operations when this policy applies. You can select one or more of the options INDEX, SELECT, INSERT, UPDATE and DELETE as a comma separated list. The default is to apply to all types except INDEX.

The function created does not restrict the visibility of the data at all, since the returned predicate is 1=1 which is always true. We want to return data more selectively, for example based on who is currently connected. One way of doing this is by using the USERENV application context.

Our function could look something like this:

IS
  l_return_value VARCHAR2(32767);
BEGIN
 IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SCOTT' THEN
    l_return_value := '1=1';
 ELSE
    l_return_value := '1=0';
 END IF;
 RETURN l_return_value;
END my_policy;

This will effectively hide all the data from anyone but SCOTT.

You can also create your own application context and check the value in the function. To achieve this we first need to create an application context:

CREATE OR REPLACE CONTEXT my_context USING my_context_pkg;

Then we create the package to actually set to context:

CREATE OR REPLACE PACKAGE my_context_pkg IS
 PROCEDURE set_max_dept;
END my_context_pkg;
CREATE OR REPLACE PACKAGE BODY my_context_pkg IS
 PROCEDURE set_max_dept
 AS
    l_max_deptno NUMBER;
 BEGIN
   CASE SYS_CONTEXT('USERENV'', 'SESSION_USER')
     WHEN 'SCOTT' THEN l_max_deptno := 9999;
     WHEN 'HR' THEN l_max_deptno := 10;
     WHEN 'OE' THEN l_max_deptno := 20;
     ELSE l_max_deptno := 0;
   END CASE;
   DBMS_SESSION.SET_CONTEXT('my_context', 'max_deptno', l_max_deptno);
 END set_max_dept;
END my_context_pkg;

Now we need to create a logon trigger to set the context (run this a SYS or a user with enough privileges):

CREATE TRIGGER tr_set_max_dept AFTER LOGON ON DATABASE
  BEGIN
    scott.my_context_pkg.set_max_dept;
  END;

In our function we can now check the application context and return the appropriate predicate:

 IS
  l_return_value VARCHAR2 (32767);
 BEGIN
  CASE SYS_CONTEXT('my_context', 'max_deptno')
    WHEN 10 THEN l_return_value := 'DEPTNO <= 10';
    WHEN 20 THEN l_return_value := 'DEPTNO <= 20';
    WHEN 30 THEN l_return_value := 'DEPTNO <= 30';
    WHEN 40 THEN l_return_value := 'DEPTNO <= 40';
    WHEN 9999 THEN l_return_value := '1=1';
    ELSE l_return_value := '1=0';
  END IF;
  RETURN l_return_value;
 END my_policy;

This function can be written shorter and more flexible to the following:

 IS
  l_return_value VARCHAR2 (32767);
 BEGIN
   l_return_value := q'[DEPTNO <= SYS_CONTEXT('my_context', 'max_deptno')]';
   RETURN l_return_value;
 END my_policy;

I have used alternative quoting in the predicate to get rid of the problem of doubling the quotes to get a quote in a string. You can read more about this in the oracle docs or view a simple example at oracle-developer.net.
To test our policy we just created, grant access to the table to a couple of users and check their results:

GRANT ALL ON SCOTT.EMP TO HR;
GRANT ALL ON SCOTT.EMP TO OE;

Logon as HR:

CONNECT HR/<PASSWORD>

Remember we set the context to the max DEPTNO for HR to 10, so our SELECT should result in only these EMPs:

SELECT e.empno, e.ename, e.deptno
  FROM emp e;

The result of the query is:

EMPNO ENAME DEPTNO
7839 KING 10
7782 CLARK 10
7934 MILLER 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    99 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |    99 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"<=10)

Now we logon as OE:

CONNECT OE/<PASSWORD>

We set the context to the max DEPTNO for OE to 20, so our SELECT should result in only the EMPs from these DEPTs:

SELECT e.empno, e.ename, e.deptno
  FROM emp e;

The result of the query is:

EMPNO ENAME DEPTNO
7839 KING 10
7782 CLARK 10
7566 JONES 20
7788 SCOTT 20
7902 FORD 20
7369 SMITH 20
7876 ADAMS 20
7934 MILLER 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   264 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     8 |   264 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"<=20)

Now we logon as SCOTT:

CONNECT SCOTT/<PASSWORD>

In the context function we set the max DEPTNO for SCOTT to 9999, effectively showing all rows.

SELECT e.empno, e.ename, e.deptno
  FROM emp e;

The result of the query is:

EMPNO ENAME DEPTNO
7839 KING 10
7698 BLAKE 30
7782 CLARK 10
7566 JONES 20
7788 SCOTT 20
7902 FORD 20
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7934 MILLER 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   462 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

In Oracle 12c a new function, EXPAND_SQL_TEXT, has been added to the DBMS_UTILITY packages, which makes seeing what SQL is actually executed pretty easy.

variable x clob

begin
  dbms_utility.expand_sql_text
    ( input_sql_text => 'select e.EMPNO, e.ENAME, e.DEPTNO from scott.EMP e',
      output_sql_text => :x );
end;
/
print x
X
--------------------------------------------------------------------------------
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."DEPTNO" "DEPTNO" FROM  
(SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB"
,"A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL"
,"A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO" 
FROM "SCOTT"."EMP" "A2" WHERE 1=1) "A1"

A table or view can have multiple policy functions defined. All predicates are AND-ed together. Using the EXPAND_SQL_TEXT program can help in debugging. If you have multiple policy functions defined, it might be a good idea to add a comment indicating which function resulted in a certain predicate.

If you are granted the EXEMPT ACCESS POLICY system privilege then all policies are by-passed (ignored). SYS has this privilege by default. Foreign Keys and Unique Keys also by-passed the Policy functions. If you have enough time, you could learn about the existence of records you are not allowed to see.

If you have an error in your policy function you get the ORA-28110: Policy function or package has error. This means no rows can be selected from the table. This also happens when you change an object where this program is, in any way, depending on. The policy function is not automatically recompiled. Fix the error or recompile the package to re-activate select on the table.

ref:
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
http://www.oracle-developer.net/display.php?id=311
http://tkyte.blogspot.com/2013/07/12c-sql-text-expansion.html
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_rls.htm