Row Level Security (RLS)

[Anonymous]

Hi, everyone. 

I am testing out row level security in my pbix file and it is not working as expected. I am pulling in CRM data. I have a "System Users, Aggregate" table that brings in security roles, business units, and team, in addition to user information. Because people can have more than one security role (e.g. System Admin, Sales Manager) I need to also include a "System Users" table that only has each user once. I can then join these tables using the UserID.

I have "Opportunities" that I am trying to filter for Users that have a specific Business Unit or Role. So I connect "System Users" to "Opportunities". At this point, I have "System Users, Aggregate" connected to "System Users" and "System Users" connected to "Opportunities". I have a RLS role where "System Users, Aggregate > Business Unit = 'Division 1'" And yet, when I turn on this role, it does not filter "System Users" at all, and therefore "Opportunities" are not filtered. 

And when I edit the relationship and try and click the "Apply security filter in both directions" when the Cross Filter Direction is set to both, I get an error that says "Table 'System Users Agg' is configured for row-level security, introducing constrains on how security filters are specified. the setting for security filter behavior...cannot be both. 

This confuses the heck out of me! The cross filter direction has to be both in order for the security filter to be applied in both directions, as the setting indicates. And it only makes sense that way. I have a value in Table 3 that I want to use to filter Table 1. they are joined via Table 2. Based on the experience I am seeing, this does not seem possible. 

I can send screenshots of anyone wants to see.

Thanks, Scott

[idanco]

Hi,

I managed to resolve the similar issue i was facing by reading Kaspar's article about Dynamic RLS with BI Directional

http://download.microsoft.com/download/2/7/8/2782DF95-3E0D-40CD-BFC8-749A2882E109/Bidirectional%20cr...

(specifically look at the schema on page 28)

Send me a screenshot of your schema and i will let you know how i resolved it.

[Greg_Deckler]

I think that what you want to do is to create an RLS role, for Opportunities, Business Unit = 'Division 1' and then add the Division 1 users into that role.

[Anonymous]

Thanks, @Greg_Deckler. Technically, I could do a merge query on Opportunities that brings in the related information from teams, business units, etc, but that seems like a fundamental shortcoming of RLS right now. I would assume that I can filter 'grandparent' and 'parent' records based on my selection criteria on the child record.

By way of example, let's say the Opportunity is owned by John Smith. And the Users table has John Smith and several other people in it. And then the Aggregated Users table shows that John Smith is on Team A. Then if I have a RLS role where Aggregated Users = Team A, then I would assume I could use that to filter the Users table so only John Smith is returned, and then in turn this would filter the Opportunity table so that only the record owned by John Smith is returned.

The alternative is a series of merged fields and an excessive number of roles that cover every eventuality. THAT would be a huge pain in the neck.

Hopefully, I am overlooking something obvious here. Or perhaps RLS really just isn't ready for primetime yet?

Best,

Scott 

[v-yuezhe-msft]

Hi @Anonymous,

Based on your description, "System Users, Aggregate" table (Many side) and "System Users" table(One side) have many to one relationship, when you create a RLS role where "System Users, Aggregate > Business Unit = 'Division 1'", it doesn’t filter "System Users" table, right? If that is the case, we reported this issue internally two months ago and  got an update from our product team that this behavior of RLS is by design. For more details, please check this similar thread.

Currently, to make RLS work as expected in your scenario, you will need to merge query on Opportunities and create a RLS role that Business Unit = 'Division 1' in Opportunities table.

Thanks,
Lydia Zhang

[idanco]

Hi,

I have the same issue - probably a preview feature bugs ?