EW 9.1 Bypass Row Level Security in the Warehouse without removing SJTs in the OBIEE Repository

(Doc ID 1562616.1)

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.

Symptoms

On : PeopleSoft Enterprise EPM Performance Management Warehouse 9.1

ACTUAL BEHAVIOR
---------------
There is no way to bypass Row Level Security in the warehouse without removing the security join tables from the OBIEE repository.

EXPECTED BEHAVIOR
-----------------------
Ability to bypass Row Level Security in the warehouse without removing the security join tables from the OBIEE repository.

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

BUSINESS IMPACT
-----------------------
Objective is completely disable row level and not have to manage users in EPM.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms