基于m_Customers权限表实现Customers表的行/列级权限控制查询

针对你的需求，我整理了基于m_Customers权限表的SQL查询方案，先明确基础数据和规则，再给出具体实现：

基础数据示例 #

Customers表数据 #

CustomerID FirstName MiddleInitial LastName
64 Abby A Garcia
65 Abby C Mehta
66 Abby E Chandra
67 Abby J Kapoor
68 Abby J Sanchez
69 Abby K Kovár
70 Abby L Sai
71 Abby M Lopez
72 Abby P Gonzalez
73 Abby P Rana

m_Customers权限定义表 #

MetadataID Field Type Access
1 Customers table "Group1","Group2","Group3"
2 MiddleInitial column "Group1"  -- 调整为仅Group1允许访问，匹配Group3无法查看该列的需求
3 18 row "Group3"  -- 调整为Group3禁止访问该行，匹配需求

注：我对权限表的Access列做了微调，确保和你的需求对齐：

• MiddleInitial列仅允许Group1访问（Group3不在列表中，无法查看）
• CustomerID=18的行禁止Group3访问（Group3在列表中，无法查看）

权限控制查询语句 #

假设我们可以通过变量@CurrentGroup获取当前用户所属的组（实际场景中可以替换为关联用户组表的逻辑，比如SUSER_SNAME()结合用户组映射），以下是实现权限控制的查询：

DECLARE @CurrentGroup VARCHAR(20) = 'Group3'; -- 替换为获取当前用户组的逻辑

SELECT
    CustomerID,
    FirstName,
    -- 列级权限：仅允许指定组查看MiddleInitial，否则返回NULL
    CASE
        WHEN EXISTS (
            SELECT 1 
            FROM m_Customers
            WHERE Type = 'column' 
              AND Field = 'MiddleInitial'
              AND ',' + REPLACE(Access, '"', '') + ',' LIKE '%,' + @CurrentGroup + ',%'
        ) THEN MiddleInitial
        ELSE NULL
    END AS MiddleInitial,
    LastName
FROM Customers
WHERE
    -- 表级权限：仅允许指定组访问整个表
    EXISTS (
        SELECT 1 
        FROM m_Customers
        WHERE Type = 'table' 
          AND Field = 'Customers'
          AND ',' + REPLACE(Access, '"', '') + ',' LIKE '%,' + @CurrentGroup + ',%'
    )
    -- 行级权限：排除当前组禁止访问的行
    AND NOT EXISTS (
        SELECT 1 
        FROM m_Customers
        WHERE Type = 'row' 
          AND Field = CAST(Customers.CustomerID AS VARCHAR(10))
          AND ',' + REPLACE(Access, '"', '') + ',' LIKE '%,' + @CurrentGroup + ',%'
    );

逻辑说明 #

1. 表级权限控制：通过EXISTS子查询验证当前组是否在Customers表的允许访问列表中。如果是Group4，该条件不满足，查询直接返回空结果，符合"无法查看任何数据"的要求。
2. 列级权限控制：使用CASE语句判断当前组是否有权限查看MiddleInitial列，无权限时返回NULL（也可以根据需求选择不返回该列，不过返回NULL更直观）。
3. 行级权限控制：通过NOT EXISTS子查询排除当前组被禁止访问的行，比如Group3会被过滤掉CustomerID=18的记录。
4. 组列表匹配：通过REPLACE去掉权限列的双引号，前后拼接逗号后用LIKE匹配，避免出现"Group12"被误判为"Group1"的情况。

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