Summary
Manage filters specificaly both BIConsumer and BIServiceAdministrator
Content
Dears,
We recently implemented data level security in OAC-BI for BIConsumer role, to do so we designed a basic table with user email column (ref to :user) and another one with the filter string (ref to PRODUCT_CODE).
What I want is that filters apply only to my BIConsumer role and not for other roles such BIServiceAdministrator. So the users that are provisioned into BIServiceAdministrator won't have to be specified in the security table.
I tried the following but without success, any best practice to recommend?
Thank you for your help.
-- This part is for BISERVICEADMINISTRATOR which has no filtered access
SELECT DISTINCT 'USER_ACCESS', PRODUCT_CODE
FROM DWH_DEVT.DIM_PRODUCT
WHERE (
CASE
WHEN INSTR(UPPER(VALUEOF(NQ_SESSION.ROLES) || ';'), 'BISERVICEADMINISTRATOR') > 0
THEN 'ADMINISTRATOR'
ELSE 'USER'
END) = 'ADMINISTRATOR'
AND NOT EXISTS (
SELECT NULL
FROM DWH_DEVT.P_USER_SECURITY
WHERE UPPER(EMAIL) = UPPER(':USER'))
UNION
-- This part is for BICONSUMER
SELECT DISTINCT 'USER_ACCESS', PRODUCT_CODE
FROM DWH_DEVT.P_USER_SECURITY
WHERE UPPER(EMAIL) = UPPER(':USER')
