3. 权限数据模型设计：五张核心表的设计与SQL实现

好，咱们直接进入正题。权限系统说到底，就是解决「谁」能「做什么」的问题。而数据模型，就是把这个逻辑落地到数据库里。

我见过不少团队，一上来就搞复杂的ABAC（基于属性的权限控制），结果连最基本的RBAC都没跑通。我个人习惯是：先做好RBAC，再考虑扩展。今天咱们就聊聊最经典的5张表设计。

3.1 用户表（User）—— 权限的起点

用户表没什么花头，但有几个坑我得提醒你。

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(128) NOT NULL COMMENT '密码（加密后）',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0禁用 1启用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
注意：密码字段长度至少128位。为什么？因为现在主流加密算法（如BCrypt）输出的哈希值长度不短。我曾经见过有人用varchar(32)存密码，结果BCrypt的哈希截断了，用户永远登不上——排查了半天才发现是字段长度不够。

另外，status字段我建议用数字而不是字符串。0和1的判断在代码里更干净，索引效率也更高。

3.2 角色表（Role）—— 权限的载体

角色，说白了就是一组权限的集合。在CRM里，常见的角色有：销售主管、客户经理、数据管理员等。

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(50) NOT NULL COMMENT '角色名称',
  `role_code` varchar(50) NOT NULL COMMENT '角色编码（唯一）',
  `description` varchar(200) DEFAULT NULL COMMENT '角色描述',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序号',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0禁用 1启用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

这里有个设计细节：role_code 我用了唯一索引。为什么？因为代码里经常要通过编码来查找角色，比如 ROLE_SALES_MANAGER。用编码比用ID更直观，也方便做国际化。

小技巧：角色编码建议用大写+下划线，比如 ROLE_ADMIN、ROLE_SALES。这样在代码里一眼就能看出是角色常量。

3.3 权限表（Permission）—— 权限的定义

权限表定义的是「能做什么」。在CRM里，权限通常对应一个URL或一个按钮。

CREATE TABLE `sys_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '权限ID',
  `perm_name` varchar(50) NOT NULL COMMENT '权限名称',
  `perm_code` varchar(100) NOT NULL COMMENT '权限编码（如 customer:create）',
  `perm_type` tinyint(4) DEFAULT '1' COMMENT '类型：1菜单 2按钮 3API',
  `parent_id` bigint(20) DEFAULT '0' COMMENT '父权限ID',
  `path` varchar(200) DEFAULT NULL COMMENT '路由路径',
  `component` varchar(200) DEFAULT NULL COMMENT '组件路径',
  `icon` varchar(50) DEFAULT NULL COMMENT '图标',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序号',
  `status` tinyint(4) DEFAULT '1',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_perm_code` (`perm_code`),
  KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

嗯，这里要注意 perm_code 的设计。我建议用「冒号分隔」的层级结构，比如：

  • customer:list —— 查看客户列表
  • customer:create —— 创建客户
  • customer:edit —— 编辑客户
  • customer:delete —— 删除客户

这种命名方式，在代码里做权限校验时特别方便。比如判断用户是否有「客户管理」的权限，直接检查 customer:* 通配符就行。

核心思路：权限编码就是权限的「身份证」。设计得好，后续的权限校验、权限树展示都会很顺畅。设计得不好...嗯，我见过有人用自增ID当权限标识，结果每次加权限都要改代码。

3.4 用户-角色关联表（User-Role）—— 多对多的桥梁

一个用户可以有多个角色，一个角色也可以属于多个用户。这就是典型的多对多关系。

CREATE TABLE `sys_user_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_role` (`user_id`, `role_id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户-角色关联表';

这里有个关键点：联合唯一索引。为什么要加？防止同一个用户被重复分配同一个角色。我曾经在项目里见过，因为没加唯一索引，用户被分配了3次「销售主管」角色，结果权限校验时查出3条记录，代码逻辑直接崩了。

避坑指南：关联表不要只建在user_id上建索引。因为查询「某个角色下有哪些用户」也很常见，所以给role_id也建个索引。这叫「覆盖查询」，能避免全表扫描。

3.5 角色-权限关联表（Role-Permission）—— 权限的分配

这张表决定了「角色能做什么」。和用户-角色表类似，也是多对多关系。

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

你想想看，这张表其实就决定了整个权限系统的灵活性。比如：

  • 给「销售主管」角色分配 customer:list、customer:create、customer:edit
  • 给「普通销售」角色只分配 customer:list、customer:create

这样，不同角色的权限就区分开了。而且修改权限时，只需要改这张表的数据，不需要改代码。

3.6 五张表的关系总览

咱们用一张图来总结这五张表的关系（嗯，虽然不能画图，但用文字描述一下）：

表名 核心字段 作用
sys_user id, username, password, status 存储用户基本信息
sys_role id, role_name, role_code, status 定义角色（权限组）
sys_permission id, perm_name, perm_code, parent_id 定义具体的权限点
sys_user_role user_id, role_id 用户与角色的关联
sys_role_permission role_id, permission_id 角色与权限的关联

说白了，权限校验的流程就是：

  1. 通过用户ID，在 sys_user_role 里找到所有角色ID
  2. 通过角色ID，在 sys_role_permission 里找到所有权限ID
  3. 通过权限ID，在 sys_permission 里拿到具体的权限编码
  4. 判断当前请求的权限编码是否在用户的权限列表中
性能优化建议：实际项目中，不要每次都查数据库。我习惯在用户登录时，把用户的权限列表缓存到Redis里。这样后续的权限校验走缓存，毫秒级响应。

3.7 扩展思考：数据权限怎么处理？

咱们今天聊的是「功能权限」——能不能访问某个页面或按钮。但CRM里还有个更头疼的问题：数据权限。

比如：销售A只能看自己的客户，销售主管能看整个团队的客户，管理员能看所有客户。这怎么设计？

嗯，这个咱们后面章节会详细讲。但简单提一句：数据权限通常不放在这5张表里，而是通过「数据权限规则表」来实现。比如定义一条规则：「用户只能查看 owner_id = 当前用户ID 的客户」。

好了，这一章就到这里。五张表的设计，说白了就是「用户-角色-权限」三层模型，加上两张关联表。把这个基础打牢了，后面再复杂的权限需求都能往上加。

下一章，咱们聊聊权限校验的代码实现——怎么在Spring Boot里用拦截器做权限校验。

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