SQL Server行级权限实现求助：管理员无法查看成员角色行

问题根源 #

你写的安全函数逻辑搞反了判断维度：当检查role='members'的行时，函数里的@username = USER_NAME()会拿该行的username（也就是某个成员的用户名）和当前管理员的用户名对比，结果肯定不相等，导致整个条件分支不生效，所以管理员只能看到自己的行。

正确的逻辑应该是：先获取当前登录用户在user表中的角色，再根据这个角色判断是否允许访问目标行，而不是用目标行的字段去匹配当前用户的身份。

修正后的安全函数 #

CREATE FUNCTION Security.fn_securitypredicate3
         (@username AS nvarchar(50), @role AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT 1 AS Result
    WHERE 
        -- 当前用户是members：仅能查看自身行
        (
            (SELECT role FROM dbo.[user] WHERE username = USER_NAME()) = 'members'
            AND @username = USER_NAME()
        )
        -- 当前用户是admins：可查看自身行及所有members的行
        OR
        (
            (SELECT role FROM dbo.[user] WHERE username = USER_NAME()) = 'admins'
            AND (@username = USER_NAME() OR @role = 'members')
        )
        -- 当前用户是ceos：可查看自身行及所有admins的行
        OR
        (
            (SELECT role FROM dbo.[user] WHERE username = USER_NAME()) = 'ceos'
            AND (@username = USER_NAME() OR @role = 'admins')
        )
);

逻辑说明 #

1. 先通过(SELECT role FROM dbo.[user] WHERE username = USER_NAME())获取当前登录用户的角色
2. 根据当前用户的角色，分别匹配对应的访问规则：
   • 普通成员只能看自己的行
   • 管理员能看自己的行，以及所有角色为members的行
   • CEO能看自己的行，以及所有角色为admins的行

注意事项 #

• 确保USER_NAME()返回的是user表中存储的用户名（比如SQL登录名或Windows登录名，要和表中username字段值一致）
• 记得重新绑定这个函数到行级安全策略上，执行类似以下语句：

ALTER SECURITY POLICY YourSecurityPolicyName
ADD FILTER PREDICATE Security.fn_securitypredicate3(username, role)
ON dbo.[user]
WITH (STATE = ON);

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