1

Generic_Schema has one common table, GENERIC_TABLE. The primary key of GENERIC_TABLE is COL1 and COMPANY_ID.

Company_id     Col1 Col2 Col3
-------------- ---- ---- ----
ALL_COMPANIES  A    B    C
Comp1          A    B1   C1
Comp3          A    B3   C

Specific Company Schema

  1. Schema1 =>Comp1
  2. Schema2 =>Comp2
  3. SchemaN =>CompN

We have implemented RLS on GENERIC_TABLE. The RLS function returns a clob value, but the logic to retrieve the filter values inside the function is:

  1. Compare the key with other values, and if the Comp1 Schema request data, then A|B1|C1 data is retrieved and the ALL_COMPANIES data is not retrieved because a company-specific value exists in the table.
  2. If the Comp4 retrieve data, it will get only the A|B|C record.
  3. The filter condition need to be retrieved from another function which returns the rowid.

Now, here is the issue. After implementing this, the elapsed time of the RLS is much higher, though we didn't see much I/O. Can anyone think that this design is an issue, because this is not a static predicate and it is context sensitive. Please let me know if you need more inputs.

Functions Definition is 

create or replace function
  book_access_policy
  (obj_schema varchar2, obj_name varchar2)
  return clob

 is

CURSOR get_d_predicate
IS
 SELECT /*+ RESULT_CACHE */ rd
  FROM
  (
  SELECT rowid rd
        ,DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY CASE WHEN COMPANY_ID = sys_context('publishing_application','company_id') THEN 1 ELSE 2 END) rnk
FROM GENERIC_TABLE
  )
   WHERE rnk=1;

get_d_predicate_rec get_d_predicate%ROWTYPE;
d_predicate clob;

begin

 if sys_context('publishing_application','company_id') IS NULL THEN
  d_predicate:= ' 1=1';

 else
   d_predicate =' rowid in ('
    OPEN get_d_predicate;
    LOOP
     FETCH get_d_predicate INTO get_d_predicate_rec;
     EXIT WHEN get_d_predicate%NOTFOUND;
       d_predicate = d_predicate|| get_d_predicate_rec.rd;
    END LOOP;
   CLOSE get_d_predicate;
    d_predicate:=d_predicate||')';


 end if;

return d_predicate;

 end;
 /
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72
  • 1
    @APC :`https://stackoverflow.com/questions/14953270/performance-implications-of-using-dbms-rls-oracle-row-level-securityrls` i read through link ,but i need implementation insight on the same – Gaurav Soni Feb 07 '18 at 10:34
  • @AlexPoole Sorry , i meant row level security – Gaurav Soni Feb 07 '18 at 10:41
  • Can you consider redesign for shared_context_sensitive ? – jareeq Feb 07 '18 at 21:26
  • @jareeq we are yet to explore these features – Gaurav Soni Feb 07 '18 at 21:40
  • @jareeq How does it improve performance ? – Gaurav Soni Feb 08 '18 at 11:33
  • Shared with context_sensitive is step up in performance - don't know is it applicable in your situation. Since rls encapsulate statement with where clause it ca be over kill and should be simple and fast. With shared context sensitive your predicate is evaluated once with session. You can for example create rule where table.company_id is compared to evaluated (once) company_id not generating rowids from dynamic function for each record.But it depends from additional logic that you already use. – jareeq Feb 09 '18 at 07:14
  • Dosent worked ,i can see the function being parsed every time and the elapsed time does not reduced ,any suggestions – Gaurav Soni Feb 10 '18 at 07:39
  • Added RLS function code, which looks somewhat like above only, and also tried this functionality with Context Sensitive as well as Shared Context Sensitive, but the parsing and elapsed time does not reduce. – Gaurav Soni Feb 11 '18 at 18:32

0 Answers0