用户认证和权限表结构

Question

我正在创建一个用户身份验证库（用于练习）。

我要添加的一件事是，一个用户可以分配给多个角色。

每个角色都有一组权限（例如 editUser、createUser 等）。

如果用户属于两个组，并且任何组有权执行某项操作，则该用户可以执行该操作。

我想知道从 mysql 数据库的角度存储这些信息的最佳方法。

我在想

users            : ID            | username | etc  
groups           : ID            | name     | etc  
user_group       : group_ID      | user_ID  
permissions      : ID            | name     | description (lookup table)
group_permission : permission_ID | group_ID

，基本上，如果一个组拥有权限，那么它就会在 group_permission 中获得一个条目。

我的问题是，这是最有效的方法吗？或者我最好将每个权限作为组表中的一列并删除 group_permission 表？

Answer 1

你的方法看起来不错并且标准化，对此表示赞赏。
我缺少的一件事是非权限表，即不允许执行操作的表。
Active Directory 具有此功能，这使您可以快速阻止对对象的权限。

这允许您允许访问所有帐户，除了......
如果您以相反的方式执行此操作，则必须允许访问每个对象，同时忽略 HR 数据。
第一种方式设置 2 个对象的权限（1 个对父对象的权限，1 个对子对象的解雇），第二种方式可能会遇到数十个权限。

就我个人而言，我会更新权限表以也包括排除项。
这将允许您将排除项附加到组和用户。

使用黑洞表来简化新权限的添加
为了简化添加新权限的过程，您可以创建一个新的黑洞表。
这不会存储任何内容，但会触发一个触发器来为您执行插入操作，通过这种方式，您可以隐藏数据库已从插入代码中标准化的事实。

CREATE TABLE bh_permission (
  user_or_group_id unsiged integer not null,
  isuser ENUM('user','group') not null default 'user',
  permission_description varchar(255) not null,
  allow_or_not ENUM('allow','forbid') not null default 'allow'
) ENGINE = BLACKHOLE;

现在您可以插入指定组或 user_id 的表

INSERT INTO bh_permission VALUES ('123','group','p_HR_files_2011','forbid');

，并有一个触发器来处理技术细节：

DELIMITER $

CREATE TRIGGER ai_bh_permission_each AFTER INSERT ON bh_permission FOR EACH ROW
BEGIN
  DECLARE Mypermission_id INTEGER;
  //like is always case-insensitive, `=` is not.
  SELECT p.id INTO Mypermission_id FROM permissions p 
    WHERE name LIKE NEW.permission_description LIMIT 1;
  IF isuser = 'user' THEN
    INSERT IGNORE INTO user_permission (user_id, permission_id, allow_or_not)
      VALUES (NEW.user_or_group_id, Mypermission_id, NEW.allow_or_not);
  ELSE
    INSERT IGNORE INTO group_permission (group_id, permission_id, allow_or_not)
      VALUES (NEW.user_or_group_id, Mypermission_id, NEW.allow_or_not);
  END IF;
END $

DELIMITER ;