Reporting & Visualisation

Power BI Row Level Security

Intro

Row Level Security is a technique to have a user see only the part of the data in the report. The report is automatic filtered based on who is viewing the data.

With Row Level Security (RLS) you can only limit the rows a specific user can see, not the columns.

There are basically two ways to setup Row Level Security (RLS). Both methods require a field in the dataset that can be used to filter the data for the specific group or user.

Static Row Level Security
Easiest to set up. Best when there is only a small number of different and distinct groups to set up. This method requires that the data contains a field which can filtered to create the subset of data for a specific group.

Dynamic Row Level Security
More complicated to set up. Best when there are many groups to set up when the groups frequently change over time. This method requires that the data contains a field with the username that can be used to filter the data.
The best way is to have a table with the ID’s of the employees with the logins they use for outlook. These are the credentials in the format of an email address an might look like: John.Doe@sogeti.com. It is best is to have this table pushed from a source system, if possible, to avoid the burden of having to maintain this table yourself.

Close the backdoor

Before you can start on working with Row Level Security you have to make sure the users do not have access to the underlying data source itself. To accomplish this there are two things that need to be considered.

Limit access to the data source itself
Do not give the end users access rights to the data source and use different credentials to connect to the source. Use a function account or service account to connect to the data if necessary.

Limit access to the Workgroup
When users are able to edit the report in the workspace they can alter the setting for the row level security and thereby disable the Row level security. This is especially important if the report contains sensitive information like details on individual customers or on individual employees.

Set the workspace to the following settings for a classic workspace:
• Private – Only approved members can see what’s inside
• Members can only view Power BI Content• Private – Only approved members can see what’s inside
• Members can only view Power BI Content

Set the workspace to the following settings for a new style workspace:
• Assign users that require the RLS to the View role
• Members of the other roles can bypass the RLS

Or publish the workspace to an app.

Create a new Workspace if necessary if the settings conflict with the requirements for other reports in the Workspace. Review the setting for each workspace type as this is important to prevent users from editing the report and alter the setting for the row level security.

Static Row Level Security

1. Prepare the model for Static Row Level Security

To set up Static Row level security you first need to select the field you can filter the data on. This table must have a relation to the other tables that hold the data that has to be filtered.

2. Create Roles

Select from the Modelling ribbon under security: “Manage Roles”

 

Manage Roles

Create at least two roles. One role that will be used for Administrators. People assigned to this role are unaffected by the Role Level Security. This Role can be used to assign to a select group of functional managers and administrators if needed.

Add additional roles for every group that needs a different view of the dataset. Navigate to the table with the field that needs to be filtered and enter the DAX expression to filter on the appropriate value:

[Region] = "International"
Add DAX filter to role

Repeat these steps for every Role that needs to be filtered.

3. Testing the static filter

To test the filter in Power BI Desktop select “View as Roles” and select only the role that you want to view.

Vies as Role for static RLS

4. Publish to powerbi.com

When the Row Level security works as expected, you need to publish report to powerbi.com

The last step is to assign users to the roles that just have been created. Go to the dataset (not the report!) and select the menu from the 3 dots. Klick on Security.

Security settings for dataset

Assign the groups or users to the roles. Avoid assigning rights to individual users but use Groups whenever possible. This will save you from the hassle to haven to maintain the users yourself. It is considered a good practice not to use mailing lists or distribution lists as for many organisations these groups do not comply to the regulation in order to be used to give access to for sensitive data.

Dynamic Row Level Security

1. Prepare the data model for dynamic RLS

Setting up Dynamic Row Level Security requires some preparing. First you import a table that includes the names of the users that will be granted row level bases access to the data. This table needs to be connected to the table holding the data. This can be through intermediate tables.

Datamodel for users related to Fact table

In this example the table Users contains the list of users and the table CountryUsers contains the countries a specific user is allowed to see. The tables Country and FactInternetSales hold the Country dimension and fact table respectively.

Make sure you specify to have the filtering direction to Both where the filter needs to be propagated through a 1 to many relation going from the many to one side (from a fact table to a dimension table). Also set the checkbox for “Apply security filter in both directions”. In our example that would be the relation between the tables Countey and CountryUser

Edit relationship

To present a cleaner model to the users you can mark the tables involved in the mapping to the users accounts as “Hide in Report View”. In our example that would be the tables CountryUsers and Users

2. Create Roles

Select from the Modelling ribbon under security: “Manage Roles”

Manage Roles

Create at least two roles. One role that will be used for Administrators. People assigned to this role are unaffected by the Role Level Security. This Role can be used to assign to a select group of functional managers and administrators if needed.

The second group will be used for the users that should use the Role Level Security. In our example this is the group RLSUsers. Navigate to the table with the users and set the following DAX expression to filter on the current user:

[AccountName] = userprincipalname()
Add DAX filter to role

3. Testing the dynamic filter

To test the filter in Power BI Desktop select “View as Roles”. Specify the user you will test with as “Other user” and select the role that hold the dynamic filer.

View as Role for dynamc RLS

The data should show only the records that the user is entitled to see.

To check if the user is filtered correctly you can add a visual of the type table that holds only the field that the filter is applied to. In our Example that would be Users[AccountName]. You can see if the selected user is the only user in the selection. You can do the same for any of the intermediate table between the Fact table and the table with the dynamic filter to assure the filter will correctly flow through the relations.

In our example the visuals would look like this:

Example visual for testing dynamic RLS

4. Publish to powerbi.com

When the Row Level security works as expected, you need to publish report to powerbi.com

The last step is to assign users to the roles we created. Go to the dataset (not the report!) and select the dots. Click on Security.

Security settings for dataset

Assign the groups or users to the roles. Avoid assigning rights to individual users but use Groups whenever possible. This will save you from the hassle to haven to maintain the users yourself. It is considered a good practice not to use mailing lists or distribution lists as for many organisations these groups do not comply to the regulation in order to be used to give access to for sensitive data.

Assign users to role

Be aware that working with Power BI Desktop and in de Power BI Service (app.powerbi.com) will show a different list result when looking for accounts and groups.

For more information on RLS : https://docs.microsoft.com/en-us/power-bi/service-admin-rls

2 gedachten over “Power BI Row Level Security

  1. Nice summary of this functionality Lex! Couple of remarks though:

    I think that where you use AccountName you actually mean UserPrincipleName if I look at the fields in your Users table in your model. So not:

    [AccountName] = userprincipalname()

    But:

    [UserPrincipalName] = userprincipalname()

    Also later there is one more time Account name where it should be UserPrincipalName I think.

    Furthermore, are you sure you have to assign users to the roles in the Power BI Service in the case of Dynamic RLS? Because I think this will automatically work correctly because of the use of the function of userprinciplename(). Maybe this has changed over time though as my use of RLS has been a while ago by now.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *