Maklum balas Edit

Restrict data access with row-level security (RLS) for Power BI Desktop

  • Artikel
  • 7 minit untuk dibaca

APPLIES TO: ✔️ Power BI Desktop ❌ Power BI service

You can use row-level security (RLS) with Power BI Desktop to restrict data access for given users. Filters restrict data at the row level. You can define filters within roles.

You can now configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. Previously, you were only able to implement RLS within on-premises Analysis Services models outside Power BI. For Analysis Services live connections, you configure Row-level security on the on-premises model. The security option doesn't show up for live connection datasets.

Important

If you defined roles and rules within the Power BI service, you need to recreate those roles within Power BI Desktop and publish the report to the service. Learn more about options for RLS within the Power BI service.

Define roles and rules in Power BI Desktop

You can define roles and rules within Power BI Desktop. When you publish to Power BI, it also publishes the role definitions.

To define security roles, follow these steps.

  1. Import data into your Power BI Desktop report, or configure a DirectQuery connection.

    Note

    You can't define roles within Power BI Desktop for Analysis Services live connections. You need to do that within the Analysis Services model.

  2. From the Modeling tab, select Manage Roles.

    Select Manage Roles

  3. From the Manage roles window, select Create.

    Select Create

  4. Under Roles, provide a name for the role.

    Note

    You can't define a role with a comma, for example London,ParisRole.

  5. Under Tables, select the table to which you want to apply a DAX rule.

  6. In the Table filter DAX expression box, enter the DAX expressions. This expression returns a value of true or false. For example: [Entity ID] = “Value”.

    Manage roles window

    Note

    You can use username() within this expression. Be aware that username() has the format of DOMAIN\username within Power BI Desktop. Within the Power BI service and Power BI Report Server, it's in the format of the user's User Principal Name (UPN). Alternatively, you can use userprincipalname(), which always returns the user in the format of their user principal name, username@contoso.com.

  7. After you've created the DAX expression, select the checkmark above the expression box to validate the expression.

    Validate DAX expression

    Note

    In this expression box, you use commas to separate DAX function arguments even if you're using a locale that normally uses semicolon separators (e.g. French or German).

  8. Select Save.

You can't assign users to a role within Power BI Desktop. You assign them in the Power BI service. You can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured.

Validate the roles within Power BI Desktop

After you've created your roles, test the results of the roles within Power BI Desktop.

  1. From the Modeling tab, select View as.

    Select View as Roles

    The View as roles window appears, where you see the roles you've created.

    View as roles window

  2. Select a role you created, and then select OK to apply that role.

    The report renders the data relevant for that role.

  3. You can also select Other user and supply a given user.

    Select Other user

    It's best to supply the User Principal Name (UPN) as that's what the Power BI service and Power BI Report Server use.

    Within Power BI Desktop, Other user displays different results only if you're using dynamic security based on your DAX expressions.

  4. Select OK.

    The report renders based on what that user can see.

    Note

    The View as role feature doesn't work for DirectQuery models with Single Sign-On (SSO) enabled.

Considerations and limitations

The current limitations for row-level security on cloud models are as follows:

  • If you previously defined roles and rules in the Power BI service, you must re-create them in Power BI Desktop.
  • You can define RLS only on the datasets created with Power BI Desktop. If you want to enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first. Learn more.
  • Service principals cannot be added to an RLS role. Accordingly, RLS won’t be applied for apps using a service principal as the final effective identity.
  • Only Import and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premises model.
  • The Test as role/View as role feature doesn't work for DirectQuery models with Single Sign-On (SSO) enabled.

Issue: Republishing when RLS is configured

There's a known issue where you'll get an error message if you try to publish a previously published report from Power BI Desktop. The scenario is as follows:

  1. Anna has a dataset that is published to the Power BI service and has configured RLS.

  2. Anna updates the report in Power BI Desktop and republishes.

  3. Anna receives an error.

Workaround

Republish the Power BI Desktop file from the Power BI service until this issue is resolved. You can do that by selecting Get Data > Files.

Issue: Multiple roles and limited relationships

You get an error message if you belong to multiple RLS roles and at least one of the roles relies on a limited relationship.

Consider the following data model:

A Power BI data model showing Sales, Product and Customer. A limited relationship exist between Sales and Customer because the tables originate from a different source.

In this simplified data model, which combines data from two Power BI Datasets, two relationships exist:

  • A regular relationship between Sales and Product.
  • A limited relationship between Sales and Customer. This relationship is limited because Customer is in a different source group. That's not the only reason a relationship can be limited. For more information, see limited relationships.

Also, two RLS roles have been defined in this data model:

  • RLS_Product, which is defined on Product and restricts access to product information.
  • RLS_Customer, which is defined on Customer and restricts access to customer information.

User A belongs both RLS_Product and RLS_Customer. When User A accesses the data in the report, both RLS_Product and RLS_Customer get evaluated. To evaluate RLS_Customer, data needs to be shared across the limited relationship between Sales and Customer. This sharing might unintentionally disclose potential information about Products. Therefore, Power BI doesn't allow this sharing to happen and instead generates the following error:

"The user belongs to multiple roles 'RLS_Product, RLS_Customer' that have security filters, which isn't supported when one of the roles has filters affecting table 'Sales' with SecurityFilteringBehavior=Both relationships."

Workaround

Adopt one of the following workarounds to avoid this error:

  • If feasible, don't put any user into multiple RLS roles. In the scenario above, we can create another RLS role, e.g. RLS_Product_Customer, which combines the RLS filters set in both RLS_Product and RLS_Customer. Next, we can assign User A to just RLS_Product_Customer, and remove the user from both RLS_Product and RLS_Customer.
  • Define RLS roles only on one source group. If it's necessary for a user to belong to multiple RLS roles, make sure all RLS filters set in the roles are defined on tables from a single source group. In the scenario above, if we could define RLS_Customer on the source group that contains Sales and Product, we could avoid the error.

Note

We're aware that in many situations Power BI is too restrictive and the information can safely be shared between the sources involved. While we're working on releasing a solution for this situation, consider adopting one of the workarounds above.

FAQ

Question: What if I had previously created roles and rules for a dataset in the Power BI service? Will they still work if I do nothing?
Answer: No, visuals will not render properly. You will have to re-create the roles and rules within Power BI Desktop and then publish to the Power BI service.

Question: Can I create these roles for Analysis Services data sources?
Answer: You can if you imported the data into Power BI Desktop. If you are using a live connection, you will not be able to configure RLS within the Power BI service. This is defined within the Analysis Services model on-premises.

Question: Can I use RLS to limit the columns or measures accessible by my users?
Answer: No, if a user has access to a particular row of data, they can see all the columns of data for that row.

Question: Does RLS let me hide detailed data but give access to data summarized in visuals?
Answer: No, you secure individual rows of data but users can always see either the details or the summarized data.

Question: My data source already has security roles defined (for example SQL Server roles or SAP BW roles). What is the relationship between these and RLS?
Answer: The answer depends on whether you're importing data or using DirectQuery. If you're importing data into your Power BI dataset, the security roles in your data source aren't used. In this case, you should define RLS to enforce security rules for users who connect in Power BI. If you're using DirectQuery, the security roles in your data source are used. When a user opens a report Power BI sends a query to the underlying data source, which applies security rules to the data based on the user's credentials.

Next steps

For more information related to this article, check out the following resources: