代码之家 › 专栏 › 技术社区 › Dan Aditi

如何通过SQL表进行访问控制?

access-control postgresql sql

Dan Aditi · 技术社区 · 16 年前

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

下面是一个简化的示例,说明我试图控制访问的表的外观:

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)
    )

但当我只需要为“things”表中的每一行返回一行时,返回多行。我不知道如何对每一个“事物”只进行一行,或者如何将“事物”规则优先于“群体”规则。

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

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

事先谢谢!

4 回复 | 直到 16 年前

Tony Andrews 16 年前

我不知道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

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

tvanfosson 16 年前

我昨晚刚刚看了一篇关于这个的论文。它对如何做到这一点有一些想法。如果您不能使用标题上的链接,请尝试在 Limiting Disclosure in Hippocratic Databases.

Josef 16 年前

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

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

它只是使用添加到查询中的摘要来获取所需内容。

Dan Aditi 16 年前

托尼:

一个不错的解决方案,我喜欢它,似乎奏效了。下面是您在小调整后的查询:

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 |

我愿意 完全地 请注意,它不是一个理想的模式。但是,在某种程度上,我还是坚持着。

Josef:

你的解决方案和我玩的东西非常相似,我的直觉告诉我应该可以这样做。不幸的是,它不能产生完全正确的结果:

 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 |

“thing 1”的访问级别采用了更高的“group”访问值,而不是更具体的“thing”访问值10,这正是我所追求的。我认为没有办法在 GROUP BY 但是如果有人有任何建议,我很高兴在这一点上被证明是错误的。