Oracle Analytics Cloud and Server

Products Banner

Data-level-security & roles

Received Response
21
Views
1
Comments

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')

 

  

Answers