使用行级安全性(RLS)保护权限跟踪表时查询无结果的问题排查

核心错误原因 #

你配置的RowLevelSecurityPermissionsRLSFilter函数中，参数@Principal AS VARCHAR未指定长度。在SQL Server中，未指定长度的VARCHAR默认长度为1，这会导致传入的sales1被截断为单个字符s。此时USER_NAME()返回的sales1与截断后的s不匹配，过滤条件USER_NAME() IN ('admin', @Principal)无法满足，因此sales1登录后查询无结果返回。

修正方案 #

1. 修复权限表的RLS函数 #

修改函数参数为指定长度的VARCHAR类型（可根据实际业务需求调整长度，示例设为50）：

CREATE OR ALTER FUNCTION sec.RowLevelSecurityPermissionsRLSFilter(@Principal AS VARCHAR(50)) 
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN SELECT 1 AS R WHERE USER_NAME() IN ('admin', @Principal)

2. 重新创建安全策略（若原策略已存在） #

DROP SECURITY POLICY IF EXISTS sec.RowLevelSecurityPermissionsSecurityPolicy;
CREATE SECURITY POLICY sec.RowLevelSecurityPermissionsSecurityPolicy
ADD FILTER PREDICATE sec.RowLevelSecurityPermissionsRLSFilter(Principal)
ON sec.RowLevelSecurityPermissions
WITH (STATE = ON);

3. 额外检查：Orders表RLS函数的字段匹配问题 #

根据你提供的RowLevelSecurityPermissions表结构，列名为Principal，但你在OrdersRLSFilter函数中写的是Username = USER_NAME()，这属于字段名不匹配（除非你的表实际存在Username列），会导致Orders表的RLS逻辑失效，建议同步修正：

CREATE OR ALTER FUNCTION sec.OrdersRLSFilter(@OrderId AS int) 
RETURNS TABLE WITH SCHEMABINDING AS RETURN 
SELECT 1 AS R
WHERE EXISTS (
    SELECT null 
    FROM sec.RowLevelSecurityPermissions 
    WHERE Principal = USER_NAME() -- 替换原Username为Principal
    AND TableName = 'Orders' 
    AND Id IN (-1, @OrderId)
)

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