CODEWITHSUNDEEP
sqloracle-databasequery-optimizationrow-level-securityvpd
Unziello
Unziello

Reputation: 113

Speeding up queries subject to VPD policy

I'm currently applying this predicate on a VPD policy:

CREATE OR REPLACE FUNCTION limit_table(
  schema_p   IN VARCHAR2,
  table_p    IN VARCHAR2)
  RETURN VARCHAR2
AS
  pred VARCHAR2 (400);
BEGIN 
  pred := ' ((select count(granted_role) from user_role_privs where (granted_role = Jurisdiction OR granted_role = Business_Domain))  = 2)  OR
                  ((Jurisdiction = '''' OR Jurisdiction IS NULL) AND (Business_Domain = '''' OR Business_Domain IS NULL)) '  ; 
  RETURN pred;
END;
/

What it does is checking if the user has roles matching both the Jurisdiction and Business domain (of the table I assigned the policy to) and also if they are null or empty. In those cases, the row is whitelisted and the user can see it. The problem is that this policy slows down the queries made by the users affected by it more than I would like. The bottleneck is the first clause of the predicate. I also tried with dba_role_privs which allegedly should be faster but performance stays the same.

I followed what this page suggests https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:484621563404 but unfortunately I'm not able to try the third method because my client tells me the sys.user$ table does not exist.

Also tried indexes on jurisdiction and business domain and they don't improve anything.

Originally, the predicate was

pred := '(
  (
    (
      (Business_Domain IS NULL OR 
        Business_Domain = '''' OR 
        Business_Domain IN (select granted_role from user_role_privs)
      ) 
      AND  
      (Jurisdiction IS NULL OR 
        Jurisdiction = '''' OR 
        Jurisdiction IN (select granted_role from user_role_privs) 
      )
    )  )
 )'; 
 RETURN pred;

But it was way slower.

Does anyone know ways I can speed up that predicate? Thanks.

Upvotes: 1

Views: 328

Answers (1)

Unziello
Unziello

Reputation: 113

Thanks to the suggestion by the very kind @WilliamRobertson, I switched the predicate to:

' ((sys_context(''SYS_SESSION_ROLES'', jurisdiction) = ''TRUE'' AND sys_context(''SYS_SESSION_ROLES'', business_domain) = ''TRUE'')  OR
              ((Jurisdiction = '''' OR Jurisdiction IS NULL) AND (Business_Domain = '''' OR Business_Domain IS NULL))) '  ;

This required, however, to make the roles granted by the users defaults, otherwise they would not appear in the session_roles. I did it with:

ALTER USER <name> DEFAULT ROLE ALL;

Upvotes: 2

Related Questions