How to Implement Row Level Security in Power BI

Published on Oct 14,2019 121 Views

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

Restricting access to any data to a user is one of the key techniques used when it comes to the security aspect of any Data. In this article, we will understand how to implement Row Level Security in Power BI:

 

What is Row Level Security in Power BI?

Row-level security in  Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. Be aware that in the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn’t restrict this data access.

Power BI Reports

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. Previously, you were only able to implement RLS within on-premises Analysis Services models outside of Power BI. For Analysis Services live connections, you configure Row-level security on the on-premises model. The security option will not show up for live connection datasets.

 

Define Roles and Rules in Power BI Desktop

You can define roles and rules within the Power BI Desktop. When you publish to Power BI, it also publishes the role definitions. It is an important aspect of the Row Level Security in Power BI. To define security roles, follow these steps.

  1. Import data into your Power BI Desktop report, or configure a DirectQuery connection.

    Note: You can’t define roles within Power BI Desktop for Analysis Services live connections. You need to do that within the Analysis Services model.

  2. Select the Modeling tab.

  3. Select Manage Roles.

    Row Level Security in Power BI

  4. Select Create.create- Row Level Security in Power BI
  5. Provide a name for the role.

  6. Select the table that you want to apply a DAX rule.

  7. Enter the DAX expressions. This expression should return a true or false. For example: [Entity ID] = “Value”.

    Note: You can use the username() within this expression. Be aware that the username() has the format of DOMAINusername within the Power BI Desktop. Within the Power BI service and Power BI Report Server, it’s in the format of the user’s User Principal Name (UPN). Alternatively, you can use userprincipalname(), which always returns the user in the format of their user principal name, username@contoso.com.

    manage- Row Level Security in Power BI

  8. After you have created the DAX expression, you can select the check above the expression box to validate the expression.

    DAX Expression

    Note: In this expression box, you use commas to separate DAX function arguments even if you’re using a locale that normally uses semicolon separators (e.g. French or German).

  9. Select Save.

You can’t assign users to a role within Power BI Desktop. You assign them in the Power BI service. You can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured.

By default, row-level security filtering uses single-directional filters, regardless of whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. You should check this box when you’ve also implemented dynamic row-level security at the server level, where row-level security is based on the user name or login ID.

Apply Security Filter

Validate the roles within Power BI Desktop

After you’ve created your roles, test the results of the roles within Power BI Desktop.

  1. Select View As Roles.In View as roles, you see the roles you’ve created.
  2. Select a role you created > OK to apply that role. The report renders the data relevant for that role.

  3. You can also select Other user and supply a given user. It’s best to supply the User Principal Name (UPN) as that’s what the Power BI service and Power BI Report Server use.

  4. Select OK and the report renders based on what that user can see.

Within Power BI Desktop, Other users only displays different results if you’re using dynamic security based on your DAX expressions.

 

Manage security on your model

To manage security on your data model, you will want to do the following.

  1. Select the ellipse (…) for a dataset.

  2. Select Security.

    Apply security filter in both directions

This will take you to the RLS page for you to add members to a role you created in Power BI Desktop. Only the owners of the dataset will see Security available. If the dataset is in a Group, only Administrators of the group will see the security option.

You can only create or modify roles within Power BI Desktop.

 

Working with Members

Add Members

You can add a member to the role by typing in the email address, or name, of the user, security group or distribution list you want to add. You cannot add Groups created within Power BI. You can add members external to your organization.

Add a member

You can also see how many members are part of the role by the number in parenthesis next to the role name, or next to Members.

Members in role - Row Level Security in Power BI

Remove members

You can remove members by selecting the X next to their name.

Remove member

 

Validating the Role Within the Power BI service

You can validate that the role you defined is working correctly by testing the role.

  1. Select the ellipsis (…) next to the role.

  2. Select Test data as role

Test as role

You will then see reports that are available for this role. Dashboards are not presented in this view. In the blue bar above, you will see what is being applied.

Now viewing as - Row Level Security in Power BI

You can test other roles, or combination of roles, by selecting Now viewing as.

Test other roles -Row Level Security in Power BI

You can choose to view data as a specific person, or you can select a combination of available roles to validate they are working.

To return to normal viewing, select Back to Row-Level Security.

 

Using the username() or userprincipalname() DAX function

You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You can use them within expressions in Power BI Desktop. When you publish your model, it will be used within the Power BI service.

Within Power BI Desktop, username() will return a user in the format of DOMAINUser and userprincipalname() will return a user in the format of user@contoso.com.

Within the Power BI service, username() and userprincipalname() will both return the user’s User Principal Name (UPN). This looks similar to an email address.

 

Using RLS with app workspaces in Power BI

If you publish your Power BI Desktop report to an app workspace within the Power BI service, the roles will be applied to read-only members. You will need to indicate that members can only view Power BI content within the app workspace settings.

Warning: If you have configured the app workspace so that members have edit permissions, the RLS roles will not be applied to them. Users will be able to see all of the data.

Group settings

 

Limitations Row Level Security in Power BI

Following is a list of the current limitations for row-level security on cloud models.

  • If you previously defined roles and rules in the Power BI service, you must re-create them in Power BI Desktop.

  • You can define RLS only on the datasets created with Power BI Desktop. If you want to enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first. Learn more

  • Only ETL and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.

  • Cortana is not supported by RLS at this time.

 

With this, we come to an end of this Row Level Security in Power BI. I hope you got an understanding of row-level security.

If you wish to learn Power BI and build a career in data visualization or BI, then check out our Power BI Training Certification which comes with instructor-led live training and real-life project experience. This training will help you understand Power BI in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of “Row Level Security in Power BI” and we will get back to you.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.