By default, an Oracle Business Intelligence installation is configured with an authentication provider that uses the Oracle WebLogic Server embedded LDAP server for user and group information. Subsequently row level security is setup by either adding the data filters directly in the RPD or as is usually done by managing in a table. Mark Rittman has a very good post of setting up secuity for OBIEE 11g here.
However when working with BI Apps, you need to think differently. Ideally you should setup security filters in OBIEE to mimic the responsibility setup in Oracle eBusiness Suite In one of my recent recent BI Application projects, there was a requirement to read Organization based security from EBS and apply this on BI Applications.
To this end, I found that there is a simple view available in EBS which returns all the Organizations that a user has access to. This is ORG_ACCESS_V.
Using this view, you can use a row-wise initialized variable to apply security. An example of the SQL used in the initialization block is below:
SELECT DISTINCT 'SEC_ORG_CODE' var_name, org.organization_code var_val
FROM apps.fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
apps.org_access_v org
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = org.responsibility_id
AND furg.start_date < SYSDATE
AND NVL (furg.end_date, SYSDATE) >= SYSDATE
AND UPPER (fu.user_name) = UPPER (':USER') --OBIEE User
The EBS version I have tried this on is R12.2
.svg)
.svg)