行级安全性Row-Level Security

适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

“行级安全性”图Row level security graphic

借助行级别安全性,可以使用组成员资格或执行上下文来控制对数据库表中行的访问权限。Row-Level Security enables you to 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 in your application. RLS 可帮助你实现对数据行访问的限制。RLS helps you implement restrictions on data row access. 例如,可以确保工作人员仅访问与其部门相关的数据行。For example, you can ensure that workers access only those data rows that are pertinent to their department. 再例如,将客户的数据访问权限限制为,仅访问与其公司相关的数据。Another example is to restrict customers' data 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 that data access is attempted from any tier. 这样,你的安全系统可以通过减少安全系统的外围应用来更加可靠和强健。This makes your security system more reliable and robust by reducing the surface area of your security system.

可使用 CREATE SECURITY POLICYTransact-SQLTransact-SQL 语句以及作为 内联表值函数创建的谓词来实现 RLS。Implement RLS by using the CREATE SECURITY POLICYTransact-SQLTransact-SQL statement, and predicates created as inline table-valued functions.

适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本)、SQL 数据库SQL Database获取)、SQL 数据仓库SQL Data WarehouseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), SQL 数据库SQL Database (Get it), SQL 数据仓库SQL Data Warehouse.

备注

Azure SQL 数据仓库仅支持筛选谓词。Azure SQL Data Warehouse supports filter predicates only. Azure SQL 数据仓库暂不支持阻止谓词。Block predicates aren't currently supported in Azure SQL Data Warehouse.

说明Description

RLS 支持两种类型的安全谓词。RLS supports two types of security predicates.

  • 筛选器谓词以静默方式筛选可用于读取操作(SELECT、UPDATE 和 DELETE)的行。Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

  • 阻止谓词显式阻止违反该谓词的写入操作(AFTER INSERT、AFTER UPDATE、BEFORE UPDATE、BEFORE DELETE)。Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

对表中的行级数据的访问将受到定义为内联表值函数的安全谓词的限制。Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. 随后调用该函数,并由安全策略进行实施。The function is then invoked and enforced by a security policy. 对于筛选谓词,应用程序不知道从结果集中筛选掉的行。For filter predicates, the application is unaware of rows that are filtered from the result set. 如果所有行都被筛选掉,返回的是空集。If all rows are filtered, then a null set will be returned. 对于阻止谓词,违反该谓词的任何操作将失败并出错。For block predicates, any operations that violate the predicate will fail with an error.

筛选谓词在读取基表中数据时应用。Filter predicates are applied while reading data from the base table. 它们影响所有 Get 操作:SELECT 、DELETE 和 UPDATE 。They affect all get operations: SELECT, DELETE and UPDATE. 用户无法选择或删除筛选掉的行。The users can't select or delete rows that are filtered. 用户无法更新筛选掉的行。The user can't update rows that are filtered. 但可以更新以后将要筛选掉的行。But, it's possible to update rows in such a way that they'll be filtered afterward. 阻止谓词影响所有写入操作。Block predicates affect all write operations.

  • AFTER INSERT 和 AFTER UPDATE 谓词可以防止用户将行更新为违反该谓词的值。AFTER INSERT and AFTER UPDATE predicates can prevent users from updating rows to values that violate the predicate.

  • BEFORE UPDATE 谓词可以防止用户更新当前违反该谓词的行。BEFORE UPDATE predicates can prevent users from updating rows that currently violate the predicate.

  • BEFORE DELETE 谓词可以阻止删除操作。BEFORE DELETE predicates can block delete operations.

筛选器和阻止谓词以及安全策略具有以下行为:Both filter and block predicates and security policies have the following behavior:

  • 你可以定义与另一个表联接和/或调用函数的谓词函数。You may define a predicate function that joins with another table and/or invokes a function. 如果使用 SCHEMABINDING = ON创建安全策略,则该联接或函数可以从查询进行访问并按预期方式工作而无需进行任何其他权限检查。If the security policy is created with SCHEMABINDING = ON, then the join or function is accessible from the query and works as expected without any additional permission checks. 如果安全策略是使用 SCHEMABINDING = OFF 创建,用户必须对这些附加表和函数拥有 SELECT 或 EXECUTE 权限,才能查询目标表。If the security policy is created with SCHEMABINDING = OFF, then users will need SELECT or EXECUTE permissions on these additional tables and functions to query the target table.

  • 你可以针对已定义但禁用安全谓词的表发出查询。You may issue a query against a table that has a security predicate defined but disabled. 筛选掉或阻止的任何行都不会受影响。Any rows that are filtered or blocked aren't affected.

  • 如果 dbo 用户、db_owner 角色的成员或表所有者查询已定义并启用安全策略的表,行按照安全策略所定义被筛选掉或阻止。If a dbo user, a member of the db_owner role, or the table owner queries a table that has a security policy defined and enabled, the rows are filtered or blocked as defined by the security policy.

  • 尝试更改架构绑定安全策略绑定的表的架构会导致错误。Attempts to alter the schema of a table bound by a schema bound security policy will result in an error. 但是,可以更改谓词未引用的列。However, columns not referenced by the predicate can be altered.

  • 如果表已针对指定操作定义了谓词,尝试向表添加谓词会导致错误出现。Attempts to add a predicate on a table that already has one defined for the specified operation results in an error. 无论是否已启用谓词,都会这样。This will happen whether the predicate is enabled or not.

  • 如果函数在架构绑定安全策略中用作表中的谓词,尝试修改函数会导致错误出现。Attempts to modify a function, that is used as a predicate on a table within a schema bound security policy, will result in an error.

  • 定义包含非重叠谓词的多个活动安全策略会成功。Defining multiple active security policies that contain non-overlapping predicates, succeeds.

筛选器谓词具有以下行为:Filter predicates have the following behavior:

  • 定义筛选表中的行的安全策略。Define a security policy that filters the rows of a table. 应用程序不知道任何针对 SELECT 、UPDATE 和 DELETE 操作被筛选掉的行。The application is unaware of any rows that are filtered for SELECT, UPDATE, and DELETE operations. 包括所有行都被筛选掉的情况。应用程序可以对行执行 INSERT 操作,即使这些行将在其他任何操作过程中被筛选掉,也不例外。Including situations where all the rows are filtered out. The application can INSERT rows, even if they will be filtered during any other operation.

阻止谓词具有以下行为:Block predicates have the following behavior:

  • UPDATE 的阻止谓词根据 BEFORE 和 AFTER 拆分为单独的操作。Block predicates for UPDATE are split into separate operations for BEFORE and AFTER. 因此,举例来说,无法阻止用户将行值更新为大于当前值。Consequently, you can't, for example, block users from updating a row to have a value higher than the current one. 如果需要这种逻辑,必须对 DELETED 和 INSERTED 中间表使用触发器来一起引用旧值和新值。If this kind of logic is required, you must use triggers with the DELETED and INSERTED intermediate tables to reference the old and new values together.

  • 如果谓词函数使用的列未更改,优化器不会检查 AFTER UPDATE 阻止谓词。The optimizer will not check an AFTER UPDATE block predicate if the columns used by the predicate function weren't changed. 例如:Alice 不应该能够将薪金更改为大于 100,000。For example: Alice shouldn't be able to change a salary to be greater than 100,000. 只要谓词中引用的列未更改,Alice 就可以更改薪金已超过 100,000 的员工的地址。Alice can change the address of an employee whose salary is already greater than 100,000 as long as the columns referenced in the predicate weren't changed.

  • 批量操作 API(包括 BULK INSERT)未发生变化。No changes have been made to the bulk APIs, including BULK INSERT. 这意味着,阻止谓词 AFTER INSERT 将应用于批量插入操作,就像普通的插入操作一样。This means that block predicates AFTER INSERT will apply to bulk insert operations just as they would regular insert operations.

用例Use Cases

下面是有关如何使用 RLS 的设计示例:Here are design examples of how RLS can be used:

  • 医院可以创建安全策略,允许护士仅查看自己患者的数据行。A hospital can create a security policy that allows nurses to view data rows for their patients only.

  • 银行可以创建策略,以根据员工所属的业务部门或在公司中的职责来限制对财务数据行的访问权限。A bank can create a policy to restrict access to financial data rows based on an employee's business division or role in the company.

  • 多租户应用程序可以创建一个策略以强制对每个租户的数据行与所有其他租户的行进行逻辑分离。A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. 可通过将许多租户的数据存储在单个表中来实现效率。Efficiencies are achieved by the storage of data for many tenants in a single table. 每个租户只能查看自己的数据行。Each tenant can see only its data rows.

RLS 筛选器谓词在功能上等效于追加 WHERE 子句。RLS filter predicates are functionally equivalent to appending a WHERE clause. 谓词可以如同业务做法规定一样复杂,或子句可以如同 WHERE TenantId = 42一样简单。The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

用更正式的术语来说,RLS 引入了基于谓词的访问控制。In more formal terms, RLS introduces predicate based access control. 它以基于谓词的集中式灵活评估为重点。It features a flexible, centralized, predicate-based evaluation. 谓词可以基于元数据,也可以基于管理员根据需要确定的其他任何条件。The predicate can be based on metadata or any other criteria the administrator determines as appropriate. 谓词用作一个条件,以便基于用户属性来确定用户是否具有合适的数据访问权限。The predicate is used as a criterion to determine if the user has the appropriate access to the data based on user attributes. 可以使用基于谓词的访问控制来实现基于标签的访问控制。Label-based access control can be implemented by using predicate-based access control.

权限Permissions

创建、更改或删除安全策略需要 ALTER ANY SECURITY POLICY 权限。Creating, altering, or dropping security policies requires the ALTER ANY SECURITY POLICY permission. 创建或删除安全策略需要针对架构的 ALTER 权限。Creating or dropping a security policy requires ALTER permission on the schema.

另外,每个添加的谓词都需要以下权限:Additionally the following permissions are required for each predicate that is added:

  • 针对用作谓词的函数的SELECTREFERENCES 权限。SELECT and REFERENCES permissions on the function being used as a predicate.

  • 针对绑定到策略的目标表的REFERENCES 权限。REFERENCES permission on the target table being bound to the policy.

  • 针对目标表上用作参数的每一列的REFERENCES 权限。REFERENCES permission on every column from the target table used as arguments.

安全策略应用于所有用户(包括数据库中的 dbo 用户)。Security policies apply to all users, including dbo users in the database. Dbo 用户可以更改或删除安全策略,但是可以审核他们对安全策略进行的更改。Dbo users can alter or drop security policies however their changes to security policies can be audited. 如果高特权用户(如 sysadmin 或 db_owner)需要查看所有行来排除故障或验证数据,必须为此编写安全策略。If high privileged users, such as sysadmin or db_owner, need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

如果使用 SCHEMABINDING = OFF创建安全策略,则若要查询目标表,用户必须具有针对谓词函数及在其中使用的任何其他表、视图或函数的 SELECTEXECUTE 权限。If a security policy is created with SCHEMABINDING = OFF, then to query the target table, users must have the SELECT or EXECUTE permission on the predicate function and any additional tables, views, or functions used within the predicate function. 如果使用 SCHEMABINDING = ON 创建安全策略(默认值),则当用户查询目标表时,会绕过这些权限检查。If a security policy is created with SCHEMABINDING = ON (the default), then these permission checks are bypassed when users query the target table.

最佳实践Best Practices

  • 强烈建议为 RLS 对象、谓词函数和安全策略单独创建架构。It's highly recommended to create a separate schema for the RLS objects, predicate function, and security policy.

  • ALTER ANY SECURITY POLICY 权限适用于高特权用户(如安全策略管理员)。The ALTER ANY SECURITY POLICY permission is intended for highly privileged users (such as a security policy manager). 安全策略管理员不需要针对他们保护的表的 SELECT 权限 。The security policy manager doesn't require SELECT permission on the tables they protect.

  • 避免在谓词函数中进行类型转换以避免潜在的运行时错误。Avoid type conversions in predicate functions to avoid potential runtime errors.

  • 尽可能避免在谓词函数中进行递归以避免性能降低。Avoid recursion in predicate functions wherever possible to avoid performance degradation. 查询优化器会尝试检测直接递归,但无法保证查找间接递归。The query optimizer will try to detect direct recursions, but isn't guaranteed to find indirect recursions. 间接递归是指第二个函数调用谓词函数。An indirect recursion is where a second function calls the predicate function.

  • 避免在谓词函数中使用过多表联接以便使性能最大化。Avoid using excessive table joins in predicate functions to maximize performance.

避免使用依赖于会话特定 SET 选项的谓词逻辑:如果用户可以执行任意查询,则其逻辑依赖于会话特定的 SET 选项的谓词函数可能会透漏信息,不过,这种逻辑很少在实际应用程序中使用 。Avoid predicate logic that depends on session-specific SET options: While unlikely to be used in practical applications, predicate functions whose logic depends on certain session-specific SET options can leak information if users are able to execute arbitrary queries. 例如,将字符串隐式转换为 datetime 的谓词函数可能会根据当前会话的 SET DATEFORMAT 选项筛选不同的行。For example, a predicate function that implicitly converts a string to datetime could filter different rows based on the SET DATEFORMAT option for the current session. 一般而言,谓词函数应遵守以下规则:In general, predicate functions should abide by the following rules:

安全说明:旁道攻击Security Note: Side-Channel Attacks

恶意安全策略管理员Malicious security policy manager

观察到以下这点十分重要:具有足够权限来基于敏感列创建安全策略并且有权创建或更改内联表值函数的恶意安全策略管理员可以与另一个对表具有选择权限的用户串通,通过恶意创建旨在使用旁路攻击推断数据的内联表值函数来泄漏数据。It is important to observe that a malicious security policy manager, with sufficient permissions to create a security policy on top of a sensitive column and having permission to create or alter inline table-valued functions, can collude with another user who has select permissions on a table to perform data exfiltration by maliciously creating inline table-valued functions designed to use side channel attacks to infer data. 此类攻击需要进行串通(或向恶意用户授予过多权限),并且可能需要多次反复修改策略(需要删除谓词以便中断架构绑定的权限)、修改内联表值函数并重复对目标表运行选择语句。Such attacks would require collusion (or excessive permissions granted to a malicious user) and would likely require several iterations of modifying the policy (requiring permission to remove the predicate in order to break the schema binding), modifying the inline table-valued functions, and repeatedly running select statements on the target table. 建议根据需要限制权限,并监视是否有任何可疑活动。We recommend you limit permissions as necessary and monitor for any suspicious activity. 应监视不断更改与行级别安全性相关的策略和内联表值函数等活动。Activity such as constantly changing policies and inline table-valued functions related to row-level security should be monitored.

精心设计的查询Carefully crafted queries

使用精心设计的查询可能会造成信息泄露。It is possible to cause information leakage through the use of carefully crafted queries. 例如, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' 会让恶意用户知道 John Doe 的薪金是 100000 美元。For example, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' would let a malicious user know that John Doe's salary is $100,000. 即使采用安全谓词来防止恶意用户直接查询其他人的薪金,用户仍然可以确定查询何时返回被零除异常。Even though there is a security predicate in place to prevent a malicious user from directly querying other people's salary, the user can determine when the query returns a divide-by-zero exception.

跨功能兼容性Cross-Feature Compatibility

一般情况下,行级别安全性将按预期对各种功能正常运行。In general, row-level security will work as expected across features. 但存在几种例外情况。However, there are a few exceptions. 本部分介绍对 SQL ServerSQL Server的某些其他功能使用行级别安全性的说明和注意事项。This section documents several notes and caveats for using row-level security with certain other features of SQL ServerSQL Server.

  • DBCC SHOW_STATISTICS 报告有关未筛选数据的统计信息,因此可能会泄漏在其他情况下受安全策略保护的信息。DBCC SHOW_STATISTICS reports statistics on unfiltered data, and can leak information otherwise protected by a security policy. 因此,应限制使用行级别安全性策略查看表的统计信息对象的访问权限。For this reason, access to view a statistics object for a table with a row-level security policy is restricted. 用户必须是表所有者,或必须是 sysadmin 固定服务器角色、db_owner 固定服务器角色或 db_ddladmin 固定数据库角色的成员。The user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

  • Filestream: RLS 与 Filestream 不兼容。Filestream: RLS is incompatible with Filestream.

  • PolyBase: 仅支持对 Azure SQL 数据仓库的 PolyBase 外部表使用 RLS。PolyBase: RLS is supported with Polybase external tables for Azure SQL Data Warehouse only.

  • 内存优化表: 必须使用 WITH NATIVE_COMPILATION 选项定义在内存优化表中用作安全谓词的内联表值函数。Memory-Optimized Tables: The inline table-valued function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. 使用此选项时,内存优化表不支持的语言功能将被禁止,并在创建时发出相应的错误。With this option, language features not supported by memory-optimized tables will be banned and the appropriate error will be issued at creation time. 有关详细信息,请参阅 内存优化表简介 中的 内存优化表中的行级别安全性部分。For more information, see the Row-Level Security in Memory Optimized Tables section in Introduction to Memory-Optimized Tables.

  • 索引视图: 一般情况下,可以在视图基础之上创建安全策略,并能在安全策略绑定的表基础之上创建视图。Indexed views: In general, security policies can be created on top of views, and views can be created on top of tables that are bound by security policies. 但是,不能在具有安全策略的表顶层创建索引视图,因为通过索引执行的行查找将跳过策略。However, indexed views cannot be created on top of tables that have a security policy, because row lookups via the index would bypass the policy.

  • 变更数据捕获: 变更数据捕获可能会将应被筛选掉的全部行泄露给 db_owner 角色的成员,或在为表启用 CDC 时指定的“限制”角色的成员用户(请注意,可以显式将此功能设置为 NULL ,让所有用户都能访问变更数据)。Change Data Capture: Change Data Capture can leak entire rows that should be filtered to members of db_owner or users who are members of the "gating" role specified when CDC is enabled for a table (note: you can explicitly set this function to NULL to enable all users to access the change data). 实际上, db_owner 和此选通角色的成员可以看到对表所做的所有数据更改,即使表中存在安全策略。In effect, db_owner and members of this gating role can see all data changes on a table, even if there is a security policy on the table.

  • 更改跟踪: 更改跟踪可能会将应被筛选掉的行的主键泄露给同时拥有 SELECT 和 VIEW CHANGE TRACKING 权限的用户。Change Tracking: Change Tracking can leak the primary key of rows that should be filtered to users with both SELECT and VIEW CHANGE TRACKING permissions. 实际数据值不会泄漏;只会透露已更新/插入/删除具有 B 主键的行的列 A 这一事实。Actual data values are not leaked; only the fact that column A was updated/inserted/deleted for the row with B primary key. 如果主键包含机密元素(如社会安全号码),这会产生问题。This is problematic if the primary key contains a confidential element, such as a Social Security Number. 但是,在实践中,此 CHANGETABLE 几乎始终与原始表联接以获取最新数据。However, in practice, this CHANGETABLE is almost always joined with the original table in order to get the latest data.

  • 全文搜索: 对于使用以下全文搜索和语义搜索函数的查询,性能应该会下降,因为引入了附加联接,以应用行级别安全性,并避免泄露应被筛选掉的行的主键:CONTAINSTABLE、FREETEXTTABLE、semantickeyphrasetable、semanticsimilaritydetailstable、semanticsimilaritytable 。Full-Text Search: A performance hit is expected for queries using the following Full-Text Search and Semantic Search functions, because of an extra join introduced to apply row-level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • 列存储索引: RLS 与聚集和非聚集列存储索引兼容。Columnstore Indexes: RLS is compatible with both clustered and nonclustered columnstore indexes. 但是,由于行级别安全性应用了一个函数,优化器可能会修改查询计划,从而不会使用批处理模式。However, because row-level security applies a function, it is possible that the optimizer may modify the query plan so that it doesn't use batch mode.

  • 分区视图: 无法对分区视图定义阻止谓词,无法在使用阻止谓词的表基础之上创建分区视图。Partitioned Views: Block predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use block predicates. 筛选器谓词与分区视图兼容。Filter predicates are compatible with partitioned views.

  • 临时表: 临时表与 RLS 兼容。Temporal tables: Temporal tables are compatible with RLS. 但是,当前表中的安全谓词不会自动复制到历史记录表。However, security predicates on the current table are not automatically replicated to the history table. 若要将安全策略应用到当前表和历史记录表,必须单独在每个表中添加安全谓词。To apply a security policy to both the current and the history tables, you must individually add a security predicate on each table.

示例Examples

A.A. 用户在数据库中进行身份验证的方案Scenario for users who authenticate to the database

此示例创建三个用户和一个表,并在表中填充六行。This example creates three users and creates and populates a table with six rows. 然后,它为表创建内联表值函数和安全策略。It then creates an inline table-valued function and a security policy for the table. 接下来,此示例展示如何为各种用户筛选选择语句。The example then shows how select statements are filtered for the various users.

创建将演示不同访问功能的三个用户帐户。Create three user accounts that will demonstrate different access capabilities.

备注

Azure SQL 数据仓库不支持“以用户身份执行”,因此必须事先为每个用户创建登录。Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so you must CREATE LOGIN for each user beforehand. 稍后,将以适当的用户身份登录以测试此行为。Later, you will log in as the appropriate user to test this behavior.

CREATE USER Manager WITHOUT LOGIN;  
CREATE USER Sales1 WITHOUT LOGIN;  
CREATE USER Sales2 WITHOUT LOGIN;  

创建用于保留数据的表。Create a table to hold data.

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  

使用六行数据填充该表(对于每个销售代表显示三个订单)。Populate the table with six rows of data, showing three orders for each sales representative.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales;

向每个用户授予表的读取访问权限。Grant read access on the table to each of the users.

GRANT SELECT ON Sales TO Manager;  
GRANT SELECT ON Sales TO Sales1;  
GRANT SELECT ON Sales TO Sales2;  

创建一个新架构和一个内联表值函数。Create a new schema, and an inline table-valued function. 该函数在 SalesRep 列中的行与执行查询的用户相同时 (@SalesRep = USER_NAME()) 或是在执行查询的用户是 Manager 用户 (USER_NAME() = 'Manager') 时返回 1。The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';  

创建一个安全策略(将该函数添加为筛选器谓词)。Create a security policy adding the function as a filter predicate. 状态必须设置为 ON 以启用该策略。The state must be set to ON to enable the policy.

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales  
WITH (STATE = ON);  

允许 fn_securitypredicate 函数的 SELECT 权限Allow SELECT permissions to the fn_securitypredicate function

GRANT SELECT ON security.fn_securitypredicate TO Manager;  
GRANT SELECT ON security.fn_securitypredicate TO Sales1;  
GRANT SELECT ON security.fn_securitypredicate TO Sales2;  

现在通过作为每个用户从 Sales 表进行选择来测试筛选器谓词。Now test the filtering predicate, by selected from the Sales table as each user.

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;
REVERT;  

备注

Azure SQL 数据仓库不支持 EXECUTE AS USER,因此请以适当的用户身份登录以测试上述行为。Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so log in as the appropriate user to test the above behavior.

管理员应看到所有六行。The Manager should see all six rows. Sales1 和 Sales2 用户应只能看到自己的销售情况。The Sales1 and Sales2 users should only see their own sales.

更改安全策略以禁用策略。Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

现在,Sales1 和 Sales2 用户可以看到所有六行。Now Sales1 and Sales2 users can see all six rows.

连接到 SQL 数据库以清理资源Connect to the SQL database to clean up resources

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

B.B. 对 Azure SQL 数据仓库外部表使用行级别安全性的方案Scenarios for using Row Level Security on an Azure SQL Data Warehouse external table

此简短示例创建三个用户,以及一个包含六行的外部表。This short example creates three users and an external table with six rows. 然后,它为外部表创建内联表值函数和安全策略。It then creates an inline table-valued function and a security policy for the external table. 该示例演示如何为各种用户筛选选择语句。The example shows how select statements are filtered for the various users.

创建将演示不同访问功能的三个用户帐户。Create three user accounts that will demonstrate different access capabilities.

CREATE LOGIN Manager WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales1 WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales2 WITH PASSWORD = 'somepassword'
GO

CREATE USER Manager FOR LOGIN Manager;  
CREATE USER Sales1  FOR LOGIN Sales1;  
CREATE USER Sales2  FOR LOGIN Sales2 ;

创建用于保留数据的表。Create a table to hold data.

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  

使用六行数据填充该表(对于每个销售代表显示三个订单)。Populate the table with six rows of data, showing three orders for each sales representative.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales;

通过已创建的 Sales 表,创建 Azure SQL 数据仓库外部表。Create an Azure SQL Data Warehouse external table from the Sales table created.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);
  
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='RLSExtTabletest.tbl', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

为外部表的三个用户授予 SELECT。Grant SELECT for the three users external table.

GRANT SELECT ON Sales_ext TO Sales1;  
GRANT SELECT ON Sales_ext TO Sales2;  
GRANT SELECT ON Sales_ext TO Manager;

将会话 A 中的函数用作筛选谓词,对外部表创建安全策略。Create a security policy on external table using the function in session A as a filter predicate. 状态必须设置为 ON 以启用该策略。The state must be set to ON to enable the policy.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext  
WITH (STATE = ON);

现在,通过从 Sales_ext 外部表中进行选择,测试筛选谓词。Now test the filtering predicate, by selecting from the Sales_ext external table. 以每个用户(Sales1、Sales2 和管理员)的身份登录。Sign in as each user, Sales1, Sales2, and manager. 以每个用户的身份运行以下命令。Run the following command as each user.

SELECT * FROM Sales_ext;

管理员应看到所有六行。The Manager should see all six rows. Sales1 和 Sales2 用户应只能看到自己的销售额。The Sales1 and Sales2 users should only see their sales.

更改安全策略以禁用策略。Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);  

现在 Sales1 和 Sales2 用户可以看到所有六行。Now the Sales1 and Sales2 users can see all six rows.

连接到 SQL 数据仓库数据库以清理资源Connect to the SQL Data Warehouse database to clean up resources

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred; 
DROP MASTER KEY;

连接到逻辑主数据库以清理资源。Connect to logical master to clean up resources.

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C.C. 用户通过中间层应用程序连接到数据库的方案Scenario for users who connect to the database through a middle-tier application

备注

在此示例中,Azure SQL 数据仓库当前不支持阻止谓词功能,因此 Azure SQL 数据仓库不会阻止插入错误用户 ID 的行。In this example block predicates functionality isn't currently supported for Azure SQL Data Warehouse, hence inserting rows for the wrong user ID isn't blocked with Azure SQL Data Warehouse.

此示例演示一个中间层应用程序如何实现连接筛选,其中应用程序用户(或租户)共享同一个 SQL ServerSQL Server 用户(应用程序)。This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL ServerSQL Server user (the application). 应用程序连接到数据库之后在 SESSION_CONTEXT (Transact-SQL) 中设置当前应用程序用户 ID,然后安全策略以透明方式筛选不应对此 ID 可见的行,同时阻止用户插入错误用户 ID 的行。The application sets the current application user ID in SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. 无需进行任何其他应用更改。No other app changes are necessary.

创建用于保留数据的表。Create a table to hold data.

CREATE TABLE Sales (  
    OrderId int,  
    AppUserId int,  
    Product varchar(10),  
    Qty int  
);  

使用六行数据填充该表(对于每个应用程序用户显示三个订单)。Populate the table with six rows of data, showing three orders for each application user.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),  
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);  

创建应用程序用来建立连接的低特权用户。Create a low-privileged user that the application will use to connect.

-- Without login only for demo  
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;  
  
-- Never allow updates on this column  
DENY UPDATE ON Sales(AppUserId) TO AppUser;  

创建一个新架构和谓词函数(将使用存储在 SESSION_CONTEXT 中的应用程序用户 ID 来筛选行)。Create a new schema and predicate function, which will use the application user ID stored in SESSION_CONTEXT to filter rows.

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)  
    RETURNS TABLE  
    WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result  
    WHERE  
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO  

创建一个安全策略,用于将此函数添加为 Sales上的筛选器谓词和阻止谓词。Create a security policy that adds this function as a filter predicate and a block predicate on Sales. 阻止谓词只需要 AFTER INSERT,因为 BEFORE UPDATEBEFORE DELETE 已筛选;不需要 AFTER UPDATE ,因为 AppUserId 列不能更新为其他值,原因是前面设置了列权限。The block predicate only needs AFTER INSERT, because BEFORE UPDATE and BEFORE DELETE are already filtered, and AFTER UPDATE is unnecessary because the AppUserId column cannot be updated to other values, due to the column permission set earlier.

CREATE SECURITY POLICY Security.SalesFilter  
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,  
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);  

Sales SESSION_CONTEXT 中设置不同的用户 ID 后,可以通过从表进行选择,来模拟连接筛选。Now we can simulate the connection filtering by selecting from the Sales table after setting different user IDs in SESSION_CONTEXT. 在实践中,应用程序负责在打开连接后在 SESSION_CONTEXT 中设置当前用户 ID。In practice, the application is responsible for setting the current user ID in SESSION_CONTEXT after opening a connection.

EXECUTE AS USER = 'AppUser';  
EXEC sp_set_session_context @key=N'UserId', @value=1;  
SELECT * FROM Sales;  
GO  
  
/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
  
SELECT * FROM Sales;  
GO  
  
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID  
GO  
  
REVERT;  
GO  

清理数据库资源。Clean up database resources.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

另请参阅See Also

CREATE SECURITY POLICY (Transact-SQL)CREATE SECURITY POLICY (Transact-SQL)
ALTER SECURITY POLICY (Transact-SQL)ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)DROP SECURITY POLICY (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
SESSION_CONTEXT (Transact-SQL)SESSION_CONTEXT (Transact-SQL)
sp_set_session_context (Transact-SQL)sp_set_session_context (Transact-SQL)
sys.security_policies (Transact-SQL)sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)sys.security_predicates (Transact-SQL)
创建用户定义函数(数据库引擎)Create User-defined Functions (Database Engine)