Hi everybody, I’m looking for advice regarding possible implementations of row-level security in Oracle. Here is some context information: I’m a data warehouse architect (not an Oracle DBA) and I’ve been asked to design a solution for an Oracle db where users should be able to see only their slice of data.
We are talking about a production system for a large corporation here in Italy, so of course the solution will be verified and implemented by a team with the appropriate expertise, but I’d like to have some opinions from you guys before submitting it to this team.
Basically, I know of two methods to implement row-level security: the first is based on the usage of views and synonyms, so that when users query the db tables what they see is actually a filtered view, because the table name is a synonym that points to the view. I have seen this implemented and working.
The second method is a native Oracle mechanism called Oracle Row-Level Security (RLS), which resides in the DBMS_RLS package.
In my opinion, the second method is superior in almost all respects, but I’ve never seen it implemented, so I’m a little nervous proposing it.
I’d like to hear comments based on real experiences, with a special focus on possible problems / drawbacks.
Here are a couple of very good articles that I already found, just to get started:
http://www.symantec.com/connect/articles/oracle-row-level-security-part-1 http://blog.ctco.lv/articles/introduction-to-oracle-row-level-security-2.html
It is heavily used - and since all it does is add a where clause, the query runtime 'hit' is totally up to you.
You could either
a) create a view with a where clause
b) create a table that will have a where clause attached to it
c) write your application to hopefully append the right where clause
In all cases - you are running a query with a where clause.
I think you'll find the flexibility of DBMS_RLS to be superior to all of a,b,c. Also, since you are a warehouse, not an OLTP system, any parsing issues would be obviated. You'd be able to easily use a dynamic policy if you needed (although static is good if possible)