下面是权限框架会用到的五张最基础的表设计
数据库采用 mysql
1、表设计
用户表
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `user` */ insert into `user`(`id`,`username`,`password`,`email`) values (1,'admin','admin',NULL),(2,'zhangsan','admin',NULL),(3,'hechen','admin',NULL);用户、角色关联表
DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `user_id` bigint(20) NOT NULL, `role_id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `user_role` */ insert into `user_role`(`user_id`,`role_id`) values (2,2);权限表
DROP TABLE IF EXISTS `permission`; CREATE TABLE `permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `resource` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; -- 数据 insert into `permission`(`id`,`name`,`resource`) values (6,'员工删除','employee:delete'),(7,'员工保存','employee:save'),(8,'员工编辑','employee:edit'),(15,'用户列表','employee:list'),(16,'查询用户列表','user:list'),(17,'增加用户','user:add'),(18,'修改用户','user:update'),(19,'删除用户','user:delete'),(20,'查询用户','user:get');角色表
DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `sn` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `role` */ insert into `role`(`id`,`name`,`sn`) values (1,'部门经理','deptMgr'),(2,'人事经理','empMgr');角色、权限关联表
CREATE TABLE `role_permission` ( `role_id` bigint(20) NOT NULL, `permission_id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `role_permission` */ insert into `role_permission`(`role_id`,`permission_id`) values (2,7),(2,15);一键导入表
CREATE DATABASE /*!32312 IF NOT EXISTS*/`shiro` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `shiro`; /*Table structure for table `permission` */ DROP TABLE IF EXISTS `permission`; CREATE TABLE `permission` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `resource` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `permission` */ insert into `permission`(`id`,`name`,`resource`) values (6,'员工删除','employee:delete'),(7,'员工保存','employee:save'),(8,'员工编辑','employee:edit'),(15,'用户列表','employee:list'),(16,'查询用户列表','user:list'),(17,'增加用户','user:add'),(18,'修改用户','user:update'),(19,'删除用户','user:delete'),(20,'查询用户','user:get'); /*Table structure for table `role` */ DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `sn` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `role` */ insert into `role`(`id`,`name`,`sn`) values (1,'部门经理','deptMgr'),(2,'人事经理','empMgr'); /*Table structure for table `role_permission` */ DROP TABLE IF EXISTS `role_permission`; CREATE TABLE `role_permission` ( `role_id` bigint(20) NOT NULL, `permission_id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `role_permission` */ insert into `role_permission`(`role_id`,`permission_id`) values (2,7),(2,15); /*Table structure for table `user` */ DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `user` */ insert into `user`(`id`,`username`,`password`,`email`) values (1,'admin','admin',NULL),(2,'zhangsan','admin',NULL),(3,'hechen','admin',NULL); /*Table structure for table `user_role` */ DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `user_id` bigint(20) NOT NULL, `role_id` bigint(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; /*Data for the table `user_role` */ insert into `user_role`(`user_id`,`role_id`) values (2,2);2、表操作
基础查询
SELECT u.* FROM USER u ; -- 用户权限关联表 SELECT ur.* FROM user_role ur; -- 权限表 SELECT p.* FROM permission p; -- 角色表 SELECT r.* FROM role r; -- 用户角色、权限关联表 SELECT rp.* FROM role_permission rp;关联查询
-- 查找某个用户关联的角色 SELECT u.id, u.`username`, r.`name`, r.`sn` FROM USER u, user_role ur, role r WHERE u.`id` = ur.`user_id` AND ur.`role_id` = r.`id` AND u.id = 2 -- 查找某个用户的权限 SELECT u.id, u.`username`, r.`name`, p.name, p.resource FROM USER u, user_role ur, role r, permission p, role_permission rp WHERE u.`id` = ur.`user_id` AND ur.`role_id` = r.`id` AND r.`id` = rp.role_id AND rp.permission_id = p.id AND u.id = 2
