行级共享锁和行级排他锁_Power BI中的行级安全性简介

行级共享锁和行级排他锁

Analyzing data with Power BI has always been fascinating to me. With ever-growing data comes more risk, and hence more security is needed. In this article, I am going to introduce what row-level security in Power BI is and how does it work. I’ll also demonstrate in detail how to implement the same in Power BI Desktop. Later, we will also discuss how to implement this row-level security in Power BI service as well. To learn about row-level security in SQL Server, please have a look at my previous article, where I have explained how to implement the same.

使用Power BI分析数据一直令我着迷。随着数据的不断增长，带来了更多的风险，因此需要更高的安全性。在本文中，我将介绍Power BI中的行级安全性及其作用方式。我还将详细演示如何在Power BI Desktop中实现相同的功能。稍后，我们还将讨论如何在Power BI服务中实现此行级安全性。要了解SQL Server中的行级安全性，请查看我以前的文章，其中我解释了如何实现相同的安全性。

将数据导入Power BI (Importing Data into Power BI)

The first step in demonstrating any Power BI feature is to load the dataset into the Power BI environment and build the required visuals. For this article, I’m going to use the famous AdventureWorksDW database, as provided by Microsoft. You can download this database and get it installed on your PC.

演示任何Power BI功能的第一步是将数据集加载到Power BI环境中并构建所需的视觉效果。对于本文，我将使用Microsoft提供的著名的AdventureWorksDW数据库。您可以下载该数据库并将其安装在PC上。

Open Power BI Desktop and click on Get Data. Select SQL Server from the drop-down menu.

打开Power BI Desktop ，然后单击“ 获取数据” 。从下拉菜单中选择“ SQL Server ”。

Select a few tables from the AdventureWorksDW database. I’ve selected a few tables from the database, which are as follows.

从AdventureWorksDW数据库中选择一些表。我从数据库中选择了一些表，如下所示。

Once these tables are imported into Power BI, you can view these on the right-hand pane as follows.

将这些表导入Power BI之后，您可以在右侧窗格中按如下方式查看它们。

Alternatively, you can also view the Data Model and the underlying relationships.

或者，您也可以查看数据模型和基础关系。

Finally, create a sample report on which you’d like to apply the row-level security in Power BI. For this example, I’m going to set the row-level security on the SalesTerritoryGroup column. As you can see, the SalesTerritoryGroup column can be further divided into SalesTerritoryRegion. When we assign roles to a specific territory group, only data relevant for that group will be visible while other records will be hidden from the display.

最后，创建一个示例报告，您要在其中应用Power BI中的行级安全性。对于此示例，我将在SalesTerritoryGroup列上设置行级安全性。如您所见， SalesTerritoryGroup列可以进一步划分为SalesTerritoryRegion 。当我们将角色分配给特定区域组时，只有与该组相关的数据才可见，而其他记录将从显示中隐藏。

第1部分：在Power BI Desktop中实现行级安全性 (Part 1: Implement Row-Level Security in Power BI Desktop)

In order to implement row-level security in Power BI, navigate to the Modeling tab on the top, and select Manage roles.

为了在Power BI中实现行级安全性，请导航至顶部的“ 建模”标签，然后选择“ 管理角色” 。

In the Manage roles dialog box that appears, click on Create.

在出现的“ 管理角色”对话框中，单击“ 创建” 。

Once you click on Create, you’ll have a list of tables from the data model from which you can select a filter to apply the row-level security. Since I’m going to filter on the territory group, I’ll expand the DimSalesTerritory and select Add Filter and then select the column SalesTerritoryGroup.

一旦点击Create ，您将拥有数据模型中的表格列表，您可以从中选择一个过滤器以应用行级安全性。因为我要过滤的区域组，我会扩大DimSalesTerritory并选择添加过滤器 ，然后选择列SalesTerritoryGroup。

You’ll see that a new sample DAX expression has been added with SalesTerritoryGroup as the table.

您会看到添加了新的示例DAX表达式，并将SalesTerritoryGroup作为表。

Replace the “value” in the DAX expression with an actual value from the SalesTerritoryGroup. Let’s add the value as “Europe” and such that this role will filter data only for those records for Europe as the territory group.

用来自SalesTerritoryGroup的实际值替换DAX表达式中的“ 值 ”。让我们将值添加为“ Europe ”，这样该角色将仅过滤欧洲作为区域组的那些记录的数据。

Likewise, let’s create one more for “North America” as well.

同样，让我们也为“ 北美 ”创建一个。

Finally, let’s create a role for a manager who wants to monitor sales across Europe and North America. We need to provide access to both these territories for the manager. You can use the following expression to assign more than one value for a role.

最后，让我们为想要监控整个欧洲和北美销售的经理创建一个角色。我们需要为经理提供访问这两个区域的权限。您可以使用以下表达式为一个角色分配多个值。

Now that we have created the roles let us go ahead and view the implementation in actual. There is an option in Power BI, where we can see the reports from the context of the roles that we just created. This will help us test if the DAX filter expressions are working as expected, and if the row-level security in Power BI can be implemented correctly.

现在我们已经创建了角色，让我们继续并实际查看实现。 Power BI中有一个选项，在这里我们可以从刚刚创建的角色的上下文中查看报告。这将帮助我们测试DAX筛选器表达式是否按预期工作，以及Power BI中的行级安全性是否可以正确实现。

Click on View as, by default, the role None will be selected.

默认情况下，单击“ 查看为” ，将选择“ 无 ”角色。

Let us try to see how the reports look if we select a different role. Select Europe as the role and proceed ahead.

让我们尝试看看如果选择其他角色，报告的外观。选择欧洲作为角色并继续前进。

You’ll notice that the report renders the visualization, but only for the specific role that is selected. You can also notice on the left-hand top that it is mentioned as “Now viewing report as Europe”. Notice how the regions only for Europe are filtered and the line chart now shows only one line, that is for Europe only.

您会注意到该报告呈现了可视化效果，但仅针对所选的特定角色。您还可以在左上方注意到它被称为“ 正在将报告作为欧洲查看 ”。请注意，如何仅过滤欧洲区域，现在折线图仅显示一条线，即仅针对欧洲。

Let us repeat the same again but by changing the role and selecting North America.

让我们再重复一次，只是改变角色并选择北美。

As you can see now, all the territories that belong to North America as being displayed along with a single line for the same in the line chart.

如您现在所见，折线图中，属于北美的所有领土都与一条直线一起显示。

Finally, let us see how the manager role will display the results, as this role needs to display both the groups – Europe and North America. Select the Manager as the role and click OK.

最后，让我们看看经理角色将如何显示结果，因为该角色需要同时显示欧洲和北美这两个组。选择Manager作为角色，然后单击OK 。

As you can see, the manager has access to the data for both the territory groups, and thus two lines are being displayed on the line chart. Also, the values on the cards are now summarized for both the territory groups.

如您所见，经理可以访问两个区域组的数据，因此折线图上将显示两条线。此外，现在还汇总了两个地区组的卡片上的值。

Now that we have some understanding of how row-level security in Power BI Desktop works let’s go ahead and understand how to implement the same security in Power BI service as well.

现在，我们已经了解了Power BI Desktop中行级安全性的工作原理，让我们继续了解如何在Power BI服务中实现相同的安全性。

Publish the report to a workspace and open the published report link.

将报告发布到工作区，然后打开已发布的报告链接。

第2部分：在Power BI Service中实现行级安全性 (Part 2: Implement Row-Level Security in Power BI Service)

Open the report that you have just published.

打开您刚刚发布的报告。

Next, in order to implement RLS, navigate to Datasets, and then click on the options button for the report dataset that you just published.

接下来，为了实现RLS，请导航到“ 数据集”，然后单击刚发布的报表数据集的选项按钮。

Select Security from the drop-down menu.

从下拉菜单中选择安全性 。

The Row-Level Security page opens, and you can see all the three roles that we defined in the desktop are present. Let’s add some users to these roles and try to view from each of the role contexts. Select Europe and the email address for the user who can view the territory for Europe only. Repeat the same for North America and Manager as well. Since I’m the only user in my directory, I’ll be adding my email address for all these roles; however, you can add as many as required based on the requirements.

将打开“行级安全性”页面，您可以看到我们在桌面中定义的所有三个角色。让我们向这些角色添加一些用户，并尝试从每个角色上下文中进行查看。选择欧洲，然后为只能查看欧洲地区的用户提供电子邮件地址。对北美和经理重复相同的步骤。由于我是目录中的唯一用户，因此我将为所有这些角色添加电子邮件地址；但是，您可以根据需要添加任意数量的内容。

Now that we have added users let’s test the roles as well. Right-click on the options for a role and select Test as Role.

现在我们已经添加了用户，让我们也测试角色。右键单击角色选项，然后选择“ 测试为角色” 。

As you can see, the Power BI report is rendered with data only for North America.

如您所见，Power BI报告仅使用北美的数据呈现。

Repeating the same as with the Manager role.

重复与“ 经理”角色相同的操作。

结论 (Conclusion)

As we have learned, row-level security in Power BI is one of the most important security features in both the desktop and the cloud service. In addition to the email addresses that we mapped the users to, users from the Azure Active Directory can also be mapped to the roles and given secured access to the reports. However, an important thing to note that any user who has privileges to modify the report and the dataset will be able to view the underlying data for other roles as well.

据了解，Power BI中的行级安全性是台式机和云服务中最重要的安全功能之一。除了我们将用户映射到的电子邮件地址之外，Azure Active Directory中的用户也可以映射到角色，并获得对报告的安全访问。但是，需要注意的重要一点是，任何有权修改报表和数据集的用户都将能够查看其他角色的基础数据。

翻译自: https://www.sqlshack.com/introduction-to-row-level-security-in-power-bi/