RLS in Composite model Error

[gavin007]

I have 3 set of data in a composite model-one is a live link power bi dataset with RLS, one Excel table and the other is Salesforce import data. These 3 datasets has a employee name table to connect them together. After I publish it, Viewer level user can open the report but with X over all visual but Contributor level user can view it fine with proper RLS applied to PowerBi dataset and Salesforce data. 

I did some research and test, I was told that RLS only applied to original dataset where it is set up, but not the other import data. But somehow it works on both dataset and only in contributor level.

I remove the Saleforce data and try again, this time, Viewer level user can see the visual with proper RLS.

The standard gateway set up is as follow,

-PowerBi Dataset uses no gateway.

-Salesforce data uses no gateway as I import it to desktop first, then publish to service due to the verion of Saleforce

-An excel table uses gateway

I assume it is something to do with Saleforce data but I can't figure out why.

[GilbertQ]

Hi @gavin007 

It sounds to be as if the SalesForce data has got a relationship to your other data where the RLS is being applied and that is why it is getting the RLS applied?

What happens if you remove that relationship?

[gavin007]

@GilbertQ That is just one of the question. I think RSL can apply to Salesforce data because RSL is set up in the employee table in the power bi dateset. This table happen to link to both Salesforce data and the other tables in Pbi dataset.

I did not take out the relationship and check like you suggest as it is working fine now. I thought RLS will not apply to external data in composite model (refer to below workaround). Maybe this bug is fixed now. Can you clarify?

https://radacad.com/row-level-security-on-a-directquery-to-power-bi-dataset-composite-model-my-findi...

Further question bothering me is assign user role in security window.

In the original power bi dataset, A user has been set up as dynamic role (using UPN)  in security window.  in the new report based on the Salesforce and Pbi dataset composite model, the same user cannot open the report unless I create a role in Desktop and assign the role to that user. This is like double handling. 

The workaround I mentioned before by giving that user Contributor access level is not ideal, but because RLS doesn't apply to Contributor, he can view the report.  Here is the interesting part, even RLS in the current workspace doesn't apply to him, but the RLS in the original Pbi STILL APPLY. That is why he can view the report. 

I did a further test on report with ONLY Pbi dataset. In this case, I still need to assign a role to the same user although he has got on in the original dataset.

That means any report I create from composite model, I will need to set up a generic role in Desktop and assign user to that role in Service. That is quite a interesting finding.

[GilbertQ · Accepted solution]

hi @gavin007 

Yeah it does get confusing and quite complicated when combining datasets and then using RLS, this can be quite a challenge to get right when applied across different datasets depending on where the data is imported and where it is being applied.