下面是权限框架会用到的五张最基础的表设计数据库采用 mysql1、表设计用户表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 ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB AUTO_INCREMENT21 DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; -- 数据 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 ) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB AUTO_INCREMENT21 DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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 ) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATCOMPACT; /*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