ACL的数据库架构 [英] Database schema for ACL
问题描述
我想为ACL创建一个架构；但是，我在实现它的两种方法之间陷入了困境.
I want to create a schema for a ACL; however, I'm torn between a couple of ways of implementing it.
我很确定我不想处理级联权限，因为这会导致后端以及网站管理员感到困惑.
I am pretty sure I don't want to deal with cascading permissions as that leads to a lot of confusion on the backend and for site administrators.
我想我也可以只和一个角色一起与用户共处.这样的设置将允许在网站扩展时根据需要添加角色和权限，而不会影响现有的角色/规则.
I think I can also live with users only being in one role at a time. A setup like this will allow roles and permissions to be added as needed as the site grows without affecting existing roles/rules.
首先，我要对数据进行规范化，并使用三个表来表示关系.
At first I was going to normalize the data and have three tables to represent the relations.
ROLES { id, name }
RESOURCES { id, name }
PERMISSIONS { id, role_id, resource_id }
一个查询以确定是否允许某个地方的用户的查询看起来像这样:
A query to figure out whether a user was allowed somewhere would look like this:
SELECT id FROM resources WHERE name = ?
SELECT * FROM permissions WHERE role_id = ? AND resource_id = ? ($user_role_id, $resource->id)
然后我意识到我将只有大约20个资源，每个资源最多具有5个动作(创建，更新，查看等)，也许还有8个角色.这意味着我可以公然无视数据规范化，因为我永远不会拥有数百条可能的记录.
Then I realized that I will only have about 20 resources, each with up to 5 actions (create, update, view, etc..) and perhaps another 8 roles. This means that I can exercise blatant disregard for data normalization as I will never have more than a couple of hundred possible records.
所以也许这样的模式更有意义.
So perhaps a schema like this would make more sense.
ROLES { id, name }
PERMISSIONS { id, role_id, resource_name }
这将允许我在单个查询中查找记录
which would allow me to lookup records in a single query
SELECT * FROM permissions WHERE role_id = ? AND permission = ? ($user_role_id, 'post.update')
那么哪个更正确? ACL是否还有其他架构布局?
推荐答案
以我的经验，真正的问题主要是取决于是否会发生任何数量的特定于用户的访问限制.
In my experience, the real question mostly breaks down to whether or not any amount of user-specific access-restriction is going to occur.
例如，假设您正在设计社区的架构，并且允许用户切换其个人资料的可见性.
Suppose, for instance, that you're designing the schema of a community and that you allow users to toggle the visibility of their profile.
一种选择是坚持使用公开/私有配置文件标志，并坚持进行广泛的先占权限检查:"users.view"(查看公共用户)与"users.view_all"(查看所有用户，对于主持人).
One option is to stick to a public/private profile flag and stick to broad, pre-emptive permission checks: 'users.view' (views public users) vs, say, 'users.view_all' (views all users, for moderators).
另一个涉及更精细的权限，您可能希望他们能够配置事物，以便他们可以使自己(a)所有人可见，(b)亲手挑选的伙伴可见，(c)完全不公开，并且也许(d)除了他们亲手挑选的bozo之外，所有人都可以看见.在这种情况下，您需要为各个行存储与所有者/访问相关的数据，并且需要对其中的一些内容进行大量抽象，以避免实现密集的，面向图的传递闭包.
Another involves more refined permissions, you might want them to be able to configure things so they can make themselves (a) viewable by all, (b) viewable by their hand-picked buddies, (c) kept private entirely, and perhaps (d) viewable by all except their hand-picked bozos. In this case you need to store owner/access-related data for individual rows, and you'll need to heavily abstract some of these things in order to avoid materializing the transitive closure of a dense, oriented graph.
无论采用哪种方法，我发现角色编辑/分配中增加的复杂性都可以通过对单个数据的分配权限产生的轻松/灵活而抵消，并且以下各项效果最佳:
With either approach, I've found that added complexity in role editing/assignment is offset by the resulting ease/flexibility in assigning permissions to individual pieces of data, and that the following to worked best:
- 用户可以扮演多个角色
- 角色和权限在同一张表中合并在一起，并带有标记以区分两者(在编辑角色/权限时有用)
- 角色可以分配同一表中的其他角色，角色和权限可以分配权限(但权限不能分配角色).
然后可以在两个查询中提取结果定向图，使用您使用的任何一种语言在合理的时间内一次一劳永逸地构建，并将其缓存到Memcache或类似物中以供后续使用.
The resulting oriented graph can then be pulled in two queries, built once and for all in a reasonable amount of time using whichever language you're using, and cached into Memcache or similar for subsequent use.
从那里开始，获取用户的权限就是检查他拥有的角色，并使用权限图对其进行处理以获取最终权限.通过验证用户是否具有指定的角色/权限来检查权限.然后基于该权限检查运行查询/发出错误.
From there, pulling a user's permissions is a matter of checking which roles he has, and processing them using the permission graph to get the final permissions. Check permissions by verifying that a user has the specified role/permission or not. And then run your query/issue an error based on that permission check.
如果需要的话，您可以扩展对单个节点的检查(例如，check_perms($user, 'users.edit', $node)表示可以编辑此节点"，而check_perms($user, 'users.edit')表示可以编辑节点")，那么您将获得非常灵活/容易的操作供最终用户使用.
You can extend the check for individual nodes (i.e. check_perms($user, 'users.edit', $node) for "can edit this node" vs check_perms($user, 'users.edit') for "may edit a node") if you need to, and you'll have something very flexible/easy to use for end users.
正如开头的示例所示，请谨慎对待过多地针对行级权限.性能瓶颈在检查单个节点的权限方面要比拉出有效节点列表(即仅用户可以查看或编辑的节点)少.如果您不太(非常)精通查询优化，则建议不要在行本身内的标志和user_id字段之外进行任何操作.
As the opening example should illustrate, be wary of steering too much towards row-level permissions. The performance bottleneck is less in checking an individual node's permissions than it is in pulling a list of valid nodes (i.e. only those that the user can view or edit). I'd advise against anything beyond flags and user_id fields within the rows themselves if you're not (very) well versed in query optimization.
这篇关于ACL的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
