php 如何设计基于层次角色的访问控制系统

How to design a hierarchical role based access control system

提问by Hailwood

Basic deal is, we have a custom built "kickstart" for our projects. For this we are looking at redoing the user control. I know there are a lot of questions out there about general rbac, but I cannot find any on hierarchical rbac?

基本交易是，我们为我们的项目定制了一个“kickstart”。为此，我们正在考虑重做用户控件。我知道有很多关于一般 rbac 的问题，但我在分层 rbac 上找不到任何问题？

Our requirements are:

我们的要求是：

• Roles can be assigned to group permissions
• If the role does not have a permission entry then it is automatically denied
• A user can be given overriding permissions
• A users overriding permissions is either a grant or deny
• If a user is explicitly denied a permission no matter what roles say "granted" the override wins.
• Users can have multiple roles
• Roles can have hierarchy
• Roles can inherit from other roles (e.g. A "Forum Super Moderator" role is a "Forum Moderator", and a "System Maintainer", and the "Forum Moderator" role already inherits from the "Forum User" role )
• Roles that inherit from another role that deny or grant a privilege override their child permission
• Permissions are grouped by "module" (e.g. a "Blog" module can have an "edit entry" permission, and a "Forum" module can have an "edit entry" permission and they will not clash)
• There is a "Everything and Anything" permission that automatically grants full access

• 角色可以分配给组权限
• 如果角色没有权限条目，则会自动拒绝
• 可以授予用户覆盖权限
• 用户覆盖权限是授予或拒绝
• 如果用户被明确拒绝权限，无论什么角色说“授予”，则覆盖获胜。
• 用户可以有多个角色
• 角色可以有层次结构
• 角色可以从其他角色继承（例如“论坛超级版主”角色是“论坛版主”和“系统维护者”，“论坛版主”角色已经从“论坛用户”角色继承）
• 从拒绝或授予特权的另一个角色继承的角色会覆盖其子权限
• 权限按“模块”分组（例如，“博客”模块可以具有“编辑条目”权限，“论坛”模块可以具有“编辑条目”权限，它们不会发生冲突）
• 有一个“一切和任何东西”权限，可以自动授予完全访问权限

So, with those requirements out of the way, here's how I am thinking of doing it.

因此，排除了这些要求，这就是我的想法。

Table: Users

表：用户

id            | int     | unique id

Table: Roles

表：角色

id            | int     | unique id
--------------|---------------------------------------------
title         | varchar | human readable name

Table: Permissions

表：权限

id            | int     | unique id
--------------|---------------------------------------------
module        | varchar | module name
--------------|---------------------------------------------
title         | varchar | human readable name
--------------|---------------------------------------------
key           | varchar | key name used in functions

Table: Role_User

表：Role_User

role_id       | int     | id from roles table
--------------|---------------------------------------------
user_id       | int     | id from users table

Table: Permission_Role

表：Permission_Role

id            | int     | unique id
--------------|---------------------------------------------
permission_id | int     | id from permissions table
--------------|---------------------------------------------
role_id       | int     | id from roles table
--------------|---------------------------------------------
grant         | tinyint | 0 = deny, 1 = grant

Table: Permission_User

表：Permission_User

id            | int     | unique id
--------------|---------------------------------------------
permission_id | int     | id from permissions table
--------------|---------------------------------------------
user_id       | int     | id from users table
--------------|---------------------------------------------
grant         | tinyint | 0 = deny, 1 = grant

Well, actually that's half of it, that part I am sure about, the part I am getting stuck on is the hierarchical roles.

嗯，实际上这就是它的一半，我确定的那部分，我陷入困境的部分是等级角色。

So, how do I design this? My idea is that to save on the database queries I am just going to build the permission matrix on login and save it to session so the queries don't have to be too simple as they are only run once for each login.

那么，我该如何设计呢？我的想法是，为了节省数据库查询，我只需要在登录时构建权限矩阵并将其保存到会话中，这样查询就不必太简单，因为每次登录只运行一次。

The issue I see is that, I am going to need to know the hierarchy of the roles so I can resolve the inherited roles permissions before I resolve the inheriting.

我看到的问题是，我需要知道角色的层次结构，以便在解决继承之前解决继承的角色权限。

The user permissions is the easy part, the per-user permissions are essentially the finally resolved group.

用户权限是简单的部分，每个用户的权限本质上是最终解析的组。

回答by BlitZ

There is a way to implement role inheritance by using recursive relation on table Roles, by making role reference to another record:

有一种方法可以通过在 table 上使用递归关系来实现角色继承Roles，通过将角色引用到另一条记录：

This relation will add 1 : ninheritance within Rolesrecord. You might obtain whole hierarchy tree with this stored function:

这种关系将1 : n在Roles记录中添加继承。您可以使用此存储函数获取整个层次结构树：

CREATE FUNCTION `getHierarchy`(`aRole` BIGINT UNSIGNED)
RETURNS VARCHAR(1024)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE `aResult` VARCHAR(1024) DEFAULT NULL;
DECLARE `aParent` BIGINT UNSIGNED;

SET `aParent` = (SELECT `parent` FROM `Roles` WHERE `id` = `aRole`);

WHILE NOT `aParent` IS NULL DO

    SET `aResult` = CONCAT_WS(',', `aResult`, `aParent`);
    SET `aParent` = (SELECT `parent` FROM `Roles` WHERE `id` = `aParent`);

END WHILE;

RETURN IFNULL(`aResult`, '');
END

Then, you might obtain all grantedpermissions with something like this:

然后，您可能会通过以下方式获得所有授予的权限：

SELECT
    `permission_id`
FROM
    `Permission_Role`
WHERE
    FIND_IN_SET(`role_id`, `getHierarchy`({$role}))
    AND
    grant;

If it's not enough, then you might do another table for inheritance:

如果这还不够，那么您可能会为继承做另一个表：

But, in this case, needed another hierarchy obtainment algorithm.

但是，在这种情况下，需要另一种层次获取算法。

---

---

To resolve overridingissue you will have to get role permissions and user permissions. Then, write userpermissions over rolespermissions to session.

要解决首要问题，您必须获得角色权限和用户权限。然后，写user了权限roles的权限session。

---

---

Also, I suggest to remove grantcolumns in Permission_Roleand Permission_User. There is no need to mapevery permission for each of them. Just enough to use EXISTSqueries: if there is a record, then permission granted, else - it's not. If you need to retrieve all permissions and statuses, you might use LEFT JOINs.

另外，我建议删除和中的grant列。无需为每个权限映射每个权限。足以使用查询：如果有记录，则授予权限，否则 - 不是。如果您需要检索所有权限和状态，您可以使用s。Permission_RolePermission_UserEXISTSLEFT JOIN