2 Replies Latest reply: Sep 10, 2013 9:49 AM by bluefrog RSS

    Question on enabling/disabling VPD policies

    bluefrog

      Hi

       

      There are two polcies on a single TABLE, ACC. One is disabled and another enabled.

      The disabled policy is column based and enabled row based.

      The requirement is to "switch" between the two, i.e. when enabling either one the other should be disabled.

      The reason for the having two policies and requiring to enable/disable is  that the row based policy is used for

      the online UI purposes whereas the column based policy is used for reporting purposes.

       

      The question is, how can I "switch" between the two, specific to a session, without effecting

      the enabled/disabled policies in other sessions and/or new sessions?

       

      Another question might be, can one enable/disable a policy at a session level as I think it is applied

      at a system level?

       

      On last question, should I rather post these question in any other forum category?

       

      A description of the rules created is listed below:

       

      create table ACC (acc_ID number, acc_no number);

       

      The function rule uses a view, which is defined as follows:

       

      SQL> desc vw_acc_branch_map;

      Name                    Null?    Type

      ----------------------- -------- -----------

      ACC_ID                           NUMBER(13)

      BRANCH_NO                        VARCHAR2(4)

      BR_CNT                           NUMBER

       

      For exampe, the result for account 4234 is:

        SQL> select acc_id,branch_no,br_cnt from vw_acc_branch_map where acc_id=4234;

       

      ACC_ID BRANCH_NO BR_CNT

      ------ --------- ------

         4234 6059           1

       

      There may be more than 1 branch per account.

       

      The actual functions in use is somewhat more complicated but the following represents a cut-down version:

       

      -- Function used for Row based VPD policy

      create or replace function fn_Acc_Row_Rule (p_Schema in varchar2

                                                 ,p_Table  in varchar2) return varchar2 as

        begin

          return ' exists (select 1 from vw_Acc_Branch_Map  am

                           where  am.ACC_ID          = ACC.ACC_ID

                           and    am.Branch_No       = sys_context(''BRANCH_CONTEXT'',''ACC''))';

      end fn_Acc_Row_Rule;

       

      -- Function used for Column based VPD policy

      create or replace function fn_Acc_Column_Rule (p_Schema in varchar2

                                                    ,p_Table  in varchar2) return varchar2 as

        begin

          return ' not exists (select 1 from vw_Acc_Branch_Map  am

                               where  am.ACC_ID          = ACC.ACC_ID

                               and    am.Branch_No       = sys_context(''BRANCH_CONTEXT'',''ACC''))';

      end fn_Acc_Column_Rule;

       

      The two policies, one row based and another column based, are defined as follows:

       

      -- ROW BASED FILTERING

      exec  dbms_rls.add_grouped_policy(object_schema     => sys_context('userenv','current_schema')

                                   ,object_name           => 'ACC'

                                   ,policy_group          => 'BRANCH_ACC_ROW_GRP'

                                   ,policy_name           => 'BRANCH_ACC_ROW'

                                   ,policy_type           => dbms_rls.static

                                   ,enable                => true

                                   ,policy_function       => 'fn_acc_row_rule');

       

      -- COLUMN BASED FILTERING

      exec  dbms_rls.add_grouped_policy(object_schema     => sys_context('userenv','current_schema')

                                   ,object_name           => 'ACC'

                                   ,policy_group          => 'BRANCH_ACC_COLUMN_GRP'

                                   ,policy_name           => 'BRANCH_ACC_COLUMN'

                                   ,policy_function       => 'fn_acc_column_rule'

                                   ,statement_types       => 'SELECT'

                                   ,enable                => false

                                   ,policy_type           => dbms_rls.static

                                   ,sec_relevant_cols     => 'ACC_NO'

                                   ,sec_relevant_cols_opt => dbms_rls.all_rows

                                   );

       

      Notice the "enable" parameter is disabled for the column based policy and enabled for the row based polcy.

       

      The following procedure is used to enable or disable a policy:

       

      begin

        for r in (select

                         object_name

                        ,policy_group

                        ,policy_name

                        ,enable

                  from

                         user_policies up

                  where

                         up.policy_name in ('BRANCH_ACC_COLUMN','BRANCH_ACC_ROW') ) loop

                        

          dbms_rls.enable_grouped_policy(object_schema  => sys_context('userenv','current_schema')

                                        ,object_name    => r.Object_Name

                                        ,group_name     => r.Policy_Group

                                        ,policy_name    => r.Policy_Name

                                        ,enable         => (r.Enable = 'YES') );

        end loop;

      end;

      /

        • 1. Re: Question on enabling/disabling VPD policies
          Frank Kulash

          Hi,

           

          I don't think you can have different policies in different sessions.

           

          What if you had 2 different views, each with a different policy?  Authorized users could decide which one they want to use.

           

          Can you have 2 different enabled policies on the same table?  If so, one could check a session-dependent value (such as a SYS_CONTEXT value), and not hide anything, while the other does its normal job.

           

          Can you have a single policy that does (or, more precisely, claims to do) both row- and column filtering?  If so, depending on the SYS_CONTEXT value, either the row filtering or the column filtering would actually let everything pass through, while the other part would do its normal job.

           

          Sorry, I can't test these ideas myself right now.

          • 2. Re: Question on enabling/disabling VPD policies
            bluefrog

            Hi Frank

             

            Thanks, I think having multiple views each with a policy might be the solution. I am faced with the prospect of modifying legacy code though, so I was hoping I could get away with no code changes.

            Having two enabled policies and checking which one to use based on a a session sys_context variable unfortunately results in significantly degrading performance relative to the current systemm performance.

            Reason being the function predicate is somewhat more complicated than what I posted above as it references a view which in turn references several materialized views that belong to a schema with the EXEMPT ACCESS POLICY privilege (i.e. can view all data).

             

            Thanks