Shiro表结构设计

冷不防 2021-11-16 15:26 452阅读 0赞

## 表设计 ##

开发用户-角色-权限管理系统，首先我们需要知道用户-角色-权限管理系统的表结构设计。  
在用户-角色-权限管理系统找那个一般会涉及5张表，分别为：

1.sys\_users用户表  
2.sys\_roles角色表  
3.sys\_permissions权限表（或资源表）  
4.sys\_users\_roles用户-角色关联表  
5.sys\_roles\_permissions角色-权限关联表（或角色-资源关联表）

详细的建表信息如下

**sys\_users**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpa3VucGVuZzY2NTYyMDE_size_16_color_FFFFFF_t_70]  
**解释**  
用户表中至少包含以上的字段，主键id、用户名username、密码password、盐值salt（因为密码是经过Shiro加密的，需要通过盐值校验，由Shiro生成，不需要用户手动填写）、角色列表roleId（这个字段不是必须的，仅实现在展示用户信息的时候能同时展示用户当前角色）、是否锁定locked（决定当前账户是否是锁定的）。  
创建新的用户，仅需要输入用户名和密码即可，盐值由Shiro生成，角色列表和是否锁定都可以在后期管理。  
其中是否锁定字段类型为tinyint(1)，设置这种类型，数据库中实际存储的是int类型数据，一般是0和1，在使用Mybatis取这个字段的数据时，Mybatis会自动将tinyint(1)字段值为0的转换成false，将字段值为1以上的转换为true。

**sys\_roles**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpa3VucGVuZzY2NTYyMDE_size_16_color_FFFFFF_t_70 1]  
**解释**  
角色表中role角色名称一般为存储着类似user:create这种格式，Shiro在Realm中校验用户身份的时候会通过role这个字段值进行校验；description是此角色的描述信息，比如用户创建。  
其中pid表示父节点，就是说，当前的角色可能有上级节点，比如老师，这个角色可能就有父节点计科教师，如果存在父节点，这个字段值就是父级节点的ID，根据这个ID，在展示数据的时候就很方便的展示出其在哪个父节点下。  
available表示当前节点是否锁定，同样是tinyint(1)类型，如果为false就说明没有锁定。

**sys\_users\_roles**  
![在这里插入图片描述][20190728211028425.png]  
**解释**  
用户角色表就比较简单了，仅仅包含了主键id、用户user\_id、角色role\_id；这张表主要描述指定用户与角色间的依赖关系。其中用户表与角色表是一对多的关系，一个用户可以拥有多个角色。

**sys\_permissions**  
![在这里插入图片描述][20190728211017207.png]

**解释**  
权限表和角色表类似，其中不同的字段是rid，这个字段表示此权限关联的角色的id值，当然不是必要的，但是后端角色更新时用到了，后面会介绍。

**sys\_roles\_permissions**  
![在这里插入图片描述][20190728211051180.png]  
**解释**  
角色-权限表和用户-角色表类似，包含了主键id、角色role\_id、权限permission\_id，主要描述角色和权限间的依赖关系，同样，角色和权限间也是一对多的关系，一个角色会关联多个权限。

**schema.sql**  
上述表设计的源码如下：

-- create database shiro default character set utf8;
    
    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 '盐值',
      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;

**拓展**  
上面就是当前用户-角色-权限系统的表设计。细心的你可能会发现这些表中并不包含外键约束。  
为什么不设计外键呢？  
可能你会跟我一样有这样的疑问。比如用户表和角色表间，我们这里创建了用户-角色表来实现两者的关联；并没有通过给两张表建立外键来实现一对多、多对多的关联关系。  
因为如果你要建立外键来关联两张表，你需要遇到如下：

如果两张表之间存在一对多的关系，在给一的一方新增数据的时候，你要考虑多的一方是否存在指定的id。  
如果两张表之间存在一对多的关系，在删除多的一方时你要先删除其关联的一的一方，再删除多的一方。  
…

也就是说如果使用了外键关联，那么在对表进行数据操作时就必须考虑另一张关联的表，相当于两张表就绑在一起了，操作这张表就必须考虑另一张关联表。  
但是实际中，我们不想立即就修改或更新关联表的数据，我可能一会再去更新另一张关联表的数据，那么就产生了这种方式：通过单独建立一张关联来实现两张表的数据关联。  
所以，我建议大家在设计表时尽量减少表与表直接的外键约束，这样能避免很多麻烦，并且两张表之间的关联关系也会格外清晰。  
实例  
上面创建了用户-角色-权限表以及其关联表，下面就介绍一些实际的案例吧！  
**初始化表**

insert into sys_users values(1,'TyCoding','123','salt','管理员',0);
    insert into sys_roles values(21,'user:create','用户创建',0,0);
    insert into sys_permissions values(31,'user:create','用户创建',0,0);
    insert into sys_users_roles values(1,1,21);
    insert into sys_roles_permissions values(1,21,31);

## **查询** ##

**根据用户名查询其角色**

[mysql> SELECT r.id, r.role, r.description FROM sys_users u, sys_roles r, sys_users_roles ur WHERE u.username = 'TyCoding' AND u.id = ur.user_id AND r.id = ur.role_id;
    +----+-------+-------------+
    | id | role  | description |
    +----+-------+-------------+
    | 21 | admin | 管理员      |
    +----+-------+-------------+
    1 row in set (0.00 sec)

从上面的SQL中足以看出，通过关联表查询另一张关联的数据要点在于WHERE条件中添加关联表与两张表的关系，在这里即是关联表中存在两张表的主键id，所以把相同的字段加入WHERE条件过滤就能查询到了。

**根据用户名查询其权限**

[mysql> SELECT p.id, p.permission, p.description FROM sys_users u, sys_roles r, sys_users_roles ur, sys_permissions p, sys_roles_permissions rp WHERE u.username = 'TyCoding' AND u.id = ur.user_id AND r.id = ur.role_id AND r.id = rp.role_id AND p.id = rp.permission_id;
    +----+-------------+--------------+
    | id | permission  | description  |
    +----+-------------+--------------+
    | 31 | user:create | 用户创建     |
    +----+-------------+--------------+
    1 row in set (0.00 sec)

**根据角色id查询其权限**

[mysql> SELECT p.id, p.description FROM sys_permissions p, sys_roles r, sys_roles_permissions rp WHERE r.id = 21 AND rp.role_id = r.id AND rp.permission_id = p.id;
    +----+--------------+
    | id | description  |
    +----+--------------+
    | 31 | 用户创建     |
    +----+--------------+
    1 row in set (0.00 sec)

**更新user表中role\_id字段**  
我们设计的sys\_users表中，存在一个字段role\_id，目的是用来展示当前用户关联的角色名称，但是我们直接更新sys\_roles表的description字段时，又不会更新sys\_users表中的role\_id字段。  
所以，这里我们要通过更新的角色数据来更新sys\_users表中的role\_id字段。

**根据权限ID查询其角色**

[mysql> SELECT r.id, r.description, r.pid FROM sys_permissions p, sys_roles r, sys_roles_permissions rp WHERE p.id = 31 AND p.id = rp.permission_id AND r.id = rp.role_id;
    +----+-------------+------+
    | id | description | pid  |
    +----+-------------+------+
    | 21 | 管理员      |    0 |
    +----+-------------+------+
    1 row in set (0.00 sec)

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpa3VucGVuZzY2NTYyMDE_size_16_color_FFFFFF_t_70]: /images/20211116/2ad5ae56314b404ab5fee756e16cdd76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpa3VucGVuZzY2NTYyMDE_size_16_color_FFFFFF_t_70 1]: /images/20211116/c649413e75e342b5a6c86aee0befea88.png
[20190728211028425.png]: /images/20211116/2c4556ae5ecd4960a2542434803016c9.png
[20190728211017207.png]: /images/20211116/ea7d3a4dbb38452fb7faef0ba5d6cfe4.png
[20190728211051180.png]: /images/20211116/969d15cc86864732afc6c904bcd1ce96.png