Keep Your Rows in Line: RLS

Data is a valuable asset, and companies that use data to drive their operations need to ensure that this asset is protected, accurate, and reliable.

That's where Row Level Security or RLS comes in - the superhero feature of data security, ensuring that certain users can only access the data that they are authorized to see based on their roles and responsibilities.

RLS can play an important role in a company's strategy to become a data-driven company in such ways as:

1.?Enhanced Data Security: RLS ensures that only authorized users can access specific data sets, which improves data security and reduces the risk of data breaches. This is especially important for companies that handle sensitive data, such as financial or personal information.

2.?Improved Data Quality: RLS can help improve data quality by ensuring that users only access the data sets that are relevant to their roles and responsibilities. This reduces the risk of errors or inconsistencies that can arise when users access data outside of their areas of expertise.

3.?Efficient Data Governance: RLS can support efficient data governance by providing a framework for managing data access and security. This helps ensure that the right people have access to the right data at the right time, which is essential for effective decision-making.

4.?Facilitates Collaborative Data Analysis: RLS can facilitate collaborative data analysis by enabling multiple users to access and analyze data sets simultaneously. This improves collaboration and knowledge-sharing among team members, which is important for developing data-driven insights and strategies.

Let us move on to a couple of examples on how to implement RLS in your Power BI reports which can serve as a basis for any RLS Model.

Scenario 1: Let’s say we are looking at the sales report having a hierarchical structure of data Sales Groups and Sales Organisations.

The client wants to have 3 permission groups: Management, Sales Group and Sales Organisation.

Management - can see all the data.

Sales Group - can see all the records of all the Sales Organisations within the Group.

Sales Organisation-can see only the records of its own Sales Organisation.

1.To set up the RLS we need to maintain an Excel file with the Name of a Person, Group Code, Organisation Code, Permission Level, Login and Permission Group. You can add extra details if you wish, but the below information is sufficient to develop the RLS logic.

2.There is no need to create a relationship between Permissions Table and the other dimension tables. Best is to hide that table.

3. Create your Roles. Start with the lowest granularity: Sales Organisation

and use that table for both roles: Sales Organisation and Sales Group since it has both codes.

Sales Organisation Role

Contains('Permissions', 'Permissions'[Organisation Code],[Organisation Code],'Permissions'[Login],UserPrincipalName())

If the value from Login Column equals the UserNamePrincipal, then the Sales Organisation Code from the Sales Organisation table shall be limited to the corresponding Code from Permissions table.

Sales Group Role

Sales Group Role shall be based also on the lower granularity table in this case Sales Organisation.

Contains('Permissions', 'Permissions'[Organisation Code],[Organisation Code],'Permissions'[Login],UserPrincipalName())

|| Contains('Permissions', 'Permissions'[Group Code],[Group Code],'Permissions'[Login],UserPrincipalName(),'Permissions'[Permission Group],"Sales Group")

Management Role

The role shall be created but nothing has to be set up.

In the end you need to create 3 security AD groups and add them to the roles in Power BI Services.

It is a very easy way to set up this RLS and maintain the file.

Scenario 2: Suppose the client didnt want to create 3 Security Roles and 3 AD groups. The client wants to have one security group and one role handling the data access via Usernames. How shall we set it up? There are many different ways, but one can handle it clean via variables and new RLS table.

Lets create a management table variable by cross joining all the login values from the Permissions Table with the Sale Org and Group code combinations from the Sales Oragnisation Dimension Table.

Now we shall filter out the Permissions table for the non-management groups and then append both variables together via UNION into a table that will replace the original Permissions table.

Once the "new" RLS table is created, hide it and use it in the role definition.

(Contains('RLS', 'RLS'[Organisation Code],[Organisation Code],'RLS'[Login],UserPrincipalName(),'RLS'[Permission Group],"Sales Organisation") )

||

(Contains('RLS', 'RLS'[Organisation Code],[Organisation Code],'RLS'[Login],UserPrincipalName())?

|| Contains('RLS', 'RLS'[Group Code],[Group Code],'RLS'[Login],UserPrincipalName(),'RLS'[Permission Group],"Sales Group"))

||?

(Contains('RLS', 'RLS'[Organisation Code],[Organisation Code],'RLS'[Login],UserPrincipalName())?

|| Contains('RLS', 'RLS'[Group Code],[Group Code],'RLS'[Login],UserPrincipalName(),'RLS'[Permission Group],"Management"))

All is left is to create an AD group and add it to the Security in PBI Services.

This way you have only one role and group to maintain.

So, if you want to keep your data safe and secure, make sure you have RLS in place.