Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Row Level Security by Org

Received Response
43
Views
3
Comments

Summary

Row Level Security by Org

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

Answers

  • FTisiot
    FTisiot Rank 6 - Analytics Lead

    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

  • FTisiot
    FTisiot Rank 6 - Analytics Lead

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

  • Paul Flynn-37804
    Paul Flynn-37804 Rank 4 - Community Specialist

    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.