Implementation of Power BI Row Level Security (RLS) when connecting to On-Premises databases from the cloud.
Row-level security enables controlling access to rows in a database table based on a user role. With new updates from PowerBI team we can now implement a logic when users from different roles/groups are limited to see only the data allowed to read by their role/group.
Previously, row-level security could be implemented in Power BI Service by leveraging Analysis Services On-Premises, based on user with USERNAME function of DAX. Now Microsoft added the RLS support to cloud models.
Let’s look at how to implement Row-level security on PBI dataset level and how to directly connect to Oracle on premises from the Power BI service in the cloud.
For demo purposes we created the following three users and two groups in Office 365 AD:
- Bill Smith – global sales manager(USA) can see orders all of customers.
“PBI Demo 11” group
- Justin Trudeau – regional manager(Canada) can see orders customers from Canada only.
“PBI Demo 14” group
- Klaus Schmidt – regional manager(Germany) can see orders customers from Germany only.
“PBI Demo 14” group
Figure 1: Users Location
As data source we choose the well-known “Northwind” database.
Figure 2: Tables relationship schema
Power BI Service is connected to the database through the Enterprise Getaway.
Figure 3: Data Flow schema
Next, we created a simple sales report in the Power BI desktop and publish it in “PBI Demo 11” group. Each Office 365 groups is shown as workspace in Power BI Service.
Figure 4: Publishing to Power BI
Let’s return back to our demo users and see how to configure RLS for them. User Bill Smith can view the report on Power BI website and he has a permission to apply row level security.
Figure 5: The Sales Report
We pined a live page to the new “Sales Geography” dashboard.
Figure 6: Pinning the Sales Report to a new dashboard
On Datasets tab we opened the dataset and chose security tab.
Figure 7: Security tab
In Row-Level Security section we added two new Roles: “Germany Managers”,” Canada Managers”.
We added Justin Trudeu user to the “Canada Manager“ role members.
Figure 8: Row-Level Security section
Now we create a new rule for “Canada Manager” role. In rule expression we can use DAX expressions and any data tables from the dataset. We created very simple DAX expression for our demo: country field from Customer table is filtered by value of Country column.
Figure 9: Row-Level Security section
The same operation we made for Klaus Schmidt user from “Germany Manager” role.
In our demo use in the “Global sales manager” role can share the dashboard with regionally managers and view all orders for all customers.
Figure 10: The dashboard sharing
When Klaus Schmidt user logs in in PBI Service, he can view orders for customers from Germany only.
Figure 11: The regional manager dashboard
Figure 12: The global sales manager and regional manager dashboards
Conclusion
As Power BI evolves, we see more and more features helping users to provide more sophisticated reporting. Row level security feature, which was until recently only available through Microsoft Analysis Services, now available through native Power BI means, allowing connection to other databases, such as Oracle. Being released as a new feature, it is still based on familiar tools, such as DAX queries, while providing convenience of configuring RLS from within a web browser.
