TimoRiikonen's avatar
TimoRiikonen
Icon for Advocate V rankAdvocate V
4 years ago
Status:
Investigating

How to stop RLS leaking when I want to mask a column?

My task to do on Power BI report is relatively simple: mask one column with blank or "***" when the user doesn't have enough permissions to see it.

Everything works well when I am looking at the data in Desktop with View As feature:

I filter table A based on email on table B to get the team Id, only one per user. Table B has all team members. Table C then change one id to another id. And table D has nothing but user ids and the column I wish to mask. And this works in Desktop: I only see those rows I am supposed to see.

But problems start after that:

- I can't make an active connect from the filtered "sensitive data" table to the main table Issues as then issues table would be filtered.

1) So I create non-active connection instead and create a calculated column to Issues: 

LOOKUPVALUE('Sensitive data'[Summary], 'Sensitive data'[Id], 'Issues'[Id], "***")
Result: RLS filtering doesn't work. All users see all of the sensitive data and blank rows are just replaced with "***".
2) LOOKUPVALUE('Sensitive data'[Summary], 'Sensitive data'[Id], 'Issues'[Id],'Sensitive data'[TeamID], [RLSTeamId], "***"
Result: When I calculate RLSTeamId = CALCULATE( MAX('RLSDimInnofactorTeam'[TeamId]) ), it shows the correct team id on screen in Card. But on table again RLS doesn't work and everyone gets the maximum TeamId value of the whole company. So masking works to that one team and not to everyone else.
3) CALCULATE(MAX('Sensitive data'[Summary]), USERELATIONSHIP('Sensitive data'[Id], 'Issues'[Id]))
Result: Still RLS doesn't work and result is about same as with case #1.
 
I found some instructions on how to do masking in more complex manner, but why does the RLS keep leaking when I try to use it on a table and calculated column?
Do I have to filter all tables in RLS instead of just one?
Here is my RLS filter:
[TeamId] = LOOKUPVALUE('B'[TeamId], 'B'[email], USERPRINCIPALNAME())
Reports

6 Comments

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi TimoRiikonen ,

     

    When you set up security Roles in Power BI Service, did you confirm that the user does not have edit permissions in this workspace?

    If the user is a role other than viewer in the workspace, then RLS is no longer in effect for them.

     

    Best Regards,
    Community Support Team _ Caitlyn

  • TimoRiikonen's avatar
    TimoRiikonen
    Icon for Advocate V rankAdvocate V
    4 years ago

    Yes,

    Two users with Viewer or Read settings have tested it.

    And also I tested this as an admin using View As.

    And third is that View As in Desktop shows all data as well.

  • TimoRiikonen's avatar
    TimoRiikonen
    Icon for Advocate V rankAdvocate V
    4 years ago

    I added RLS filtering on the 'Sensitive data' table, but this didn't help.

  • TimoRiikonen's avatar
    TimoRiikonen
    Icon for Advocate V rankAdvocate V
    4 years ago

    Perhaps the problem is that RLS doesn't work on calculated column, only on measures?

  • TimoRiikonen's avatar
    TimoRiikonen
    Icon for Advocate V rankAdvocate V
    4 years ago

    Ok, I found the solution, which is this line:

    Summary := LOOKUPVALUE( 'Sensitive data'[Summary], 'Sensitive data'[Id], MIN(Issues[Id]), 'Sensitive data'[TeamId], MIN('Sensitive data'[TeamId]), "***" )

    Why this works:
    When the calculated column is defined, RLS has not been implemented yet. So the Sensitive data table has all of the information.
    Then when MIN (or MAX or AVERAGE or MEDIAN) method is executed, RLS has been implemented. And thus with my and with normal settings, there is only one value that matches, so this works if and only if just only one team can see the sensitive data. If this is not a case, then you have to find the solution from elsewhere or create a fake unique id just for this. Also this calculation is done to a single item, so MIN(Id) returns correct value as well.
    Note that admins don't see their own team, but they see instead contents of another team since RLS doesn't work in measures either.
    This may be somewhat slow. If it is too slow, then I suggest that you show this instead in a tooltip to speed it up.

Completed Ideas