Predicate to restrict data for corporation

[namitsinghi]

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

[james_foran]

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.

[namitsinghi]

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...