Row-Level and Object-Level Security

In the previous chapter, we learned advanced data modeling techniques, such as implementing various aggregation types, incremental refresh, parent-child hierarchies, role-playing dimensions, and calculation groups. This chapter discusses an essential aspect of data modeling, row-level security and object-level security (RLS and OLS respectively). We will cover the following topics:

• What RLS and OLS mean in data modeling
• Terminology
• Implementation flows
• Common RLS and OLS implementation approaches

We try to cover the preceding topics with real-world scenarios, but remember that each Power BI project may have specific requirements, so it is virtually impossible to cover all possibilities and scenarios in one chapter.

When it comes to Power BI security, many people immediately think it relates to Power BI administration, which is correct to some extent. RLS filters the data within an entire data model to show...

What RLS and OLS mean in data modeling

As mentioned previously, RLS and OLS are mechanisms to control user access over data or hide data model objects so that the relevant user or group of users can access relevant data or data model objects. This is possible by filtering the data and hiding the objects based on the users’ usernames and the role(s) assigned to them by writing simple Data Analysis Expressions (DAX) or, in more complex scenarios, by making changes in the data model. Therefore, the relationships between tables and their cross-filtering direction are vital. While we predominantly develop RLS within Power BI Desktop, implementing OLS is not available in the desktop itself; instead, we use third-party tools such as Tabular Editor. We publish the report file (PBIX) to the Power BI Service after implementing the data model, including RLS, OLS, or both. When we publish the report file to the Power BI Service, it constructs the PBIX file into two separate objects: a...

RLS implementation flow

Implementing RLS in Power BI always follows the same flow, which applies to all implementation approaches and all supported storage modes. We implement RLS in the data model; therefore, the dataset’s storage mode must be in Import mode, DirectQuery mode, or Composite mode (Mixed mode). The following steps explain the RLS implementation flow:

1. Creating security roles.
2. Defining rules within the roles.
3. Validating roles in Power BI Desktop.
4. Publishing the model to the Power BI Service or Power BI Report Server.
5. Assigning members to roles within the Power BI Service or Power BI Report Server.
6. Validating roles in the Power BI Service (role validation is unavailable in Power BI Report Server).

The following diagram illustrates the preceding flow:

Figure 11.9: RLS implementation flow

In the next section, we look at different RLS implementation approaches.

Common RLS implementation approaches

There are usually two different approaches to implementing RLS in Power BI Desktop: static RLS and dynamic RLS. In the following few sections, we look at both approaches by implementing real-world scenarios.

Static RLS implementation

A static RLS approach is when we define rules that statically apply filters to the data model. For example, in Figure 11.2, we created a static RLS rule to filter the Internet Sales amounts by currency when the currency equals AUD. While static RLS is simple to implement, depending on the scenario, it can get quite expensive to maintain and support. Moreover, static RLS is sometimes just enough to satisfy business requirements.

Let us go through a scenario; Adventure Works is an international organization with a few security groups within Azure Active Directory (Azure AD) or Microsoft 365, separating users based on their geographical locations. The business wants to implement RLS so that Australian users...

Introduction to OLS

In the previous sections, we learned how to control the user’s access to data using RLS. In this section, we look at OLS in Power BI. With OLS, we can hide tables and columns that contain sensitive data from the model, such as hiding an entire table or columns for specific users. A more real-world example could be hiding people’s salaries, their bank accounts, or any other personal data from the Employees table in an HR data model. OLS also secures the metadata. Like RLS, OLS kicks in only in the Power BI Service for the users with the Workspace Viewer role and the users with read or build permissions on the dataset. So, sensitive objects are hidden from them, even though the users with a build permission on the dataset can create new reports or use the Analyse in Excel feature to connect to the dataset.

The next section explains the implementation flow for OLS.

OLS implementation flow

OLS implementation flow in Power BI is very similar...

OLS implementation

Let’s look at OLS implementation with a common scenario. The business wants to make the customers’ data visible only to the marketing team and the customers themselves. Therefore, no one who is not working in the marketing team must see the Customer table. The business also decided to secure all measures related to the OrderQuantity column from the Internet Sales table from the marketing team and the customers.

We will use the Chapter 11, OLS.pbix sample file provided with the book. The sample file is accessible here: https://github.com/PacktPublishing/Expert-Data-Modeling-with-Power-BI-Second-Edition/blob/main/Samples/Chapter%2011/Chapter%2011%2C%20OLS.pbix.

In the preceding scenario, we need to have two security groups available. One security group contains all the users except the marketing team members and the customers, and the other is for all the users. The following steps show how to implement the scenario:

1. In Power BI Desktop...

Summary

In this chapter, we learned how to implement RLS and OLS in Power BI Desktop and third-party tools. We now know how to manage the roles within the Power BI Service and in Power BI Report Server. We learned what static and dynamic RLS approaches are and how to implement them in our data model to make the relevant data available to authorized users. We also learned how the mixture of RLS and OLS implementation works and what crucial points to note when designing a security model. In the next chapter, Dealing with More Advanced Data Warehousing Concepts in Power BI, we look at slowly changing dimensions, and degenerate dimensions.

Join us on Discord!

Join The Big Data and Analytics Community on the Packt Discord Server!

Hang out with 558 other members and enjoy free voice and text chat.

https://packt.link/ips2H