Announcement

Collapse
No announcement yet.

Predicate to restrict data for corporation

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Predicate to restrict data for corporation

    Hi,

    I have a requirement to restrict the data that user can view using Predicates. I cannot use Domain/Sub-domain to restrict the data view due to some business requirement.

    I want to restrict the data that user can view to within a corporation using predicates. I have defined a VPD context variable 'CORPORATION_GID' for the corporation to which I wish to restrict the user and defined following predicate (for example) on OB_ORDER_BASE table:

    Predicate:
    EXISTS (SELECT 1 FROM ob_line obl WHERE obl.order_base_gid = OB_ORDER_BASE.order_base_gid and SOURCE_LOCATION_GID IN (SELECT LOCATION_GID FROM LOCATION_CORPORATION WHERE CORPORATION_GID = SYS_CONTEXT('gl_user_ctx','CORPORATION_GID')))

    Test Query:
    SELECT * from OB_ORDER_BASE
    WHERE EXISTS
    (SELECT 1 FROM ob_line obl
    WHERE obl.order_base_gid = OB_ORDER_BASE.order_base_gid
    and SOURCE_LOCATION_GID IN (SELECT LOCATION_GID FROM LOCATION_CORPORATION WHERE CORPORATION_GID = SYS_CONTEXT('gl_user_ctx','CORPORATION_GID')))

    When running the above test query on sql developer, the data shown is only for that specified corporation. But when defininig the predicate and opening the order base page, I see all the order bases, immaterial of corporation. Can you please verify and let me know what is wrong with the above predicate and how can I restrict the search window to show only those order bases that belong to specific corporation.

    I similarly would then define predicates for all the entities within OTM, Order base is just one example.

    Thanks.
    Namit

  • #2
    Re: Predicate to restrict data for corporation

    Have a look at how the VPD is set up. there is another way to test the query...

    1) Run the following PL/SQL
    begin vpd.set_user('USER_GID");end;

    2) Run the query
    select * from ob_order_base.

    3) Run the query
    SELECT SYS_CONTEXT('gl_user_ctx','CORPORATION_GID') from dual

    The results of 2 will allow you to quickly see what effect the VPD is having, as it is applied at the Database level.

    The results of 3 should tell you if your VPD Context is set correctly.

    If you are still having issues, I would advise that you get a DBA to run through the above also. They should be able to get right into the issue.
    James Foran
    Toll Global Information Services
    http://www.tollgroup.com

    Comment


    • #3
      Re: Predicate to restrict data for corporation

      Thanks James.

      I realized the reason for issue. I had defined predicate for Read/Write access only, but not Read access. Thus, when selecting data on search page, data was not getting filtered. Added predicate for Read access also, and the above issue is resolved. I had initially thought that Read/Write internally covers Read also...

      Comment

      Working...
      X