Implementing row-level security in embedded paginated reports

APPLIES TO:  App owns data  User owns data

This article explains how to embed a paginated report that uses RLS (row-level security) into your app owns data application.

Note

This article is only relevant for app owns data customers.

To use RLS for your paginated reports:

  1. Set up the environment to filter the report
  2. Filter the data at report or query level
  3. Pass the configured parameter using an embed token

Prerequisites

  • This article assumes that you know how to embed a Power BI paginated report. It explains how to generate the embed token so that the report only shows what the user has permission to access.

  • Paginated reports are created using the SQL Server Reporting Services engine, and not the Power BI (Analysis Services) engine, so the RLS filtering is set up in Power BI Report Builder.

Set up the environment

To apply row-level security to a Power BI paginated report, use the built-in field UserID to assign a parameter. This parameter is used to filter or query your data.

Then, pass the UserID to the Embed Token - Generate Token API to get the embed token.

Use UserID as a filter at report or query level

You can use UserId as a filter or in a query to the data source.

Filter the data

  1. In the Semantic model Properties window, from the left pane, select Filter.

    Screenshot of the Power BI Report Builder filter.

  2. From the Expression dropdown menu, select the parameter you want to use for filtering the data.

    Screenshot shows the value Color selected from the Expression menu.

  3. Select the Value function button.

    Power BI Report Builder value

  4. In the Expression window, from the Category list, select Built-in Fields.

    Screenshot shows the Expression window with Built-in Fields selected as Category and ExecutionTime selected as Item.

  5. From the Item list, select UserID and select OK.

    Power BI Report Builder UserID

  6. In the Semantic model Properties window, verify that the expression is your selected parameter = UserID, and select OK.

    Power BI Report Builder semantic model properties

Using a query

  1. In the Semantic model Properties window, from the left navigation pane, select Parameters, and select Add.

    Power BI Report Builder parameters

  2. In the Parameter Name field enter @UserID, and in the Parameter Value add [&UserID].

    Power BI Report Builder parameter name

  3. From the left pane, select Query, in the Query add the UserID parameter as part of your query, and select OK.

    Note

    In the screenshot below the color parameter is used as an example (WHERE FinalTable.Color = @UserID). If needed, you can create a more complex query.

    Power BI Report Builder queries edit

Generate an embed token

When you embed a paginated report for your customers, use the Reports GenerateTokenInGroup API to get the embed token. This token can also be used to filter some data out of the paginated report.

You can only generate a token using a service principal. You can't generate a token as a master user. The service principal has to have at least member permissions to the workspace in the Power BI service. (If the service principal is a contributor or viewer it isn't able to generate a token).

To generate a token, assign the username field with the information you want to display. For example, in a paginated report that has a color parameter, if you enter green in the username field, the embed token restricts the embedded data to just the data that has green as its value in the color column.

{
 "reports": [
 {
  "id": "8d57615e-cfed-4d60-bd21-7dc05727193c"
 }
 ],
 "identities": [
 {
  "username": "green",
  "reports": [
  "8d57615e-cfed-4d60-bd21-7dc05727193c"
  ]
 }
 ]
}

Note

If you generate embed token without specifying a user-id, the object-id of service principal will be used.

Considerations and limitations

  • Master-user isn't supported with paginated reports for embed for your customers. Master-user is supported for embed for your organization.
  • The service principal must have workspace permissions of at least member or (not viewer or contributor).

Generate an embed token