有更好的方法吗？ MySQL

Question

我正在将权限系统从 PHP 迁移到 MySQL 存储过程，以便我可以将网站跨不同设备放置。

我的权限系统是这样工作的： 操作是用户可以执行的操作。 （上传照片等） 用户可以拥有单独的权限、可以按角色设置权限或仅全局权限。 拒绝权限接管允许权限。 为用户设置的权限将覆盖所有其他权限。

我的表看起来像这样

对于每个操作

ACL_Actions: 
   ActionID | Default_DOA

对于每个角色

ACL_Roles: 
   RoleID | Name_Of_Role

为角色中的用户设置操作权限

ACL_Role_Actions: 
   ID | RoleID | ActionID | Role_DOA

放置用户在角色中

ACL_Role_Users:
   ID | RoleID | UserID

对于每个用户

ACL_Users:
   UserID | Details

允许用户定义操作权限

ACL_User_Actions:
   ID | ActionID | UserID | User_DOA

这些只是表的一部分，DOA 代表 DenyOrAllow。

DOA 字段是位。 0 = 拒绝，1 = 允许

现在讨论我的问题。这个存储过程是我可以做到这一点的最佳方法吗？ 我正在尝试加载用户的操作权限。

BEGIN
DECLARE current_doa INT;
DECLARE action_id INT;
DECLARE done INT DEFAULT 0;

DECLARE cur1 CURSOR FOR SELECT ActionID, User_DOA FROM ACL_User_Actions WHERE UserID = euser_id;
DECLARE cur2 CURSOR FOR SELECT ActioNID, Role_DOA FROM ACL_Role_Actions WHERE RoleID IN (SELECT RoleID FROM ACL_Role_Users WHERE UserID = euser_id);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TEMPORARY TABLE IF EXISTS user_roles;
CREATE TEMPORARY TABLE user_roles
SELECT ACL_Actions.ActionID as ActionID, ACL_Actions.Default_DOA as DOA
From ACL_Actions;

OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO action_id, current_doa;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF current_doa = 0 THEN
            UPDATE user_roles SET DOA = current_doa WHERE ActionID = action_id;
        END IF;     
    END LOOP;
CLOSE cur1;

SET done = 0;

OPEN cur2;
    read_loop: LOOP
        FETCH cur2 INTO action_id, current_doa;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE user_roles SET DOA = current_doa WHERE ActionID = action_id;
    END LOOP;
CLOSE cur2;


SELECT * FROM user_roles;
END

Answer 1

请注意，下表不需要 id 列，因此请将其删除。至少使相关列为 UNIQUE NOT NULL。显然，对于重复项，数据毫无意义，因为例如，您可以为同一用户/操作同时拥有允许和拒绝行。

ACL_角色_操作：：
身份证 |角色ID |行动ID |角色_DOA

=> PK 为 (RoleID,ActionID)

ACL_Role_Users：
身份证 |角色ID |用户ID

=> PK 是 (UserID,RoleID)

ACL_User_Actions：
身份证 |行动ID |用户ID |用户_DOA

=> PK 是 (UserID,ActionID)

因为您有一个用户到角色表，这意味着一个用户可以是多个角色的成员，但您没有指定角色之间的优先级。如果用户是允许操作 X 的角色 A 和拒绝操作 X 的角色 B 的成员，则行为未定义。因此，您需要在角色表中添加一个“优先级”列，我将其定义为正整数，其中 0 具有最高优先级。角色表中的优先级必须是唯一的。

我的第一个想法是在两个权限表之间使用 FULL OUTER JOIN，但 mysql 不支持这一点。

我的第二个想法是联合用户/角色权限查询的结果（将用户权限放在第一位）并使用窗口函数（FIRST（）OVER（））来获取覆盖其他行的第一行，但MySQL不支持这一点。

尝试#1：

SELECT ActionID, GROUP_CONCAT(doa ORDER BY priority) FROM (
  SELECT NULL as priority, ActionID, User_DOA doa FROM ACL_User_Actions WHERE UserID = euser_id;
  UNION ALL
  SELECT priority, ActioNID, Role_DOA doa FROM ACL_Role_Actions JOIN ACL_Role_Users USING (RoleID) WHERE UserID = euser_id
) foo GROUP BY ActioNID

如果 user=deny 且 role=allow，GROUP_CONCAT(User_DOA) 将返回“01”，只需 substr() 其中的第一个字符。请注意，ORDER BY 优先级将 NULL 放在第一位，这意味着用户权限将首先出现。