Row Level Security by Org

Question

Summary

Content

Does anyone have any good practice to share on applying row level security against users who belong to different business units.

To date we have implemented a simple 1 to many user creds to orgs tables and managed through ApEx and applying a filter on the fact and dims in the BICs model.  The OTTB method would mean replicating all roles for every BU so we can add the filters to the model, rather unwieldy imo as we have so many BU's.

https://docs.oracle.com/cloud/latest/reportingcs_use/BILPD/GUID-FD4A379D-22F0-40FE-902A-48A094A5CE54.htm#BILUG682

FTisiot · Answer

Then you are doing it correctly, why are you saying you need to create different roles?

Paul Flynn-37804 · Answer

This is what I did and also employed the same tactic in ApEx using a user_groups and user_privs, so similar to what you have above.

FTisiot · Answer

Why don't you assign the BU to filter to a session variable and then use the session variable to filter DIM and FACT?

So you would have:

- User to BU table

e.g.

TABLE_USR_BU

--------------

USER1 BU1

USER2 BU1

USER2 BU2

- BU to list of filters to apply table

e.g.

TABLE_BU_FILTER

----------------------

BU1 "Business Unit ABC"

BU2 "Business Unit CDE"

create a variable with the SQL like

select distinct TEXT_TO_FILTER from TABLE_BU_FILTER join TABLE_USR_BU on TABLE_BU_FILTER.BU=TABLE_USR_BU.BU where TABLE_USR_BU.USER=:USER

This will set into your variable the list of BU filters for a certain user and you can use it in your filtering