1 Reply Latest reply: Mar 2, 2012 8:01 AM by 667905 RSS

    VPD and public database links

    667905
      I am trying to set up VPD using global application contexts. The architecture is a middle tier .NET web application connecting to the database via TNS.

      The context is created using:
      CREATE OR REPLACE CONTEXT class_ctx USING class_ctx_mgr ACCESSED GLOBALLY;

      I have created a security user which owns a package (class_ctx_mgr) which contains a procedure to set the session id (using DBMS_SESSION.SET_IDENTIFIER and a procedure to set an attribute name and attribute value in the session context using DBMS_SESSION.SET_CONTEXT.

      I also have a policy function that writes my predicate and have associated this with a policy and a view that I am restricting access to.

      I have the following database application schema owners:
      USER1 – owns the view which is being restricted using VPD.
      USER2 – owns an API in the form of a package containing various ‘get’ functions. One of these functions gets the list of values in the view owned by USER1 via a public database link and returns the list as a ref cursor, thus:

      FUNCTION f_get_classes
      RETURN t_output
      IS
      cur_output   t_output;
      BEGIN
      OPEN cur_output FOR
      SELECT identifier as class
      FROM my_class_view@zzz.world
      ORDER BY identifier;

      RETURN cur_output;
      END f_get_classes;


      I am testing this from SQL Developer. I do the following:

      exec secadmin.class_ctx_mgr.set_session_id ('12345'); -- set the session id
      exec secadmin.class_ctx_mgr.set_class_ctx ('STZ'); -- set the class context attributes

      SELECT identifier as class FROM USER1.my_class_view ORDER BY identifier;

      CLASS
      T42
      CVS

      This is exactly what I expect to get. So the policy and predicate are doing their job correctly and this is evident if I directly access the view.

      Now I want to take it a step further and use the API call, which is what the web application will do in reality:

      SELECT spk_app_dqg.f_get_classes FROM dual;

      I get nothing back. It seems to be something to do with the DB link because if I run the query in the f_get_classes function above

      SELECT identifier as class
      FROM my_class_view@zzz.world
      ORDER BY identifier;

      then I get nothing, whereas if I use direct access to the schema

      SELECT identifier as class
      FROM USER1.my_class_view
      ORDER BY identifier;

      then it works.

      The question is then, how do I get the session context to be accessible to the VPD policy when I’m using the DB link?

      This is all on the 11gR2 database.
        • 1. Re: VPD and public database links
          667905
          More digging leads me to think that I can't do what I want without coding a mechanism to duplicate the session context on the other end of the DB link. The DB link has a fixed name and the target will always see the user and never the session context associated with the web application session.