On 9 March, we offered the first quiz on the Virtual Private Database feature (also known as "Row Level Security"). This is a very powerful and complex feature, allowing you to enforce row-level security (filtering the rows that a user may see) in a transparent and highly secure manner.
Given the complexity of VPD, I was very glad to receive this email from a player:
"Nice quiz! I always wondered how this topic could be covered in a quiz with code that fits in a page."
But the quiz also raised questions from a number of players, including:
1. I wondered why this is a INTERMEDIATE level quiz, not ADVANCED. I feel like more players will answer this correctly 'without really knowing what is going on' than players 'knowing all VPD concepts'. I think most of the players would know very little or nothing about VPD. Anyway, this was a nice topic to cover. Thanks!
2. GRANT EXECUTE ON plch_vpd TO SCOTT is not necessary for making VPD effective.
3. Is DBMS_RLS a default package? I generated an error when running this. I am running Oracle 10g release 2 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PLS-00201: identifier 'DBMS_RLS' must be declared
4. I think the today quiz on RLS policy could output 2 different results depending if the schema scott has got the system privilege EXEMPT ACCESS POLICY or not. The output will be "jobs" if the schema has been granted the mentioned privilege or "NDF" if the privilege has not been granted. I read the assumption and I couldn't find anything about system privileges, so I was wondering if this is what you wanted. Or am I missing something?
5. Wouldn't it be better to say "WHEN 'HR' THEN NULL ELSE...". I cannot test it on 11g right now, but at a production DB with 10R2 we could not use MV when we had policies on multiple tables, each adding a 1=1 instead of NULL.
My responses:
1. I chose to set this to intermediate because it was a very simple example, with a "black and white" security policy. As a result, a minimal knowledge of VPD or relatively quick and high level review of documentation on the feature would likely tell you all you need to know to get it right.
2. Good point! The package is executed by Oracle based on the security policy defined. A schema only needs authority on the table, not the security package. I will change the question text to remove this.
3. You do not, by default, have the authority to use DBMS_RLS. That's why I was sure to point out in the question that "all statements complete successfully (that is, all the necessary privileges have been granted to HR)." If, however, you want to use VPD in your application, you will definitely need to ask for help from your DBA.
4. That's very interesting (EXEMPT ACCESS POLICY). Since I do not explicitly tell you in the question that this privilege is granted to HR - and this privilege is not required to get the code to compile and run - you should not assume that it was granted. This is, however, good material for a future quiz!
5. Interesting point (NULL instead of 1=1). I have no experience with this (I assume that the reference to "MV" is for a materialized view). I hope to hear more about this from some of you!
Any other thoughts regarding VPD and the quiz? Would any players like to share their experiences with VPD and how it has helped them? And, best of all, would any of you like to submit quizzes on VPD based on your experiences and knowledge? I hope so!
Cheers, SF
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
I know that on our (other) production database policies are used for better performance because of partitioned tables. A global session variable in a package is used to select the correct partition for speedy access or to select the whole table (and wait forever). Alas I did not know the details, hence my wrong answer. But I surely take a look again.
ReplyDeleteI love the VPD feature and have used it with lots of joy in a more or less ASP like application: an application with one code base but multiple 'customers'. Each sharing that same data model, but not being able to see each other's data, only their own. I see VPD as both a way to secure classified data, but certainly also as a way to centralize security filters on data that would otherwise be spread among all sorts of application code.
ReplyDeleteDBMS_RLS is indeed needed for the 'user' that can create/drop/change policies, but it does not necessarily be the table owner itself. I was recently pointed to the fact that someone who has execute privilege on DBMS_RLS, can also drop/alter policies on other users tables, which may be an issue in a database hosting multiple (VPD protected) applications. I wrote a blogpost on that subject (http://toinevanbeckhoven.wordpress.com/2011/02/07/how-to-deal-with-dbms_rls/).
Regarding the MV (Materialized View): interesting and indeed in the past (but that was Oracle 8.1.7) we found some issue with MV's on VPD enabled tables. For that we had to test for DBMS_MVIEW.I_AM_A_REFRESH in the policy function and if TRUE, not return a VPD predicate, but NULL. The same I read on a forum: http://dbaspot.com/forums/oracle-server/373327-materialized-view-conflict-vpd-ora-30372-please-help.html
In my current project it will be VPD again...the application will "host" at least 10 operating companies in a centralized customer database.
Toine (happy with a few 100% scores again after a series of bad quizzes ;-))
I searcherd my mail archive about the MV error (yes, I meant Materialized View).
ReplyDeleteDifferent kinds of insurances can only be accessed by authorized clerks. This information is stored in a basic "insurance" table. Each table that contains confidential data is joined to this table by a predicate like "EXISTS (SELECT 1 FROM insurance WHERE )" or gets a "1=1" when the clerk has sufficient privileges to access any insurance.
While this works well with any "normal" query we get an ORA-28113: policy predicate has error in a MV we use for reporting. I just tested it in our new 11gR2 database and the error is still there. I know that it works when we replace the 1=1 predicate with NULL, but I cannot change it because we would need to go through a complete test and this is too expensive compared to the limitations we face.
As workaround we replaced the MV with a table which we truncate every night and fill it again.
Regards
Marcus