Implementing Row Level Security in Power BI

What is Row Level Security?

Row-level security (RLS) is a handy tool that helps developers and admins control which data each user can see in a Power BI report or dataset. Think of it as a way to set rules about who gets to see what, based on each row of data. If object-level security is about hiding entire columns or tables, RLS is about getting specific with individual rows.

Here’s how it works: the developer sets up some instructions in Power BI, creating rules that determine what data will be visible to various roles, or even to individual users.

Why should you care about RLS? It’s crucial for keeping your data safe. With RLS, you can make sure that everyone in your organization only sees the information they need and nothing more. This means sensitive data, like payroll or sales figures, stays protected. Without it, you risk exposing confidential information to people who shouldn’t have access to it.

Row level security usecases in real life

Business Line-based RLS: This type of RLS is perfect for companies wanting to grant users access to data specific to a particular business line, product, service, or department. For example, a company with multiple product lines can ensure that the sales team for Product A only sees data related to Product A, while the Product B team sees only their relevant data. This helps streamline data access and ensures users focus on the information directly impacting their work.

Location-based RLS: Companies can use this RLS to restrict data access based on geographical areas. For instance, a regional manager in Europe would only have access to data pertinent to European operations, while a manager in Asia would see data relevant to their region. This scenario also applies to retail chains, where store managers can only access data for their specific stores, or logistics companies that restrict data to regional hubs.

Employee-based RLS: This scenario limits data access based on job roles and responsibilities. For example, an HR manager may only have access to employee data within their department, while a finance manager can view financial reports for their team but not for the entire company. Additionally, a project manager could be restricted to view only the data related to projects they oversee, preventing unnecessary exposure to unrelated project details.

Other RLS Scenarios: Beyond these common use cases, RLS can be configured for a variety of other scenarios. For instance, time-based RLS can limit access to data for specific periods, such as quarterly financial reports accessible only to senior management during review periods. Customer-based RLS allows sales representatives to view information only for the clients they manage, enhancing customer data security. Furthermore, it can be applied to control access to sensitive data like compliance records, ensuring only authorized users in compliance roles can view such information.

Difference between static and dynamic row level security

Static Row Level Security:

Static Row-Level Security is when the access rules are predefined and do not change based on the user’s context or data. In this approach, the security filters are set up manually by the administrator and remain constant until explicitly modified.

How it works:

• Predefined Roles: Administrators create specific roles with fixed filters.
• Manual Assignment: Users are assigned to these roles based on their predefined access needs.
• Fixed Data Access: The data visibility is set and doesn’t change dynamically.

Pros:

• Simplicity in setup and management.
• Clear and predictable access controls.

Cons:

• Lacks flexibility for users with changing data needs.
• Requires manual updates for any changes in access requirements.

Dynamic Row Level Security:

Dynamic Row-Level Security adapts the data access rules based on the context or attributes of the user accessing the data. This approach uses more advanced techniques to filter data on-the-fly, making the access controls responsive to the user’s identity and other dynamic conditions.

How it works:

• User Context: Access filters are based on user attributes such as username, roles, or organizational hierarchy.
• Dynamic Filtering: Data access is determined at runtime, with filters applied dynamically based on the user’s context.
• Automated Assignment: Users’ access is automatically adjusted based on their current attributes and roles.

Example: A sales representative logs into Power BI and sees data only for the clients they manage. As they switch to a new client or region, their data view automatically updates to reflect the new context.

Pros:

• Highly flexible and adaptive to changing user needs.
• Reduces administrative overhead by automating access controls.

Cons:

• More complex to set up and manage.
• Requires accurate and up-to-date user attribute data.

This article focusses mainly on static RLS.

How Does RLS Work in Power BI?

RLS works by applying filters to the data based on the security roles defined within Power BI. When a user interacts with a Power BI report, the data is dynamically filtered to show only the relevant rows based on the user’s permissions.

How to Implement static RLS in Power BI Desktop?

Steps to configure row level security

Implementing RLS in Power BI Desktop involves defining security roles, creating DAX expressions to filter data, and applying them  to relevant datasets. Power BI Desktop provides a user-friendly interface to set up and manage RLS efficiently.

step 1 – open the Roles screen from the modeling tab

step 2 – add a role by pressing the ‘new’ button on the left hand side of the dialog

In the example above I have added a ‘Sales’ role, and added a filter on my StaffGroups entity. In the filter data pane I added a row and added a filter on the ‘group’ column with value ‘Sales’. This report shows performance of staff, and for anyone with the ‘Sales’ role I only want to show the performance of sales staff.

Testing RLS in Power BI Desktop

Before deploying RLS in a production environment, it is essential to test the security roles and filters in Power BI Desktop thoroughly. Testing helps in identifying and resolving any issues related to data filtering and access permissions. Testing roles in Power BI is fairly easy and can be done in two clicks.

Back in the Modeling tab, click on the view as button in the ribbon. A dialog appears, and you are able to select your created role(s) from there. After pressing ‘Ok’ your report is filtered to only contain the data as specified in your row level role!

By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filtering with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. Note that if a table takes part in multiple bi-directional relationships you can only select this option for one of those relationships.

Now that you have created and tested your roles, it’s time to assign users to it. Unfortunately you can’t assign users to a role within Power BI Desktop.

Assigning users to your roles in Power BI service

Upload your report to the Power BI service and navigate to the workspace the report is is.

Step 1 – On the semantic model of your report → select the vertical ellipses (…) (More options) → Select Security.

Step 2 – Assign users to your role(s)

Add the users, and that’s it. Whenever a user opens the report, it will filter all data based on the configuration you made in the role.

Please note: When a report user is assigned to multiple roles, RLS filters become additive.

Collaboration and Sharing with RLS in Power BI

RLS facilitates secure collaboration and sharing of reports within Power BI by ensuring that each user only sees the relevant data based on their permissions. This feature enables organizations to collaborate on sensitive data without compromising data security standards.