EW 9.1 Bypass Row Level Security in the Warehouse without removing SJTs in the OBIEE Repository
Last updated on MARCH 09, 2017
Applies to:PeopleSoft Enterprise EPM Foundation - Version 9.1 and later
Information in this document applies to any platform.
On : PeopleSoft Enterprise EPM Performance Management Warehouse 9.1
There is no way to bypass Row Level Security in the warehouse without removing the security join tables from the OBIEE repository.
Ability to bypass Row Level Security in the warehouse without removing the security join tables from the OBIEE repository.
The issue can be reproduced at will with the following steps:
Functionality behind SJT Tables
All the UserIDs used in PeopleSoft EPM are stored in the table “PSOPRDEFN”. Desired users will be grouped under a “Functional Role” and it will be stored in the table “PS_PF_SY_ROLE_USER”. Then by running some setups in PIA pages, for the functional role we will map the Corresponding SID values from the dimensions and these values will be stored in the corresponding “SJT” tables. In the PIA pages setup, there is a Special Access called “Grant All”. When we specify this for a particular “Functional User”, we are granting Admin Access for that Function User.
Let us have a look in OBIEE:
A view was created in the Physical Layer by joining the corresponding SJT table and PS_PF_SY_ROLE_USER. Then this view will be joined with the corresponding dimension. OBIEE Logged in user will be joined with OPRID from PS_PF_SY_ROLE_USER and the function user role is selected. Then this function user role is joined with the SJT table and the SID values are matched with the corresponding dimension. Here the SID value (2147483647) for “Admin Access” is hardcoded in the “OR” condition. So if the logged in user is having “Admin Access”, then all the SIDs will be fetched from the corresponding dimension.
Objective is completely disable row level and not have to manage users in EPM.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms