sql - 如何通过 SQL 表进行访问控制？

Question

我正在尝试创建一个访问控制系统。

这是我试图控制访问权限的表的精简示例:

things table:
id   group_id   name
1    1          thing 1
2    1          thing 2
3    1          thing 3
4    1          thing 4
5    2          thing 5

访问控制表是这样的:

access table:
user_id  type   object_id  access
1        group  1          50
1        thing  1          10
1        thing  2          100

可以通过直接指定“事物”的 ID 来授予访问权限，也可以通过指定组 ID 来授予整个事物组的访问权限。在上面的示例中，用户 1 已被授予组 1 的 50 访问级别，除非有任何其他规则授予对单个事物的更具体访问权限，否则应该适用。

我需要一个查询来返回事物列表(只有 ID 是可以的)以及特定用户的访问级别。因此，使用上面的示例，我希望用户 ID 1 像这样:

desired result:
thing_id   access
1          10
2          100
3          50   (things 3 and 4 have no specific access rule,
4          50   so this '50' is from the group rule)
5               (thing 5 has no rules at all, so although I 
                still want it in the output, there's no access
        level for it)

我能想到的最接近的是:

SELECT * 
FROM things 
LEFT JOIN access ON 
    user_id = 1
    AND (
        (access.type = 'group' AND access.object_id = things.group_id)
        OR (access.type = 'thing' AND access.object_id = things.id)
    )

但是这会返回多行，而我只想为“事物”表中的每一行返回一个。我不确定如何将每个“事物”归为一行，或者如何确定“事物”规则优先于“组”规则的优先级。

如果有帮助，我使用的数据库是 PostgreSQL。

如果我遗漏了任何信息，请随时发表评论。

提前致谢!

Answer 1

我不知道 Postgres SQL 方言，但可能是这样的:

select thing.*, coalesce ( ( select access
                             from   access
                             where  userid = 1
                             and    type = 'thing'
                             and    object_id = thing.id
                           ),
                           ( select access
                             from   access
                             where  userid = 1
                             and    type = 'group'
                             and    object_id = thing.group_id
                           )
                         )
from things

顺便说一下，我不喜欢这个设计。我希望将访问表分成两部分:

thing_access (user_id, thing_id, access)
group_access (user_id, group_id, access)

然后我的查询变成:

select thing.*, coalesce ( ( select access
                             from   thing_access
                             where  userid = 1
                             and    thing_id = thing.id
                           ),
                           ( select access
                             from   group_access
                             where  userid = 1
                             and    group_id = thing.group_id
                           )
                         )
from things

我更喜欢这个，因为现在可以在访问表中使用外键。