Power BI supports the security of the data at the dataset level. This security means everyone can see the data they are authorized to see. There are different levels of that in Power BI, including Row-Level Security, Column-Level Security, and Object-Level Security. All these help Power BI Developers create one dataset but give users different views of the data from the same report. In this article, I’ll explain each of those methods and give some guidance on how to use them.
Data-Level Security
If you want to create one piece of content, but users have different views, then Data-Level Security is what you need. There are four levels for that, which we will discuss here;
- Row-Level Security
- Column-Level Security
- Object-Level Security
- Page-Level Security
Amongst the four options above, at the time of writing this article, the last one is not supported in Power BI yet. However, I’ll guide you to some workarounds for it.
Row-Level Security
Row-Level Security is perhaps the most common term when discussing security data in the Power BI world. Let’s understand it through an example;
Jack built a Power BI report for the sales department of his organization. However, his organization has five different sales branches, one in each country. Each country’s sales manager should see only that country’s data, but not others (For example, Diana, the Sales Manager of the USA, should only see USA’s data, but not UK’s data). Everything else about the report and the layout and calculations is the same.
In such a scenario, the first answer that comes to mind might be creating five copies of the same report and creating filters in each report for each country, then sharing the report of each country with the sales managers of that country. This is not a good solution because you will have five reports to manage. Every change means you must create five copies of it or apply the change in five reports. This is not a maintainable solution. What if there are 20 countries? You can imagine the hardship of maintaining that many copied reports.
Suppose the data is stored in a table like the one below. There is a column for Country, and the country values are in each table row.
The right way to implement this would be to apply a security filter on the data and assign that security filter to the users. In that case, when the user logs in to the Power BI report, they can only see their own data and no one else’s. You won’t need copies of the report because everyone sees the data based on the security filter applied for his or her user. Because this type of security is applied on the data-row level, It is called Row-Level Security. Row-Level Security is sometimes called RLS short.
Row-Level security will be applied at the dataset level. That means even if you have some reports with a live connection to the dataset, they would still follow the settings applied for each user. Maintaining a row-level security solution is simple because you only need to configure the security rules rather than multiple copies of the same table.
Now that you know what Row-Level Security means, let’s look at how it is implemented.
Static Row-Level Security
Static Row-Level Security is the simplest way of implementing RLS. Here is an example of how Static RLS can be implemented;
Dynamic Row-Level Security
Although Static RLS is simple to set up, it is hard to maintain if the roles are many. Dynamic Row-Level Security makes the maintenance of the RLS much simpler. However, it would require more steps to set up at first. Here is an example of a Dynamic RLS;
To set up a Dynamic Row-Level Security, you must set up your data model properly. Here are some details about that;
Dynamic Row-Level Security Patterns
Dynamic Row-Level Security can get complicated because the way that tables are related makes a big difference in how the filter propagates through the model. Below there are some examples of a few Dynamic RLS patterns;
- Dynamic Row Level Security with Manager Level Access in Power BI
- Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship
- Dynamic Row Level Security with Organizational Hierarchy Power BI
- Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 1
- Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 2
For more information about Row-Level Security, I strongly suggest reading my book on Row-Level Security; https://www.amazon.com/Row-Level-Security-Power-BI-different/dp/1651119287.
Column-Level Security
What if you want to control the access to a whole column? For example? The Sales team cannot see the Profit column but can see the Sales column in the same table. This is called Column-Level Security.
Although Column-Level security is supported in Power BI, The implementation of that is not yet supported in the Power BI Desktop. This means it has to be done using another tool; This tool can be Tabular Editor, Visual Studio, or any other tool that can connect to the Power BI dataset and apply changes to it. The screenshot below is an example of implementing it using Tabular Editor.
Object-Level Security
What if you want a whole table to be controlled by the access? Some users will see the Sales table; some users won’t. This is called Object-Level Security, and similar to the Column-Level, it has to be implemented using another tool.
Page-Level Security
RLS, CLS, and OLS are set at the dataset level. This means all the reports connected live to the shared dataset will follow the security rules and setup. Sometimes, you may need security at the visual level, though. Visual-Level Security means that some users will see some visuals or pages, and some will see others. At the time of writing this article, Visual-Level Security is not supported in Power BI. However, there are a few workarounds that I mentioned below articles;
- Share Different Visual Pages with Different Security Groups in Power BI
- Page-Level Security workaround in Power BI
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.