1.用户、角色、权限表(mysql)设计

逻辑图

建表

用户表

/* 用户表 */
DROP TABLE IF EXISTS `sa_user`;
CREATE TABLE `sa_user` (
   `id` varchar(40) NOT NULL COMMENT'用户ID',
   `username` varchar(20) NOT NULL COMMENT'用户名',
	 `password` varchar(100) 	NOT NULL COMMENT'密码',
	 `enable` BOOLEAN DEFAULT TRUE NOT NULL COMMENT'是否启用',
	 `is_delete` BOOLEAN DEFAULT FALSE NOT NULL COMMENT '是否删除（伪删除）',
	 `create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	 `update` datetime DEFAULT NULL COMMENT '更新时间',
   PRIMARY KEY (`id`),
	 UNIQUE KEY `uq_username` (`username`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

角色表

/* 角色表 */
DROP TABLE IF EXISTS `sa_role`;
CREATE TABLE `sa_role` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT'角色ID',
   `name` varchar(20) NOT NULL COMMENT'角色名称',
   `description` varchar(255) DEFAULT NULL COMMENT'角色描述',
	 `order_num` int NOT NULL DEFAULT 1 COMMENT '排序',
	 `create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	 `update` datetime DEFAULT NULL COMMENT '更新时间',
   PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

权限表

/* 权限表 */
DROP TABLE IF EXISTS `sa_permission`;
CREATE TABLE `sa_permission` (
		`id` int(11) NOT NULL AUTO_INCREMENT COMMENT'权限ID',
		`parent_id` int NOT NULL DEFAULT 0 COMMENT '父菜单id (paren_id为0 => type为M是一级菜单)',
		`name` varchar(20) NOT NULL COMMENT'权限名称',
		`menu_type` char(1) NOT NULL COMMENT '权限类型 (R管理 M目录 C菜单)',
		`perms` varchar(100) NULL DEFAULT '' COMMENT '权限标识',
		`description` varchar(255) DEFAULT NULL COMMENT'权限描述',
		`order_num` int NOT NULL COMMENT '排序',
	  `create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
		`update` datetime DEFAULT NULL COMMENT '更新时间',
		PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

用户角色关系表

DROP TABLE IF EXISTS `user_role`;
 CREATE TABLE `user_role` (
		`id` int(11) NOT NULL AUTO_INCREMENT COMMENT'用户角色ID',
		`user_id` varchar(40) NOT NULL COMMENT'用户ID',
		`role_id` int(11) NOT NULL COMMENT'角色ID',
		`create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
		`update` datetime DEFAULT NULL COMMENT '更新时间',
		PRIMARY KEY (`id`)
-- 		 KEY `fk_user_role_t_role_1` (`role_id`),
-- 		 KEY `fk_user_role_t_user_1` (`user_id`),
-- 		 CONSTRAINT `fk_user_role_t_role_1` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
-- 		 CONSTRAINT `fk_user_role_t_user_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

角色权限关系表

/* 角色权限关系表 */
DROP TABLE IF EXISTS `role_permission`;
CREATE TABLE `role_permission` (
		`id` int(11) NOT NULL AUTO_INCREMENT  COMMENT'角色权限ID',
		`role_id` int(11) NOT NULL COMMENT'角色ID',
		`permission_id` int(11) NOT NULL COMMENT'权限ID',
		`create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
		`update` datetime DEFAULT NULL COMMENT '更新时间',
		PRIMARY KEY (`id`)
--    KEY `fk_role_permission_t_permission_1` (`permission_id`),
--    KEY `fk_role_permission_t_role_1` (`role_id`),
--    CONSTRAINT `fk_role_permission_t_permission_1` FOREIGN KEY (`permission_id`) REFERENCES `t_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
--    CONSTRAINT `fk_role_permission_t_role_1` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=utf8;

插入数据

/* ---------------------------------------------------------------------------------------------- */
/* ---------------------------------------------------------------------------------------------- */
/* ---------------------------------------------------------------------------------------------- */

/* 插入用户数据 */
SELECT * FROM `sa_user`;

INSERT INTO `sa_user` (`id`,`username`,`password`) 
VALUES 
('u1','root','$2a$10$CiFDQQ5vRiw4MV/kqXRE7u449AjRvzab0dXch5WDTvDl5Fbi2X0Oi'),
('u2','admin','$2a$10$CiFDQQ5vRiw4MV/kqXRE7u449AjRvzab0dXch5WDTvDl5Fbi2X0Oi'),
('u3','user1','$2a$10$CiFDQQ5vRiw4MV/kqXRE7u449AjRvzab0dXch5WDTvDl5Fbi2X0Oi'),
('u4','user2','$2a$10$CiFDQQ5vRiw4MV/kqXRE7u449AjRvzab0dXch5WDTvDl5Fbi2X0Oi'),
('u5','user3','$2a$10$CiFDQQ5vRiw4MV/kqXRE7u449AjRvzab0dXch5WDTvDl5Fbi2X0Oi'),
('u6','user4','$2a$10$CiFDQQ5vRiw4MV/kqXRE7u449AjRvzab0dXch5WDTvDl5Fbi2X0Oi');

/* 插入角色数据 */
INSERT INTO `sa_role` (`name`,`description`,`order_num`) 
VALUES 
('root','超级用户',0),
('admin','管理员',1),
('user','普通用户',2);


/* 插入权限数据 */
INSERT INTO `sa_permission` (`id`,`parent_id`,`name`,`menu_type`,`perms`,`description`,`order_num`) 
VALUES 
(1,0,'文章管理','M','blog:*','文章管理',1),
(2,1,'添加文章','C','blog:article:add','添加文章',1),
(3,1,'修改文章','C','blog:article:update','修改文章',1),
(4,1,'删除文章','C','blog:article:delete','删除文章',1),


(5,0,'用户管理','M','user:*','用户管理',2),
(6,5,'修改用户邮箱','C','user:email:update','修改用户邮箱',2),
(7,5,'修改用户头像','C','user:avatar:update','修改用户头像',2),
(8,5,'修改用户密码','C','user:password:update','修改用户密码',2),
(9,5,'重置用户密码','C','user:password:reset','重置用户密码',2),


(10,0,'系统管理','M','system:*','系统管理',3),
(11,10,'添加角色','C','system:role:add','添加角色',3),
(12,10,'删除角色','C','system:role:delete','删除角色',3),
(13,10,'修改角色','C','system:role:update','修改角色',3);


/* 用户角色关系表插入数据 */
INSERT INTO `user_role` (`user_id`,`role_id`) 
VALUES 
('u1',1),
('u2',2),
('u3',3),
('u4',3),
('u5',3),
('u6',3);


/* 角色权限关系表插入数据 */
INSERT INTO `role_permission` (`role_id`,`permission_id`) 
VALUES 
(1,1),
(1,5),
(1,10),

(2,1),
(2,5),

(3,1);
/* ---------------------------------------------------------------------------------------------- */
/* ---------------------------------------------------------------------------------------------- */
/* ---------------------------------------------------------------------------------------------- */

操作演示

查询拥有某角色的用户信息

SELECT
    u.id,u.username
FROM
    sa_user u,sa_role r,user_role ur
WHERE
    r.id = 1 AND ur.role_id=r.id AND u.id = ur.user_id;

查询某用户的对应的角色

SELECT
    u.id , u.username , r.`name` role_name
FROM 
    sa_user u,sa_role r,user_role ur
WHERE
    u.username LIKE "a%" AND u.id=ur.user_id AND ur.role_id=r.id;

查询拥有某权限的角色

/* 查询拥有 某权限 的 角色 */


-- 测试：设置一个变量
SET @test_permission_name = "重置用户密码";

-- 1.查询某权限
SELECT * FROM sa_permission WHERE `name`=@test_permission_name;

SELECT * FROM role_permission;

-- 2.查询 某权限 与 角色 对应的id
SELECT rp.role_id,rp.permission_id,p.`name`
FROM 
    (
        SELECT * FROM sa_permission WHERE `name`=@test_permission_name
    ) p
JOIN 
    role_permission rp 
ON 
    rp.permission_id = p.id OR rp.permission_id = p.parent_id;

-- 3.查询拥有 某权限 的 角色 
SELECT * FROM sa_role;

SELECT r.`name` as '用户名',r.description as '描述',res.`name` as '权限'
FROM 
    sa_role r
JOIN (
        SELECT rp.role_id,rp.permission_id,p.`name`
        FROM (
                SELECT * FROM sa_permission WHERE `name`=@test_permission_name
            ) p
        JOIN 
            role_permission rp 
        ON rp.permission_id = p.id OR rp.permission_id = p.parent_id
    ) res
ON res.role_id = r.id;

查询某角色拥有的权限

-- root admin user
SET @test_role_name = "user";

-- 1.查询某角色
SELECT * FROM sa_role WHERE `name`=@test_role_name;
SELECT * FROM role_permission;


-- 2.查询 某角色 与 权限 对应的id
SELECT r.`name`,r.description, rp.permission_id
FROM (
		SELECT * FROM sa_role WHERE `name`=@test_role_name
) r
JOIN role_permission rp ON rp.role_id = r.id;


-- 3.查询 某角色 拥有 的权限
-- SELECT * FROM sa_permission;
SELECT res.`name` as '角色',res.description as '角色描述',p.`name` as '权限',p.`description` as '权限描述'
FROM sa_permission p
JOIN (
		SELECT r.`name`,r.description, rp.permission_id
		FROM (
				SELECT * FROM sa_role WHERE `name`=@test_role_name
		) r
		JOIN role_permission rp ON rp.role_id = r.id
) res
ON res.permission_id = p.id OR res.permission_id = p.parent_id;

查询某用户拥有的权限

SET @test_user_name = "root";

SELECT * FROM sa_user;
SELECT * FROM sa_role;
SELECT * FROM user_role;
SELECT * FROM role_permission;
SELECT * FROM sa_permission;


-- 1.查询 某用户
SELECT * FROM sa_user WHERE `username`=@test_user_name;

-- 2.查询 某用户 与 角色对应 的 id
SELECT u.`username`,u.`enable`,ur.user_id,ur.role_id
FROM (
		SELECT * FROM sa_user WHERE `username`=@test_user_name
) u
JOIN user_role ur ON ur.user_id = u.id;

-- 写法2
SELECT u.`username`,u.`enable`,ur.user_id,ur.role_id
FROM
	sa_user u
JOIN
	user_role ur
ON
	ur.user_id = u.id
WHERE 				
	u.`username`=@test_user_name


-- 3.查询 某角色 拥有 的权限
SELECT se_ur.user_id,se_ur.`username`,se_ur.`enable`,rp.permission_id 
FROM
	role_permission rp
JOIN
	(
		SELECT u.`username`,u.`enable`,ur.user_id,ur.role_id
		FROM (
				SELECT * FROM sa_user WHERE `username`=@test_user_name
		) u
		JOIN user_role ur ON ur.user_id = u.id
	)	se_ur
	ON
		se_ur.role_id = rp.role_id;
		
-- 写法2
SELECT se_ur.user_id,se_ur.`username`,se_ur.`enable`,rp.permission_id 
FROM
	role_permission rp
JOIN
	(
		SELECT u.`username`,u.`enable`,ur.user_id,ur.role_id
		FROM
			sa_user u
		JOIN
			user_role ur
		ON
			ur.user_id = u.id
		WHERE 				
			u.`username`=@test_user_name
	)	se_ur
ON
		se_ur.role_id = rp.role_id;
                
-- 4.查询 某用户 拥有的 权限
SELECT res.user_id,res.`username`,res.`enable`,res.permission_id , p.`name`,p.description
FROM
	sa_permission p
JOIN
	(
		SELECT se_ur.user_id,se_ur.`username`,se_ur.`enable`,rp.permission_id 
		FROM
			role_permission rp
		JOIN
			(
				SELECT u.`username`,u.`enable`,ur.user_id,ur.role_id
				FROM (
						SELECT * FROM sa_user WHERE `username`=@test_user_name
				) u
				JOIN user_role ur ON ur.user_id = u.id
			)	se_ur
		ON
		se_ur.role_id = rp.role_id
	) res
ON p.id = res.permission_id OR res.permission_id = p.parent_id;                
		

查询拥有某权限的用户

/* 查询拥有 某权限 的 用户 */
SET @test_permission_name = "文章管理";

SELECT * FROM sa_user;
SELECT * FROM sa_role;
SELECT * FROM user_role;
SELECT * FROM role_permission;
SELECT * FROM sa_permission;

SELECT * FROM sa_permission WHERE `name`=@test_permission_name;

SELECT * 
FROM 
	sa_user u
JOIN 
	user_role ur
ON 	
	ur.user_id = u.id
JOIN
	(
		SELECT r.id
		FROM sa_role r
		JOIN (
				SELECT rp.role_id,rp.permission_id,p.`name`
				FROM (
						SELECT * FROM sa_permission WHERE `name`=@test_permission_name
				) p
				JOIN role_permission rp ON rp.permission_id = p.id OR rp.permission_id = p.parent_id
		) res
		ON res.role_id = r.id
	)	res
ON 
	res.id = ur.role_id;