4. RBAC 数据库设计：用户表、角色表、权限表，关联关系与 SQL 示例

聊到权限系统，很多人第一反应就是「建几张表，存一下」。但实际上，表结构设计得好不好，直接决定了后续的扩展性和维护成本。

我个人习惯，在设计 RBAC 的数据库时，会遵循一个核心原则：职责分离。用户只管是谁，角色只管是什么岗位，权限只管能干什么。三者之间通过关联表解耦。

4.1 核心三张表长什么样？

先说最基础的三张表：users（用户表）、roles（角色表）、permissions（权限表）。

表名 核心字段 职责说明
users id, username, password_hash, status, created_at 记录系统用户的基本信息。尽量不存冗余的权限相关字段。
roles id, role_name, role_code, description, is_system 定义角色，比如「高级审批员」「部门经理」。role_code 用于代码中硬编码判断。
permissions id, perm_name, perm_code, resource, action, description 定义具体的操作权限，比如「审批-通过」「订单-查看」。

一个小建议： 字段类型上，主键我用 BIGINT 自增，除非你需要分布式 ID。角色表和权限表的 code 字段建议设置唯一索引，这是你在代码里做权限判断的依据。

4.2 关联关系：多对多的桥梁

用户和角色之间，角色和权限之间，都是 多对多 关系。所以要引入两张关联表：user_role 和 role_permission。

你想想看，如果没有关联表，直接在用户表里加一个 role_id 字段，那一个用户只能有一个角色，这在真实业务中根本行不通。一个人可能同时是「运维」又是「审批员」。

核心关联关系：

  • users 与 user_role：一对多
  • roles 与 user_role：一对多
  • roles 与 role_permission：一对多
  • permissions 与 role_permission：一对多

下面这张图，把四张表的关系串起来了。

RBAC 四表关系图 users id, username user_role user_id, role_id roles id, role_code role_permission role_id, perm_id permissions id, perm_code 关联表只存 ID，不存冗余 解耦用户和权限的映射

4.3 SQL 建表示例（MySQL 方言）

纸上谈兵没意思，直接上 SQL。这是我个人比较喜欢的一套模板，加了些注释，方便团队新人理解。

-- 1. 用户表
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username` VARCHAR(64) NOT NULL COMMENT '登录名',
    `password_hash` VARCHAR(255) NOT NULL COMMENT '加密密码',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态: 1启用, 0禁用',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 2. 角色表
CREATE TABLE `roles` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色ID',
    `role_name` VARCHAR(64) NOT NULL COMMENT '角色显示名称',
    `role_code` VARCHAR(64) NOT NULL COMMENT '角色编码, 用于代码判断',
    `description` VARCHAR(255) DEFAULT NULL COMMENT '角色描述',
    `is_system` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否是系统内置角色',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

-- 3. 权限表
CREATE TABLE `permissions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '权限ID',
    `perm_name` VARCHAR(64) NOT NULL COMMENT '权限名称',
    `perm_code` VARCHAR(128) NOT NULL COMMENT '权限编码, eg: order:approve',
    `resource` VARCHAR(64) DEFAULT NULL COMMENT '所属资源, eg: order',
    `action` VARCHAR(64) DEFAULT NULL COMMENT '操作, eg: approve',
    `description` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_perm_code` (`perm_code`),
    INDEX `idx_resource` (`resource`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 4. 用户-角色关联表
CREATE TABLE `user_role` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `role_id` BIGINT UNSIGNED NOT NULL COMMENT '角色ID',
    `assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '分配时间',
    `assigner` VARCHAR(64) DEFAULT NULL COMMENT '分配人',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_role` (`user_id`, `role_id`),
    INDEX `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

-- 5. 角色-权限关联表
CREATE TABLE `role_permission` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `role_id` BIGINT UNSIGNED NOT NULL COMMENT '角色ID',
    `perm_id` BIGINT UNSIGNED NOT NULL COMMENT '权限ID',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_role_perm` (`role_id`, `perm_id`),
    INDEX `idx_perm_id` (`perm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

需要注意：

  • 关联表里我用了 UNIQUE KEY，防止同一条关联记录重复插入。
  • role_permission 表里我没有加 created_at，因为权限和角色的关系相对稳定，不需要追溯分配时间。但如果你的业务需要审计，加上也无妨。

4.4 核心查询 SQL：如何查用户权限？

表设计好了，最终要能查出「某个用户到底有哪些权限」。说白了，就是一个四表联查。

-- 查询用户 'zhangsan' 拥有的所有权限编码
SELECT DISTINCT p.perm_code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
JOIN role_permission rp ON r.id = rp.role_id
JOIN permissions p ON rp.perm_id = p.id
WHERE u.username = 'zhangsan'
  AND u.status = 1
  AND r.is_system = 1; -- 或者根据业务过滤

一个小优化： 如果用户量和权限量非常大，我会建议在应用层做缓存。比如用户登录时，一次性把他的所有权限编码查出来，缓存到 Redis 里。避免每次请求都去联查这五张表，数据库扛不住的。

4.5 我在项目中踩过的一个坑

曾经，我在一个老项目里接手了一套权限系统。设计者把用户和角色的关联关系直接存在 users 表的一个 role_ids 字段里，用逗号分隔。当时觉得挺简单，后来要支持「一个用户有多个角色且不同角色有不同权限时」，查询逻辑变得极其复杂，而且索引完全用不上。

那次重构我花了整整两天时间，把数据迁移到标准的多对多关联表。所以我的经验是：一开始就老老实实用关联表，别走捷径。

4.6 扩展思考：双人审批场景下的表设计

我们这个课程的主题是 双人审批与 RBAC 融合。那么你可能会问：双人审批的权限表要不要单独设计？

我的建议是：不要单独建表。双人审批是一种流程模式，而不是一种新的权限类型。你可以在 permissions 表里增加一个字段，比如 approval_flow，标识这个权限是否需要双人审批，以及具体的审批角色。

举个例子：

ALTER TABLE `permissions`
ADD COLUMN `approval_flow` TINYINT UNSIGNED DEFAULT 0 COMMENT '审批模式: 0 单签, 1 双人审批, 2 会签',
ADD COLUMN `first_approver_role` BIGINT UNSIGNED DEFAULT NULL COMMENT '初审角色ID',
ADD COLUMN `second_approver_role` BIGINT UNSIGNED DEFAULT NULL COMMENT '复审角色ID';

这样，你既复用了现有的 RBAC 表结构，又为双人审批场景留出了扩展点。耦合度低，以后改起来也方便。

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