2 Replies Latest reply: Jan 4, 2013 5:50 PM by fac586 RSS

    VPD access not working with APEX

    983026
      Hi,

      We have a table restr_access in schema A. This table has sensitive data and hence its access is controlled via a VPD policy. Schema user B has been given required VPD role access for this table.

      I am trying to develop an APEX application which will show data from table restr_access. In the APEX application I am using Schema B as the parsing schema. But when I run the report to show the data, nothing is being returned from the table.

      When I login to TOAD as user B and run the select query it returns data. But the same query does not return any data via apex application.

      The VPD policy uses the following code to check whether a user has access to table data or not:

           select count(grantee) into v_count
           from dba_role_privs
           where granted_role = 'A_VPD'
           and upper(grantee) = upper(user) ;
           
      I am guessing since APEX always uses APEX_PUBLIC_USER login to connect to database, VPD policy is restricting table access via APEX. Therefore even though the parsing schema user has required table access, APEX is not able to return the data.

      Is there any way to make this application work without granting VPD role access to APEX_PUBLIC_USER (not a recommended option)?

      Thanks,
      Aman
        • 1. Re: VPD access not working with APEX
          TexasApexDeveloper
          Implement your VPD via APEX... Unless the data tables will be accessed via other applications too..

          Thank you,

          Tony Miller
          Ruckersville, VA
          • 2. Re: VPD access not working with APEX
            fac586
            980023 wrote:
            Hi,
            Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already), and update your forum profile with a real handle instead of "980023".

            When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

            <li>Full APEX version
            <li>Full DB/version/edition/host OS
            <li>Web server architecture (EPG, OHS or APEX listener/host OS)
            <li>Browser(s) and version(s) used
            <li>Theme
            <li>Template(s)
            <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

            Always post code wrapped in tags<tt>\
            ...\
            </tt> tags
            to preserve formatting and prevent it being mangled by the forum software.

            With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
            We have a table restr_access in schema A. This table has sensitive data and hence its access is controlled via a VPD policy. Schema user B has been given required VPD role access for this table.

            I am trying to develop an APEX application which will show data from table restr_access. In the APEX application I am using Schema B as the parsing schema. But when I run the report to show the data, nothing is being returned from the table.

            When I login to TOAD as user B and run the select query it returns data. But the same query does not return any data via apex application.

            The VPD policy uses the following code to check whether a user has access to table data or not:

                 select count(grantee) into v_count
                 from dba_role_privs
                 where granted_role = 'A_VPD'
                 and upper(grantee) = upper(user) ;
                 
            I am guessing since APEX always uses APEX_PUBLIC_USER login to connect to database, VPD policy is restricting table access via APEX. Therefore even though the parsing schema user has required table access, APEX is not able to return the data.
            Correct. The <tt>user</tt> pseudocolumn will always return <tt>APEX_PUBLIC_USER</tt>.

            Note also that privileges granted through roles will not be applied in APEX applications.
            Is there any way to make this application work without granting VPD role access to APEX_PUBLIC_USER (not a recommended option)?
            It depends on whether the APEX application authentication scheme uses the same username as the existing scheme, then the policy can be modified to support this:
            select count(grantee) into v_count
            from dba_role_privs
            where granted_role = 'A_VPD'
            and upper(grantee) = (select coalesce(v('APP_USER'), upper(user)) from dual);
            See also +{thread:id=2241713}+, although I'd really prefer a policy based on application contexts.

            Don't even think about granting anything to APEX_PUBLIC_USER.