I have written some articles on the RLS topic already. Through my consulting experience, however, I get the feeling that many people can’t easily move from static RLS to dynamic. If you know what are things you need to have the dynamic RLS implemented, it makes the whole process simple. So this is what I intend to explain in this article; read through to learn what are the things you need to implement a dynamic RLS.
What is Dynamic Row-Level Security, and Why it is better?
At first, you need to know what is the dynamic RLS, and why it is better than static RLS.
Static RLS means that you define the logic of security inside the Power BI file (PBIX), and for every change in the logic, you have to open the PBIX file, apply the change, save the file, and publish it again.
Learn more about static RLS using the example below;
Dynamic RLS means that you define the logic of security inside the data model (tables, their relationships, etc). For a change in the logic, you just need to add/edit/delete records in the tables, and that’s it!
If you want to learn more about Dynamic RLS read the post below;
The definition above also says the benefits of dynamic RLS. If you want to add a role or change it, you don’t need to open the PBIX file, make changes and publish it again. It is all done through the tables.
In other words; Dynamic Row-Level Security requires less maintenance efforts in the future for the changes.
Users Table
In order to get the dynamic row-level security working, you need to have a table of all users. This table needs to have all users in it with a field which is their login id to the Power BI report.
If your report is hosted in the Power BI service, their login id is the email address that they use to login to the Power BI service. If the report is hosted in the Power BI report server, then the login id is the network account they use to login to the report server.
This is an example of a users table:
I often get this question that; how can you produce this table? You can query this data from Office 365, or from Active Directory in many way, such as PowerShell scripts. Even in Power BI there is an option to get data from active directory.
Roles Table
You also need a table for roles. Remember that roles are defined dynamic inside the data model. The roles table is a table with a row for each different access level.
For example, If you want to have a different access level for each region, there should be one record per region in this table. This table can be called Profile table too. Here is an example of roles table;
Note that sometimes you don’t need a separate table for Roles. Your Users table might act as a Roles table. In what scenarios you might ask. If you want to have one role per user, then you don’t need a separate Roles table. Your user table will act also as your Roles table.
For example, If you are implementing a Payroll Power BI report, you want every user to see their own data only. the user table and the role table are the same, you just need one user table. Here is an example of having only a user table as a role table;
However, if you may have multiple users with the same role, you do need to have a roles table. Here is an example for that;
The Users Table to Filter the Roles Table
If you expect every role to have multiple users assigned to it, you can have the users table to filter the roles table using a one-to-many relationship.
However, in many cases, the users and roles build a many-to-many relationship. In these scenarios, I do suggest to use DAX filter logic instead of the relationship to filter the Roles table based on the logged in user, here is an example.
Role Table Should Filter Other Tables
It is important that the role table should filter other tables in the data model. Filtering between Power BI tables is defined as a relationship. You need to have a relationship with a proper direction to the other tables in the data model from the roles table.
Here is an example of when the roles table is the same as the users table and filtering the sales table;
Here is an example of roles table (which is separate from the users table) filtering the sales table;
There is a tendency among many people to make some of the relationships both-directional to get the RLS working. I highly recommend you for not doing this. There are better ways to implement it which performs much faster such as the method I explained in this article.
DAX Filters
Another essential element of dynamic RLS is the DAX expression you write in the role as a filter. The purpose of this DAX expression it to get the user’s login id, and filter the Users table in your model. The DAX expression often uses UserName(), UserPrincipalName(), or CustomData() functions to get users’s information.
Here is an example of a simple DAX filter:
and this is another example of a DAX filter:
Sometimes the filter needs to be applied on not only one table (users table), but also some other tables (Roles and others);
Summary
Although every dynamic RLS implementation can be different based on the logic and the data model, there are some fundamentals that you need to have in every model. In this article you learn about some of those. If you are implementing dynamic RLS, you need to have;
- A users table with the login id of all users
- A roles table with the definition of all roles
- The users table should filter the roles table
- The roles table should filter other tables in the data model
- DAX Filter
If you are interested in reading more about RLS scenarios, check out my other articles here:
- Row Level Security in Power BI
- Dynamic Row Level Security
- Dynamic Row Level Security with Manager Level Access
- Dynamic Row Level Security with Users and Profiles
- Dynamic Row Level Security with Organizational Hierarchy
- Row Level Security with Analysis Services Live Connection
- Row-level Security with Many to Many and Organizational Hierarchy
- Calculating Totals in a row-level security implementation
- Data Masking without RLS
- Data Masking with RLS
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.