Row level Security (RLS)

  Hey There! 

In this article I will be explaining row level security in detail. 

Firstly, what is row level security? When you have a dataset where not all groups can view all the data, we need some kind of security to show only that data that's appropriate for viewing by each group. And we have to do it based on who has logged in. Since the security is applied at row level (only some rows of data are available for a certain group) they call it row level security.

Use case:

Report that shows the claims of the employees. But the Geo leads can only see the claims from their regions. 

Solution:

IN PBIX:

Setting up the data:

1. Load the data from the file shown above. 

2. Load another inline mapping table to map the geo leads with their respective geos. (alternatively, you can maintain this data in another excel file outside power bi. Advantage of this approach is that for changes with the geo leads, you dont have to open pbix and alter the file)

2.a. To load this geo leads table click on Enter data. 

2.b. Create a column for Geo and one for the leads. You can either give the lead email id or the name with domain like domain\username. But the next step will be dependent on what you give here. For my example I am taking emailid.

2.c. Click OK. Close and apply. 

3. Create relationship between the two tables with both as the direction.

4. Create a measure as follows in the claim sheet table

Currentuser = USERPRINCIPALNAME()

In step 2 if you had chosen to use name in the created table the measure has to be Currentuser = USERNAME()

5. Check if the value of current user is right by using this measure in a table. You must see the logged in user email id (we are taking Currentuser = USERPRINCIPALNAME() for this example) 

Managing Users:

1. Click on Modeling and Manage user

2. Click create and give a name for the user group: I am giving Geo Leads as the name. Click on the Mapping Table and type the DAX in the right pane as  

                    

Lead=[Currentuser]

This will filter the Lead column from the mapping table with the logged in user. thereby filtering the original claim data for the geo assocaited. 

3. Create another user group called "Super User" (name anything you want) and dont give any dax expression. This helps you manage a super user group, can be userself for data validation, who can view all regions without any filter. 

4. Test the roles by clicking on view as and giving the test email ids.

loaded data for reference:

click ok to see the following filtered result

Now that everything is working fine, publish the file. 

Cloud Service:

1. Navigate to Data sets +data flows. Click on the 3 dots near the data set associated with the file we just published. Select security.

2. Add people in their groups. For example the ones listed as geo leads in our table need to be listed undder geo leads for the RLS to work. 

3. To test it, click on the  three dots and test as a role. Enter the email id you want to test the RLS for. 

If this works, we have done all the steps right. 

Hope this article helped you. Please leave a thumbs up below if it did. :)