Power BI Dataflows & Object Level Security (table level)
Power BI Dataflow security is managed at the workspace level, this means when you add the user to the workspace he will be able to see all the Dataflows inside the workspace and every table within.
This blog post aims to show you a workaround to achieve table level permission in Dataflows by leveraging:
First you need to create a workspace and link it to a storage account, see the link for more details.
Now every dataflow you create on this workspace will write its data to the linked storage account using the CDM format:
If you want to only allow the end user to see & connect to a subset of tables (ex: Sales and Store), you can do the following:
Copy the url of the “model.json”, you can use Azure Storage Explorer to easily connect to the storage account and copy the url:
The url should be like this:
https://yourstorage.blob.core.windows.net/powerbi/Workspace/Contoso/model.json
Replace “.blob” for “.dfs.”:
https://yourstorage.dfs.core.windows.net/powerbi/Workspace/Contoso/model.json
Create a new workspace in Power BI and add the end user as viewer:
| PS – The end user is inside the security group “RRMSFT | Power Users” |
Create a new Dataflow, select the option “Attach a Common Data Model folder” and copy the url from above:
You should end with an attached dataflow on the workspace like this:
Using the end user credentials in Power BI Desktop, if you try to connect to the Dataflow you will get the following error:
“Power BI can’t access your organization’s Azure Data Lake Storage Gen2 account. Please ask your administrator to restore access, and try again”
The error its because the connection to the dataflow storage is made with the end user credentials and he doesn’t have permissions on the data lake storage account.
If we assign the following permissions on the data lake:
- model.json – Read + Execute
- All tables you want to give access to, ex: Sales & Store) – Read + Execute on folder & children
- All parent folders up to the container- Execute
Now if you go back to Power BI Desktop and try to connect to the Dataflow you will notice that the Dataflow tables are listed and the end user can see data for both Store & Sales:
But if he tries to connect to any other table (Ex: Customer) it will fail with a Forbidden error:
Its not a perfect solution because the user will still be able to view all the available tables but could be useful versus the alternative of duplicating the dataflow data on multiple workspaces to achieve the same goal.