循环成员资格层次结构,找出用户的权限

发布于 2024-11-25 02:47:25 字数 1090 浏览 0 评论 0原文

我对下面的问题感到疯狂。
有用户和组,组中可以同时包含用户和其他组:
创建表 GroupMembership(GroupMembershipID、GroupID、GroupTypeID、MemberID、MemberTypeID)
字段 GroupTypeIDMebmerTypeID 需要提供类型,因为我有各种类型的组和成员。
让我将我命名为吉姆,我有一个“吉姆的朋友”组,其中包含用户鲍勃、简以及“杰瑞的朋友”组。同时,杰瑞有一个“杰瑞的朋友”组,其中包括史蒂夫,克洛伊有一个“吉姆的组”。正如我们现在所看到的,我和 Jerry 之间存在循环引用。每个人都说层次结构中的循环是一个错误。我的情况呢?有办法避免循环吗?

现在第二个问题。 我有一个表 Permission ,它描述了主体对某些对象的权限:
创建表权限(PermissionID、SubjectID、SubjectTypeID、ObjectID、ObjectTypeID、PermissionTypeID、[Value])
每个主体(用户、组或其他)都对某个对象具有明确的权限。 最后,为了找出某个用户对某个对象的权限,我必须渲染所有用户的成员资格树(通过 GroupMembership),然后将其加入到权限并确定最接近的(在 GroupMembership 层次结构中)和最佳权限。< br> 看起来这是一种常见的方式,不是吗?

因此,在这个范围内我需要解决接下来的任务:
1) 查询用户对某个对象的权限
2) 找出某个用户允许的所有对象
3) 找出对某个对象有显式权限的所有用户
看起来这是一个常见的任务,对吧?

首先,我想创建一些通用视图,它代表一个主题、一个对象以及该对象的实际权限(记住 GroupMembership 中的层次结构!)。它可以让我轻松查询它或将其他查询加入其中。我已经尝试进行正确的选择很多天了,但这对一项任务很有好处,而对另一项任务则非常糟糕(有完整扫描等)。如果我制作函数也会出现同样的情况。现在我已经彻底崩溃了:(我不知道解决这些任务的好方法是什么。架构和任务看起来都很常见,但我在互联网上找不到类似问题的解决方案。
您能否提供链接、建议或指出我的错误?
非常感谢,
吉姆.

I'm crazy of the problem below.
There are users and groups, and the groups can contain both users and other groups:
CREATE TABLE GroupMembership (GroupMembershipID, GroupID, GroupTypeID, MemberID, MemberTypeID)
Fields GroupTypeID and MebmerTypeID are need to provide types because i have various types of groups and members.
Let name me as Jim and I have a group "Jim's friends" which contains users Bob, Jane and also group "Jerry's friends". At the same time Jerry has group "Jerry's friends" containing Steve, Chloe an "Jim's group". As we can see now there is a loop reference between me and Jerry. Everyone says that loops in hierarchies is an error. What about my case? Is there way to avoid loop?

Now the second question.
I have a table Permission which describes subject's permissions to certain objects:
CREATE TABLE Permission (PermissionID, SubjectID, SubjectTypeID, ObjectID, ObjectTypeID, PermissionTypeID, [Value])
Every subject (user, group or something else) has explicit permission to a certain object.
At the end point, to find out the rights of a certain user for certain object, I must render all user's membership tree (via GroupMembership), than join it to Permission and determine the closest (in GroupMembership hierarchy) and the best permission.
Seems like it's a common way, isn't it?

So, in this scope I need to solve next tasks:
1) Find out user's permission to the certain object
2) Find out all objects which are permitted for certain user
3) Find out all users who has explicit permissions for a certain object
Seems like it's a common task, right?

At the first I wanted to create some universal view which represents a subject, an object and the actual permission for that object (remember hierarchy in GroupMembership!). It would let me easily query it or join other queries to it. I've been trying to make proper SELECT for many days but it was good for one task and very bad for another (there were full scans etc). The same situation if I make functions. Now i'm broken with it all :( I don't know what it the good way to solve that tasks. The architecture and the tasks both look common but I can't find solutions for similar problems in the internet.
Could you please give links, advises or point to my errors?
Very thankful,
Jim.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

壹場煙雨 2024-12-02 02:47:25

你实际上无法在 SQL 中做到这一点。一群中的一群?没有办法生成可以遍历它的查询。在 SQL 之外执行此操作。

您也许可以: 组:可以包含用户(但不能包含组)。超级组:可以包含组,但不能包含超级组或用户。这样会容易处理很多。

另外,如果您坚持自己的结构,请勿将用户和组放在同一个表中。创建两个表:一个用于组中的组的表,一个用于组中的用户的表。

像你这样的组合对于 SQL 来说是一场噩梦。

You can't really do this in SQL. A group in a group? There is no way to generate a query that can walk that. Do it outside SQL.

You may be able to make: Groups: can contain users (but not groups). Supergroup: Can contain groups, but not supergroups or users. That will be a lot easier to handle.

Also if you stick with your structure, don't put both users and groups in the same table. Make two tables: a table for the groups in a group, and a table for the users in a group.

A combo like you have is a nightmare for SQL.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文