Power BI — Securing Data Across Multiple Workspaces and Facilitating Self-Serve
This post discusses how to protect data with Row Level Security in Power BI whilst allowing contributor or higher access to a workspace.
When a fellow colleague politely questioned if what I had written in a solution design document was correct (given it “conflicted” with the Microsoft Documentation), I was a little surprised.
Surely “it works”, I thought, I've done it before. but why doesn't the Microsoft documentation indicate it does!?!?!
Navigating the murky waters of Power BI Security can be confusing for newcomers at the best of times. But what options are available to organisations that wish to not only secure their data using Row Level Security (RLS), but allow some users “build permissions” on a central dataset all whilst:
Separating out Small-users bases in departmental/multiple workspaces
and
Avoiding having multiple datasets in every workspace
not too much to ask for? right..?
Well, let's take the following Example:
- The Production (PROD) workspace contains, reports and a dataset with defined RLS Roles.
- Reports are shared to each of the department’s workspaces. (With RLS)
- Admins, Members and Contributors within the department’s workspace are granted build permission to the dataset residing within the production workspace. (With RLS)
Will Row level security still function?
Good Question!
If you go by the official Microsoft documentation:
the RLS roles are applied to members who are assigned to the Viewer role in the workspace. Even if Viewers are given Build permissions to the dataset, RLS still applies. For example, if Viewers with Build permissions use Analyze in Excel, their view of the data will be protected by RLS. Workspace members assigned Admin, Member, or Contributor have edit permission for the dataset and, therefore, RLS doesn’t apply to them.
You would assume that the above example is in fact not possible, the department Admins, Members and Contributors would see all the data! However, think again! The above only corresponds to the workspace the dataset resides in if the user is an admin, member or contributor of a workspace where a report or dataset has been shared to, then RLS does in fact apply and therefore data will be protected via RLS.
So, time to clear the way…..
What is needed?
To ensure RLS still applies to Admins, Members and contributors of a workspace. (Such as the department’s workspace in the above example)
- Read-Only users will need the following: Viewer Role assigned to the department’s workspace, No role is required for the Production workspace but read permissions are required on the central dataset (within the production workspace). Additionally, the user will need to be assigned a Row Level Security Role.
- Users who wish to create reports within their department’s workspaces (Admin, Member, Contributors) will require the following: Admin, Member, Contributor role assigned to their department's workspace, No role is required for the Production workspace but Read, Build permissions are required on the central dataset (within the production workspace). Re-share permissions on the dataset can also be granted. Additionally, the user will need to be assigned a Row Level Security Role.
- Additionally, the following tenant setting will need to be enabled for either the entire organisation or for the users who are building reports! (Control the use of datasets across workspaces)
The end result?
RLS will not only still apply to the reports within the department's workspace for users with the Admin, Member or Contributor role but also when they are building reports (using the central dataset) within the department's workspace RLS will still apply!!
Score!
….and before you ask. Why not just use Apps?
In scenarios where department’s/teams within the organisation still require the ability to create reports/self-serve whilst still keeping the data secured but at the same time avoiding duplication of datasets…. then the above scenario is still the best option!
About the Author:
Marco is a Data Visualisation Consultant at Version 1, providing data solutions and visualisations expertise to organisations. With a Power BI focus, Marco helps to ensure customers get the best value from their data.
