SQL Server WHERE CASE子查询多值错误的权限查询方案

现有3张关联业务表需实现线索查看权限控制：

• clients：客户表
• users：用户表，用户分为Admin（管理员）、User（普通用户）两类，Users.Client字段多对一关联Clients.Id
• leads：线索表，Leads.CreatedBy字段多对一关联Users.Username

权限规则：

• 普通用户仅能查看自己创建的线索
• 管理员可查看自身所属客户下的所有线索，无权查看其他客户的线索

表结构定义 #

CREATE TABLE Clients (
  Id INT IDENTITY PRIMARY KEY,
  Name VARCHAR(32) NOT NULL
);

CREATE TABLE Users (
  Id INT,
  Username VARCHAR(32) NOT NULL,
  Type VARCHAR(8) NOT NULL CHECK (Type IN ('Admin', 'User')),
  Client INT NOT NULL,
  PRIMARY KEY (Username),
  UNIQUE (id),
  FOREIGN KEY (Client) REFERENCES Clients (Id)
);

CREATE TABLE Leads (
  Id INT IDENTITY PRIMARY KEY,
  Name VARCHAR(64),
  Company VARCHAR(64),
  Profession VARCHAR(64),
  CreatedBy VARCHAR(32) NOT NULL,
  FOREIGN KEY (CreatedBy) REFERENCES Users (Username)
);

原写法问题 #

最初尝试用CASE表达式拼接查询条件时触发报错：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

报错核心原因是CASE属于标量表达式，仅支持返回单个值，管理员场景下同客户会对应多个用户，子查询返回多值自然触发错误。如果去掉CASE直接按客户匹配，又会导致普通用户越权看到同客户下所有线索，不符合权限要求。

测试数据 #

SET IDENTITY_INSERT Clients ON;

INSERT INTO Clients (Id, Name)
  VALUES
(1, 'IDM'),
(2, 'FooCo')
;

SET IDENTITY_INSERT Clients OFF;

INSERT INTO Users (Id, Username, Type, Client)
  VALUES
(1, 'Sathar', 'Admin', 1),
(2, 'bafh', 'Admin', 1),
(3, 'fred', 'User', 1),
(4, 'bloggs', 'User', 1),
(5, 'jadmin', 'Admin', 2),
(6, 'juser', 'User', 2)
;


INSERT INTO Leads (Name, Company, Profession, CreatedBy)
  VALUES
('A. Person', 'team lead', 'A Co', 'Sathar'),
('A. Parrot', 'team mascot', 'B Co', 'Sathar'),

('Alice Adams', 'analyst', 'C Co', 'juser'),
('"Bob" Dobbs', 'Drilling Equipment Salesman', 'D Co', 'juser'),
('Carol Kent', 'consultant', 'E Co', 'juser'),

('John Q. Employee', 'employee', 'F Co', 'fred'),
('Jane Q. Employee', 'employee', 'G Co', 'fred'),

('Bob Howard', 'Detached Special Secretary', 'Capital Laundry Services', 'jadmin')
;

直接通过关联用户表做布尔条件判断，逻辑清晰且性能更好，将@CurrentUsername替换为实际登录的用户名即可：

DECLARE @CurrentUsername VARCHAR(32) = 'Sathar';

SELECT l.*
FROM Leads l
-- 关联线索创建人信息
INNER JOIN Users create_user 
  ON l.CreatedBy = create_user.Username
-- 关联当前登录用户信息
INNER JOIN Users current_user 
  ON current_user.Username = @CurrentUsername
WHERE
  -- 普通用户仅看自己创建的线索
  (current_user.Type = 'User' AND l.CreatedBy = @CurrentUsername)
  -- 管理员看所属客户下所有线索
  OR (current_user.Type = 'Admin' AND create_user.Client = current_user.Client)

效果验证 #

• 传入客户1的管理员Sathar，仅返回客户1下所有用户创建的4条线索，看不到客户2的线索
• 传入客户1的普通用户fred，仅返回fred自己创建的2条线索，看不到同客户其他用户的线索
• 传入客户2的管理员jadmin，仅返回客户2下所有用户创建的4条线索，看不到客户1的线索
  完全符合权限规则要求。

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