Shiro权限框架之数据库表设计

阳光穿透心脏的1/2处 2024-04-18 20:05 52阅读 0赞

**使用Shiro作为权限框架，我们一般用到 5 张表：**

*  用户表 (user)
 *  角色表 (role)
 *  权限表 (permission)
 *  用户 - 角色关联表 (user\_role ；一对多的关系，一个角色对应多个用户)
 *  角色 - 权限关联表 (role\_permission ；多对多关系)

**创建表：**

drop table if exists sys_users;
    drop table if exists sys_roles;
    drop table if exists sys_permissions;
    drop table if exists sys_users_roles;
    drop table if exists sys_roles_permissions;
    
    create table sys_users (
      id bigint auto_increment comment '编号',
      username varchar(100) comment '用户名',
      password varchar(100) comment '密码(需加密)',
      salt varchar(100) comment '盐值(因为密码是经过Shiro加密的，需要通过盐值校验，由Shiro生成，不需要用户手动填写)',
      role_id varchar(50) comment '角色列表(这个字段不是必须的，仅实现在展示用户信息的时候能同时展示用户当前角色)',
      locked bool default false comment '是否锁定(决定当前账户是否是锁定的)',
      constraint pk_sys_users primary key(id)
    ) charset=utf8 ENGINE=InnoDB;
    create unique index idx_sys_users_username on sys_users(username);
    
    create table sys_roles (
      id bigint auto_increment comment '角色编号',
      role varchar(100) comment '角色名称',
      description varchar(100) comment '角色描述',
      pid bigint comment '父节点(当前的角色可能有上级节点)',
      available bool default false comment '是否锁定',
      constraint pk_sys_roles primary key(id)
    ) charset=utf8 ENGINE=InnoDB;
    create unique index idx_sys_roles_role on sys_roles(role);
    
    create table sys_permissions (
      id bigint auto_increment comment '编号',
      permission varchar(100) comment '权限编号',
      description varchar(100) comment '权限描述',
      rid bigint comment '此权限关联角色的id,不是必要,后端角色更新时会用到',
      available bool default false comment '是否锁定',
      constraint pk_sys_permissions primary key(id)
    ) charset=utf8 ENGINE=InnoDB;
    create unique index idx_sys_permissions_permission on sys_permissions(permission);
    
    create table sys_users_roles (
      id  bigint auto_increment comment '编号',
      user_id bigint comment '用户编号',
      role_id bigint comment '角色编号',
      constraint pk_sys_users_roles primary key(id)
    ) charset=utf8 ENGINE=InnoDB;
    
    create table sys_roles_permissions (
      id bigint auto_increment comment '编号',
      role_id bigint comment '角色编号',
      permission_id bigint comment '权限编号',
      constraint pk_sys_roles_permissions primary key(id)
    ) charset=utf8 ENGINE=InnoDB;

**初始化数据：**

insert into sys_users values(1,'admin','123456','salt','管理员',0);
    insert into sys_roles values(1,'user:select','用户创建',0,0);
    insert into sys_permissions values(1,'user:select','用户创建',0,0);
    insert into sys_users_roles values(1,1,1);
    insert into sys_roles_permissions values(1,1,1);

**常用 sql ：**

-- 根据用户名查询角色
    SELECT r.id,r.role,r.description,r.pid,r.available 
    FROM sys_roles r,sys_users u,sys_users_roles ur 
    WHERE u.username = "admin"
    AND r.id = ur.role_id 
    AND u.id=ur.user_id;
    -- 根据用户名查询权限
    SELECT p.id,p.permission,p.description,p.rid,p.available 
    FROM sys_permissions p,sys_users u,sys_roles r,sys_roles_permissions rp,sys_users_roles ur 
    WHERE u.username='admin'
    AND r.id=rp.role_id 
    AND u.id=ur.user_id 
    AND r.id=ur.role_id 
    AND p.id=rp.permission_id;
    -- 根据角色id查询其权限
    SELECT p.id,p.permission,p.description,p.rid,p.available 
    FROM sys_permissions p, sys_roles r, sys_roles_permissions rp 
    WHERE r.id = 1 
    AND rp.role_id = r.id 
    AND rp.permission_id = p.id;
    -- 根据权限ID查询其角色
    SELECT r.id, r.description, r.pid 
    FROM sys_permissions p, sys_roles r, sys_roles_permissions rp 
    WHERE p.id = 1 
    AND p.id = rp.permission_id 
    AND r.id = rp.role_id;
    -- 通过更新的角色数据来更新sys_users表中的role_id字段
    UPDATE sys_users u, sys_users_roles ur 
    SET u.role_id = '更新' 
    WHERE ur.role_id = 1
    AND u.id = ur.user_id;

结语：Shiro用作权限的表结构就是这些，可满足大部分需求，但是在实际开发过程中，根据业务的不同去改变部分内容，使其能更符合实际业务才是最重要的。