4 Replies Latest reply: Dec 22, 2012 11:00 AM by JustinCave RSS

    access all rows inspite of vpd policy row access

    Naamas
      Hi all ,
      lets say that i've created a policy to select specific rows, i'm taking the example in oracle documentation :
      CREATE OR REPLACE FUNCTION auth_orders( 
        schema_var IN VARCHAR2,
        table_var  IN VARCHAR2
       )
       RETURN VARCHAR2
       IS
        return_val VARCHAR2 (400);
       BEGIN
        return_val := 'SALES_REP_ID = 159';
        RETURN return_val;
       END auth_orders;
      ----------------------------------------------------------------------
      BEGIN
        DBMS_RLS.ADD_POLICY (
          object_schema    => 'oe',
          object_name      => 'orders',
          policy_name      => 'orders_policy',
          function_schema  => 'sys',
          policy_function  => 'auth_orders',
          statement_types  => 'select, insert, update, delete'
         );
       END;
      ---------------------------------------------------------------------------
      
      CONNECT oe
      Enter password: password
      2.Enter the following SELECT statement:
      
      SELECT COUNT(*) FROM ORDERS;
      The following output should appear:
      
      COUNT(*)
      ---------
              7
      The 7 rows above are all 'SALES_REP_ID = 159' according to the policy that i've created.
      i know there is an option to fetch all the rows from orders , without droping the policy , and of course from the same user.
      can someone please tell me how ?
      Thanks In Advanced
      Naama
        • 1. Re: access all rows inspite of vpd policy row access
          JustinCave
          i know there is an option to fetch all the rows from orders , without droping the policy , and of course from the same user.
          can someone please tell me how ?
          I'm not sure I understand. VPD would be pretty pointless if an unprivileged user could simply bypass the VPD policy whenever they liked.

          You could grant the OE user the EXEMPT ACCESS POLICY privilege. That would cause it to always bypass all VPD policies. That would generally defeat the purpose of having a VPD policy but it is useful for users like SYS.

          You could also add something to the policy function that allows a user to toggle the policy by, say, setting a package variable. I'm hard-pressed to imagine why you would want to do that but you could.

          Justin

          Edited by: Justin Cave on Dec 20, 2012 1:21 PM
          • 2. Re: access all rows inspite of vpd policy row access
            Naamas
            Hi justin
            thanks for your response ,
            i thought there is something called application context value i. i just coyldn't understand how to use it to my purpose
            please your help
            Naama

            Edited by: Naamas on 23:18 18/12/2012
            • 3. Re: access all rows inspite of vpd policy row access
              Zoran Pavlovic
              Applications contexts are special memory locations, in which you can store specific data (so for instance you don't have to query employee_id of currently connected user every time he is accessing some resource. Instead of that, you query it once and then store it in application context).

              But that cannot override access policy. If you have policy created, then every time user accesses data, predicate will be added to query. The only way that you can avoid this is by granting user an EXEMPT ACCESS POLICY privilege.


              Zoran
              • 4. Re: access all rows inspite of vpd policy row access
                JustinCave
                An application context doesn't let you bypass a VPD policy.

                If you choose to code the VPD policy to allow it, you could define a policy that returns '1=1' if a particular value is set in the context. For example
                CREATE OR REPLACE FUNCTION auth_orders( 
                  schema_var IN VARCHAR2,
                  table_var  IN VARCHAR2
                 )
                 RETURN VARCHAR2
                 IS
                  return_val VARCHAR2 (400);
                 BEGIN
                  IF( sys_context( 'YOUR_CONTEXT', 'SALES_REP_ID' ) = 'ALL' )
                  THEN
                    return_val := '1 = 1';
                  ELSE
                    return_val := 'SALES_REP_ID = to_number( SYS_CONTEXT(''YOUR_CONTEXT'', ''SALES_REP_ID'') )';
                  END IF;
                  RETURN return_val;
                 END auth_orders;
                which assumes that you have create the context YOUR_CONTEXT and that you have populated the attribute SALES_REP_ID with the ID that you want to allow (or 'ALL' if you want to allow the user to access every row). Of course, you could base the logic in the VPD policy on anything you would like-- you could allow certain users or users with a particular group to get a predicate of '1 = 1' for example.

                Justin

                Edited by: Justin Cave on Dec 22, 2012 12:00 PM