OBIEE Data-Level Security with a Twist

Row-level data security can be enforced in the RPD by filtering column values based on role membership of the user. With this approach, users can only retrieve records (i.e. rows) to which they have access to. In other words, users only have access to a subset of records.

We have encountered the following data-level security requirement that needs a different approach: Our client’s organization was divided into several geographic regions. Members of one region had to be able to see all summary-level data for ALL regions but were only allowed to see detail-level data of their own region.

The organization was divided into 10 regions and the OBIEE implementation was to measure sales revenue generated by sales persons associated to these regions. All users, regardless to which region they belonged, had to be able to see the sales revenue numbers for all regions. However, they were not allowed to see the names of the sales persons from other regions. In this scenario, the standard row-level security approach did not work since it would have prevented the users from having access to the summary level data for all regions. After all, all records would have been filtered by their own region by design. Therefore, records from other regions would have been invisible.

The solution required the following alternate approach: Instead of filtering data rows we had to implement conditional data masking based on role membership.

We have implemented the solution as follows:

1) We configured OBIEE to use the client’s enterprise LDAP as its authentication source.

2) We then created the following new LDAP groups and assigned users to their appropriate group:

□ ORCL_BI_EMBI_Region_1_Restricted_Data
□ ORCL_BI_EMBI_Region_2_Restricted_Data
□ ORCL_BI_EMBI_Region_3_Restricted_Data
□ …
□ ORCL_BI_EMBI_Region_10_Restricted_Data
□ ORCL_BI_EMBI_HQ_Restricted_Data (people in this group should be able to see all detail data)

3) We created a GROUP session variable in the INIT BLOCK of the RPD to hold all LDAP group names to which the current users belongs.

4) All sales person attributes were stored in the DIM_RESPONSIBLE_PERSON dimension table. In order to simplify the data-level security logic in the RPD, we have added an additional column “LDAP_GROUP_NAME” to this dimension table. The ETL process populates the LDAP_GROUP_NAME based on the region membership of the sales person.



5) We then created new logical columns in the DIM_RESPONSIBLE_PERSON table in the RPD’s business layer to include the conditional data masking logic. For example, the code for the new logical FirstName column looked like this. We have added the ORCL_BI_EMBI_HQ_Restricted_Data group to allow HQ users to see all data:

CASE
WHEN LOCATE('ORCL_BI_EMBI_HQ_Restricted_Data', VALUEOF(NQ_SESSION."GROUP")) > 0
THEN EMBI."DimResponsible Person"."First Name1"
WHEN LOCATE(EMBI."Dim Responsible Person".LDAP_GROUP_NAME, VALUEOF(NQ_SESSION."GROUP")) > 0
THEN EMBI."Dim Responsible Person"."First Name1"
ELSE '*****'CAST (EMBI."Dim Check In Out Person"."Person Ky" AS CHARACTER ( 30 ))
END

6) When logging into OBIEE as a Region 6 user, one will see all summary data for all regions und detail-level data for region 6 only as seen in the screenshot below.