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

2022-01-04 00:00:00 permissions hierarchy php mysql rbac

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

我们的要求是:

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

所以，排除了这些要求，这就是我的想法.

表:用户

id |国际|唯一身份

表:角色

id |国际|唯一身份--------------|---------------------------------------------标题 |变量 |人类可读的名字

表:权限

id |国际|唯一身份--------------|---------------------------------------------模块 |变量 |模块名称--------------|---------------------------------------------标题 |变量 |人类可读的名字--------------|---------------------------------------------键 |变量 |函数中使用的键名

表:Role_User

role_id |国际|角色表中的 id--------------|---------------------------------------------用户 ID |国际|来自用户表的 id

表:Permission_Role

id |国际|唯一身份--------------|---------------------------------------------权限_id |国际|来自权限表的 id--------------|---------------------------------------------role_id |国际|角色表中的 id--------------|---------------------------------------------授予|微小的|0 = 拒绝，1 = 授予

表:Permission_User

id |国际|唯一身份--------------|---------------------------------------------权限_id |国际|来自权限表的 id--------------|---------------------------------------------用户 ID |国际|来自用户表的 id--------------|---------------------------------------------授予|微小的|0 = 拒绝，1 = 授予

嗯，实际上这是一半，我确定那部分，我陷入困境的部分是层级角色.

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

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

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

解决方案

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

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

CREATE FUNCTION `getHierarchy`(`aRole` BIGINT UNSIGNED)返回 VARCHAR(1024)不确定读取 SQL 数据开始声明 `aResult` VARCHAR(1024) 默认 NULL;DECLARE `aParent` BIGINT UNSIGNED;SET `aParent` = (SELECT `parent` FROM `Roles` WHERE `id` = `aRole`);虽然不是 `aParent` 是 NULL 做SET `aResult` = CONCAT_WS(',', `aResult`, `aParent`);SET `aParent` = (SELECT `parent` FROM `Roles` WHERE `id` = `aParent`);结束时；返回 IFNULL(`aResult`, '');结尾

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

SELECT`permission_id`从`权限_角色`在哪里FIND_IN_SET(`role_id`,`getHierarchy`({$role}))和授予;

如果还不够，那你可以再做一个表来继承:

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

<小时>

要解决覆盖问题，您必须获得角色权限和用户权限.然后，将 user 权限超过 roles 权限写入 session.

<小时>

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

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?

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_id       | int     | id from roles table
--------------|---------------------------------------------
user_id       | int     | id from users table

Table: 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

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.

解决方案

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

This relation will add 1 : n inheritance within Roles record. You might obtain whole hierarchy tree with this stored function:

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 granted permissions 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 overriding issue you will have to get role permissions and user permissions. Then, write user permissions over roles permissions to session.

Also, I suggest to remove grant columns in Permission_Role and Permission_User. There is no need to map every permission for each of them. Just enough to use EXISTS queries: 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.

相关文章