SQL Server行级安全实现：无用户名列的Sales1数据过滤方案

任务背景 #

基于公开教育数据源Microsoft AdventureWorks2017（5.5GB），需实现行级安全控制，让用户/组Sales1仅能查看Sales架构下Sales.CreditCard表中CardType列值为'Vista'的行。

问题解决说明 #

网上常见的行级安全示例多依赖表中含用户名或权限名的字段，无此类字段的过滤场景参考较少。最初编写的脚本因错误地在创建安全策略时直接传入'Vista'值导致报错，修正后逻辑为：在安全函数的WHERE子句中定义目标过滤值，创建安全策略时传入需要过滤的列名。

最终实现脚本 #

USE AdventureWorks2017
GO

-- 授予Sales1对目标表的查询权限
GRANT SELECT ON Sales.CreditCard TO Sales1;
GO

-- 创建行级安全专用架构
CREATE SCHEMA Vista;
GO

-- 创建行级安全谓词函数
CREATE FUNCTION Vista.fn_SecurityPredicateEmployee
    (@UserID AS sysname, 
     @CardType AS varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Result
    WHERE (@UserID = 'Sales1'
           AND 'Vista' = @CardType);
GO

-- 创建并启用安全策略
CREATE SECURITY POLICY Vista.Sales1Filter
ADD FILTER PREDICATE Vista.fn_SecurityPredicateEmployee(USER_NAME(), CardType)  
ON [Sales].[CreditCard]
WITH (STATE = ON); 
GO

-- 授予Sales1对安全函数的查询权限
GRANT SELECT ON Vista.fn_SecurityPredicateEmployee TO Sales1;

验证步骤 #

通过模拟Sales1用户查询目标表，验证行级安全是否生效：

USE AdventureWorks2017
GO

-- 切换为Sales1用户
EXECUTE AS USER = 'Sales1';
-- 查询目标表，仅能看到CardType为Vista的行
SELECT * FROM [Sales].[CreditCard]

-- 切换回原用户
REVERT;
GO

内容的提问来源于stack exchange，提问作者Kristopher Penland