基于IS_MEMBER函数的行级安全性

Question

目标

我正在Azure Synapse上实现基于Active Directory(AD(身份验证的行级安全性(RLS(。其思想是，添加到security_group_A_access的客户可以访问具有列CATEGORY = 'A'的所有MY_TABLE行。请参阅下面的代码。

问题

1. IS_MEMBER函数每行调用一次，使SELECT DISTINCT CATEGORY FROM MY_TABLE查询运行时间超过3分钟，而不是1秒。看起来是个常见问题
2. 即使使用WHERE CATEGORY = 'A'对fn_predicate的条件进行硬编码(移除IS_MEMBER调用(，查询也会变慢。RLS表有多达十亿行，用于Power BI报告，因此性能/响应时间对它们来说非常关键

问题

1. 是否有任何方法可以在不超过安全组的次数的情况下调用IS_MEMBER函数意味着以某种方式缓存结果或将其存储到全局变量中，等等
2. RLS是用于具有关键性能的十亿行表的常用方法吗？或者，为每个AD安全组创建一个视图是一种常见的做法

代码

CREATE SCHEMA [security]
GO
CREATE FUNCTION [security].[fn_predicate](@category_column as varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_predicate
WHERE IS_MEMBER(CONCAT('security_group_', @category_column, '_access')) = 1
GO
GRANT SELECT ON [security].[fn_predicate] TO security_group_A_access
GRANT SELECT ON [security].[fn_predicate] TO security_group_B_access
GO
CREATE SECURITY POLICY MY_TABLE_FILTER
ADD FILTER PREDICATE [security].[fn_predicate]([CATEGORY])
ON [dbo].[MY_TABLE]
WITH (STATE = ON)
GO

P.S

我可以把这个问题改为-有比第一个更好的选择吗

三种可能的方法：

1. 根据CATEGORY创建视图，并将每个组限制为单独的视图。

   • 优点：MY_TABLE用于PBI报告的性能不受影响
   • 缺点：视图数。假设你有A、B、C、D、E类，那么你需要5个视图。如果有一个用户属于a组和C组，该怎么办

2. 在MY_TABLE的顶部应用RLS。

   • 优点：我目前的方法。实现简单，无需额外的表和视图。可扩展，如果有更多的CATEGORIES，将添加组或规则
   • 缺点：请参阅"问题"部分。MY_TABLE的性能几乎没有受到影响，不可接受

3. 混合方法。创建单个MY_TABLE_VIEW并在视图顶部应用RLS。

   • 优点：第二个选项的所有优点，加上用于PBI报告的MY_TABLE性能不受影响
   • 缺点：仍然需要解决IS_MEMBER性能问题，请参阅"问题"部分的第一点

Answer 1

我知道这是一篇相当古老的帖子，但我只是想分享我的解决方案。我遵循了微软建议模式中的模式#2。

https://learn.microsoft.com/en-us/archive/blogs/sqlsecurity/row-level-security-performance-and-common-patterns#pattern-查找表中的2行签名

这可以使用查找表进行操作。为此，我创建了一个OwnerAssignments表，其中包含应用程序中使用的每个AD组的名称。然后，我的表上还有一个Owner列，我正在将RLS应用于该列。

我的谓词函数看起来像这个

CREATE FUNCTION Security.tvf_securitypredicateOwner(@Owner AS VARCHAR(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_securitypredicate_result FROM [Security].OwnerAssignments
WHERE IS_MEMBER(Name) = 1
AND Name = @Owner

最后，我应用了类似的政策

CREATE SECURITY POLICY MyTableFilter
ADD FILTER PREDICATE Security.tvf_securitypredicateOwner([Owner])
ON dbo.MyTable
WITH (STATE = ON);
GO

这大大提高了性能。它带来了一个花了近一分钟时间运行到几分之一秒的查询。