none
Setting up row level security based on a bridging table

    Question

  • I have three tables:  A fact table with a ledger_key (Fact_Ledger) , a bridge table that contains User_Key and Ledger_key(Bridge_Ledger_User), and finally a Dim Table  for Users (Dim_User), with the User_Key, and the AD User Name

    I am trying to set up Row Level security on the Fact table, so that, if the logged in user doesn't have the rights to a particular ledger they are thrown out.  

    On the Bridge_Ledger_User table, I have added a calculated column called "Implied_Security" that is correctly getting the AD User and Ledger joined, so that I only have to make a single hop from the fact to the bridge tables to imply security.

    On my fact table, I add a row filter ROLE like so:

    =CONTAINS(
        Bridge_Ledger_User
       ,Bridge_Ledger_User[Implied_Security]
       ,CONCATENATE(CONCATENATE(USERNAME(),"|"), 'Fact_Ledger'[Ledger_Key])

    )

    Implied_Security is =CONCATENATE(CONCATENATE(LOOKUPVALUE(Dim_User[Active_Directory_Name],[User_Key],[User_Key]),"|"),Bridge_Ledger_User[Ledger_Key]) and appears to be working correctly - It absorbs the underlying AD Group and the ledger ID into a single string, that I can match on in the fact later. 

    Im wondering if the "Contains" is just finding that a row exists somewhere in the table and always returns true, rather than looking at just the context of the current row.

    The symptom is fairly simply:  Every person can see every row.  I had expected either every person will see their rows, or would see nothing.

    Anyone got any thoughts?

    Friday, July 13, 2018 3:56 AM

All replies

  • Hi Peppermallow,

    Thanks for your question.

    If I understand you correctly, It is not ncessary to do the concatnation, and you can delete the relationship between Dim_User and Bridge_Ledger_User, as LOOKUPVALUE did not use the relationship. You can just set Row filters in table Bridge_Ledger_User with below DAX formula:

    =Bridge_Ledger_User[Ledger_Key] = 
                                 LOOKUPVALUE(Dim_User[Ledger_Key],                            
                                 Dim_User[Active_Directory_Name],USERNAME(),
                                 Dim_User[User_Key],Bridge_Ledger_User[User_Key]
                                 Dim_User[Ledger_Key],Bridge_Ledger_User[Ledger_Key])


    For more information, please refer to below blog:
    https://www.kasperonbi.com/dynamic-security-made-easy-with-ssas-2016-and-power-bi/

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 13, 2018 6:37 AM
    Moderator