Restricting Dataset Access

[andygoh]

Hi All,

I'm looking for a solution to the abovementioned in title. 

I understand there are Viewers and Contributors, but I'd like to be able to limit Contributors such that they cannot edit the dataset query by downloading as .pbix and going into the query editor. We want to limit them to only seeing their own data using a WHERE clause in the query. At the same time, we want them to be able to create their own reports. Tableau has this concept of "published data sources", and I'm wondering if Power BI has something similar or something that can achieve the same effect. They can use the dataset to create their reports, but cannot download and modify this dataset.

I've searched around regarding this topic and there seems to be no good solution about it. Just trying my luck here again since most of the threads were quite old (2017-2018).

Thanks a lot.

[Burningsuit · Accepted solution]

Hi @andygoh 

You can do what you describe with a combination of "build" rights and Row Level Security.

Firstly "Build" rights.

Normally a "Viewer" in a Workspace can view the Reports and not much else. However if you grant "build" rights to the viewer on the Dataset they can do more.

Go to the Workspace Dataset you want to add build rights on.

From the Dataset "more options" (three dots) select "Manage Permissions"

On the "Direct Access" tab, on the selected user click the "more options" and click "add build".

Now the viewer has rights to create new Reports based on that Dataset.

They can do that by using Power BI Desktop and selecting "Power BI Datasets" from the data section of the Ribbon, or "Get Data" Icon. That will allow them to select from a list of Datasets they can connect to, connect to the Dataset and build Reports. Of course they can then Publish that Report to any workspace they have access to do so, or to their "My Workspace". "Build" rights also allow the user to connect to the Dataset with Excel, (from Data, Get Data, From PowerBI) and build Pivot tables from the data in the Dataset.

see: Build permission for shared datasets - Power BI | Microsoft Docs

That gives the users access to the Dataset and the ability to build reports (without editing any existing reports), now how do we restrict the user to see just the data they're allowed to ?

The solution here is "Row Level Security" (RLS). You can set up RLS on a Dataset, this is basically a filter which is applied before any others on the data, which the user is unaware of and unable to change. Most commonly RLS uses the login name of the Power BI user (exposed as USERPRINCIPALNAME() in DAX) to restrict the data the user sees.  (That's effectively your WHERE clause).

You can read more about RLS here: Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

This also is useful... What is Row-Level Security (RLS) in Power BI??? - YouTube

So then , bringin this all together. "Viewers" in a Workspace are subject to RLS (Members and Contributors are not)  Viewers with build rights are still subjects to RLS, so when they connect with Power BI Desktop or Excel they only see the data they're allowed to see and can build Reports (and Spreadsheets) based on that data.

Using RSL and Dataset build rights together should accomplish all that you want.

Hope this helps

Stuart 

[andygoh]

Hi @Burningsuit 

Thanks a lot. This is very informative.

I've tried out build access on Power BI Desktop and it seems to be working fine.

However, am I unable to view the same dataset in the workspace on Power BI Service? 

As a contributor I can click on "..." next to the dataset to create report, I would like to be able to do the same as a viewer with build access in both Desktop and Service.

EDIT: It seems I can only view the dataset on the datasets tab, and not in the workspace itself. 

Thanks again.

[Burningsuit]

Hi @andygoh 

Yes, your experience is correct. Viewers with build rights don't get to see the Dataset in the Workspace, so can't build reports on it there. However they DO see the Dataset on the Datasets tab in Service, and can build reports on it from there, or via Dataset access in Power BI Desktop or Excel.

Hope this helps

Stuart