3I. Enforce Power BI model security

Question 1

What is RLS?

Answer 1

Row-level security, which involves defining roles and rules in that filter data model.

Question 2

What is OLS?

Answer 2

Object-level security (OLS), to restrict access to entire tables or columns.

Question 3

How does RLS enforce security?

Answer 3

As a data modeler, you set up RLS by creating one or more roles. A role has a unique name in the model, and it usually includes one or more rules. Rules enforce filters on model tables by using Data Analysis Expressions (DAX) filter expressions.

By default, a data model has no roles. A data model without roles means that users (who have permission to query the data model) have access to all model data.

It’s possible to define a role that includes no rules. In this case, the role provides access to all rows of all model tables. This role set up would be suitable for an admin user who is allowed to view all data.

Question 4

What context applies to rules in RLS?

Answer 4

Rule expressions are evaluated within row context. Row context means the expression is evaluated for each row using the column values of that row. When the expression returns TRUE, the user can “see” the row.

Question 5

What two types of rules exist?

Answer 5

• Static
• Dynamic

Question 6

What are static rules?

Answer 6

Static rules use DAX expressions that refer to constants.

Consider the following rule applied to the Region table that restricts data access to Midwest sales:
‘Region’[Region] = “Midwest”

Question 7

If you wanted to, how would you restrict access to all rows in a table, and when would it be useful?

Answer 7

The simplest static rule that you can create restricts access to all table rows:
FALSE()

It could be useful if you want a role to access part of an aggregated table, but no data from the detail table.

Question 8

What are some disadvantages of static rules?

Answer 8

• Setting up static rules can involve significant effort to create and set up.
• It would also require you to update and republish the dataset if new rows are added to the dimension you use to filter.

Question 9

When might it be a good idea to use dynamic rules instead of static rules?

Answer 9

If there are many rules to set up and you anticipate adding new rules in the future, consider creating dynamic rules instead.

Question 10

What are dynamic rules, and what DAX functions do they use?

Answer 10

Dynamic rules use specific DAX functions that return environmental values (as opposed to constants). Environmental values are returned from three specific DAX functions:

• USERNAME or USERPRINCIPALNAME – Returns the Power BI authenticated user as a text value.
• CUSTOMDATA - Returns the CustomData property passed in the connection string. Non-Power BI reporting tools that connect to the dataset by using a connection string can set this property, like Microsoft Excel.

Question 11

How do USERNAME and USERPRINCIPALNAME compare?

Answer 11

Be aware that the USERNAME function returns the user in the format of DOMAIN\username when used in Power BI Desktop. However, when used in the Power BI service, it returns the format of the user’s User Principal Name (UPN), like username@adventureworks.com. Alternatively, you can use the USERPRINCIPALNAME function, which always returns the user in the user principal name format.

Question 12

Give an example of a dynamic rule.

Answer 12

Consider a revised model design that now includes the (hidden) AppUser table. Each row of the AppUser table describes a username and region. A model relationship to the Region table propagates filters from the AppUser table.

The following rule applied to the AppUser table restricts data access to the region(s) of the authenticated user:

‘AppUser’[UserName] = USERPRINCIPALNAME()

Question 13

How can you validate roles?

Answer 13

When you create roles, it’s important to test them to ensure they apply the correct filters. For data models created in Power BI Desktop, there’s the View as function that allows you to see the report when different roles are enforced, and different username values are passed.

Question 14

Where in the menus do I create roles and rules?

Answer 14

• From the Modeling tab, select Manage Roles.
• From the Manage roles window, select Create.

Question 15

How do I assign users to roles?

Answer 15

In Power BI Service. Open the workspace where you saved your report in the Power BI service and do the following steps:
- In the Power BI service, select the More options menu for a semantic model. This menu appears when you hover on a semantic model name, whether you select it from the navigation menu or the workspace page.
- Select Security. This takes you to the Role-Level Security page where you add members to a role you created in Power BI Desktop.

Question 16

What types of user groups can be mapped to roles?

Answer 16

You can use the following groups to set up row level security:

• Distribution Group
• Mail-enabled Group
• Azure Active Directory Security Group

Note, however, that Microsoft 365 groups aren’t supported and can’t be added to any roles.

Question 17

How are tasks involved in setting up RLS distributed across Power BI Desktop vs service?

Answer 17

• Roles and rules are set up in Power BI Desktop.
• Members or groups are mapped to roles in Power BI service.

Question 18

How does RLS interact with Workspace member categories?

Answer 18

If you publish your Power BI Desktop report to a workspace in the Power BI service, the RLS roles are applied to members who are assigned to the Viewer role in the workspace. Even if Viewers are given Build permissions to the semantic model, RLS still applies. For example, if Viewers with Build permissions use Analyze in Excel, their view of the data is restricted by RLS. Workspace members assigned Admin, Member, or Contributor have edit permission for the semantic model and, therefore, RLS doesn’t apply to them. If you want RLS to apply to people in a workspace, you can only assign them the Viewer role.

Question 19

Describe how you can rely on data sources to enforce RLS when using DirectQuery, and the source supports SSO.

Answer 19

When your data model has DirectQuery tables and their data source supports SSO, the data source can enforce data permissions. This way, the database enforces RLS, and Power BI datasets and reports honor the data source security.

Consider that Adventure Works has an Azure SQL Database for their sales operations that resides in the same tenant as Power BI. The database enforces RLS to control access to rows in various database tables. You can create a DirectQuery model that connects to this database without roles and publish it to the Power BI service. When you set the data source credentials in the Power BI service, you enable SSO. When report consumers open Power BI reports, Power BI passes their identity to the data source. The data source then enforces RLS based on the identity of the report consumer.

To enable SSO when setting up the database connection, set Privacy level setting to Organizational, and tick the box that says Report viewers can only access this data source with their own Power BI identities using DirectQuery.

Question 20

What is a typical use case for applying OLS?

Answer 20

Typically, you apply OLS to secure objects that store sensitive data, like employee personal data.

Question 21

What does OLS restrict beyond tables and columns themselves?

Answer 21

When Power BI enforces OLS, not only does it restrict access to tables and columns, but it can also secure metadata. When you secure metadata, it’s not possible to retrieve information about secured tables and columns by using Dynamic Management Views (DMVs).

Question 22

What are perspectives, and how do they compare to OLS?

Answer 22

Tabular models can hide tables and columns (and other objects) by using a perspective. A perspective defines viewable subsets of model objects to help provide a specific focus for report authors. Perspectives are intended to reduce the complexity of a model, helping report authors find resources of interest. However, perspectives aren’t a security feature because they don’t secure objects. A user can still query a table or column even when it’s not visible to them.

Question 23

Can you apply OLS in any version of Power BI?

Answer 23

No. OLS is a feature inherited from Azure Analysis Services (AAS) and SQL Server Analysis Services (SSAS). The feature is available in Power BI Premium to provide backward compatibility for models migrated to Power BI. For this reason, it’s not possible to completely set up OLS in Power BI Desktop.

Question 24

How is OLS set-up similar to RLS set-up?

Answer 24

To set up OLS, you start by creating roles. You can create roles in Power BI Desktop in the same way you do when setting up RLS.

Question 25

How is OLS set-up different to RLS set-up?

Answer 25

Adding OLS rules to roles isn’t supported by Power BI Desktop, so you’ll need to take a different approach.

You add OLS rules to a Power BI Desktop model by using an XML for Analysis (XMLA) endpoint.

By default, all model tables and columns aren’t restricted. You can set them to None or Read. When set to None, users associated with the role can’t access the object. When set to Read, users associated with the role can access the object. When you’re restricting specific columns, ensure the table isn’t set to None.

Once you’ve added the OLS rules, you can publish the model to the Power BI service. Use the same process for RLS to map accounts and security groups to the roles.

Question 26

What are XMLA endpoints?

Answer 26

XMLA endpoints are available with Power BI Premium, and they provide access to the Analysis Services engine in the Power BI service. The read/write endpoint supports dataset management, application lifecycle management, advanced data modeling, and more. You can use XMLA endpoint-enabled APIs for scripting, such as Tabular Model Scripting Language (TMSL) or the PowerShell SqlServer module. Or you can use a client tool, like SSMS. There are third-party tool options too, like Tabular Editor, which is an open-source tool for creating, maintaining, and managing models.

Question 27

What is a downside to OLS, and what could an alternative option be?

Answer 27

In a Power BI report, when a user doesn’t have permission to access a table or column, they’ll receive an error message. The message will inform them that the object doesn’t exist.

Consider carefully whether OLS is the right solution for your project. When a user opens a Power BI report that queries a restricted object (for them), the error message could be confusing and will result in a negative experience. To them, it looks like the report is broken. A better approach might be to create a separate set of models or reports for the different report consumer requirements.

Question 28

What are some restrictions to OLS?

Answer 28

• You can’t mix RLS and OLS in the same role. If you need to apply RLS and OLS in the same model, create separate roles dedicated to each type.
• You can’t set table-level security if it breaks a relationship chain. For example, if there are relationships between tables A and B, and B and C, you can’t secure table B. If table B is secured, a query on table A can’t transit the relationships between table A and B, and B and C. In this case, you could set up a separate relationship between tables A and C. However, model relationships that reference a secured column will work, providing that the column’s table isn’t secured.

Question 29

Can you restrict access to a measure?

Answer 29

While it isn’t possible to secure measures, a measure that references secured objects is automatically restricted.

Question 30

What are good model development practices to apply, related to roles and rules?

Answer 30

• Strive to define fewer datasets (data models) with well-designed roles.
• Strive to create fewer roles by using dynamic rules. A data-driven solution is easier to maintain because you don’t need to add new roles.
• When possible, create rules that filter dimension tables instead of fact tables. It will help to deliver faster query performance.
• Validate that the model design, including its relationships and relationship properties, are correctly set up.
• Use the USERPRINCIPALNAME function instead of USERNAME function. It provides consistency when validating the roles in Power BI Desktop and the Power BI service.
• Rigorously validate RLS and OLS by testing all roles.
• Ensure that the Power BI Desktop data source connection uses the same credentials that will be applied when set up in the Power BI service.

Question 31

What should you remember about performance when setting up RLS?

Answer 31

When possible, create rules that filter dimension tables instead of fact tables. It will help to deliver faster query performance.