Oracle Scratchpad

November 30, 2010


Filed under: Bugs,Infrastructure,Oracle — Jonathan Lewis @ 5:33 pm GMT Nov 30,2010

That’s “Virtual Private Database”, “Fine Grained Access Control”, or “Row-level Security” – three different names for the same feature. (Four if you include the separately licensed Oracle Label Security (OLS) which is a product built on top of RLS).

I’ve just seen a nice presentation from John Batchelor of Sopra hitting the key points of intent, implementation and threat from this (free) product and wondered how many sites used it. So I’ve put up a little poll about usage, with a follow-up for those who do use it about the impact on their system.

If you have any problems, insights, or comments relating to the product please feel free to add them as comments to the post. I can’t promise to offer any response – but other readers might have some relevant contribution to make.


  1. The last time I implemented such a solution, the impact was reduced down from “small but noticeable” to “barely visible” by rebuilding all the relevant indexes to include the policy column – this then had to involve having to change the pks to be protected with non-unique indexes.

    Comment by Dominic Brooks — November 30, 2010 @ 9:17 pm GMT Nov 30,2010 | Reply

    • Dominic,

      Thanks for the comment. I’m going to take a few guesses about your implementation (just to mention things that others might comment on):

      Were all your policy functions producing predicates like: “where column = sys_context()” ?
      Were your policies all “semi-static”, e.g. “context”, or “shared context” ?
      Did some of your plans change as you added the policy columns to indexes ?

      Comment by Jonathan Lewis — November 30, 2010 @ 11:27 pm GMT Nov 30,2010 | Reply

  2. For me, the biggest issue with VPD isn’t performance, but low visibility. It’s too easy to forget, or simply not know about, VPD-protected objects. Especially when inheriting a database. As for Database Vault… ouch!

    Comment by Nigel — November 30, 2010 @ 11:46 pm GMT Nov 30,2010 | Reply

  3. I’m struggling to remember all the details and it was only 2 years ago, pretty much to the day.

    This was an urgent “drop everything” implementation. For regulatory reasons, access/data visibility needed to be split between US and non-US so that regulators did not have access to investors and investments outside their jurisdiction – almost sharding (a separate database was also considered as a solution).

    > policy functions producing predicates like: “where column = sys_context()” ?
    > semi-static?
    Hmm, I think it was SHARED_CONTEXT_SENSITIVE across multiple objects. Depending on your security context, the policy produced predicates something like “AND visibility = ‘US’ “, “AND visibility = ‘ROW’ ” or “AND visibility IN (‘US’,’ROW’)”. Regarding the latter policy – it might have been better to return no policy – but there was an imminent requirement to extend a US-like visibility restriction to Australia – i.e. three distinct regions US, AUS & ROW.

    > Plan changes?

    From a performance perspective, the most important aspect was that management expectations had been set to expect an impact of either “significant” or “small but noticeable”, both of which were acceptable given the initial time pressure to implement something before the deadline. In the end it was negligible which was a bonus. Multiple load testing runs were done to validate whether the index changes were required and justifiable – they were… just.

    Partitioning (licensed but not implemented) was considered but discarded due to the additional risk of further change.

    Probably the biggest challenge was the visibility labelling with a complicated framework of triggers and procedure calls to determine the labelling of an entity according to its attributes and the labels of its relations and to maintain the labelling if there was a change somewhere in the web of relationships.

    There was already a slightly different access solution in place via views so the VPD was on top of that.
    I did prototype OLS as a solution to both the VPD and the existing access functionality but ultimately we were given a bum steer on the licensing costs of OLS from the pre-sales consultants. Fortunately, it was not too late to change tack to VPD even with the strict deadlines.

    Finally, some further “dump and run” thoughts on VPD:
    1. VPD and Function result cache – need to include the policy contexts as default arguments in the function signature, e.g.
    2. The MERGE statement doesn’t work with VPD – I’ve read the explanations, I don’t like them. It doesn’t make sense to me.
    3. Implementing VPD on a mature system can be tricky. Especially if you’re got a schema which owns the tables and the packaged code that you’re trying to limit visibility through and you’ve got materialized views. I ended up having to move the MVs to another schema with EXEMPT ACCESS POLICY. I always meant to expand on this and investigate further and write it up. Never did…

    Comment by Dominic Brooks — December 1, 2010 @ 10:08 am GMT Dec 1,2010 | Reply

  4. Most annoying issue what I have faced with OLS/VPD implementation is the following limitation in XMLDB. When OLS/VPD is implemented in a XMLDB oracle cannot use the O-R feature because of the fundamental limitation.

    Metalink note : 761921.1


    Comment by Rajaram Subramanian — December 1, 2010 @ 10:56 am GMT Dec 1,2010 | Reply

  5. Already implemented FGAC and it affected negatively some of the queries very negatively. Fix was easy, taking stored outlines when FGAC is off and activating them before FGAC on. After that did not notice any perf degradation. This was on OLTP, Linux blade 2 core, with avg 70tx/secs.

    Comment by Bernard Polarski — December 1, 2010 @ 11:52 am GMT Dec 1,2010 | Reply

  6. In our experiences/implementation, the actual overhead of OLS is measurable, but typically not an issue (there are of course exceptions to work through, but in general it would be more of a small but noticeable to barely visible depending on the operation in question).

    The performance issue(s) for us are usually a side effect of OLS’ impact on the optimizer resulting in poor plans. When we were on 9.2.0.x we discovered through some testing/10053 digging that the optimizer can’t evaluate the predicates added by OLS (bug: 5674756,4200369). The default filter factor for a predicate that can’t be evaluated is 0.05, which was being added to multiple predicates on each protected table. In our implementation the number of rows being filtered by OLS is generally small, or at least much smaller than 0.05 or (0.05*0.05) would filter. This would of course raise havoc on estimated cardinalities and resulting execution plans.

    For 9.2.0.x to 10.2.x the bug was fixed by Oracle using a filter factor of 1.0 for OLS predicates instead of the default 0.05, resulting in plans that were typically identical to not having OLS at all. This has been much better over all, but we occasionally still have issues crop up where the plan for a labeled user is changed negatively compared to the same plan for a user with READ privilege that bypasses the policy.

    Comment by Pat O — December 3, 2010 @ 1:47 pm GMT Dec 3,2010 | Reply

    • Pat,

      Thanks for the comments, and especially the bug numbers, it’s the type of information that could be very helpful to anyone who finds this blog entry.
      Nice to know that the Oracle developers have tried to take out the selectivity issues in 10g. (Of course there’s no perfect solution with the extra predicates; sometimes you will want a different plan, sometimes you won’t – it depends on the data, number of “private databases”, and different granularities of privilege)

      Comment by Jonathan Lewis — December 4, 2010 @ 2:52 pm GMT Dec 4,2010 | Reply

  7. I had got bad experience with RLS on and It produced very heavy latch contention. But after I had made migration on all problems disappeared. It seemed that new lightweight shared pool serialization mechanism was introduced in that release. I mean mutex. You can read about this on

    Comment by Yuri — December 9, 2010 @ 11:21 am GMT Dec 9,2010 | Reply

    • Yuri,

      Thanks for supplying that information.

      Do you know if the latching activity was caused by calls to the security functions, or by the fact that you can end up with a long chain of child cursors for the same SQL statement – which would all be covered by the same latch – when you use RLS ?

      Comment by Jonathan Lewis — December 10, 2010 @ 8:28 am GMT Dec 10,2010 | Reply

      • I don`t know exactly, but I can suppose that it were long chains of child cursors. At least, I saw lots of child cursors for every query which used RLS protected objects. It seemed that for every user own child cursor was created. However by design, users were divided in groups (the same security predicates were generated), and cursors were to be shared among users in group.
        By the way it was APEX application with custom authentication schema. And as I remember, policies were created with static_policy=>FALSE, but during experiments I recreated them with SHARED_CONTEXT_SENSITIVE and it relived the situation a little bit. Policy function were very lightweight, pure pl/sql without sql.

        Comment by Yuri — December 10, 2010 @ 8:57 am GMT Dec 10,2010 | Reply

  8. ORA_ROWSCN is not available on tables with a VPD policy and I’ve found out why.

    From bug 4947170:

    The problem stems from the fact that VPD builds a view to replace the table reference. The view text does not select the ORA_ROWSCN pseudo column from the underlying table even if the outer query block references it resulting in ORA-904.

    An enhancement request 6609789 has been raised and Oracle is working a solution.

    Oracle provide the following workaround – since the ora_rowscn is available inside the policy function, add a dummy predicate as follows:
    ||' AND f_ora_rowscn('||object_name||'.ORA_ROWSCN) = 1'

    RETURN 1 ;
    END ;

    CREATE CONTEXT store_rowscn USING f_ora_rowscn ;

    Also, to get the row_scn for each row, make it row dependant:


    Now to fetch the row scn in your application, use get_rowscn:

    SELECT t.*, get_rowscn( t.ROWID ) "ORA_ROWSCN" FROM test_table t ;

    Comment by Kevan Gelling — December 16, 2010 @ 11:08 am GMT Dec 16,2010 | Reply

    • Kevan,

      Thanks for that information.

      It’s always interesting to see how features that individually can be very helpful don’t always work when you combine them. It’s one of the commonest types of problem that I find in my consultancy work.

      Comment by Jonathan Lewis — December 16, 2010 @ 6:38 pm GMT Dec 16,2010 | Reply

  9. Hi Jonathan,

    I don’t know if you’ll still read this, but I was pointed to this article when searching for VPD-FGA interactions, and I’ll just try my luck :-)

    So I had intended to implement FGA and VPD at the same time, for a high-security system, but with VPD in effect I do not get the expected FGA entries for UPDATEs and DELETEs any more (while standard auditing is not affected, and neither is INSERT and SELECT in case of FGA).

    VPD is a simple function checking for cond := ‘sys_context(”userenv”, ”session_user”) = ”myappuser”;, and I’ve tried different values for policy_type including DBMS_RLS.DYNAMIC to cause most frequent re-evaluation.
    As soon as VPD is enabled, instead of the expected 2 entries per statement in dba_common_audit_trail,

    VPD tracing shows, for all of select/insert/update/delete from myappuser.X,

    Logon user : me
    Table/View : myappuser.X
    Policy name : VPD_X
    Policy function: SECURITY_ADMIN.VPD_UTILS.VISIBLE_BY_myappuser_ONLY
    RLS view :
    SELECT “X”,”Y” FROM “MYAPPUSER”.”X” “X” WHERE (sys_context(‘userenv’, ‘session_user’) = MYAPPUSER)

    I wonder what’s going on here exactly that causes this behavior… Possibly this is not surprising to you though? :-)


    Comment by recurrentnull — January 22, 2015 @ 4:26 pm GMT Jan 22,2015 | Reply

  10. […] The first execution of the query should show you only the row where unit_id = -1 as “unit_id = -1” is the default return value from the security function. The second execution should return only the row where unit_id = -2 as the call to set_parameter() changes the context value so that when the security function re-executes it generate a new security predicate “unit_it = -2”. (It’s worth noting that one of the options for security policies is to make them context-dependent so that they re-execute only when the relevant context is changed – but in this case the policy defaults to “re-execute the function on every parse and execute”.)  [NOTE: for some clues on the possible performance impact of badly defined VPD, check the comments made in response to this blog note] […]

    Pingback by Flashback Archive | Oracle Scratchpad — December 24, 2019 @ 8:33 pm GMT Dec 24,2019 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by