Dynamic Row Level Security

[mwilliamson]

Hello,

I am relatively new to PBI and working on implementing dynamic row level security. The goal is for the user to be able to see values for a specific field they have access to and blank values for all other values in that field they do not have access to. For example, a person has access to see position grades GR_1-5 but not GR_7-12 so in the table visual they should see values for 1-5 and blanks if a that row is a grade 6+ (these are not numerical grades and a similar problem will be posed by other data fields down the road). 

I have a few tables currently, with the Employee table housing the sensitive data and an Employee non sensitive table for non-sensitve data. There are also 2 dimension tables (location and position grade) along with 2 RLS tables that define what a person should have access to. The visual will use as much data from the non-sensitive table as possible with only sensitive data like grade coming from the Employee table. 

Relationship model- 

At some point there will be multiple RLS groups, but for now I am using the following code on the Employee table (for position grade- there is a similar code for location too but that is in a different RLS group right now but if it can be added to the same group that is also great) -

'Employee'[Position Grade] IN

CALCULATETABLE (

    VALUES('RLS Filter Grade'[Position Grade]),

    FILTER('RLS Filter Grade', 'RLS Filter Grade'[Email Address] = USERPRINCIPALNAME())

)

Any ideas on how to edit the DAX code to show the values someone should see while returning a blank value for anything outside of that. 

[lbendlin]

You are doing a little too much. Instead, create a single role, and set the rule for both RLS tables to 

[Email Address]=USERPRINCIPALNAME()

[mwilliamson · Accepted solution]

This is what my department has been tasked with to mirror PBI to what other data individuals see in our other systems. Filtering down to just show what they have full access to is in the RLS table was what I started with, but now we need to make sure they can see all employees (not just the filtered down people) but only have access to their permissioned data for sensitve data fields with other values just simply being blank. 

[lbendlin]

Sounds like you want to implement OLS in addition to (or instead of) RLS?

[mwilliamson]

That is an excellent question. I was told to go the RLS route and that it should work if there is a sensitive and non-sensitive table, but past that little bit of guidance have been trying to figure out. 

[lbendlin]

if you have a non-sensitive table then it must not be joined to (be controlled by) the RLS dimension tables. 

[mwilliamson]

The non-sensitive table currently is only joined to the sensitive table in a single direction relationship (NS filters the S table), if that helps. 

[lbendlin]

They should not be joined at all. Think of them as separate fact tables.

[PijushRoy]

Hi @mwilliamson 

Can you please look into the solution mentioned in the video - https://www.youtube.com/watch?v=jphj40tBPD8

[mwilliamson]

That was the original code we tried and it worked to filter out to what sensitive they should see, but then they lost visibility of all other rows of data that were not sensitive. The goal is to show all non-sensitive data and let the RLS show sensitive data and blank values for those they don't have access to (i.e. they still have all rows of data in the displayed table but the column with position grade will only show either 1-5 or blank values). 

[ajohnso2]

This is the behaviour of RLS - it filters, i.e you can only see rows that an employee is configured to see. 

You could potentially scrap the RLS idea and create measures that link to your RLS table (RLS roles are not configured though) Use dax to SWITCH measures by looking up agaisnt your RLS table, i.e If a user is configured to see data then present it, if not BLANK() out the value.