Generate filters for a report from c# API based on user

[ms92ita]

Hi,

I am generating some reports with Azure SQL Direcy Query mode that I will use in my application with the power bi javascript visuals (already implemented). Now, for some reports I need to pre-filter data depending on the user that logs in the application.
For security reasons using the javascript mode is not the best practice because by default data will be "exposed" for all users and anyone with some javascript knowledges can edit filters and retrieve data that he cannot view.
Also, users from my application are dynamic and they are a simple username/password, so implementing a RLS from the dataset is not convenient.
Our application is a multi-tenant application and each tenant can have multiple users from that they can access only data for that they are authorized.
Is there a mode to create a token from C# Power BI API (I am already using it) with these pre-filters on server side, in order to "mask" the filter and create a true security layer?


P.S. I was missing my part of code server side:

var pbiReport = client.Reports.GetReport(groupid, reportid);

EmbedToken embedToken;
// Create list of dataset
var datasetIds = new List<Guid>();

// Add dataset associated to the report
datasetIds.Add(Guid.Parse(pbiReport.DatasetId));

// Append additional dataset to the list to achieve dynamic binding later
datasetIds.Add(Guid.Parse("correctdatasource"));

// Get Embed token multiple resources
var tokenRequest = new GenerateTokenRequestV2(
reports: new List<GenerateTokenRequestV2Report>() { new GenerateTokenRequestV2Report(reportid) },
datasets: datasetIds.Select(datasetId => new GenerateTokenRequestV2Dataset(datasetId.ToString())).ToList(),
targetWorkspaces: groupid != Guid.Empty ? new List<GenerateTokenRequestV2TargetWorkspace>() {
new GenerateTokenRequestV2TargetWorkspace(groupid)
} : null
);

// Generate Embed token
embedToken = client.EmbedToken.GenerateToken(tokenRequest);

I was thinking also to implement RLS on my database, but this is not a solution because I need to create credentials for each user (I can do that) and change dataset credentials per user while accessing report (probably cannot)

[Anonymous · Accepted solution]

HI @ms92ita,

I think RLS based on username should be more suitable for your requirements.
You only need to create a mapping table with username and recorders and link to other table records and assign roles on the power bi service side. (it can be assigned to a group)

Then these filter effects will be dynamically applied to your dataset when the report data is loaded.

Solved: RLS with UserName() - Microsoft Power BI Community

In addition, if you do not want to add a detailed user mapping table to your data source, you also need to compare user mapping in your code.
You can add a tag field to your table field, then use the if statement in your code to compare the current username and default user list to return the tag and manually apply the filter effect based on the result.

Regards,

Xiaoxin Sheng