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已被授予50的访问级别给组1，该级别会应用，除非有其他规则授予了更具体的物品访问权限。

我需要一个查询，返回一个特定用户访问级别的东西列表（仅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)
    )

但是返回了多行，而我只想为每个“things”表中的行返回一个。我不确定如何将其减少到每个“thing”为一个行，或如何优先考虑“things”规则以取代“group”规则。

如果有帮助的话，我使用的数据库是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

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

Answer 2

我昨晚刚读了一篇关于这个的论文，其中提出了一些实现方法。如果您无法使用标题上的链接，请尝试在 Hippocratic 数据库中限制披露上使用 Google 学术搜索。

Answer 3

虽然有几个好答案，但最有效的可能是这样的：

SELECT things.id, things.group_id, things.name, max(access) 
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)
        )
group by things.id, things.group_id, things.name

只需简单地使用摘要信息加到您的查询中，就可以获得您所需要的内容。

Answer 4

托尼：

不错的解决方案，我喜欢它，看起来能够发挥作用。这是您稍作调整后的查询：

SELECT 
    things.*, 
    coalesce ( 
        (   SELECT access 
            FROM access 
            WHERE user_id = 1 
                AND type =  thing  
                AND object_id = things.id
        ), 
        (   SELECT access 
            FROM access 
            WHERE user_id = 1 
                AND type =  group  
                AND object_id = things.group_id 
        ) 
    ) AS access
    FROM things;

结果看起来正确：

 id | group_id |  name   | access 
----+----------+---------+--------
  1 |        1 | thing 1 |     10
  2 |        1 | thing 2 |    100
  3 |        1 | thing 3 |     50
  4 |        1 | thing 4 |     50
  5 |        2 | thing 5 |

我完全理解这个方案并不是理想的观点。然而，我在某种程度上还是被困在其中。

约瑟夫

你的解决方案与我玩弄的东西非常相似，我的直觉告诉我应该可以用这种方法做到。不幸的是，它不能完全产生正确的结果：

 id | group_id |  name   | max 
----+----------+---------+-----
  1 |        1 | thing 1 |  50
  2 |        1 | thing 2 | 100
  3 |        1 | thing 3 |  50
  4 |        1 | thing 4 |  50
  5 |        2 | thing 5 |

对于物品1，访问级别已取高组访问值，而不是更具体的访问值10，这正是我想要的。我不认为在GROUP BY中有修复的方法，但如果有人有任何建议，我很乐意在这一点上被证明是错误的。

友情链接