Part 1  |  Part 2

In the previous article in this mini-series, I demonstrated a couple of low-impact methods for enforcing data isolation between different users, or groups of users. In this article, I move on to ‘proper’ row-level security (RLS), also known as fine-grained access control (FGAC), or the virtual private database (VPD). The examples in these articles were tested using Oracle 9.2.0.3.

Requirements

The initial example of data isolation in the first article showed a table with an owner column that became the basis for a static view that produced a user-based result set. The view definition was very simple, and required a single predicate on that one column, and the only extra code required as a table level trigger.  But what do you do if you have many different requirements for separating data, and a single, simple, predicate is not enough.

Consider a (slightly contrived) example of a system used by a supermarket chain for data warehousing purposes.  A key table showing daily stock levels has the following definition:

create table stock_level (
	stock_date		date,
	product_id		number(6),
	qty				number(8),
	dept_id			varchar2(20,
	supplier_code	varchar2(20)
);

The table shows, for each date, for each product, the current stock level. The table is denormalised to show the department within the supermarket that is responsible for the product, and the external supplier of the product.

The supermarket operates a web-enabled system that allows external suppliers to query the data warehouse to view stock levels of the products that they supply to the supermarket.  Internally, the same application allows the different departments to query the data warehouse but only for the data relevant to their department.

You could, of course, apply the techniques of the previous article to create two views on this data, one for each requirement. A side effect of this approach, though, is that you may have to create two copies of all pre-canned reports, all procedures that massage the data, and so on – and of course you might have a system with more than just two divergent requirements. To minimise, or at least condense, the complications, you can the dbms_rls package to create security policies.

Security Policies

The purpose of fine-grained access control is to allow you to hide all the complexities of ‘data concealment’ in a highly centralised fashion.

There are two mandatory steps, with a further two optional steps that may not be required in a reporting system.  First you need to create a function that takes a schema name and object name as its inputs, and produces the text of a valid where clause, secondly you need to associate this function with its target table, listing the actions for which the function should be executed. The two optional steps are ones we met in the previous article – the creation of a table-based trigger to set controlling columns to relevant values and a database trigger to set user-based ‘environment’ or context variables. You may also want to build a single view on top of the table to hide the columns that are set by the table-based trigger.

In this article, we will assume that a batch process loads the table, and that the end-users are only allowed read access to the table, so we can ignore the details of inserts, updates, and deletes and concentrate only on handling select statements.

Preparing RLS

In this example of a predicate function (which has a use of the case operator that is specific to version 9) we take the user’s identity and generate a predicate that restricts by department or by supplier.  In a more realistic set-up, we would probably use some reference tables to determine which predicate should be generated, and might use a global context setting that had been determined in a middle tier to distinguish local employees from external suppliers.

create or replace function stock_predicate(
	i_schema	in varchar2,
	i_object	in varchar2
) return varchar2
as
begin
	return 
		case (sys_context('userenv','session_user'))
			when 'U1'        then 'supplier_code = ''Hershey'''
			when 'U2'        then 'dept_id = ''Confection'''
			when 'TEST_USER' then null
			          else '1 = 0'
		end;
end;
/

There are three particular points to note.

First, the input parameters are the schema name and the object (typically table or view) name that the predicate will eventually be applied to. Because the object name is an input parameter, you can create predicate functions that build correlated subqueries, using the input parameter as the correlation name for the driving table.  (This type of predicate function isn’t a good idea, though, as it can result in disastrous performance).

The second point is a personal preference – I like to ensure that there is always a safety net. If every test in the predicate function fails, I like to return a predicate which is always false (such as ‘1 = 0’) that will cause the optimiser to return no data, usually very efficiently – the best default action for security is to hide everything.

Finally, note that I have included one test that returns a null predicate. In this case test_user happens to own the table and function, so for practical reasons I have made sure that there are no restrictions on what the data owner can do or see.

Having created a function that can return a string resembling a where clause, we now associate this function with the table.  (We could associate the same function with lots of different tables if we wanted to). We do this with a call to the dbms_rls package.

begin
	dbms_rls.add_policy (
		object_schema   => 'test_user',
		object_name     => 'stock_level',
		function_schema => 'test_user',
		policy_function => 'stock_predicate',
		statement_types => 'select, insert, update, delete', 
		update_check	=> TRUE,
		policy_name     => 'stock_restrict',
		enable			=> TRUE,
		static_policy	=> FALSE	-- v9 feature only
	);
end;
. 
/

In this call to the add_policy procedure, we identify our table and the function we have written. We also note that we want the predicate to be created on selects, inserts, updates, and deletes.  The update_check is a bit like the ‘with check option’ for views; it ensures that we are not allowed to insert or update a row in such a way that we will not be able to see it after the insert or update. Finally, we give the policy (the combination of object, function and actions) a name, and enable it.  The last parameter in the procedure, static_policy, is a very important one that I will discuss later on.

If we now insert some data, we can see the effects of the policy. We start by connecting as the table owner (test_user) to load the data.

insert into stock_level values(sysdate,1,100,'Confection','Hershey');
insert into stock_level values(sysdate,2,60,'Deli','Hershey');
insert into stock_level values(sysdate,3,60,'Confection','Cadbury');
insert into stock_level values(sysdate,4,60,'Deli','Cadbury');
commit;

When we connect query the data as this user, we will see all four rows. However, if we connect as user u1 and select * from test_user.stock_level, we will see:

STOCK_DAT PRODUCT_ID        QTY DEPT_ID              SUPPLIER_CODE
--------- ---------- ---------- -------------------- -------------
19-OCT-03          1        100 Confection           Hershey
19-OCT-03          2         60 Deli                 Hershey

And if we connect as user u2 and run exactly the same query, we will see:

STOCK_DAT PRODUCT_ID        QTY DEPT_ID              SUPPLIER_CODE
--------- ---------- ---------- -------------------- -------------
19-OCT-03          1        100 Confection           Hershey
19-OCT-03          3         60 Confection           Cadbury

As you can see, each user sees a different set of data. The original query has been modified on the fly, the reference to the table stock_level has been replaced by a reference to an in-line view that contains our generated predicate (think what that might do to efficiency, especially in complex outer joins).  For example the simple select executed by user u2 will have been changed into:

Select * from (
		select * 
		from stock_level 
		where dept_id = 'Confection'
)

By the way, if you see Oracle error ORA-28113: policy predicate has error you have probably got a typing error copying out all the duplicated single quote marks in the policy function – Oracle is telling you that the text it has generated does not fit as a legal where clause.

Problems

There are inevitably a few problems with this mechanism. To start with, under Oracle 8.1 you cannot see the generated predicate anywhere in the system – as far as v$sql and trace files are concerned the modified SQL statement simply does not exist. You can work around this problem by setting sql_trace to true and then setting event 10730 in a session that is using RLS. If you do this then every hard parse of a statement will generate a section of trace file that looks something like the following:

Logon user     : U1
Table/View     : TEST_USER.STOCK_LEVEL
Policy name    : STOCK_RESTRICT
Policy function: TEST_USER.STOCK_PREDICATE
RLS view :
SELECT  "STOCK_DATE","PRODUCT_ID","QTY","DEPT_ID","SUPPLIER_CODE" FROM 
"TEST_USER". "STOCK_LEVEL"  "STOCK_LEVEL" WHERE (supplier_code = 'Hershey')

This problem has been addressed in Oracle 9 (although the efficiency of the solution is a little questionable) by the introduction of the view v$vpd_policy.  A simple query against this view can pick up the following information:

ADDRESS                       : 6F5664F0
PARADDR                       : 6F5638AC
SQL_HASH                      : 1816753535
CHILD_NUMBER                  : 0
OBJECT_OWNER                  : TEST_USER
OBJECT_NAME                   : STOCK_LEVEL
POLICY_GROUP                  : SYS_DEFAULT
POLICY                        : STOCK_RESTRICT
POLICY_FUNCTION_OWNER         : TEST_USER
PREDICATE                     : supplier_code = 'Hershey'

Based on the paraddr,sql_hash, andchild_number from this view, you can query thev$sql view to find the related SQL with a query like the following:

Select	sql_text 
from	    v$sql 
where 	address = '6F5F0020' 
and		hash_value = 2621366196
and		child_number= 0

The inefficiency of this is that the v$vpd_policy view includes the x$kglcursor object which is the object underlying v$sql anyway – so you might as well define your own version of the v$vpd_policy view to avoid the pointless extra join. Even then there is no efficient path between the two x$ objects underpinning the view.

But there are other issues –  I promised that I would mention the static_policy parameter of the add_policy procedure again. This is a boolean parameter introduced in Oracle 9 to give you a choice of two evils. If you set the parameter to true then the security predicate seems to be generated just once on the first hard parse – which means that user u2 would end up running exactly the same query as user u1 if user u1 just happened to be first person to run the query.

On the other hand, if the parameter is set to false, then the security function is executed (apparently twice) for every single execution (not just parse) of the query, and the function is executed through the following fairly hefty, anonymous, pl/sql block, which isn’t going to do much for concurrency and scalability.

begin  
	p := STOCK_PREDICATE(:sn,:on);                    
	:v1  := substr(p,1,4000);		:v2  := substr(p,4001,4000);
	:v3  := substr(p,8001,4000);	:v4  := substr(p,12001,4000);
	:v5  := substr(p,16001,4000);	:v6  := substr(p,20001,4000);
	:v7  := substr(p,24001,4000);	:v8  := substr(p,28001,4000);
	:v9  := substr(p,32001,767);	
	:v10 := substr(p, 4000, 1);		:v11 := substr(p,8000,1);
	:v12 := substr(p, 12000, 1);	:v13 := substr(p,16000,1);
	:v14 := substr(p, 20000, 1);	:v15 := substr(p,24000,1); 
	:v16 := substr(p, 28000, 1);	:v17 := substr(p,32000,1);
end;

I suspect this change was an emergency fix in response to the observation that some ‘time-variant’ security predicates were not being re-evaluated when required. The response, however, is a bit extreme. The issue has been addressed more subtly in version 10g of Oracle, where several levels of ‘variability’ have been introduced for the policy type.

A final thought – there is a suggestion from Oracle that it is not a good idea to use joins to reference tables to impose security (think what all those in-line views could do) and that you should try to restrict your security predicates to simple usage of the sys_context() function. But look what the SQL Reference (version 9.2, page 6-154) says about sys_context():

Note: SYS_CONTEXT returns session attributes. Therefore, you cannot use it in parallel queries or in a Real Application Clusters environment.

Conclusion

Row-level security is quite easy to design and impose, but there are costs you need to be aware of. In particular, if you are using RLS on version 8, you might get a nasty surprise when you migrate to version 9. For relatively simple requirements I’m not convinced that you really need to go any further than the deliberate creation of views described in the first article in this series.

--

Jonathan Lewis is a freelance consultant with more than 18 years' experience of Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of Practical Oracle 8i - Building Efficient Databases published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, presentations, seminars and tutorials can be found at http://www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups.