3. RBAC 数据库表设计

3.1 设计原理

RBAC（基于角色的访问控制）的核心思想是将权限授予角色，再将角色授予用户，从而解耦用户与权限的直接绑定。这一设计需要五张数据库表：用户表、角色表、权限表以及两张关联表（用户‑角色关联表、角色‑权限关联表）。两张关联表分别处理“用户与角色”和“角色与权限”之间的多对多关系，保证数据的范式化和扩展性。

核心原则：用户只通过角色间接获得权限，禁止直接为用户分配权限。任何调整权限的需求仅需修改角色‑权限关联，无需遍历所有用户。

3.2 五张表结构详解

以下逐个列出每张表的字段设计，并以 MySQL 8.x 为参考。

3.2.1 用户表（users）

字段名数据类型约束说明
user_idINT UNSIGNEDPRIMARY KEY, AUTO_INCREMENT用户唯一标识
usernameVARCHAR(50)UNIQUE, NOT NULL登录用户名
password_hashVARCHAR(255)NOT NULL加密后的密码
emailVARCHAR(100)—用户邮箱，可选
statusTINYINTDEFAULT 11=启用，0=禁用
created_atDATETIMEDEFAULT CURRENT_TIMESTAMP创建时间

3.2.2 角色表（roles）

字段名数据类型约束说明
role_idINT UNSIGNEDPRIMARY KEY, AUTO_INCREMENT角色唯一标识
role_nameVARCHAR(50)UNIQUE, NOT NULL角色名称，如 admin, editor
descriptionVARCHAR(255)—角色描述
created_atDATETIMEDEFAULT CURRENT_TIMESTAMP创建时间

3.2.3 权限表（permissions）

字段名数据类型约束说明
permission_idINT UNSIGNEDPRIMARY KEY, AUTO_INCREMENT权限唯一标识
permission_nameVARCHAR(100)UNIQUE, NOT NULL权限标识符，如 user:create
descriptionVARCHAR(255)—权限说明
created_atDATETIMEDEFAULT CURRENT_TIMESTAMP创建时间

3.2.4 用户‑角色关联表（user_roles）

字段名数据类型约束说明
user_idINT UNSIGNEDFOREIGN KEY → users(user_id)用户ID
role_idINT UNSIGNEDFOREIGN KEY → roles(role_id)角色ID

联合主键：PRIMARY KEY (user_id, role_id)。该表无独立业务主键，通过外键保证数据完整性。

3.2.5 角色‑权限关联表（role_permissions）

字段名数据类型约束说明
role_idINT UNSIGNEDFOREIGN KEY → roles(role_id)角色ID
permission_idINT UNSIGNEDFOREIGN KEY → permissions(permission_id)权限ID

联合主键：PRIMARY KEY (role_id, permission_id)。

3.3 完整建表 SQL（MySQL 示例）

-- 用户表
CREATE TABLE users (
    user_id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email         VARCHAR(100),
    status        TINYINT      DEFAULT 1,
    created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 角色表
CREATE TABLE roles (
    role_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name    VARCHAR(50)  NOT NULL UNIQUE,
    description  VARCHAR(255),
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 权限表
CREATE TABLE permissions (
    permission_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(100) NOT NULL UNIQUE,
    description     VARCHAR(255),
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户-角色关联表
CREATE TABLE user_roles (
    user_id INT UNSIGNED NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 角色-权限关联表
CREATE TABLE role_permissions (
    role_id       INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.4 索引与性能优化

  • 主键自动索引：每张表的主键已由 InnoDB 自动聚簇索引覆盖。
  • 外键索引：user_roles 和 role_permissions 的外键列会因 FOREIGN KEY 自动创建索引，无需额外添加。
  • 唯一索引：username、role_name、permission_name 已由 UNIQUE 约束建立索引。
  • 查询建议：当需要按角色查询用户或按权限查询角色时，联合主键的顺序已支持从左到右的匹配，无需调整。
性能基线：单表百万级数据量下，通过主键或唯一索引检索耗时通常低于 1 ms；关联表因两列均为外键索引，多表 JOIN 也能保持在 10 ms 以内。

3.5 常见设计错误与避坑

  1. 为角色分配用户而非用户分配角色：应将角色作为中心，但实际业务中仍通过用户‑角色关联表实现双向关联，此处仅强调设计意图。
  2. 使用逗号分隔存储多角色：违反第一范式，导致无法使用索引，且更新时需要解析字符串。
  3. 缺少外键约束：应用程序逻辑可能产生孤立记录。务必定义 FOREIGN KEY 且使用 ON DELETE CASCADE（或 RESTRICT 视业务而定）。
  4. 权限粒度过细：每增加一个权限就新增一条记录，可能导致关联表数据膨胀。建议按模块+操作组合，如 user:create、user:delete。
  5. 忽略索引覆盖：若频繁按用户ID查询其所有角色，确保 user_roles 的 user_id 列在索引最左侧（联合主键已满足）。
注意：生产环境中禁止在 VARCHAR 字段上使用 utf8mb4_general_ci 以外的校对规则默认即可；不要在关联表上额外添加自增主键，除非有其他复杂需求（如记录分配时间）。
RBAC 五表关系图 用户表 角色表 权限表 用户-角色 角色-权限 关系说明： 一张用户可拥有多个角色，一个角色可被多个用户拥有 → 多对多 → 用户‑角色关联表。 一个角色可包含多个权限，一个权限可赋予多个角色 → 多对多 → 角色‑权限关联表。

3.6 设计验证与调试

建表后可通过以下 SQL 快速验证关联完整性：

-- 查询某个用户的所有角色（假设 user_id = 1）
SELECT r.role_id, r.role_name
FROM user_roles ur
JOIN roles r ON ur.role_id = r.role_id
WHERE ur.user_id = 1;

-- 查询某个角色的所有权限（假设 role_id = 2）
SELECT p.permission_id, p.permission_name
FROM role_permissions rp
JOIN permissions p ON rp.permission_id = p.permission_id
WHERE rp.role_id = 2;

-- 检查是否存在孤立记录（无对应父表）
SELECT * FROM user_roles WHERE user_id NOT IN (SELECT user_id FROM users);

若返回空结果，说明外键约束生效，数据一致。若有记录，应检查外键定义或手动清理。

← 上一章 📖 返回目录 下一章 →