sql-server - 基于用户/角色组合的 SQL 过滤结果

Question

鉴于下表，我需要根据哪个用户进行呼叫以及由 user_id/role 组合组成的其他（可选）过滤器来过滤事物数据。

在任何时候，除非他们具有完全访问权限，否则用户应该只接收与他们链接的事物的结果。附加过滤器是AND过滤器，这意味着结果应满足所有过滤器。

参数 @user_id、@has_full_access 和 user_id/role 过滤器使用 Dapper 传递。

CREATE TABLE users
(
    id int NOT NULL
    CONSTRAINT PK_users PRIMARY KEY CLUSTERED (id)
)

CREATE TABLE user_roles
(
    user_id int NOT NULL,
    role varchar(10) NOT NULL
    CONSTRAINT FK_user_roles_users FOREIGN KEY(user_id) REFERENCES users(id)
)

CREATE TABLE things
(
    id int NOT NULL
    CONSTRAINT PK_things PRIMARY KEY CLUSTERED (id)
)

CREATE TABLE thing_permissions
(
    thing_id int NOT NULL,
    user_id int NOT NULL,
    role varchar(10) NOT NULL
    CONSTRAINT FK_thing_permissions_things FOREIGN KEY(thing_id) REFERENCES things(id),
    CONSTRAINT FK_thing_permissions_users FOREIGN KEY(user_id) REFERENCES users(id)
)

INSERT INTO users VALUES (1)
INSERT INTO users VALUES (2)
INSERT INTO users VALUES (3)
INSERT INTO users VALUES (4)
INSERT INTO users VALUES (5)

INSERT INTO user_roles VALUES (1, 'Admin')
INSERT INTO user_roles VALUES (2, 'Creator')
INSERT INTO user_roles VALUES (2, 'Owner')
INSERT INTO user_roles VALUES (3, 'Creator')
INSERT INTO user_roles VALUES (3, 'Owner')
INSERT INTO user_roles VALUES (4, 'Creator')
INSERT INTO user_roles VALUES (5, 'Owner')

INSERT INTO things VALUES (1)
INSERT INTO things VALUES (2)
INSERT INTO things VALUES (3)
INSERT INTO things VALUES (4)
INSERT INTO things VALUES (5)

INSERT INTO thing_permissions VALUES (1, 2, 'Creator')
INSERT INTO thing_permissions VALUES (1, 3, 'Creator')
INSERT INTO thing_permissions VALUES (1, 2, 'Owner')
INSERT INTO thing_permissions VALUES (2, 2, 'Creator')
INSERT INTO thing_permissions VALUES (2, 5, 'Owner')
INSERT INTO thing_permissions VALUES (3, 4, 'Creator')
INSERT INTO thing_permissions VALUES (3, 3, 'Owner')
INSERT INTO thing_permissions VALUES (3, 5, 'Owner')
INSERT INTO thing_permissions VALUES (4, 3, 'Creator')
INSERT INTO thing_permissions VALUES (4, 5, 'Owner')
INSERT INTO thing_permissions VALUES (5, 2, 'Creator')

以下是各种输入组合的一些示例以及预期结果。

--Scenario 1:
--Expected Results: 1, 2, 3, 4, 5
DECLARE @user_id int = 1
DECLARE @has_full_access bit = 1
DECLARE @filters TABLE (user_id int, [role] varchar(10))

--Scenario 2:
--Expected Results: 1, 2, 5
DECLARE @user_id int = 2
DECLARE @has_full_access bit = 0
DECLARE @filters TABLE (user_id int, [role] varchar(10))

--Scenario 3:
--Expected Results: 1
DECLARE @user_id int = 1
DECLARE @has_full_access bit = 1
DECLARE @filters TABLE (user_id int, [role] varchar(10))
INSERT INTO @filters VALUES (2, 'Creator')
INSERT INTO @filters VALUES (2, 'Owner')

--Scenario 4:
--Expected Results: 3
DECLARE @user_id int = 1
DECLARE @has_full_access bit = 1
DECLARE @filters TABLE (user_id int, [role] varchar(10))
INSERT INTO @filters VALUES (3, 'Owner')
INSERT INTO @filters VALUES (5, 'Owner')

--Scenario 5:
--Expected Results: 1
DECLARE @user_id int = 2
DECLARE @has_full_access bit = 0
DECLARE @filters TABLE (user_id int, [role] varchar(10))
INSERT INTO @filters VALUES (3, 'Creator')

--Scenario 6: 
--Expected Results: no results
DECLARE @user_id int = 1
DECLARE @has_full_access bit = 1
DECLARE @filters TABLE (user_id int, [role] varchar(10))
INSERT INTO @filters VALUES (2, 'Creator')
INSERT INTO @filters VALUES (4, 'Creator')

这是一个带有设置的 SQL Fiddle。

目前，我有以下函数，它返回所有的东西以及用户链接的角色。

FUNCTION GetMyThings (@user_id INT, @has_full_access BIT)
RETURNS TABLE
AS
RETURN
(
    SELECT t.id, 'Admin' AS role
    FROM things t
    WHERE @has_full_access = 1

    UNION

    SELECT t.id, tp.role
    FROM things t
        INNER JOIN thing_permissions tp ON tp.thing_id = t.id
    WHERE tp.user_id = @user_id
)

我使用此函数来获取调用用户可以访问的内容以及过滤器中每个用户的内容列表。最后，我返回了这两个数据集中的结果。

DECLARE @my_things TABLE (id INT) 
INSERT INTO @my_things SELECT id FROM GetMyThings(@user_id, @has_full_access)

DECLARE @filtered_things TABLE (id INT) 
INSERT INTO @filtered_things SELECT ft.id FROM @filters f CROSS APPLY (SELECT DISTINCT id, role FROM GetMyThings(f.user_id, 0)) ft WHERE ft.role = f.role GROUP BY ft.id HAVING COUNT(ft.id) >= (SELECT COUNT(user_id) FROM @filters)

DECLARE @has_filter BIT = (SELECT has_filter = CASE WHEN (COUNT(user_id) > 0) THEN 1 ELSE 0 END FROM @filters)
DECLARE @final_things TABLE (id INT) 
INSERT INTO @final_things SELECT id FROM @my_things WHERE @has_filter = 0 OR id IN (SELECT id FROM @filtered_things)

SELECT * FROM @final_things

有没有更好的方法来做到这一点？我的解决方案有效，但对于更大的数据集，与从原始数据中选择相比，该函数似乎减慢了查询速度。

我也尝试过使用视图，但因为我需要 @has_full_access 参数并将 SELECTs UNIONed 分开，所以我无法为每个 SELECT 添加 WHERE。

标签： sql-server