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:
1 2 3 4 5 |
<span class="kwrd">FUNCTION</span> my_policy( schema_in <span class="kwrd">IN VARCHAR2</span>, object_in <span class="kwrd">IN VARCHAR2</span> ) <span class="kwrd">RETURN VARCHAR2</span> |
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:
1 2 3 4 5 6 |
<span class="kwrd">IS</span> l_return_value <span class="kwrd">VARCHAR2</span>(<span class="str">32767</span>); <span class="kwrd">BEGIN</span> l_return_value := '<span class="str">1=1</span>'; <span class="kwrd">RETURN</span> l_return_value; <span class="kwrd">END</span> 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:
1 2 3 4 5 6 7 8 9 10 |
<span class="kwrd">BEGIN</span> <span class="kwrd">DBMS_RLS.ADD_POLICY</span> ( object_schema => '<span class="str">SCOTT</span>', object_name => '<span class="str">EMP</span>', policy_name => '<span class="str">EMP_POLICY</span>', function_schema => '<span class="str">SCOTT</span>', policy_function => '<span class="str">MY_POLICY</span>', statement_types => '<span class="str">SELECT, INSERT, UPDATE, DELETE</span>' ); <span class="kwrd">END;<span> |
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:
1 2 3 4 5 6 7 8 9 10 |
<span class="kwrd">IS</span> l_return_value <span class="kwrd">VARCHAR2</span>(<span class="str">32767</span>); <span class="kwrd">BEGIN</span> <span class="kwrd">IF</span> SYS_CONTEXT('<span class="str">USERENV</span>', '<span class="str">SESSION_USER</span>') = '<span class="str">SCOTT</span>' <span class="kwrd">THEN</span> l_return_value := '<span class="str">1=1</span>'; <span class="kwrd">ELSE</span> l_return_value := '<span class="str">1=0</span>'; <span class="kwrd">END IF</span>; <span class="kwrd">RETURN</span> l_return_value; <span class="kwrd">END</span> 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:
1 |
<span class="kwrd">CREATE OR REPLACE CONTEXT</span> my_context <span class="kwrd">USING</span> my_context_pkg; |
Then we create the package to actually set to context:
1 2 3 |
<span class="kwrd">CREATE OR REPLACE PACKAGE</span> my_context_pkg <span class="kwrd">IS</span> <span class="kwrd">PROCEDURE</span> set_max_dept; <span class="kwrd">END</span> my_context_pkg; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<span class="kwrd">CREATE OR REPLACE PACKAGE BODY</span> my_context_pkg <span class="kwrd">IS</span> <span class="kwrd">PROCEDURE</span> set_max_dept <span class="kwrd">AS</span> l_max_deptno <span class="kwrd">NUMBER</span>; <span class="kwrd">BEGIN</span> <span class="kwrd">CASE SYS_CONTEXT</span>('<span class="str">USERENV</span>'', '<span class="str">SESSION_USER</span>') <span class="kwrd">WHEN</span> '<span class="str">SCOTT</span>' <span class="kwrd">THEN</span> l_max_deptno := <span class="str">9999</span>; <span class="kwrd">WHEN</span> '<span class="str">HR</span>' <span class="kwrd">THEN</span> l_max_deptno := <span class="str">10</span>; <span class="kwrd">WHEN</span> '<span class="str">OE</span>' <span class="kwrd">THEN</span> l_max_deptno := <span class="str">20</span>; <span class="kwrd">ELSE</span> l_max_deptno := <span class="str">0</span>; <span class="kwrd">END CASE</span>; DBMS_SESSION.SET_CONTEXT('<span class="str">my_context</span>', '<span class="str">max_deptno</span>', l_max_deptno); <span class="kwrd">END</span> set_max_dept; <span class="kwrd">END</span> 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):
1 2 3 4 |
<span class="kwrd">CREATE TRIGGER</span> tr_set_max_dept <span class="kwrd">AFTER LOGON ON DATABASE</span> <span class="kwrd">BEGIN</span> scott.my_context_pkg.set_max_dept; <span class="kwrd">END</span>; |
In our function we can now check the application context and return the appropriate predicate:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<span class="kwrd">IS</span> l_return_value <span class="kwrd">VARCHAR2</span> (32767); <span class="kwrd">BEGIN</span> <span class="kwrd">CASE</span> <span class="kwrd">SYS_CONTEXT</span>('my_context', 'max_deptno') <span class="kwrd">WHEN</span> <span class="str">10</span> <span class="kwrd">THEN</span> l_return_value := '<span class="str">DEPTNO <= 10</span>'; <span class="kwrd">WHEN</span> <span class="str">20</span> <span class="kwrd">THEN</span> l_return_value := '<span class="str">DEPTNO <= 20</span>'; <span class="kwrd">WHEN</span> <span class="str">30</span> <span class="kwrd">THEN</span> l_return_value := '<span class="str">DEPTNO <= 30</span>'; <span class="kwrd">WHEN</span> <span class="str">40</span> <span class="kwrd">THEN</span> l_return_value := '<span class="str">DEPTNO <= 40</span>'; <span class="kwrd">WHEN</span> <span class="str">9999</span> <span class="kwrd">THEN</span> l_return_value := '1=1'; <span class="kwrd">ELSE</span> l_return_value := '<span class="str">1=0</span>'; <span class="kwrd">END IF</span>; <span class="kwrd">RETURN</span> l_return_value; <span class="kwrd">END</span> my_policy; |
This function can be written shorter and more flexible to the following:
1 2 3 4 5 6 |
<span class="kwrd">IS</span> l_return_value <span class="kwrd">VARCHAR2</span> (<span class="str">32767</span>); <span class="kwrd">BEGIN</span> l_return_value := q'[<span class="str">DEPTNO <= SYS_CONTEXT('my_context', 'max_deptno')</span>]'; <span class="kwrd">RETURN</span> l_return_value; <span class="kwrd">END</span> 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:
1 2 |
<span class="kwrd">GRANT ALL ON</span> SCOTT.EMP <span class="kwrd">TO</span> HR; <span class="kwrd">GRANT ALL ON</span> SCOTT.EMP <span class="kwrd">TO</span> OE; |
Logon as HR:
1 |
<span class="kwrd">CONNECT</span> HR/<PASSWORD> |
Remember we set the context to the max DEPTNO for HR to 10, so our SELECT should result in only these EMPs:
1 2 |
<span class="kwrd">SELECT</span> e.empno, e.ename, e.deptno <span class="kwrd">FROM</span> emp e; |
The result of the query is:
EMPNO | ENAME | DEPTNO |
---|---|---|
7839 | KING | 10 |
7782 | CLARK | 10 |
7934 | MILLER | 10 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
1 |
<span class="kwrd">CONNECT</span> 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:
1 2 |
<span class="kwrd">SELECT</span> e.empno, e.ename, e.deptno <span class="kwrd">FROM</span> 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
1 |
<span class="kwrd">CONNECT</span> SCOTT/<PASSWORD> |
In the context function we set the max DEPTNO for SCOTT to 9999, effectively showing all rows.
1 2 |
<span class="kwrd">SELECT</span> e.empno, e.ename, e.deptno <span class="kwrd">FROM</span> 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 |
1 2 3 4 5 6 7 8 9 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span class="kwrd">variable</span> x <span class="kwrd">clob</span> <span class="kwrd">begin</span> dbms_utility.expand_sql_text ( input_sql_text => 'select e.EMPNO, e.ENAME, e.DEPTNO from scott.EMP e', output_sql_text => :x ); <span class="kwrd">end</span>; / <span class="kwrd">print</span> 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
Load comments