In this blog you will learn about how to implement Static and Dynamic Row Level security in PowerBI along with their implementation.
Row Level Security in PowerBI
Row level security (RLS) is essential for managing and controlling data access in your reports and dashboards. With RLS, you can restrict data visibility based on user roles or other criteria, ensuring that sensitive information is only accessible to the right people.
There are two types of RLS:
- Static Row Level Security
- Dynamic Row Level Security
Static Row Level Security in PowerBI
Static row level security applies fixed filters to the data, which are predefined and hardcoded in Power BI Desktop. These filters remain constant regardless of who views the report. This method is simple and effective, making it ideal for scenarios where user roles and data access requirements are straightforward and rarely change.
Example:
Imagine you have a sales report, and you want to ensure that users from the “former” sales region can only see data related to their region. This restriction is consistent for all users in that region.
Dynamic Row Level Security in PowerBI
Dynamic row level security uses user-specific data to dynamically apply filters based on the identity of the user viewing the report. This method provides greater flexibility and scalability because filters can be adapted to different users without changing role definitions.
Example:
Consider a global sales report where different users need to view data specific to their regions. The regions that users can access vary and are determined by a user-to-region mapping table.
Prerequisite:
Software
- Power BI Desktop
- A Dashboard which you can download from here.
Example:
Steps to Implement Static Row Level Security
Step 1: Run Power BI Desktop
Step 2: Click on Open to open an existing .pbix file
Step 3: Go to Modeling tab and click on Manage roles
Step 4: Click on New
Step 5: Rename Untitled role to Static Supplier (optional) and select suppliers table, then click on Switch to DAX editor
Step 6: Type the following filter; click on save and close Manage security roles window:
Step 7: Click on View as; select Static Supplier and hit OK
Output:
Steps to Implement Dynamic Row Level Security
Step 1: Open Power BI Desktop application.
Step 2: Click on Open to open an existing .pbix file
Step 3: Go to Modeling tab and click on Manage roles
Step 4: Click on New
Step 5: Rename Untitled role to Dynamic Supplier (optional) and select suppliers table, then click on Switch to DAX editor
Step 6: Type the following filter; click on save and close Manage security roles window:
Step 7: Click on View as; select Dynamic Supplier, Other user for giving permission to specific Supplier (e.g. Supplier Three) and hit OK
Output:
Conclusion
Static Row Level Security
- Manual filter creation: For each entity or individual, you have to manually create a separate filter.
- Example: If you set a filter for “Supplier Four”, then no one can access the data of other suppliers such as “Supplier One”, “Supplier Two” etc. To provide access to all suppliers, you have to create separate filters for each.
Dynamic Row Level Security
- Dynamic Permissions: Allows dynamic permissions on specific categories or tables, which means the same filter can behave differently based on user authentication.
- Example: By setting a dynamic filter for all suppliers, each supplier can access their own data based on their individual permissions
If you like the article and would like to support me, make sure to:
- 👏 Like for this article and subscribe to our newsletter
- 📰 View more content on my DataSpoof website
- 🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
