针对 NIST 2 级 RBAC 的长而复杂的 MySQL 分层嵌套集模型优化

Question

为 jFramework 实现了 NIST Level 2 RBAC，核心 SQL 如下所示：

"SELECT COUNT(*) AS Result
    FROM /* Version 2.05 */ 
        `".reg("jf/users/table/name")."` AS TU
    JOIN `".reg("jf/rbac/tables/RoleUsers/table/name")."` AS TUrel ON (TU.`".reg("jf/users/table/UserID")."`=TUrel.`".reg("jf/rbac/tables/RoleUsers/table/UserID")."`)

    JOIN `".reg("jf/rbac/tables/Roles/table/name")."` AS TRdirect ON (TRdirect.`".reg("jf/rbac/tables/Roles/table/RoleID")."`=TUrel.`".reg("jf/rbac/tables/RoleUsers/table/RoleID")."`) 
    JOIN `".reg("jf/rbac/tables/Roles/table/name")."` AS TR ON ( TR.`".reg("jf/rbac/tables/Roles/table/RoleLeft")."` BETWEEN TRdirect.`".reg("jf/rbac/tables/Roles/table/RoleLeft")."` AND TRdirect.`".reg("jf/rbac/tables/Roles/table/RoleRight")."`)
    /* we join direct roles with indirect roles to have all descendants of direct roles */
    JOIN 
    (   `".reg("jf/rbac/tables/Permissions/table/name")."` AS TPdirect 
        JOIN `".reg("jf/rbac/tables/Permissions/table/name")."` AS TP ON ( TPdirect.`".reg("jf/rbac/tables/Permissions/table/PermissionLeft")."` BETWEEN TP.`".reg("jf/rbac/tables/Permissions/table/PermissionLeft")."` AND TP.`".reg("jf/rbac/tables/Permissions/table/PermissionRight")."`)
    /* direct and indirect permissions */
        JOIN `".reg("jf/rbac/tables/RolePermissions/table/name")."` AS TRel ON (TP.`".reg("jf/rbac/tables/Permissions/table/PermissionID")."`=TRel.`".reg("jf/rbac/tables/RolePermissions/table/PermissionID")."`)
    /* joined with role/permissions on roles that are in relation with these permissions*/
    ) ON ( TR.`".reg("jf/rbac/tables/Roles/table/RoleID")."` = TRel.`".reg("jf/rbac/tables/RolePermissions/table/RoleID")."`)
    WHERE 
        TU.`".reg("jf/users/table/UserID")."`=? 
    AND
        TPdirect.{$PermissionCondition}"

这是为了检查某个用户是否具有特定权限，并且有效。我想知道它是否可以优化，因为它在应用程序中使用非常频繁并且被严重依赖。

该模型是：

users : ID,Username,Password
roles : ID,Title,Description, Left , Right (left and right for the nested set model)
permissions : ID,Title,Description , Left , Right
role_permissions : RoleID,PermissionID,AssignmentDate
user_roles : UserID,RoleID

请记住，角色和权限表都是分层的。因此，如果某人拥有一个角色，那么他/她也拥有所有后代角色。

如果某人拥有权限，他/她也拥有所有后代权限。

谁能帮助我吗？ 我知道这是一个巨大的事情，我将为此提供巨额赏金。

Answer 1

如果您坚持使用嵌套集，那么以下内容对您没有任何用处，但如果您愿意考虑使用邻接列表方法，以下简单示例可能会感兴趣。

完整的脚本可以在这里找到： http://pastie.org/1720133

我提供了 2 个存储过程。第一个采用 role_id 并输出授予该角色及其后代的权限列表：

mysql> call list_role_permissions(99); -- root role
+---------+---------+-----------+-----------------+
| role_id | perm_id | role_name | permission_name |
+---------+---------+-----------+-----------------+
|      99 |       5 | root      | create forum    |
|       3 |       4 | admin     | move forum      |
|       2 |       3 | moderator | edit post       |
|       1 |       2 | member    | create post     |
|       0 |       1 | guest     | view post       |
+---------+---------+-----------+-----------------+
5 rows in set (0.00 sec)

mysql> call list_role_permissions(2); -- moderator role
+---------+---------+-----------+-----------------+
| role_id | perm_id | role_name | permission_name |
+---------+---------+-----------+-----------------+
|       2 |       3 | moderator | edit post       |
|       1 |       2 | member    | create post     |
|       0 |       1 | guest     | view post       |
+---------+---------+-----------+-----------------+
3 rows in set (0.00 sec)

第二个采用 user_id 并输出授予该用户角色的权限列表：

mysql> call list_user_role_permissions(1); -- root user
+---------+---------+-----------+-----------------+
| role_id | perm_id | role_name | permission_name |
+---------+---------+-----------+-----------------+
|      99 |       5 | root      | create forum    |
|       3 |       4 | admin     | move forum      |
|       2 |       3 | moderator | edit post       |
|       1 |       2 | member    | create post     |
|       0 |       1 | guest     | view post       |
+---------+---------+-----------+-----------------+
5 rows in set (0.00 sec)

 mysql> call list_user_role_permissions(3); -- moderator user
+---------+---------+-----------+-----------------+
| role_id | perm_id | role_name | permission_name |
+---------+---------+-----------+-----------------+
|       2 |       3 | moderator | edit post       |
|       1 |       2 | member    | create post     |
|       0 |       1 | guest     | view post       |
+---------+---------+-----------+-----------------+
3 rows in set (0.00 sec)

如果您担心邻接列表的性能，您可以检查使用本月早些时候我做的 500 万行 Yahoo GeoPlanet 示例的这种方法的结果：

基于深度生成MySQL 中的分层数据树（无 CTE）

希望这会有所帮助（如果您陷入嵌套集路径，请忽略）

Rgds...

表

drop table if exists roles;
create table roles
(
role_id tinyint unsigned not null primary key,
name varchar(255) unique not null,
parent_role_id tinyint unsigned,
key (parent_role_id)
)engine=innodb;

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)engine=innodb;

drop table if exists user_roles;
create table user_roles
(
user_id int unsigned not null,
role_id tinyint unsigned not null,
primary key (user_id, role_id)
)engine=innodb;

drop table if exists permissions;
create table permissions
(
perm_id smallint unsigned not null auto_increment primary key,
name varchar(255) unique not null
)engine=innodb;

drop table if exists role_permissions;
create table role_permissions
(
role_id tinyint unsigned not null,
perm_id smallint unsigned not null,
primary key (role_id, perm_id)
)engine=innodb;

存储过程

drop procedure if exists list_role_permissions;

delimiter #

create procedure list_role_permissions
(
in p_role_id tinyint unsigned
)
proc_main:begin

declare done tinyint unsigned default 0;
declare dpth smallint unsigned default 0;

create temporary table hier(
 parent_role_id tinyint unsigned, 
 role_id tinyint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

insert into hier select parent_role_id, role_id, dpth from roles where role_id = p_role_id;
create temporary table tmp engine=memory select * from hier;

while not done do

    if exists( select 1 from roles r inner join tmp on r.parent_role_id = tmp.role_id and tmp.depth = dpth) then

    insert into hier select r.parent_role_id, r.role_id, dpth + 1 from roles r
            inner join tmp on r.parent_role_id = tmp.role_id and tmp.depth = dpth;

    set dpth = dpth+1;          
        truncate table tmp;
        insert into tmp select * from hier where depth = dpth;
    else
        set done = 1;
    end if;
end while;

select
 rp.*,
 r.name as role_name,
 p.name as permission_name
from
 role_permissions rp
inner join hier h on h.role_id = rp.role_id
inner join permissions p on rp.perm_id = p.perm_id
inner join roles r on rp.role_id = r.role_id
order by
 rp.role_id desc;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end proc_main #

delimiter ;

drop procedure if exists list_user_role_permissions;

delimiter #

create procedure list_user_role_permissions
(
in p_user_id int unsigned
)
proc_main:begin

declare done tinyint unsigned default 0;
declare dpth smallint unsigned default 0;

create temporary table hier(
 parent_role_id tinyint unsigned, 
 role_id tinyint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

insert into hier select r.parent_role_id, r.role_id, dpth from roles r 
    inner join user_roles ur on ur.user_id = p_user_id and ur.role_id = r.role_id;

create temporary table tmp engine=memory select * from hier;

while not done do

    if exists( select 1 from roles r inner join tmp on r.parent_role_id = tmp.role_id and tmp.depth = dpth) then

    insert into hier select r.parent_role_id, r.role_id, dpth + 1 from roles r
            inner join tmp on r.parent_role_id = tmp.role_id and tmp.depth = dpth;

    set dpth = dpth+1;          
        truncate table tmp;
        insert into tmp select * from hier where depth = dpth;
    else
        set done = 1;
    end if;
end while;

select distinct
 rp.*,
 r.name as role_name,
 p.name as permission_name
from
 role_permissions rp
inner join hier h on h.role_id = rp.role_id
inner join permissions p on rp.perm_id = p.perm_id
inner join roles r on rp.role_id = r.role_id
order by
 rp.role_id desc;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end proc_main #

delimiter ;

测试数据和测试

insert into roles values 
(99,'root',null),
 (3,'admin',99),
  (2,'moderator',3),
   (1,'member',2),
    (0,'guest',1);

insert into users (username) values 
('f00'),('bar'),('alpha'),('beta'),('gamma');

insert into user_roles values
(1,99), -- (1,1),
(2,3), -- (2,2),(2,1),
(3,2), -- (3,1),
(4,1),
(5,0);

insert into permissions (name) values 
('view post'), ('create post'), ('edit post'), ('move forum'), ('create forum');

insert into role_permissions values 
-- guest
(0,1),
-- member
(1,2),
-- moderator
(2,3),
-- admin
(3,4),
-- root
(99,5);

call list_role_permissions(99); -- root role
call list_role_permissions(3); -- admin role
call list_role_permissions(2); -- moderator role
call list_role_permissions(1); -- member role
call list_role_permissions(0); -- guest role

call list_user_role_permissions(1); -- root user
call list_user_role_permissions(2); -- admin user
call list_user_role_permissions(3); -- moderator user
call list_user_role_permissions(4); -- member user
call list_user_role_permissions(5); -- guest user