Hello,
I’ve been doing some reading around how to implement RLS for our embedded report but, so far it appears we can’t use this approach to trim data based on the user. I’m hoping someone out there has come up with an alternative solution to this problem. Here’s our set up
We have an Azure-hosted web app with B2C for authentication. It uses Policy-based authorization tied to a couple of generic roles, but this is mainly to control what the user can do from an administrative perspective. The permissions around accessing data are completely separate as they’re relatively complex and unique. The way data trimming works in the app is as follows: when the user authenticates using B2C, we generate a couple of claims (based on the permissions stored in an SQL DB) which are then retrieved by the service layer and used to trim whatever data the user is pulling
From a Power BI perspective, we’ve kind of manage to apply the trimming, but the approach is less than ideal. This is what we have: The report has been embedded into the app using a service account. The data source is an API endpoint which accepts a couple of parameters to identify the user and trim the data accordingly. Since we can’t set the parameters dynamically, we’ve been forced to generate a workspace for each user (and by that I mean – we create a workspace with the user’s account name so we can match the two) with a duplicate of the original report and its unique data source URL (with the user parameters) so the data is trimmed accordingly
As mentioned before – this approach works but it’s not sustainable. It’s added a lot of management burden (even though we managed partially automate the workspace creation process) and we think it might have performance issue in the future as the data grows in size
We’re after a solution that taps straight into the Database and dynamically applies the same trimming logic at the database or dataset level based on the current use. Note – the report data is currently stored in Cosmos, but we can move it to Azure SQL if it makes things easier.
