第三章 RBAC 实现：数据库表设计

这一章我们动手把 RBAC 装进数据库里。嗯，说「装」其实有点轻巧，真正落地时你会发现表结构没设计好，后面加个角色都得改一堆代码。我在好几个项目里都吃过这个亏，所以今天把我的标准做法直接端出来。

3.1 核心表结构概览

RBAC 的经典模型只有五张表，但很多人把它想复杂了。说白了就是：用户、角色、权限，再加两张关联表把它们串起来。我习惯把这套结构叫做「三主两联」。

RBAC 核心表结构（三主两联） 用户表 (sys_user) id, username, password, enabled 主表：谁在使用系统 角色表 (sys_role) id, role_name, role_key, sort 主表：功能分组 权限表 (sys_permission) id, perm_name, perm_code, type 主表：具体操作与资源 用户角色关联 (sys_user_role) 角色权限关联 (sys_role_permission)

3.2 用户表

用户表是最基础的一张表，记录系统里每一个账号。我的经验是：不要把业务字段（比如部门、工号）直接塞进用户表，RBAC 只关心身份标识和启用状态，其他信息应该拆到用户扩展表里。

CREATE TABLE sys_user (
    id          BIGINT       PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username    VARCHAR(64)  NOT NULL UNIQUE COMMENT '用户名',
    password    VARCHAR(256) NOT NULL COMMENT '加密后的密码',
    real_name   VARCHAR(64)  DEFAULT '' COMMENT '真实姓名',
    email       VARCHAR(128) DEFAULT '' COMMENT '邮箱',
    phone       VARCHAR(20)  DEFAULT '' COMMENT '手机号',
    avatar      VARCHAR(256) DEFAULT '' COMMENT '头像路径',
    enabled     TINYINT(1)   DEFAULT 1 COMMENT '启用状态:1-启用,0-禁用',
    create_time DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
小提示：username 字段加上 UNIQUE 约束，防止同名用户。我见过一个项目没加唯一索引，结果两人注册了同一个「admin」——后端炸了三天。

3.3 角色表

角色就是一个权限组。比如「管理员」「运营」「只读用户」都是角色。角色表的核心是 role_key 字段，代码里用它做鉴权判断，而不是用中文名称。

CREATE TABLE sys_role (
    id          BIGINT       PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID',
    role_name   VARCHAR(64)  NOT NULL COMMENT '角色名称（中文显示用）',
    role_key    VARCHAR(64)  NOT NULL UNIQUE COMMENT '角色标识（代码中用）',
    sort        INT          DEFAULT 0 COMMENT '排序号',
    status      TINYINT(1)   DEFAULT 1 COMMENT '状态:1-正常,0-停用',
    remark      VARCHAR(256) DEFAULT '' COMMENT '备注',
    create_time DATETIME     DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';
注意：千万不要在业务代码里硬编码角色名。我曾经在项目里直接写 if(roleName == "管理员")，后来改角色名称时气得想打自己。用 role_key 判断，比如 admin、operator，改中文名不会影响逻辑。

3.4 权限表

权限表的设计最容易出分歧。小项目可以按「菜单 + 按钮」粗暴处理，但上了规模一定要拆分。我的习惯是把权限分为三类：模块级、操作级、数据级。这张表只存模块和操作，数据级权限单独用规则表处理。

CREATE TABLE sys_permission (
    id          BIGINT       PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
    perm_name   VARCHAR(64)  NOT NULL COMMENT '权限名称',
    perm_code   VARCHAR(128) NOT NULL UNIQUE COMMENT '权限标识（如 system:user:add）',
    type        TINYINT(1)   NOT NULL COMMENT '类型:1-模块,2-菜单,3-按钮',
    parent_id   BIGINT       DEFAULT 0 COMMENT '父权限ID',
    path        VARCHAR(256) DEFAULT '' COMMENT '前端路由路径',
    component   VARCHAR(256) DEFAULT '' COMMENT '前端组件',
    icon        VARCHAR(64)  DEFAULT '' COMMENT '图标',
    sort        INT          DEFAULT 0 COMMENT '排序号',
    status      TINYINT(1)   DEFAULT 1 COMMENT '状态',
    create_time DATETIME     DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';
常用的 perm_code 命名方式：模块:子模块:操作。例如 system:user:add、system:user:edit、order:view。这样拆开的好处是，后期可以做细粒度的按钮级权限控制。

3.5 用户角色关联表

用户和角色是多对多关系。一个用户可以有多个角色，一个角色也可以属于多个用户。这张表只存两个外键，不要加其他业务字段。

CREATE TABLE sys_user_role (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
    user_id     BIGINT NOT NULL COMMENT '用户ID',
    role_id     BIGINT NOT NULL COMMENT '角色ID',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_role (user_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';
建议：联合主键用 UNIQUE KEY 确保不重复。有些人直接用两个字段做联合主键也行，但我习惯单独一个自增 id，方便以后做批量删除和游标分页。

3.6 角色权限关联表

角色和权限也是多对多。同样很简单：角色 ID + 权限 ID。

CREATE TABLE sys_role_permission (
    id             BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
    role_id        BIGINT NOT NULL COMMENT '角色ID',
    permission_id  BIGINT NOT NULL COMMENT '权限ID',
    create_time    DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_role_perm (role_id, permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

3.7 表关系总览

表名作用关联关系
sys_user存储账号和身份信息通过 sys_user_role 关联角色
sys_role定义角色分组通过 sys_role_permission 关联权限
sys_permission定义可操作的功能点被角色引用
sys_user_role用户与角色的关系多对多桥接
sys_role_permission角色与权限的关系多对多桥接

3.8 建表顺序与外键注意事项

我建议先建主表（用户、角色、权限），再建关联表。外键不是必须的——说实话，我在生产环境里很少用物理外键。为什么？

  • 性能问题：高并发下外键约束会增加检查开销。
  • 分库分表困难：外键只能单库生效。
  • 业务复杂度：删除角色时，你可以选择「先删关联再删角色」，而不是依赖数据库级联。
避坑指南：我曾经在一个老项目里用了物理外键 + CASCADE，结果运营误删了一个角色，连带删了 200 个用户的权限数据。从那以后，我的所有 RBAC 表都不设物理外键，只在应用层做逻辑校验。

3.9 索引优化建议

查权限时最频繁的操作是：根据用户 ID 查他的角色列表，再根据角色查权限列表。所以关联表的 user_id 和 role_id 一定要加索引。上面建表时已经用了联合唯一索引，MySQL 会自动为第一列创建索引，但最好再单独给 role_id 加一个索引，因为角色权限关联表也经常按角色查。

-- 额外索引（可选）
ALTER TABLE sys_user_role ADD INDEX idx_user_role_role_id (role_id);
ALTER TABLE sys_role_permission ADD INDEX idx_role_perm_permission_id (permission_id);

3.10 快速使用案例

写完表当然要跑一遍。下面是一段插入测试数据的 SQL，你可以直接复制去验证。

-- 插入用户
INSERT INTO sys_user (username, password, real_name, enabled) VALUES
('zhangsan', '加密后的密码', '张三', 1),
('lisi', '加密后的密码', '李四', 1);

-- 插入角色
INSERT INTO sys_role (role_name, role_key, sort) VALUES
('管理员', 'admin', 1),
('运营员', 'operator', 2);

-- 插入权限
INSERT INTO sys_permission (perm_name, perm_code, type, parent_id) VALUES
('用户管理', 'system:user', 1, 0),
('用户列表', 'system:user:list', 2, 1),
('新增用户', 'system:user:add', 3, 1);

-- 建立关联
INSERT INTO sys_user_role (user_id, role_id) VALUES (1, 1), (2, 2);

INSERT INTO sys_role_permission (role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3),   -- 管理员拥有所有权限
(2, 1), (2, 2);            -- 运营员只有列表权限

查某个用户的所有权限用这条 SQL（多表连查）：

SELECT DISTINCT p.perm_code
FROM sys_user u
JOIN sys_user_role ur ON u.id = ur.user_id
JOIN sys_role_permission rp ON ur.role_id = rp.role_id
JOIN sys_permission p ON rp.permission_id = p.id
WHERE u.username = 'zhangsan';

3.11 扩展思路：租户隔离

如果你做的是 SaaS 产品，每张表加一个 tenant_id 字段就行。我习惯把 tenant_id 放主表，关联表通过主表继承租户信息，这样查询时只需要过滤用户表就可以隔离整个权限数据。

好啦，表设计到这里就够用了。后面章节你会看到怎么用这些表写出实际的鉴权代码——到时候你会发现，只要表结构稳了，逻辑层就是顺手的事。嗯，今天就到这，动手建张表试试吧。

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