Power BI Row Level Security: Made Easy

Row Level Security is a powerful security mechanism that protects sensitive data by restricting data access for given users. Power BI Row Level Security restricts the data/records from a table based on the authorization context of the user that is logged in. This article will provide you with a high-level context into Row Level Security and will help you set it up in Power BI. But before getting started with Power BI Row Level Security, let’s discuss this robust BI platform in brief.

What is Power BI?

Power BI is a Business Intelligence (BI) tool and a Data Visualization platform offered by Microsoft that allows organizations to analyze business data and generate reports. Power BI comes with a set of built-in tools, apps, and connectors that can deeply delve and work with data to provide actionable insights, immersive visuals, and interactive reports.

What is Power BI Row Level Security?

• Row Level Security is a data governance capability of Power BI that restricts data based on the authorization context of the logged-in user.
• RLS forms an integral part of an organization’s data protection strategy as it implements appropriate data visibility for end-users. Power BI Row Level Security ensures that end users have visibility only into the data they are supposed to see.
• Inappropriate access management can lead to chaos and unforeseen circumstances in any organization.
• Row Level Security is a way to protect sensitive data by limiting visibility access to Power BI data and reports.
• Row Level Security Power BI is a horizontal limitation applied to rows within a table. Power BI applies filters on the data for the users with limited visibility based on the instructions outlined by the administrator.
• The filters apply data access limitations at the row level, and these filters can be defined within roles.

Defining Roles and Rules in Power BI Desktop

Power BI Desktop allows you to define roles and rules, and it also publishes the role definitions. Follow the below-mentioned steps to define security roles.

• Import your data model into your Power BI Desktop report.
• Click on “Manage roles” located under the “Modeling” tab.

• A new window will open and you’ll now be prompted to create a role, click on “Create”.

• Provide a name for the role and select the table you want to apply a DAX rule to.
• Enter the DAX expressions in the DAX Expression box and tick the same box to validate the expression.

• Save the settings.

Power BI Security uses single-directional filters by default. However, you can implement dynamic Power BI Row Level Security using username() or userprincipalname() DAX functions. After implementing dynamic Row Level Security at the serval level, you can enable bi-directional cross-filtering in Row Level Security by configuring the relationship and ticking the “Apply security filter in both directions” checkbox.

Validating Security Roles within Power BI

Now that you have already created the security roles, it’s time to validate the results within Power BI Desktop.

• Click on “View as” located under the “Modeling” tab.

• A new window will open where you can see the created roles.

• Select the role that you just created in the last section and click on “OK” to apply for that role. The report provides the data relevant to that role.

Now that you’ve validated the security roles, you can publish your report to the Power BI service.

Managing Security on your Model

Open the workspace where your report in the Power BI service is saved. Follow the below-mentioned steps to manage security on your data model.

• In Power BI service, hover over a Dataset and click on “More options”.

• Click on “Security”.
• The “Row Level Security” page will open. Here, you can add members to the security role you created in Power BI Desktop.

How to Add/Remove Members?

Power BI service allows you to add members to a role just by entering their emails or names or security groups. You can even add external members to a role, but you can’t add the security groups created in Power BI.

You can use the following groups to set up Row Level Security.

• Distribution Group
• Mail-enabled Group
• Security Group

Note: Office 365 groups cannot be added to any roles as they are not supported.

And, it is even easier to remove members, just click on the “X” symbol appearing next to their name.

Using Power BI Row Level Security with Workspaces

When a Power BI Desktop report is published to a new workspace experience in the Power BI service, the Row Level Security roles are applied to members who are assigned a Viewer role in the workspace. The Row Level Security in Power BI is applicable to the Viewers irrespective of the fact whether they have Build permissions granted or not.

To understand this better, let’s take an example of Viewers with Build permissions working with Analyze in Excel. Even though they’ve been given Build permissions, RLS protects their view of the data.

However, workspace members assigned with Admin, Member, or Contributor roles have edit permission for the dataset and, hence, RLS is not applicable to them. This brings us to a closure that you can assign people in a workspace with the Viewer role if you want RLS to be applicable to them.

Power BI Row Level Security Use Cases

Implementing Row Level Security in Power BI is a must if your dataset includes sensitive information (for example, information related to company financial accounts, customer information, or patient information). Below-mentioned is a list of the Row Level Security use cases seen across many organizations.

• Location-based RLS: When the company wants a user to only view information within a specific area or location (City/State/Country).
• Employee-based RLS: When the company wants an employee to only view information pertaining to his job responsibility. For example, a Store Manager should only view information related to the store’s business.
• Business Line-based RLS: When the company wants a user to only view information within a specific business line (Product/Service/Unit).
• Other RLS: Apart from the above-mentioned use cases, RLS can also be implemented with respect to Time (Month/Year), Customer (Specific Customer/Group of Customers), etc.

Static vs Dynamic Row Level Security

Talking about the types of Power BI Row Level Security, there are 2 broad categories of RLS that can be implemented into a Power BI report: Static RLS or Dynamic RLS. As the name suggests, Static RLS is easy to apply and requires a Power BI Developer to define the security logic within a PBIX file. Static Power BI RLS is easy to set up and implement and requires minimal IT involvement. However, it requires high maintenance, and manual implementation, and is not reusable.

Dynamic RLS, on the other hand, is a bit more complicated and requires the security logic to be defined within the PBIX file on the data model side using relationships. Setting up Dynamic Power BI RLS is time-consuming and requires dimension tables (users table/roles table). Hence, it requires a greater IT involvement with minimal maintenance. Dynamic RLS is an automated and reusable security model.

Static RLS Use Cases

• You can use Static RLS when data visibility needs to be limited for a specific group of users that need access to the same level of information.
• Static RLS is best suited for Power BI reports with less number of users and security roles.
• Use Static RLS when your Power BI report has a high level and straightforward security logic (for example, when the security logic is defined by the user, region, or company).
• As the name suggests, Static RLS is used when the user security requirements stay stagnant (or static). For example, consider a case where security groups and security group users are not changing frequently.
• Similar to the last case, implement Static RLS when the users are not added or removed frequently.

Dynamic RLS Use Cases

• You can use Dynamic RLS when data visibility needs to be limited for a specific group of users that need access to different levels of information.
• Dynamic RLS is best suited for Power BI reports with a greater number of users and security roles.
• Use Dynamic RLS when your Power BI report implements a complicated and complex security logic (for example, when the security logic is defined by job function, department, locality, or combination).
• As the name suggests, Dynamic RLS is used when the user security requirements are frequently changing (or are dynamic). For example, consider a case where security groups and security group users are changing frequently.
• Similar to the last case, implement Dynamic RLS when the users are added or removed frequently.

Limitations of Power BI Row Level Security

Below-mentioned is a list of the limitations of Power BI Row Level Security.

• Previously defined roles and rules in the Power BI service must be recreated in Power BI Desktop.
• You can apply Row Level Security only on the data models created with Power BI Desktop. You must convert the other format files into Power BI Desktop (PBIX) files to implement Power BI RLS.
• Power BI Row Level Security only supports ETL and DirectQuery connections. Live connections to Analysis Services are supported in the on-premises model.

Conclusion

• Power BI is all about Data Analytics, Data Visualization, and Business Intelligence. It is used by Data Professionals all over the world to examine data from multiple sources and create attractive Charts, Dashboards, and Reports according to user-specified data.
• However, it is quite necessary to protect sensitive Power BI data and one of the best practices to do so is by implementing Power BI Row Level Security (RLS). 
• Row Level Security ensures that end users only access what they are supposed to see. This blog introduced you to Power BI and took you through various aspects of Power BI Row Level Security.
• Power BI makes Business Analysis more efficient through intuitive, interactive, and easy-to-use services. Moreover, analyzing and visualizing your data by loading it from a Data Warehouse to Power BI can be cumbersome. This is where Hevo comes in.

Share your experience in the comments section below!