Data-level-security & roles

Sébastien Roux

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

 

  

EloiLopes-Oracle

Hi,

Did you follow these steps:

• Create group on IDCS and assign users to that group;
• Create custom application role;
• Add group(step 1) to the new Application role;
• Add custom application role to BIConsumer;
• Check if users have the custom role assigned if not do it manually;

You have an article of A-Team to explain this step by step.

https://www.ateam-oracle.com/implementing-object-and-data-level-security-in-oracle-analytics-cloud-using-identity-cloud

Regards