MySQL 设计用户角色和权限系统的最佳实践？

Best Practice for Designing User Roles and Permission System?

提问by hash

I need to add user roles and permission system into my web application built using PHP/MySQL. I want to have this functionality:

我需要将用户角色和权限系统添加到我使用 PHP/MySQL 构建的 Web 应用程序中。我想要这个功能：

1. One root user can create sub-roots, groups, rules and normal users( all privileges) .
2. Sub-roots can create only rules, permissions and users for his/her own group ( no groups).
3. A user can access either content created by him or his group, based on the permission assigned to him, by group root.

1. 一个 root 用户可以创建子根、组、规则和普通用户（所有权限）。
2. 子根只能为其自己的组（无组）创建规则、权限和用户。
3. 用户可以访问由他或他的组创建的内容，基于分配给他的权限，通过组 root。

I need the system to be flexible enough, so that new roles and permissions are assigned to content.

我需要系统足够灵活，以便为内容分配新的角色和权限。

I have a userstable storing group key along with other information. Currently I am using two feilds in each content table i.e. createdByand CreatedByGroup, and using that as the point whether a certain user has permissions. But its not flexible enough, because for every new content, I have to go throug all data updates and permission updates. Please help me by discussing your best practices for schema design.

我有一个users存储组密钥和其他信息的表。目前，我在每个内容表中使用了两个字段，即createdBy和CreatedByGroup，并将其用作某个用户是否具有权限的点。但它不够灵活，因为对于每个新内容，我都必须通过所有数据更新和权限更新。请通过讨论您的架构设计最佳实践来帮助我。

采纳答案by Eran Galperin

The pattern that suits your needs is called role-based access control.

适合您需求的模式称为基于角色的访问控制。

There are several good implementations in PHP, including Zend_Acl(good documenation), phpGACLand TinyACL. Most frameworks also have their own implementations of an ACL in some form.

PHP 中有几个很好的实现，包括Zend_Acl（好的文档）、phpGACL和TinyACL。大多数框架也有自己的某种形式的 ACL 实现。

Even if you choose to roll your own, it'll help you to review well factored solutions such as those.

即使您选择推出自己的解决方案，它也会帮助您查看诸如此类的经过充分考虑的解决方案。

回答by Suresh Kamrushi

I Think bitwise operator are the best way to implement user permission. Here I am showing how we can implement it with MySQL.

我认为按位运算符是实现用户权限的最佳方式。在这里，我将展示如何使用 MySQL 实现它。

Below is a sample tables with some sample data:

下面是带有一些示例数据的示例表：

Table 1: Permission table to store permission name along with it bit like 1, 2, 4, 8.. etc (multiple of 2)

表 1：存储权限名称的权限表，有点像 1、2、4、8 等（2 的倍数）

CREATE TABLE IF NOT EXISTS `permission` (
  `bit` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some sample data into the table.

在表中插入一些示例数据。

INSERT INTO `permission` (`bit`, `name`) VALUES
(1, 'User-Add'),
(2, 'User-Edit'),
(4, 'User-Delete'),
(8, 'User-View'),
(16, 'Blog-Add'),
(32, 'Blog-Edit'),
(64, 'Blog-Delete'),
(128, 'Blog-View');

Table 2: User table to store user id,name and role. Role will be calculated as sum of permissions.
Example:

表 2：存储用户 ID、名称和角色的用户表。角色将被计算为权限的总和。
例子：

If user 'Ketan' having permission of 'User-Add' (bit=1) and 'Blog-Delete' (bit-64) so role will be 65 (1+64).
If user 'Mehata' having permission of 'Blog-View' (bit=128) and 'User-Delete' (bit-4) so role will be 132 (128+4).

如果用户 'Ketan' 拥有 'User-Add' (bit=1) 和 'Blog-Delete' (bit-64) 的权限，那么角色将是 65 (1+64)。
如果用户 'Mehata' 拥有 'Blog-View' (bit=128) 和 'User-Delete' (bit-4) 权限，那么角色将是 132 (128+4)。

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `role` int(11) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Sample data-

样本数据-

INSERT INTO `user` (`id`, `name`, `role`, `created_date`)
   VALUES (NULL, 'Ketan', '65', '2013-01-09 00:00:00'),
   (NULL, 'Mehata', '132', '2013-01-09 00:00:00');

Loding permission of user After login if we want to load user permission then we can query below to get the permissions:

用户的加载权限登录后如果我们想加载用户权限，那么我们可以在下面查询以获取权限：

SELECT permission.bit,permission.name  
   FROM user LEFT JOIN permission ON user.role & permission.bit
 WHERE user.id = 1

Here user.role "&" permission.bit is a Bitwise operator which will give output as -

这里 user.role "&" permission.bit 是一个按位运算符，它将输出为 -

User-Add - 1
Blog-Delete - 64

If we want to check weather a particular user have user-edit permission or not-

如果我们想检查特定用户是否拥有用户编辑权限的天气 -

  SELECT * FROM `user` 
     WHERE role & (select bit from permission where name='user-edit')

Output = No rows.

输出 = 无行。

You can see also: http://sforsuresh.in/implemention-of-user-permission-with-php-mysql-bitwise-operators/

您还可以看到：http: //sforsuresh.in/implemention-of-user-permission-with-php-mysql-bitwise-operators/

回答by faulty

I had a slightly different structure, but it should be able to serve as a reference.

我的结构略有不同，但应该可以作为参考。

Each user has a 'Role', 'GroupID' associate with it, and Group table for which the GroupID refers to. Then I have 3 permission table.

每个用户都有一个“角色”、与其关联的“群组 ID”以及群组 ID 所指的群组表。然后我有 3 个权限表。

PermissionMaster(FormName)

PermissionChild(PermissionMasterID, PermissionName, Desc, DefaultValue, DependOn)and

PermissionChild(PermissionMasterID, PermissionName, Desc, DefaultValue, DependOn)和

PermissionGroupChild(GroupID, PermissionChildID, Allow)

PermissionMaster holds the name/form/module for which the permission refers to. PermissionChild will list all the possible permission available for each Master, such as 'Create', 'View', 'Edit', 'Delete', and description (I didn't have this on the first version, and it started to get confusing when there's too many permission setup even for 1 module). I allow adding more children to specifically refer to some function like 'ChangeTimeStamp', which would also allow more specific permission then 'Edit'

PermissionMaster 持有权限所指的名称/表单/模块。PermissionChild 将列出每个 Master 可用的所有可能的权限，例如“创建”、“查看”、“编辑”、“删除”和描述（我在第一个版本中没有这个，它开始变得混乱当即使是 1 个模块的权限设置过多时）。我允许添加更多子项来专门引用某些功能，例如“ChangeTimeStamp”，这也将允许比“编辑”更具体的权限

Then PermissionGroupChild is the link between PermissionChild and Group table. Every group will have a set of PermissionChild copied and set with default setting. Then I had a permission class which does the table query and check for each user. I only load it during login. Then in every form/module, I check for it's appropriate permission and applies the UI properly.

那么 PermissionGroupChild 就是 PermissionChild 和 Group 表之间的链接。每个组都会复制一组 PermissionChild 并使用默认设置进行设置。然后我有一个权限类，它执行表查询并检查每个用户。我只在登录时加载它。然后在每个表单/模块中，我检查它是否具有适当的权限并正确应用 UI。

As for role, I only use it at the Login configuration page. Smaller Role value means higher ranked. So user can only see itself and those of Role value higher than itself. He/she can edit those of lower rank than itself but not similar.

至于角色，我只在登录配置页面使用。较小的角色值意味着较高的排名。所以用户只能看到自己和高于自己的角色值。他/她可以编辑比自己级别低但不相似的那些。

回答by Neil Barnwell

You might not want groups of permissions. Instead create user groups, give user groups permissions, and put users in groups. Users should also be able to override permissions from the groups they are in. Deny should always override grant where a user is in more than one group with the pemission.

您可能不需要权限组。而是创建用户组，授予用户组权限，并将用户放入组中。用户还应该能够覆盖他们所在组的权限。拒绝应该始终覆盖用户在多个具有许可的组中的授权。

In summary:

总之：

• User has zero or more permissions (grany, deny)
• User is in zero or more groups
• Group has zero or more permissions (grant, deny)

• 用户拥有零个或多个权限（gray、deny）
• 用户在零个或多个组中
• 组具有零个或多个权限（授予、拒绝）

回答by Meloman

I have groups and users (like active directory LDAP solution). So if I give access to group I need that users in this group have herited accesses.

我有组和用户（如活动目录 LDAP 解决方案）。因此，如果我授予组访问权限，则需要该组中的用户具有继承访问权限。

So, based on the @suresh-kamrushi answer below, I made this :

因此，基于下面的@suresh-kamrushi 回答，我做了这个：

INSERT INTO `permission` (`bit`, `name`) VALUES
(1,   'add-yes'),
(2,   'add-no'),
(4,   'edit-yes'),
(8,   'edit-no'),
(16,  'del-yes'),
(32,  'del-no'),
(64,  'view-yes'),
(128, 'view-no');

If user have bit 00000000, I take first two digits 00that means add-yesand add-noare herited from group permissions.

如果用户有 bit 00000000，我取前两位数字00，这意味着add-yes并add-no继承自组权限。

If user have bit 01010110, I take first two digits 01that means add-nowill prime on group permissions, so this user have no add permission. This bitwise says that user can only view.

如果用户有 bit 01010110，我取前两位数字01，这意味着add-no将使用组权限，因此该用户没有添加权限。这按位表示用户只能查看。

It's also working with parent groups.

它还与家长团体合作。

What do you think about this solution ? Does anyone have got better way for that ?

你怎么看这个解决方案？有没有人有更好的方法？