Use static or context sensitive VPD policies to limit table access of a specific user?

Question

Let's assume there are two database users: (1) A user named APP who connects to the database very often and queries and modifies many tables in his own schema. (2) A user named WEB who only connects to the database rarely and only queries and modifies a few tables in the APP schema.

I need to limit the table access of both users depending on the data. I'm using the Oracle Virtual Private Database (VPD) feature (sometimes also known as fine-grained access control) to do this. For this question only the rule which limits the APP user is important. The rule which limits the WEB user needs to be in a seperate VPD policy (I might later ask a similar question for that policy).

Which of the following two implementations would give the better query and DML performance for the APP user?

1) Create the policy with policy_type dbms_rls.SHARED_CONTEXT_SENSITIVE and implement the policy function like this:

FUNCTION get_predicate(i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'WEB' THEN
    RETURN NULL;
  END IF;

  RETURN 'some_id_column = SYS_CONTEXT(''APP'', ''some_id'')';
END get_predicate;

2) Create the policy with policy_type dbms_rls.SHARED_STATIC and implement the policy function like this:

FUNCTION get_predicate(i_schema IN VARCHAR2, i_object IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
  RETURN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''WEB'' OR some_id_column = SYS_CONTEXT(''APP'', ''some_id'')';
END get_predicate;

Note that the APP user uses client connection pooling (provided by ODP.NET), so it should only open a few connections for each application usage session. There are around 100 clients which will connect to the database.

APC APC · Accepted Answer · 2013-01-30T13:31:08

The STATIC predicate will only be evaluated once for the session whereas the CONTEXT_SENSITIVE predicate will be re-evaluated every time the middle tier (or the application) re-sets the context. If most of the connections are through the same user (APP) then probably you won't have many context changes (depending on how you manage things in the connecting layer).

However, even if you have shedloads of context changes I would be very surprised if the overhead of re-evaluating the CONTEXT_SENSITIVE predicate was a noticeable element of the total cost of connection. Almost certainly you have bigger performance gains to be made in other parts of your architecture.

So, choose the option which is easier to understand or offers the greater flexibility. Only you can decide what criteria matter. For instance, if you built your policy string generators in a package (rather than the standalone function posted) and that package doesn't hold state you could change your policies on the fly, without disrupting the sessions. However, a STATIC policy will not be applied unless you kill the sessions.

Use static or context sensitive VPD policies to limit table access of a specific user?

1 Answers