Restricted data access and Row Level Security (RLS) in Power BI

In a previous article we discussed the main aspects of Power BI architecture and security. As organisations adopt Power BI for implementing enterprise level reporting solutions, questions will arise about data security and restricted data access.

For example, detailed payroll information can only be shared among HR users, whereas finance users may only be allowed to see summarised payroll information. Individual employees themselves will have zero visibility of an individual’s salaries and wages. In another example, an organisation’s regional sales data should be freely available to all the employees located in that region, but to no-one else outside the region.

In these scenarios, Power BI content should only be made available to specific authorised user groups or individuals. This can be achieved by leveraging the Row Level Security (RLS) feature in Power BI. This article explains few techniques that can be implemented to restrict data access in your Power BI model.

Front-tier or middle tier?

Depending on the connection type, RLS can be implemented either in the Power BI data model itself or backend data source. Whenever you use a live connection to a Microsoft Analysis Services cube, setting up a security option is not available in Power BI. Therefore, RLS must be implemented in the MSAS cube (middle-tier). Middle-Tire MSAS cube could either be a multidimensional or tabular design.

If the security model is embedded into the Power BI model (front-tier), there is no data protection at the data source level. Therefore, any other reporting tool that consumes the same data source must duplicate the RLS logic in its presentation layer. This may add maintenance overheads and extra complexity to the solution design.

Row Level Security (RLS) in Power BI

Row Level Security is the ability to filter report content based on a user’s role. Depending on the access requirements, DAX filters need to be implemented to restrict data at the row or record level.   There are two main options to set up RLS in Power BI.

Option 1: Static user role for a specific access level

In this method, a static user role is defined with appropriate DAX filters that restrict user access for a specific region or segment.

This example is based on AdventureWorksDW2017 and restricts access using Sales Territory Region name in the data model.

These are the steps involved:

1. Create a Static User Role for a specific Sales Region through Power BI Desktop using “Manage Roles” under Modelling tab.
   
   
   
   

2. Create a new Role called “SalesTeamAU”
   
   
   
   

3. Create a DAX filter as [SalesTerritoryRegion] = “Australia”. This filter will restrict access to Australian Sales Region for allocated users in the role.
   
   
   
   

4. Publish the report to Power BI portal. (In the latest version of Power BI Desktop, “assigning a user to a role” has been moved to Power BI Portal)

5. Assign all users in Australian Sales Team to “SalesTeamAU” user role in Power BI Service. Similarly, all the other Sales Territory Regions need their own user roles to manage user access.
   
   

Option 2: Creating a Dynamic security user role to allocate permission to individual users or groups

The below example shows how to restrict access to sales data for regional users by setting up a dynamic user group with DAX filters.     

Compared to the static user role method discussed earlier, dynamic security role simplifies the model design by having only one user role to manage user access. However, we must maintain a separate entity in the data model to map User IDs to their permitted business segments (in this case, Sales Territory Key against User ID).

1. Create a separate data entity in the Power BI model to maintain user mappings. In this example, we have created a table called “UserAccess” with UserId & SalesTeritoryKey columns.
   
   
   
   

2. Create a relationship between UserAccess Entity and the associated dimension table. In this example, user permission is setup at the Sales Territory and linkage is between, UserAccess and DimSalesTerritory table. Make sure Cross Filter Direction is set to “Both”.
   
   
   
   

3. Enable the security filtering for this relationship.
   
   
   
   
4. Create a User Role and set a filter in UserAcccess table as “[UserId] = USERPRINCIPALNAME()”. (USERPRINCIPALNAME() DAX function returns the email address of the logged on user)
   
   This DAX expression filters allocated SaleTerritoryKey values for the current user and hide the rest.
   
   
   
   

5. Publish the model to Power BI services.
   
   
6. Assign all report users to this Role.
   
   
   
   

Choosing the most appropriate Row Level Security approach for your organisation is a design architecture decision.

The Dynamic security role enables more flexibility to the overall model design. Maintenance of the users are managed in the User access entity and requires minimal model changes for future user access amendments. Assigning an active directory user group to the dynamic user role will also reduce interaction with Power BI model security.

In contrast, Static User group adds a bit more maintenance overheads to the model. New security roles must be defined through Power BI desktop and requires republishing the model.