6 Replies Latest reply: Mar 26, 2014 3:32 AM by user11153253 RSS

    How to add a column to a policy in VPD

    user11153253

      Hi folks,

       

       

      we have already configure VPD(virtaul private database) on set of tables, our requirement is to add one more column for secured named  YMPDBA on CRPDTA.F06106 table which has already 3 column secured (YMPHRT,YMRTAM,YMGPA) ,

      please assists me how do i do this?

      do i need to drop policy  and recreate it by adding new column with existing ?

      for example:

       

       

       

       

      CREATE TABLE CRPDTA.F06106

      (

        YMAN8    NUMBER,

        YMEXR    NCHAR(30),

        YMPDBA   NUMBER,

        YMDISO   NCHAR(1),

        YMRTAM   NUMBER,

        YMGPA    NUMBER,

        YMPHRT   NUMBER,

        YMPPRT   NUMBER)

       

       

       

       

       

       

      CREATE OR REPLACE TRIGGER CRPDTA.F06106_before_upd

       

       

            BEFORE UPDATE ON CRPDTA.F06106

            FOR EACH ROW

      WHEN (

      new.YMGPA = 0

            )

      DECLARE

       

       

          BEGIN

       

       

             if dbms_session.is_role_enabled('SECURED_PAYROLL_ROLE') then

       

       

              :new.YMPHRT := :old.YMPHRT;

          :new.YMRTAM := :old.YMRTAM;

              :new.YMGPA := :old.YMGPA;

       

       

            end if;

       

       

         END;

      /

       

       

       

       

      BEGIN

        SYS.DBMS_RLS.ADD_POLICY     (

          object_schema          => 'CRPDTA'

          ,object_name           => 'F06106'

          ,policy_name           => 'F06106_POLICY'

          ,function_schema       => 'CRPDTA'

          ,policy_function       => 'PAYROLL_FGAC.FUNC_FGAC'

          ,statement_types       => 'SELECT'

          ,policy_type           => dbms_rls.dynamic

          ,long_predicate        => FALSE

          ,sec_relevant_cols     => 'YMPHRT,YMRTAM,YMGPA'

          ,sec_relevant_cols_opt => dbms_rls.all_rows

          ,update_check          => FALSE

          ,static_policy         => FALSE

          ,enable                => TRUE );

      END;

      /

        • 1. Re: How to add a column to a policy in VPD
          Suntrupth

          Yes Indeed. I believe you have to drop/disable this policy, Perform the Alter table.. enable the policy again.

           

          Regards,

          Suntrupth

          • 2. Re: How to add a column to a policy in VPD
            user11153253

            Hi suntrupth,

            you mean drop policy like below

            BEGIN

              SYS.DBMS_RLS.DROP_POLICY (

                object_schema    => 'CRPDTA'

                ,object_name     => 'F06106'

                ,policy_name     => 'F06106_POLICY');

            END;

            /

             

            and then recreate

            BEGIN

              SYS.DBMS_RLS.ADD_POLICY     (

                object_schema          => 'CRPDTA'

                ,object_name           => 'F06106'

                ,policy_name           => 'F06106_POLICY'

                ,function_schema       => 'CRPDTA'

                ,policy_function       => 'PAYROLL_FGAC.FUNC_FGAC'

                ,statement_types       => 'SELECT'

                ,policy_type           => dbms_rls.dynamic

                ,long_predicate        => FALSE

                ,sec_relevant_cols     => 'YMPHRT,YMRTAM,YMGPA'

                ,sec_relevant_cols_opt => dbms_rls.all_rows

                ,update_check          => FALSE

                ,static_policy         => FALSE

                ,enable                => TRUE );

            END;

            /

             

             

            disable policy  like disable =>TRUE  in SYS.DBMS_RLS.ADD_POLICY 

            then

            enable policy like enable =>TRUE in SYS.DBMS_RLS.ADD_POLICY

             

            can you illustrate more?

             

            Regards.

            • 3. Re: How to add a column to a policy in VPD
              Suntrupth

              Hi,

               

              DBMS_RLS.ENABLE_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, enable IN BOOLEAN);

               

               

              Setting Enable to FALSE will disable the policy.

               

               

              Make your changes to the table and enable the policy again by setting ENABLE=True

               

              Regards,

              Suntrupth

              • 4. Re: How to add a column to a policy in VPD
                onkar.nath

                we do have same scenario but we dont drop and recreate the policy everytime we modify the table , we just grant the permission at the table level to required users. That is all. You may have to recreate the synonyms. Try these steps:

                 

                1. alter the table (add the column you want to add) -- do it as schema owner

                2. grant select permission or the permissions needed on the table to the required users -- do it as schema owner

                3. log in as the user and try to read data from the table -- do it as a user and not schema owner

                 

                you should be able to read the data.

                 

                Onkar

                • 5. Re: How to add a column to a policy in VPD
                  user11153253

                  Hi suntrupth,

                   

                  already table F06106 has got column YMPDBA, so my concern is to add that  YMPDBA cloumn to policy  'F06106_POLICY.

                  so now you tell me how can i add  YMAPDBA to   policy . and enable policy?


                  like we have  already 3 secured cloumns in that policy

                  sec_relevant_cols     => 'YMPHRT,YMRTAM,YMGPA'

                  i want to add one more YMPDBA lilke below

                  sec_relevant_cols     => 'YMPHRT,YMRTAM,YMGPA.YMPDBA'       (tell me how can i add that )

                  how can i enable the policy (please give syntax)?


                  Thanks & regards.

                   

                  • 6. Re: How to add a column to a policy in VPD
                    user11153253

                    Hi,

                     

                    already table has got the reuqired column just we nee to add that cloumn to policy and enable the policy, for that i need to know tha way.