3 Use Cases of Row Level Security (RLS)

Recently I learned about three use cases of RLS and I couldn’t journal about it. I have written a post on RLS which contains the basic information that you need to understand the concept. In this post, let’s go ahead with understanding those three use cases of RLS.

Three Use Cases of RLS

Row-level security (RLS) with Power BI can be used to restrict the data shown to an end user in a report. Filters restrict the data access at the row level, and you can define filters within roles.

Use Case 1: Simplified

Use Case 2: Bidirectional

Use Case 3: Hierarchical

Before that, refer to this post to understand the basics.

Use Case 1: Simplified

1. Dataset with Email ID Column: Use a dataset where each row has an email ID column.

2. Import to Power BI Desktop: Ensure the dataset has a Onerelationship.

3. Create Visualization: Include all columns in a visualization.

4. Modeling and Roles:

• Go to Modeling > Manage Roles.
• Add “Viewer” as a role. “Others” will be created automatically.
• Under Tables, select the table with the email ID.
• Click Filter and select the [Email] column.
• In the Table filter DAX Expression field, write [Email] = USERPRINCIPALNAME().

5. Create a Measure:

• Right-click on the dataset table and click New Measure.
• Enter Userprinciple = USERPRINCIPALNAME().

6. Card Visualization: Create a card visualization with the Userprinciple measure.

7. Validate: Go to View as under the Modeling tab, select the viewer role, and type the email address you want to view as.

8. Save and Publish: Save your report and republish it to Power BI, ensuring you select the “Enable Row Level Security” checkbox when sharing.

Use Case 2: Bidirectional

In this scenario, we have many-to-many relationships in our data model and want to use RLS. Let’s imagine you have sales data of fictional companies from different regions in Germany and a list of persons who are responsible for specific regions. The person should only see the data of the region they are responsible for.

Scenario Setup: Manage many-to-many relationships and restrict data based on user roles.

• Example: Sales data of fictional companies in different German regions, with each person responsible for specific regions.

Import Dataset:

• Person Table: Email, Name, PersonID.
• Role Table: PersonID, Region.
• Shipping Table: Category, City, Country, Order Date, Order ID, Profit, Quantity, Region.

Relationships:

• Automatically create the relationship between Person[PersonID] and Role[PersonID].
• Create a custom table Region with four regions: North, South, East, West.
• Define relationships:
  • Region[Region] to Role[Region] (1).
  • Region[Region] to Shipping[Region] (1).

Map Visualization: Create a map visual using State and Profit columns.Modeling and Roles:

• Go to Modeling > Manage Roles and add a Viewer role.
• Select the Person Table and add [Email] = USERPRINCIPALNAME().

Two Methods to Filter:

1. In this setting, the DAX filter on the email in Person table will not filter the shipping table because of the direction of their relationship. So to make this work, we can use two methods.
2. The First Method is the Bidirectional concept. This method is not recommended because of performance limitations.
3. We will change the direction of the cross-filter relationship between the Role and Region Table to both directional and also check the option for “apply security filter in both directions”.
4. Validate the role.
5. The second method is to use a DAX. First, Change the cross-filtering direction of the Region and Role to single.
6. Now we will use DAX to filter the data in the Region table which will automatically filter the shipping table as well. Let use a DAX to replace in place of Person table.
   • [Region] IN SELECTCOLUMNS(FITLER(Role,RELATED(Person[Email]) = USERPRINCIPLANAME()), “REGION”, [Region])
   • Add the above DAX for the Region table.
   • Validate the report, the filter will work just like how biredectional was set up.

Use Case 3: Hierarchical

In this scenario, we have organisation hierarchy and employees need to be authoirsed only to thier data and to the data of perople whom they are managing. So lets imagine you hava a data and want employees to see only thier data and management can access thier data and data of the employees under them.

1. Import the data set
2. Imagine you have two tables as below:
   • Employees -> Email, Id, MAnager Id, Name
   • Transactions -> Date, Employee, Sales Amount
3. We are going to use PATH() function to obtain for an employee the entire hierarchy of managers above the employee.
4. Go to Data view, select the Employee table and click on “New Column”.
5. Enter the following expression
   • Path = PATH(Employees[ID], Employees[Manager ID])
6. Now, we have a hierarchy ofeach employee in the Path column as a text field separated by pipe ( | ).
7. Next, lets set up role.
   • First, we get the email addresses of the logged in employee.
     • Filter(Employees, [Email] = USERPRINCIPLANAME())
     • Then select the ID of that employee
       • SELECTCOLUMNS ( Fitler ( Employees, [Email] = USERPRINCIPALNAME()), “ID”, [ID])
     • Third, we use pathcontains() function to get all rows where ID Exists in the path.
       • PATHCONTAINS( [PATH], SELECTCOLUMNS ( FITLER ( Employees, [EMAIL] = USERPRINCIPALNAME()), “ID”, [ID]))
8. Add the above DAX under Manage Roles.
9. Test and validate the role.
10. Save the report and publish.

Thats a wrap.

These use cases demonstrate how RLS can be applied in various scenarios to ensure data security and relevance in Power BI reports. Journal these steps to refer to them whenever needed.