如何通过 SQL 表进行访问控制?
我正在尝试创建一个访问控制系统。
下面是我试图控制访问的表的一个精简示例:
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。
如果我遗漏了任何信息,请随时发表评论。
提前致谢!
I'm trying to create an access control system.
Here's a stripped down example of what the table I'm trying to control access to looks like:
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
And the access control table looks like this:
access table:
user_id type object_id access
1 group 1 50
1 thing 1 10
1 thing 2 100
Access can be granted either by specifying the id of the 'thing' directly, or granted for an entire group of things by specifying a group id. In the above example, user 1 has been granted an access level of 50 to group 1, which should apply unless there are any other rules granting more specific access to an individual thing.
I need a query that returns a list of things (ids only is okay) along with the access level for a specific user. So using the example above I'd want something like this for user 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)
The closest I can come up with is this:
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)
)
But that returns multiple rows, when I only want one for each row in the 'things' table. I'm not sure how to get down to a single row for each 'thing', or how to prioritise 'thing' rules over 'group' rules.
If it helps, the database I'm using is PostgreSQL.
Please feel free to leave a comment if there's any information I've missed out.
Thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我不知道 Postgres SQL 方言,但可能是这样的:
顺便说一句,我不喜欢这个设计。 我希望将访问表分成两部分:
然后我的查询变为:
我更喜欢这样,因为现在可以在访问表中使用外键。
I don't know the Postgres SQL dialect, but maybe something like:
Incidentally, I don't like the design. I would prefer the access table to be split into two:
My query then becomes:
I prefer this because foreign keys can now be used in the access tables.
我昨晚刚刚读到一篇关于此的论文。 它对如何做到这一点有一些想法。 如果您无法使用标题上的链接,请尝试使用 Google 学术搜索希波克拉底限制披露数据库。
I just read a paper last night on this. It has some ideas on how to do this. If you can't use the link on the title try using Google Scholar on Limiting Disclosure in Hippocratic Databases.
虽然有几个很好的答案,但最有效的可能是这样的:
它只是使用添加到查询中的摘要来获取您正在寻找的内容。
While there are several good answers, the most efficient would probably be something like this:
Which simply uses summarization added to you query to get what you're looking for.
托尼:
不错的解决方案,我喜欢它,似乎有效。 这是经过细微调整后的查询:
结果看起来是正确的:
我完全认为它不是一个理想的模式。 然而,我在某种程度上坚持了下来。
约瑟夫:
你的解决方案与我正在玩的东西非常相似,我的直觉(例如它们)告诉我应该可以这样做。 不幸的是,它并没有产生完全正确的结果:
“事物 1”的访问级别采用了更高的“组”访问值,而不是更具体的“事物”访问值 10,这正是我所追求的。 我认为没有办法在 GROUP BY 中解决这个问题,但如果有人有任何建议,我非常乐意在这一点上被证明是不正确的。
Tony:
Not a bad solution, I like it, seems to work. Here's your query after minor tweaking:
And the results look correct:
I do completely take the point about it not being an ideal schema. However, I am stuck with it to some extent.
Josef:
Your solution is very similar to the stuff I was playing with, and my instincts (such as they are) tell me that it should be possible to do it that way. Unfortunately it doesn't produce completely correct results:
The access level for 'thing 1' has taken the higher 'group' access value, rather than the more specific 'thing' access value of 10, which is what I'm after. I don't think there's a way to fix that within a
GROUP BY
, but if anyone has any suggestions I'm more than happy to be proven incorrect on that point.