Row-Level Security is a powerful data governance capability of Power BI and should be learned by anyone who needs to protect sensitive data. In this blog, I will provide a high-level summary of row-level security, why it is important for your team, when to use it, and how to set it up in Power BI.
What is Row-Level Security, and Why Does it Matter?
Row-level security (RLS) is a way to limit the data an end-user has visibility into within a Power BI report. RLS is a horizontal limitation applied to rows within a table, whereas Object-level security (OLS) is a vertical limitation that restricts column-level and table-level visibility.
For limitation to occur, a developer needs to provide Power BI instructions outlining the data that will be filtered and the users for which visibility should be limited.
RLS is important because it is a key component of any organization’s data security strategy. By implementing RLS, your organization can ensure each Power BI report user has the appropriate data visibility. Without RLS, end users could have visibility into data that they shouldn’t have access to (e.g., payroll or sales data), causing unforeseen issues for your organization.
What are some Row-Level Security Use Cases?
Below are examples of RLS use cases seen across many organizations:
Location Based RLS
I want a user only to see information within a specific location (City/State/Region/Country)
(Example: I am the Head of Finance for the west region and should only see data for that specific area)
Employee Based RLS
I want a user only to see information they are tied to or responsible for (Email/Name)Â Â
(Example: I am a Business Development Representative and should only see data for my CRM pipeline opportunities)
Business Line Based RLS
I want a user to only see information within a specific business line (Product/Service/Department/Division/Unit)
(Example: I am a Buyer for men’s clothing and should only see data for my specific division)
Job Title Based RLS
I want a user only to see information for which their job title is responsible
(Example: I am a Store Manager and should only see data that other store managers see)
Other RLS
Time-Based (Month/Year), Customer-Based (Specific Customer/Group of Customers)
What are the Different Types of Row-Level Security?
Two main types of RLS can be implemented into your Power BI report:Â Status RLS and Dynamic RLS.
Status is the simpler of the two and requires a Power BI developer to define security-based logic manually within the PIBX file (explained later). Dynamic is more complex and requires logic to be defined within the PBIX file and the data model using relationships (explained later).
When choosing between Static RLS and Dynamic RLS, here is a good rule of thumb:
Use Static RLS if:
- You need to restrict data visibility for a specific group of users that require the same level of information (e.g., regional sales team to view data for their specific region).
- Your report has fewer users and requires fewer security roles.
- Your report’s security logic is high-level and straightforward (e.g., by user, region, or company).
- Your user security requirements remain stagnant (security groups and security group suers will not change frequently).
- Users are not added or removed frequently
Pros: Easy to set up and implement with minimal IT involvement.
Cons: High level of maintenance, not reusable, manual.
Use Dynamic RLS if:
- You need to restruct data visibility for a specific group of users that require different levels of information (e.g., regional sales team to view data for their specific regional territory(s)).
- Your report has a greater number of users and requires a greater number of security roles.
- Your user security requirements often change (security groups and security group users will not be changing frequently).
- Your report’s security logic is more complex (e.g., by job title, job function, department, division, location, territory, or a combination).
- Users will be added or removed frequently.
Pros: Reusable security model, low maintenance, automated.
Cons: Time consuming, requires dimension tables (users table/roles table), greater IT involvement.
How to Set Up Row-Level Security in Power BI
Example Situation
A rapidly growing burrito chain in Minnesota, Ohio, and New York, hired phData (a premier data and analytics consulting firm) to develop a Power BI dashboard for its restaurant managers. The purpose of the dashboard is to share sales information so the restaurant managers can understand trends and make informed business decisions. Management wants each restaurant manager to see only their states’ sales data. How can this be accomplished using Statis and Dynamic RLS?
Static RLS:
Directions:
- With the Power BI report open, select ‘Modeling’  and then ‘Manage Roles’.
- Select ‘Create’ → add a name to identify the new role (‘Minnesota’, ‘Ohio’, ‘New York’).
- Uner ‘Tables’ → select the vertical ellipses (…). Then select ‘Add filter’ → choose the field from the table you want to filer by (‘State’).
- Add the logic you want to filter the field by (‘State = MN’, ‘State = OH’, ‘State = NY’) → Select the check mark (‘Verify DAX Expression’), then select save.
- Validate: Select ‘Modeling’ → Select ‘View As’ → Select the role you just set up → Select ‘Ok’ → then verify that the data is filtering as expected.
- Save and publish the Power BI Report to a workspace.
- Navigate to the Power BI Workspace.
- On the dataset (in Organge) → select the vertical ellipses (…) (‘More options’) → Select ‘Security’.
- Add users to the roles (‘Minnesota’, ‘Ohio’, ‘New York’) you established → Select Save
- Select the vertical ellipses (…) next to the security group (‘More options’) → Select ‘Test as role‘ → ensure that the RLS is working as expected.
Dynamic RLS:
For Dynamic RLS you will perform similar steps as above, however you will use a dimension table (dim_user) to identify which location(s) eac restaurant manager is responsible for. The benefit if using a dimension table is that it will always reflect the most current data and won’t require you to make manual security changes each time the data changes. The dimension table will be linked to the fact tabe (fct_sales) with the dimension table filtering the fact table.
Directions:
- With the Power BI report open, bring in the dimension table.
- Create a relationship between the dimension and fact tables (with the dimension table filtering the fact table).
- Select ‘Modeling’ → then ‘Manage Roles’
- Select ‘Create’ → add a name to identify your new role (‘Users’).
- Under ‘Tables’ → select the vertical ellipses (…) → select ‘Add filter’ and then choose the field from the table you want to filter by (‘user_email’)
- Add in logic for what you want to filter the field by (‘user_email = userprincipalname()’) → select the check mark (‘Verify DAX Expression’) → select save.
- Validate: select ‘Modeling’ → select “View As’ → select the role you just set up → select ‘Ok’ → verify that the data is filtering as expected.
- Save and publish the Power BI report to a Power BI Workspace.
- Navigate to the Power BI Workspace.
- On the dataset (in Orange) → Select the vertical ellipses (…) (‘More Options’) → select “Security’
- Add users to the role (‘Users’) you established → select ‘Save’.
- Select the vertical ellipses (…) next to the security group (‘More options) → select ‘Test as role’ → ensure that the RLS is working as expected.
Closing
RLS can seem like a complex and daunting assignment, but our hope is that you now have a clearer understanding of what RLS is, why it is so important, and how you can start implementing RLS into your own Power BI reporting. As Power BI developers and data leaders, we have a responsibility to safeguard sensitive data and ensure that it is used appropriately.Â
Have more Power BI questions? Our team or experts are here to help!
This post originally written by Ryan Lurquin.
