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

Answers

  • I've added the DAX, as per what you posted above - in the Roles against the Payroll Ledger, but it red underlines the references to Report User as non existent.  My guess is that I haven't correctly set the bi-directional settings referred to in your post, so will play around with that for a bit.

    Hmmm interestingly - it only doesn't like 'Report User'[Active_Directory_Name] When it is used in the Related Function.  Which probably adds to the idea that it isn't related properly....


    If I remove the RELATED function, it doesn't like the fact that Ledger_Key can return multiples.  SO I wonder if I need some extra resolution table, but Bridge_Ledger_User should already be that:

    Tuesday, July 17, 2018 10:33 PM

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
  • It does seem like this should work, but when I test run in SSMS, the users can all see everything still.

    I've had to adjust it slightly, as Dim_User doesn't have a Ledger Key 

    I've renamed the entities to make things a little clearer

    What we have is now this:
    Tables - 
    Report User
       Report_User_Key
       Active_Directory_Name    

    Bridge Ledger User
       Ledger_Report_User_Key (Primary key)
       Report_User_Key  (Foreign key to [Report User])
       Ledger_Key (Foreign key to [Payroll Ledger])

    Payroll Ledger
       Payroll_Ledger_Key (Primary Key)
       ...

    Payroll Daily Summary Measures
       <Other fields>
       Payroll_Ledger_Key (Foreign key to [Payroll Ledger].Ledger_Key)
       ...

    Users should only see records from [Payroll Daily Summary Measures] where their active directory name matches that in Report User
    The filter could be conceivably placed at any level, but I'm working on the assumption it should either be the Bridge Ledger User or Payroll Ledger.
    All relationships are set up.


    Monday, July 16, 2018 9:25 PM
  • Hi Peppermallow,

    Thanks for your question.

    In your scenario, you can try below DAX formula in table Payroll Ledger, but you would have to create relationship and bi-directional cross-filtering as the post in my first reply:

    ='Payroll Ledger'[Payroll_Ledger_Key]=LOOKUPVALUE('Bridge_Ledger_User'[Ledger_Key]
    , Related('Report User'[Active_Directory_Name]), USERNAME()
    , 'Payroll Ledger'[Payroll_Ledger_Key], 'Bridge_Ledger_User'[Ledger_Key])


    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

    Tuesday, July 17, 2018 1:33 AM
    Moderator
  • I've added the DAX, as per what you posted above - in the Roles against the Payroll Ledger, but it red underlines the references to Report User as non existent.  My guess is that I haven't correctly set the bi-directional settings referred to in your post, so will play around with that for a bit.

    Hmmm interestingly - it only doesn't like 'Report User'[Active_Directory_Name] When it is used in the Related Function.  Which probably adds to the idea that it isn't related properly....


    If I remove the RELATED function, it doesn't like the fact that Ledger_Key can return multiples.  SO I wonder if I need some extra resolution table, but Bridge_Ledger_User should already be that:

    Tuesday, July 17, 2018 10:33 PM
  • Hi Peppermallow,

    Thanks for your question.

    >>>Hmmm interestingly - it only doesn't like 'Report User'[Active_Directory_Name] When it is used in the Related Function.  Which probably adds to the idea that it isn't related properly....
    Sorry about that, RELATED need a row context, thus you may encounter error "Function LOOKUPVALUE expects a column". Could you please create a calculated column called DomainNames using Related('Report User'[Active_Directory_Name]) first,  Then use this related column in the DAX query provided by me. something like below:

    ='Payroll Ledger'[Payroll_Ledger_Key]=LOOKUPVALUE('Bridge_Ledger_User'[Ledger_Key]
    , 'Bridge_Ledger_User'[DomainNames], USERNAME()
    , 'Bridge_Ledger_User'[Ledger_Key],'Payroll Ledger'[Payroll_Ledger_Key])


    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

    • Marked as answer by Peppermallow Thursday, July 19, 2018 10:04 PM
    • Unmarked as answer by Peppermallow Thursday, July 19, 2018 10:10 PM
    • Proposed as answer by alexander fun Thursday, July 19, 2018 10:26 PM
    Wednesday, July 18, 2018 7:24 AM
    Moderator
  • This works in that, I can now see data, and other users cannot.   This is better, much better, as Im an admin in the structure.

    I get this error saving the Roles, but oddly I don't see this everytime I save.

    Failed to save modifications to the Server.  Error returned: Table 'Payroll Ledger' is configured for row-level security, introducing constraints on how security filters are specified. The setting for Security Filter Behaviour on relationship <GUID> cannot be both.

    There are bi directional relationships between the FACT table, Payroll Ledger and Bridge Ledger user tables only.   The ONLY one flagged as row level security is the one between Payroll Ledger and Bridge Ledger User.

    This is disconnecting valid users, but this probably as simple as working out where the bi-directional relationships should sit, and where the one Row Level Security flag should be set.
    Thursday, July 19, 2018 10:15 PM
  • I've made a slight change.   Bridge Ledger User loads from a view, so I have included Active_Directory_Name in the view and removed the Report User table entirely.

    The DAX formula now reads 
    ='Payroll Daily Summary Measures'[Payroll_Ledger_Key]=LOOKUPVALUE('Bridge Ledger User'[Ledger_Key],'Bridge Ledger User'[Active_Directory_Name],USERPRINCIPALNAME(),'Bridge Ledger User'[Ledger_Key],'Payroll Daily Summary Measures'[Payroll_Ledger_Key])

    Note: The UserPrincipalName was just experimental.

    This hasn't fixed the error.  Interestingly google suggests this was an error in SQL 2016 and patched, but we are on SQL Server 2017 (RTM) - 14.0.1000.169

    EDIT:  The Error message goes away, IF you 1) Set up the Role security first, and then 2) Set the BI directional and Row Security flag in seperate steps. The behaviour is still odd in that when I emulate the logged in user from SSMS it gets no data.  I'm wondering if the emulation mode is what is actually broken, and not the Tabular design itself.

    Thursday, July 19, 2018 11:06 PM
  • Hi Peppermallow,

    Thanks for your response.

    This is really weird. As I test in a couple of SSAS Tabular projects, It will work just like the blog stated.

    For your specific issue, I would suggest opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com), so that a dedicated Support Professional can assist you in a more efficient manner. One more thing, If our replies are helpful to you, please kindly mark it as an answer. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    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 20, 2018 6:09 AM
    Moderator