Dynamic Row-Level Security (RLS) Implementation in Power BI (Part 1)

[cpatra]

This article is co-authored by Chinmaya Patra, Pradip VS and Vivienne Vereen Architects at Microsoft

Why Choose Dynamic RLS?

Static RLS becomes unmanageable with thousands of roles, such as creating a payroll report for 10,000 users. Dynamic RLS solves this by handling such scenarios more efficiently. This blog explores managing dynamic row-level security (DRLS) with multi-dimensional restrictions in Power BI. We’ll walk through a real-world customer scenario and demonstrate a sample model to illustrate the solution's practical application.

Current Challenges and Customer Expectations

Customers face challenges with data security, including inconsistent access, weak security enforcement, and high administrative effort due to separate management of Row-Level Security (RLS) across multiple models. They expect a unified, automated access control system. Implementing DRLS will dynamically manage data access based on user roles and hierarchies, improving governance, efficiency, and scalability in Power BI without manual intervention.

They need to define roles and profiles by Business Unit (BU) and Area/Department to control data access effectively.

How to implement Dynamic Row-Level Security (DRLS) in Power BI Desktop

Approach 1:

To address the problem, we need to ensure data restriction according to the defined hierarchy. Using a sample star schema, we highlight the DRLS capability. Our data model includes two key dimension tables for data security: DimStore and DimBrand. DimStore includes entries for business units like HQ, India, Japan, and Singapore. DimBrand represents departments or areas, such as Financial, Commercial, and Marketing.

Model Image:

We are implementing RLS based on user restrictions for both dimensions. This means that access to data will be controlled according to the user's permissions for specific stores and brands. These dimension tables are connected to our fact table through StoreKey and BrandKey. This setup allows us to filter the fact table data based on the user's access rights to specific stores and departments, ensuring that each user only sees the data they are authorized to view.

Hierarchy as per customer’s problem:

Dynamic row-level security ties security settings to user account information in the data source. For example, if a person belongs to all store Singapore Finance data, they will be restricted to view Singapore Store only and Finance brand only. This can be achieved in Power BI using the DAX functions UserName () or UserPrincipalName (). Here, I'll demonstrate how to set up dynamic row-level security using the UserPrincipalName () function in Power BI.

Before implementing the security, we need to know who can access what or what role we can assign to restrict the data and what are the possible access roles.

Role Library

For the above scenario, we have created a role library to restrict the data for the users as below.

Master Role Table:

This role library has a role name and its ID, and the store key and the brand key, with possible combinations of these two dimensions to restrict the data, which is a master table to understand the required role and its filter/relationship with the respective dimensions, here the DimBrand and DimStore table.

• Role Library: Defines the roles and their restrictions.
• Role and Restriction Table: Helps create tables with restriction roles and user information. This is also a master table with roles in the model and its data restriction.

After setting up these two tables, we need to have one master user table with assigned roles. You can plan your ETL to do this, but here I have collected the master user list along with their role restrictions. This Master user list in the model is named as “Dynamic RLS User Table”.

Dynamic RLS User Table:

Now you have the main master tables to populate the required table which will work as per DRLS. Here I will name these two tables Brand Access Per Role and Store Access Per Role. The Brand Access Per Role table will be created while merging or doing ETL between the “Dynamic RLS User Table” and “Roles and Restriction” table on role ID. We created the Brand Access Per Role table which will be used in our Model.

Let's decide on cardinality of relationship between User Table and Dimensional Table:

We have established a relationship between the Brand Access Per Role table and the Store table using the Store Key and similarly for the Brand Key. To ensure proper data flow and security, the relationship between the DimStore table and the Store Access Per Role table should be set to bi-directional, with security filters enabled on both sides.

Store Dim Relationships

Brand Dim Relationships

The relationship between the DimBrand table and the Brand Access Per Role table is currently a bi-directional M-1 relationship, like the relationship with DimStore. We considered changing this to a M-M relationship with a single direction, where the Brand Access Per Role table would filter the DimBrand table. The proposed setup is shown below:

Let's conduct a performance comparison to determine the best solution:

We can start with comparing the M-1 bi-directional relationship and the M-M single directional relationship between the Brand Access Per Role (User Table with restriction) and Dim Brand (Dimensional Table) to understand why we chose the M-1 bi-directional relationship.

Performance Comparison

Hence we choosed to go with M-1 bi directional relationship between the user with role and dimension table.

Lets Test if the security is working fine

After this, we can proceed with implementing the DRLS in the manage role section and create a role named User Access. Then, add restrictions on the Brand Access Per Role Table and Store Access Per Role table and use USERPRINCIPALNAME () DAX on the Email column of the table. This will restrict the table with the access provisioned for the only user who signs in to Power BI.

Then we can test as role in the report to see if this is working fine for user “[StoreBrand@demo.onmicrosoft.com] 

How to implement the same in Direct Lake Semantic model

The above model can be implemented using Direct Lake Semantic model with same configuration and relationship between tables.

Let’s consider we have populated required tables to a Final Lakehouse and then we created a direct lake semantic model as below

Solving Data Access Issues in Direct Lake Semantic Models with Fixed Identity

• Managing data access in a Direct Lake Semantic Model within Microsoft Fabric can be challenging when using default Single Sign-On (SSO). End users often face issues accessing report data because the underlying data source (Lakehouse or Warehouse) is secured and not directly accessible. This document explains the problem and how implementing a Fixed Identity can resolve it, ensuring consistent and secure data access.
• Problem with Default SSO in Direct Lake Semantic Models: The diagram below highlights the data access issues that arise when using a Direct Lake Semantic Model with default SSO. Let’s break down the problem step-by-step

• Data is stored in a Lakehouse or Data Warehouse within OneLake, with restricted direct access for security and governance. End users typically lack permission to read from Lakehouse.

• A Direct Lake Semantic Model, based on the Lakehouse table schema, structures data for Power BI reports and uses user credentials (via SSO) to access Lakehouse. However, since end users don't have direct read access, the semantic model can't retrieve data, resulting in empty reports.

• Granting direct access to Lakehouse introduces risks:
  1. Unauthorized data access: Users might access sensitive data.
  2. Data leakage: Potential for data to be shared externally.
  3. Complex permission management: Difficult to manage across many users.
  4. Inconsistent security policies: Varying access levels lead to governance issues.

Root Cause

The problem occurs because the semantic model depends on the user’s identity (via SSO) to retrieve data. If the user lacks permission to read Lakehouse, the report will fail to display data.

Solution: Implementing Fixed Identity in Direct Lake Semantic Models

The diagram below shows how using a Fixed Identity in a Direct Lake Semantic Model solves the access issue while maintaining security and governance.

Components and Data Flow

1. Data Source (Lakehouse or Warehouse): Data remains stored in a secured Lakehouse or Data Warehouse within One Lake. End users do not need direct access to the data source.
2. Direct Lake Semantic Model: The semantic model defines the data structure and schema for Power BI reports. It connects to Lakehouse using Fixed Identity rather than user credentials.
3. Fixed Identity: A Fixed Identity is configured in the Direct Lake Semantic Model settings. The model uses this predefined identity to authenticate and retrieve data from Lakehouse. This removes the need to provide direct read access to Lakehouse for each user.
4. Power BI Report: The report is connected to the Direct Lake Semantic Model. Since the model retrieves data using Fixed Identity, users can now see report data without needing direct access to Lakehouse.
5. End User: End users can securely view report data through Power BI. They don’t need direct access to Lakehouse, improving data security and governance.

How This Solution Works

Fixed Identity acts as a secure bridge between Lakehouse and the semantic model. The semantic model retrieves data on behalf of the user using Fixed Identity. This ensures consistent security and governance without exposing the data source to individual users.

Benefits of Using Fixed Identity

• No Direct Access Required: Users can view data without needing direct permission to Lakehouse.
• Centralized Control: Data access is managed through a single identity, simplifying permission management.
• Improved Security: Reduces the risk of unauthorized access and data leaks.
• Seamless User Experience: Users can access data in reports without permission issues.

Steps to implement Fixed Identity:

1. Go to the workspace and then select 3 dots for the direct lake semantic model and then open Semantic model settings page.
2. 
3. Then in the setting page we can see the Gateway and cloud connections. Under this section we can see a connection mapped to default SSO. Here we need to create a new connection as a fixed identity for the semantic model.
4. 
5. Select any authentication suitable for your business, then create the connection from the manage connection and gateway window.
6. 
7. After creating this, return to the semantic model settings page and map the new connection under the gateway and cloud connection section.
8. This will serve as a fixed connection to the source, allowing the end user to access data through a fixed identity instead of accessing Lakehouse.

As we have completed the understanding of how to implment the DRLS effectiviely, Lets understand what are the advantages and disadvantages of this approach.

Advantages of the above approach 1:

1. Scalability: This approach allows for easy scalability as it uses dynamic row-level security (DRLS) to handle multiple users and roles efficiently.
2. Flexibility: It provides flexibility in managing data access by using account user information to filter data dynamically.
3. Security: Ensures that data is securely shared with the right users based on their roles and permissions.
4. Efficiency: Reduces the need for manual or static configurations, making it easier to manage and maintain.
5. Customization: Allows customized data access based on specific business units and departments.

Disadvantages of the above approach 1:

1. Complexity: Implementing DRLS can be complex and may require a thorough understanding of Power BI and DAX functions.
2. Maintenance: Requires ongoing maintenance to ensure that roles and permissions are up-to-date and accurately reflect the organization's structure.
3. Performance: May impact performance if not implemented correctly, especially with large datasets and multiple users.
4. Dependency: Relies on accurate user account information and proper configuration of roles and permissions.

 In Part 2 we will discuss more about other approaches that can be used for the above RLS implementation where the model/ design will be more specific to scenario and not applicable for every modeling situation.

If you have experience with similar initiatives and have helped customers unlock potential using DRLS in Power BI, please share your insights in the comments.

@cpatra , @PradipVS and @VivienneVereen Architects in Microsoft