行级别安全性(预览版)Row Level Security (Preview)

使用组成员资格或执行上下文来控制对数据库表中行的访问权限。Use group membership or execution context to control access to rows in a database table.

行级别安全性 (RLS) 简化了安全性的设计和编码。Row Level Security (RLS) simplifies the design and coding of security. 它允许在应用程序中对数据行访问进行限制。It lets you apply restrictions on data row access in your application. 例如,限制用户,仅允许其访问与其部门相关的行;或限制客户,仅允许其访问与其公司相关的数据。For example, limit user access to rows relevant to their department, or restrict customer access to only the data relevant to their company.

访问限制逻辑位于数据库层中,而不是在远离数据的另一个应用层中。The access restriction logic is located in the database tier, rather than away from the data in another application tier. 数据库系统会在用户每次尝试从任何层进行数据访问时应用访问限制。The database system applies the access restrictions every time data access is attempted from any tier. 此逻辑减少了安全系统的外围应用,使安全系统变得更加可靠和稳健。This logic makes your security system more reliable and robust by reducing the surface area of your security system.

使用 RLS,你可以向其他应用程序和用户仅提供对表的某个部分的访问权限。RLS lets you provide access to other applications and users, only to a certain portion of a table. 例如,您可能希望:For example, you might want to:

  • 仅授予对满足某些条件的行的访问权限Grant access only to rows that meet some criteria
  • 将某些列中的数据匿名化Anonymize data in some of the columns
  • 以上都是All of the above

有关详细信息,请参阅用于管理行级别安全性策略的控制命令For more information, see control commands for managing the Row Level Security policy.

提示

以下函数通常用于 row_level_security 查询:These functions are often useful for row_level_security queries:

限制Limitations

对于可以配置行级别安全性策略的表的数量没有限制。There's no limit on the number of tables on which Row Level Security policy can be configured.

不能在符合以下条件的表上启用 RLS 策略:The RLS policy can't be enabled on a table:

示例Examples

限制对 Sales 表的访问Limit access to Sales table

在名为 Sales 的表中,每行都包含有关销售的详细信息。In a table named Sales, each row contains details about a sale. 其中一列包含销售人员的姓名。One of the columns contains the name of the salesperson. Sales 表上启用行级别安全性策略,而不是授予销售人员对此表中所有记录的访问权限,以便仅返回销售人员是当前用户的记录:Instead of giving your salespeople access to all records in Sales, enable a Row Level Security policy on this table to only return records where the salesperson is the current user:

Sales | where SalesPersonAadUser == current_principal()

你还可以掩盖信用卡号码:You can also mask the credit card number:

Sales | where SalesPersonAadUser == current_principal() | extend CreditCardNumber = "****"

如果希望每个销售人员都能看到某个特定国家/地区的所有销售项,则可以定义类似于以下内容的查询:If you want every sales person to see all the sales of a specific country, you can define a query similar to:

let UserToCountryMapping = datatable(User:string, Country:string)
[
  "john@domain.com", "USA",
  "anna@domain.com", "France"
];
Sales
| where Country in (UserToCountryMapping | where User == current_principal_details()["UserPrincipalName"] | project Country)

如果你有一个包含经理的组,则可能需要向他们授予对所有行的访问权限。If you have a group that contains the managers, you might want to give them access to all rows. 查询行级别安全策略。Query the Row Level Security policy.

let IsManager = current_principal_is_member_of('aadgroup=sales_managers@domain.com');
let AllData = Sales | where IsManager;
let PartialData = Sales | where not(IsManager) and (SalesPersonAadUser == current_principal());
union AllData, PartialData
| extend CreditCardNumber = "****"

将不同数据公开给不同 Azure AD 组的成员Expose different data to members of different Azure AD groups

如果你有多个 Azure AD 组,并且希望每个组的成员可以查看不同的数据子集,请将此结构用于 RLS 查询。If you have multiple Azure AD groups, and you want the members of each group to see a different subset of data, use this structure for an RLS query. 假定一个用户只能属于单个 Azure AD 组。Assume a user can only belong to a single Azure AD group.

let IsInGroup1 = current_principal_is_member_of('aadgroup=group1@domain.com');
let IsInGroup2 = current_principal_is_member_of('aadgroup=group2@domain.com');
let IsInGroup3 = current_principal_is_member_of('aadgroup=group3@domain.com');
let DataForGroup1 = Customers | where IsInGroup1 and <filtering specific for group1>;
let DataForGroup2 = Customers | where IsInGroup2 and <filtering specific for group2>;
let DataForGroup3 = Customers | where IsInGroup3 and <filtering specific for group3>;
union DataForGroup1, DataForGroup2, DataForGroup3

在多个表上应用同一 RLS 函数Apply the same RLS function on multiple tables

首先,定义一个函数,使该函数接收表名作为字符串参数,并使用 table() 运算符引用该表。First, define a function that receives the table name as a string parameter, and references the table using the table() operator.

例如:For example:

.create-or-alter function RLSForCustomersTables(TableName: string) {
    table(TableName)
    | ...
}

然后通过以下方式在多个表上配置 RLS:Then configure RLS on multiple tables this way:

.alter table Customers1 policy row_level_security enable "RLSForCustomersTables('Customers1')"
.alter table Customers2 policy row_level_security enable "RLSForCustomersTables('Customers2')"
.alter table Customers3 policy row_level_security enable "RLSForCustomersTables('Customers3')"

在进行未经授权的访问时生成错误Produce an error upon unauthorized access

如果希望未经授权的表用户收到错误而不希望返回空表,请使用 assert() 函数。If you want non-authorized table users to receive an error instead of returning an empty table, use the assert() function. 以下示例演示如何在 RLS 函数中生成此错误:The following example shows you how to produce this error in an RLS function:

.create-or-alter function RLSForCustomersTables() {
    MyTable
    | where assert(current_principal_is_member_of('aadgroup=mygroup@mycompany.com') == true, "You don't have access")
}

可以将此方法与其他示例组合在一起。You can combine this approach with other examples. 例如,可以向不同 AAD 组中的用户显示不同的结果,并为所有其他人生成一个错误。For example, you can display different results to users in different AAD Groups, and produce an error for everyone else.

控制对后继数据库的权限Control permissions on follower databases

你在生产数据库上配置的 RLS 策略还将在随从数据库中生效。The RLS policy that you configure on the production database will also take effect in the follower databases. 不能在生产数据库和随从数据库上配置不同的 RLS 策略。You can’t configure different RLS policies on the production and follower databases. 但是,你可以在 RLS 查询中使用 current_cluster_endpoint() 函数来达到与在后继表中使用不同 RLS 查询相同的效果。However, you can use the current_cluster_endpoint() function in your RLS query to achieve the same effect, as having different RLS queries in follower tables.

例如:For example:

.create-or-alter function RLSForCustomersTables() {
    let IsProductionCluster = current_cluster_endpoint() == "mycluster.eastus.kusto.windows.net";
    let DataForProductionCluster = TempTable | where IsProductionCluster;
    let DataForFollowerClusters = TempTable | where not(IsProductionCluster) | extend CreditCardNumber = "****";
    union DataForProductionCluster, DataForFollowerClusters
}

更多用例More use cases

  • 呼叫中心支持人员通过身份证号或信用卡号的几个数字就可以辨识呼叫者。A call center support person may identify callers by several digits of their social security number or credit card number. 不应向支持人员完全公开这些号码。Those numbers shouldn't be fully exposed to the support person. 可以在表上应用 RLS 策略以掩盖任意查询的结果集中任何身份证号或信用卡号除最后四位数以外的其他所有数字。An RLS policy can be applied on the table to mask all but the last four digits of any social security or credit card number in the result set of any query.
  • 设置对个人身份信息 (PII) 进行掩盖的 RLS 策略,使开发人员能够在不违反合规性法规的情况下,对生产环境进行查询,以便进行故障排除。Set an RLS policy that masks personally identifiable information (PII), and enables developers to query production environments for troubleshooting purposes without violating compliance regulations.
  • 医院可以设置 RLS 策略,以允许护士仅查看自己患者的数据行。A hospital can set an RLS policy that allows nurses to view data rows for their patients only.
  • 银行可以设置 RLS 策略,以根据员工的业务部门或角色来限制对财务数据行的访问权限。A bank can set an RLS policy to restrict access to financial data rows based on an employee's business division or role.
  • 多租户应用程序可以将来自多个租户的数据存储在单个表集中(这非常高效)。A multi-tenant application can store data from many tenants in a single tableset (which is efficient). 它们将使用 RLS 策略来强制对每个租户的数据行与所有其他租户的行进行逻辑分离,使每个租户只能看到其自己的数据行。They would use an RLS policy to enforce a logical separation of each tenant's data rows from every other tenant's rows, so each tenant can see only its data rows.

对查询的性能影响Performance impact on queries

在表上启用 RLS 策略后,对访问该表的查询会产生一定的性能影响。When an RLS policy is enabled on a table, there will be some performance impact on queries that access that table. 对表的访问实际上将被替换为在该表上定义的 RLS 查询。Access to the table will actually be replaced by the RLS query that's defined on that table. RLS 查询的性能影响通常包括两部分:The performance impact of an RLS query will normally consist of two parts:

  • Azure Active Directory 中的成员身份检查Membership checks in Azure Active Directory
  • 应用于数据的筛选器The filters applied on the data

例如:For example:

let IsRestrictedUser = current_principal_is_member_of('aadgroup=some_group@domain.com');
let AllData = MyTable | where not(IsRestrictedUser);
let PartialData = MyTable | where IsRestrictedUser and (...);
union AllData, PartialData

如果用户不属于 *some_group@domain.com* ,则 IsRestrictedUser 的评估结果将为 falseIf the user isn't part of *some_group@domain.com*, then IsRestrictedUser will be evaluated to false. 要评估的查询类似于以下查询:The query that will be evaluated is similar to this one:

let AllData = MyTable;           // the condition evaluates to `true`, so the filter is dropped
let PartialData = <empty table>; // the condition evaluates to `false`, so the whole expression is replaced with an empty table
union AllData, PartialData       // this will just return AllData, as PartialData is empty

同样,如果 IsRestrictedUser 的评估结果为 true,则只会评估对 PartialData 的查询。Similarly, if IsRestrictedUser evaluates to true, then only the query for PartialData will be evaluated.

提高使用 RLS 时的查询性能Improve query performance when RLS is used

对引入的性能影响Performance impact on ingestion

对引入没有任何性能影响。There's no performance impact on ingestion.