MySQL ACL 的数据库架构

Database schema for ACL

提问by Xeoncross

I want to create a schema for a ACL; however, I'm torn between a couple of ways of implementing it.

我想为 ACL 创建一个架构；但是，我在实现它的几种方法之间左右为难。

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)

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.

然后我意识到我将只有大约 20 个资源，每个资源最多有 5 个操作（创建、更新、查看等）和另外 8 个角色。这意味着我可以公然无视数据规范化，因为我永远不会有超过几百个可能的记录。

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

So which of these is more correct? Are there other schema layouts for ACL?

那么这些中哪个更正确呢？ACL 是否还有其他架构布局？

回答by Denis de Bernardy

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.

例如，假设您正在设计社区的架构，并且您允许用户切换其个人资料的可见性。

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

一种选择是坚持公共/私人档案标志并坚持广泛的、先发制人的权限检查：“users.view”（查看公共用户）与“users.view_all”（查看所有用户，供版主查看） .

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.

另一个涉及更精细的权限，您可能希望他们能够配置内容，以便他们可以 (a) 所有人都可以查看，(b) 他们精心挑选的好友可以查看，(c) 完全保密，也许 (d) ) 除了他们精心挑选的 bozos 外，所有人都可以查看。在这种情况下，您需要为各个行存储与所有者/访问相关的数据，并且您需要对其中的一些内容进行大量抽象，以避免实现密集的定向图的传递闭包。

With either approach, I've found that added complexity in role editing/assignment is offset by the resulting ease/flexibility in assigningpermissions to individual pieces of data, and that the following to worked best:

无论采用哪种方法，我都发现角色编辑/分配中增加的复杂性被由此产生的为单个数据分配权限的简便性/灵活性所抵消，并且以下方法最有效：

1. Users can have multiple roles
2. Roles and permissions merged in the same table with a flag to distinguish the two (useful when editing roles/perms)
3. Roles can assign other roles, and roles and perms can assign permissions (but permissions cannot assign roles), from within the same table.

1. 用户可以有多个角色
2. 角色和权限合并在同一个表中，用一个标志来区分两者（编辑角色/权限时很有用）
3. 角色可以分配其他角色，角色和权限可以分配权限（但权限不能分配角色），从同一个表中。

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.

然后可以在两个查询中提取生成的定向图，使用您使用的任何语言在合理的时间内一劳永逸地构建，并缓存到 Memcache 或类似内容中以供后续使用。

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.

从那里，拉取用户的权限就是检查他拥有哪些角色，并使用权限图处理它们以获得最终权限。通过验证用户是否具有指定的角色/权限来检查权限。然后根据该权限检查运行您的查询/发出错误。

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.

如果需要，您可以扩展对单个节点的检查（即check_perms($user, 'users.edit', $node)“可以编辑此节点”与check_perms($user, 'users.edit')“可以编辑节点”），并且最终用户将拥有非常灵活/易于使用的东西。

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.

正如开头的例子应该说明的那样，小心不要过多地转向行级权限。性能瓶颈不在于检查单个节点的权限，而在于拉取有效节点列表（即只有用户可以查看或编辑的节点）。如果您不是（非常）精通查询优化，我建议您不要在行本身中使用 flags 和 user_id 字段以外的任何内容。

回答by Mike Sherrill 'Cat Recall'

This means that I can exercise blatant disregard for data normalization as I will never have more than a couple hundred possible records.

这意味着我可以公然无视数据规范化，因为我永远不会有超过几百条可能的记录。

The number of rows you expect isn't a criterion for choosing which normal form to aim for. Normalization is concerned with data integrity. It generally increases data integrity by reducing redundancy.

您期望的行数不是选择要针对哪种范式的标准。规范化与数据完整性有关。它通常通过减少冗余来提高数据完整性。

The real question to ask isn't "How many rows will I have?", but "How important is it for the database to always give me the right answers?" For a database that will be used to implement an ACL, I'd say "Pretty danged important."

要问的真正问题不是“我将有多少行？”，而是“数据库始终为我提供正确答案有多重要？” 对于将用于实现 ACL 的数据库，我会说“非常重要”。

If anything, a low number of rows suggests you don't need to be concerned with performance, so 5NF should be an easy choice to make. You'll want to hit 5NF before you add any id numbers.

如果有的话，行数少表明您不需要关心性能，因此 5NF 应该是一个容易做出的选择。在添加任何 ID 号之前，您需要先达到 5NF。

A query to figure out if 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)

That you wrote that as two queries instead of using an inner join suggests that you might be in over your head. (That's an observation, not a criticism.)

您将其编写为两个查询而不是使用内部联接表明您可能会不知所措。（这是观察，不是批评。）

SELECT p.* 
FROM permissions p
INNER JOIN resources r ON (r.id = p.resource_id AND 
                           r.name = ?)

回答by Johan

You can use a SET to assign the roles.

您可以使用 SET 来分配角色。

CREATE TABLE permission (
  id integer primary key autoincrement
  ,name varchar
  ,perm SET('create', 'edit', 'delete', 'view')
  ,resource_id integer );