基于RBAC(Role-Based Access Control)角色的权限访问控制。
- 用户表 ( sys_user ):保存用户信息
- 角色表 ( sys_role ):保存角色信息
- 权限表 ( sys_permission ):保存系统资源信息。如:菜单、按钮 和对应 URL
它们的关系 :用户表与角色表是 多对多关系 ,角色表与资源表是多对多关系。
- 用户角色关系表(sys_user_role):用于维护用户和角色的关系
- 角色资源关系表(sys_role_permission):用于维护角色与资源的关系
设计方案一
权限表
DROP TABLE IF EXISTS `sys_permission`;
CREATE TABLE `sys_permission` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '权限 ID',
`parent_id` bigint(20) DEFAULT NULL COMMENT '父权限 ID (0为顶级菜单)',
`name` varchar(64) NOT NULL COMMENT '权限名称',
`code` varchar(64) DEFAULT NULL COMMENT '授权标识符',
`url` varchar(255) DEFAULT NULL COMMENT '授权路径',
`type` int(2) NOT NULL DEFAULT '1' COMMENT '类型(1菜单,2按钮)',
`icon` varchar(200) DEFAULT NULL COMMENT '图标',
`remark` varchar(200) DEFAULT NULL COMMENT '备注',
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='权限表';
角色表
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色 ID',
`name` varchar(64) NOT NULL COMMENT '角色名称',
`remark` varchar(200) DEFAULT NULL COMMENT '角色说明',
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='角色表';
用户表
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(64) NOT NULL COMMENT '密码,加密存储, admin/1234',
`is_account_non_expired` int(2) DEFAULT '1' COMMENT '帐户是否过期(1 未过期,0已过期)',
`is_account_non_locked` int(2) DEFAULT '1' COMMENT '帐户是否被锁定(1 未过期,0已过期)',
`is_credentials_non_expired` int(2) DEFAULT '1' COMMENT '密码是否过期(1 未过期,0已过期)',
`is_enabled` int(2) DEFAULT '1' COMMENT '帐户是否可用(1 可用,0 删除用户)',
`nick_name` varchar(64) DEFAULT NULL COMMENT '昵称',
`mobile` varchar(20) DEFAULT NULL COMMENT '注册手机号',
`email` varchar(50) DEFAULT NULL COMMENT '注册邮箱',
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`) USING BTREE,
UNIQUE KEY `mobile` (`mobile`) USING BTREE,
UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户表';
权限和角色的关系表
DROP TABLE IF EXISTS `sys_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',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COMMENT='角色权限表';
用户和角色的关系表
DROP TABLE IF EXISTS `sys_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',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='用户角色表';
设计方案二
操作类型
DROP TABLE IF EXISTS `tb_action`;
CREATE TABLE `tb_action` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`action_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '行为编号',
`action_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '行为名称',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `unq_action_name`(`action_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '行为表' ROW_FORMAT = DYNAMIC;
INSERT INTO `tb_action` VALUES (1, 'INSERT', '添加');
INSERT INTO `tb_action` VALUES (2, 'DELETE', '删除');
INSERT INTO `tb_action` VALUES (3, 'UPDATE', '修改');
INSERT INTO `tb_action` VALUES (4, 'SELECT', '查询');
INSERT INTO `tb_action` VALUES (5, 'APPROVAL', '审批');
INSERT INTO `tb_action` VALUES (6, 'EXPORT', '导出');
INSERT INTO `tb_action` VALUES (7, 'BACKUP', '备份');
模块表
DROP TABLE IF EXISTS `tb_module`;
CREATE TABLE `tb_module` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`module_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模块编号',
`module_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模块名称',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `unq_module_id`(`module_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '模块资源表' ROW_FORMAT = DYNAMIC;
INSERT INTO `tb_module` VALUES (1, 'USER', '用户管理');
INSERT INTO `tb_module` VALUES (2, 'EMPLOYEE', '员工管理');
INSERT INTO `tb_module` VALUES (3, 'DEPT', '部门管理');
INSERT INTO `tb_module` VALUES (4, 'MEETING', '会议管理');
INSERT INTO `tb_module` VALUES (5, 'WORKFLOW', '工作流管理');
权限表
模块和模块中的操作,就是资源,就是权限。
DROP TABLE IF EXISTS `tb_permission`;
CREATE TABLE `tb_permission` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`permission_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '权限',
`module_id` int(10) UNSIGNED NOT NULL COMMENT '模块ID',
`action_id` int(10) UNSIGNED NOT NULL COMMENT '行为ID',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `unq_permission`(`permission_name`) USING BTREE,
UNIQUE INDEX `unq_complex`(`module_id`, `action_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `tb_permission` VALUES (1, 'USER:INSERT', 1, 1);
INSERT INTO `tb_permission` VALUES (2, 'USER:DELETE', 1, 2);
INSERT INTO `tb_permission` VALUES (3, 'USER:UPDATE', 1, 3);
INSERT INTO `tb_permission` VALUES (4, 'USER:SELECT', 1, 4);
INSERT INTO `tb_permission` VALUES (5, 'EMPLOYEE:INSERT', 2, 1);
INSERT INTO `tb_permission` VALUES (6, 'EMPLOYEE:DELETE', 2, 2);
INSERT INTO `tb_permission` VALUES (7, 'EMPLOYEE:UPDATE', 2, 3);
INSERT INTO `tb_permission` VALUES (8, 'EMPLOYEE:SELECT', 2, 4);
INSERT INTO `tb_permission` VALUES (9, 'DEPT:INSERT', 3, 1);
INSERT INTO `tb_permission` VALUES (10, 'DEPT:DELETE', 3, 2);
INSERT INTO `tb_permission` VALUES (11, 'DEPT:UPDATE', 3, 3);
INSERT INTO `tb_permission` VALUES (12, 'DEPT:SELECT', 3, 4);
INSERT INTO `tb_permission` VALUES (13, 'MEETING:INSERT', 4, 1);
INSERT INTO `tb_permission` VALUES (14, 'MEETING:DELETE', 4, 2);
INSERT INTO `tb_permission` VALUES (15, 'MEETING:UPDATE', 4, 3);
INSERT INTO `tb_permission` VALUES (16, 'MEETING:SELECT', 4, 4);
INSERT INTO `tb_permission` VALUES (17, 'WORKFLOW:APPROVAL', 5, 5);
INSERT INTO `tb_permission` VALUES (18, 'ROOT', 0, 0);
UPDATE tb_permission SET id=0 WHERE permission_name='ROOT';
角色表
采用json类型 permissions json NOT NULL COMMENT '权限集合'
DROP TABLE IF EXISTS `tb_role`;
CREATE TABLE `tb_role` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`role_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',
`permissions` json NOT NULL COMMENT '权限集合',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `unq_role_name`(`role_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = DYNAMIC;
INSERT INTO `tb_role` VALUES (1, '总经理', '[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17]');
INSERT INTO `tb_role` VALUES (2, '部门经理', '[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]');
INSERT INTO `tb_role` VALUES (3, '普通员工', '[1, 2, 3, 4, 5, 6, 7, 8]');
INSERT INTO `tb_role` VALUES (4, '超级管理员', '[0]');
UPDATE tb_role SET id=0 WHERE role_name='超级管理员';
用户表
采用json类型 role json NOT NULL COMMENT '角色',
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`open_id` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '长期授权字符串',
`nickname` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
`photo` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像网址',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`sex` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
`tel` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号码',
`email` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
`hiredate` date NULL DEFAULT NULL COMMENT '入职日期',
`role` json NOT NULL COMMENT '角色',
`root` tinyint(1) NOT NULL COMMENT '是否是超级管理员',
`dept_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '部门编号',
`status` tinyint(4) NOT NULL COMMENT '状态',
`create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `unq_open_id`(`open_id`) USING BTREE,
INDEX `unq_email`(`email`) USING BTREE,
INDEX `idx_dept_id`(`dept_id`) USING BTREE,
INDEX `idx_status`(`status`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;