RLS: how can I enforce RLS for report creators? Can I do RLS inside the data source?

[OneWithQuestion]

I have several common data sources that various report creators want to access.

However, we have to restrict access based off the security of who is accessing the data.

In PBI and SSAS Tabular this normally works fine as I will build the data models, use RLS inside the datamodel and let the user consume that.

However, we have users who would like to use multiple datasets or sources in a single report.

Example:  We have data that needs RLS protection on our SQL server and we want to also access three or four other data sources (SPO, Excel, etc...) that do NOT need RLS.

How can I enfore RLS on one or two data sources, but allow report creators to build their own reports adding other data sources as they desire?

[lbendlin · Accepted solution]

Clarify what you mean by Report Creators.  Also please specify which SKU you are on.

Notes: 

RLS does not work in a workspace. Anyone with access to the workspace can see all the data.  One of the many reasons to restrict access to workspaces as much as possible.

RLS works as expected in apps. It also works in reports created off the apps, and in the "Analyze in Excel"  files that connect to the dataset.

[edhans · Accepted solution]

@OneWithQuestion 
RLS is on the model, not a data source. But when you are setting up RLS, if you only set it up on some fields that are in some tables, then only those fields/tables will be affected.

But if your users are report creators and using Power BI Desktop, they can see everything. That is where you set up the RLS conditions, then you set up the RLS Security in the service. So you cannot restrict data in Power BI Desktop unless it is restricted at your source - SQL Server or SSAS before the data gets to Power BI Desktop.

However, if you do that, then I am not certian how any additional RLS could be done. For example,

• Report Writer A has permissions to see Sales Regions A, B, and C, but not D, E and F. 
• Report Viewer B has permissions to see region F
• How would Report Writer A be able to see and test to write RLS to limit region F to Viewer B if they cannot even see F?

Typically report writers see all data. I am not saying it cannot be done, but it really becomes more of a data management issue at the source, not within Power BI.

Is that your question?

[edhans · Accepted solution]

@OneWithQuestion 
RLS is on the model, not a data source. But when you are setting up RLS, if you only set it up on some fields that are in some tables, then only those fields/tables will be affected.

But if your users are report creators and using Power BI Desktop, they can see everything. That is where you set up the RLS conditions, then you set up the RLS Security in the service. So you cannot restrict data in Power BI Desktop unless it is restricted at your source - SQL Server or SSAS before the data gets to Power BI Desktop.

However, if you do that, then I am not certian how any additional RLS could be done. For example,

• Report Writer A has permissions to see Sales Regions A, B, and C, but not D, E and F. 
• Report Viewer B has permissions to see region F
• How would Report Writer A be able to see and test to write RLS to limit region F to Viewer B if they cannot even see F?

Typically report writers see all data. I am not saying it cannot be done, but it really becomes more of a data management issue at the source, not within Power BI.

Is that your question?

[OneWithQuestion]

Thank you to both replies, and yes I was not articulating my thoughts well.  I realized I was confused about the details in general.


I wanted to create a data source that report creators (Power BI Pro users, we don't have Premium) could consume and then add additional data sources to their PBI data model.

However, for RLS to work it has to be handled inside the data source itself or via PBI RLS in a dataset.

At present, PBI does not support multiple datasets in a single PBI desktop report, so I can't define RLS in a shared dataset.

[edhans · Accepted solution]

Yes. Creating a report based on multiple datasets is coming but not sure when, and I wouldn't be surprised if additional features like RLS, Incremental Refresh, etc. wouldn't be available day one. Could be, but that is a lot to launch right out of the gate.

[lbendlin · Accepted solution]

Clarify what you mean by Report Creators.  Also please specify which SKU you are on.

Notes: 

RLS does not work in a workspace. Anyone with access to the workspace can see all the data.  One of the many reasons to restrict access to workspaces as much as possible.

RLS works as expected in apps. It also works in reports created off the apps, and in the "Analyze in Excel"  files that connect to the dataset.