spring boot 之shrio数据表设计

数据表设计

基本五个数据表

• 权限表
• 用户表
• 角色表
• 用户和角色关联表
• 角色和权限关联表

表的对应关系：

权限表：

用户表

角色表

角色权限关联表

用户角色关联表

对应的bean:（就只用角色表和用户表和数据库有所不同）

• 角色表

package com.learn.shrio.domain;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * 基本用户
 */
public class User implements Serializable {

    private int id;

    private String username;

    private String password;

    private Date createTime;

    private String salt;
    /**
     * 角色集合，一个用户对应多个角色
     */
    private List<Role> roleList;

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

//    public int getAuthCacheKey(){
//        return id;
//    }
}

• 用户表

package com.learn.shrio.domain;

import java.io.Serializable;
import java.util.List;

/**
 * 角色
 */
public class Role implements Serializable {

    private int id;

    private String name;

    private String description;
    //一个角色对应对各权限
    private List<Permission> permissionList;

    public List<Permission> getPermissionList() {
        return permissionList;
    }

    public void setPermissionList(List<Permission> permissionList) {
        this.permissionList = permissionList;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

下面提供数据库脚本：

/*
 Navicat Premium Data Transfer

 Date: 12/07/2019 11:08:14
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for permission
-- ----------------------------
DROP TABLE IF EXISTS `permission`;
CREATE TABLE `permission`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '接口路径',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '权限表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of permission
-- ----------------------------
INSERT INTO `permission` VALUES (1, 'video_update', '/api/video/update');
INSERT INTO `permission` VALUES (2, 'video_delete', '/api/video/delete');
INSERT INTO `permission` VALUES (3, 'video_add', '/api/video/add');
INSERT INTO `permission` VALUES (4, 'order_list', '/api/order/list');
INSERT INTO `permission` VALUES (5, 'user_list', '/api/user/list');

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `description` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, 'admin', '普通管理员');
INSERT INTO `role` VALUES (2, 'root', '超级管理员');
INSERT INTO `role` VALUES (3, 'editor', '审核人员');

-- ----------------------------
-- Table structure for role_permission
-- ----------------------------
DROP TABLE IF EXISTS `role_permission`;
CREATE TABLE `role_permission`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NULL DEFAULT NULL,
  `permission_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role_permission
-- ----------------------------
INSERT INTO `role_permission` VALUES (1, 3, 1);
INSERT INTO `role_permission` VALUES (2, 3, 2);
INSERT INTO `role_permission` VALUES (3, 3, 3);
INSERT INTO `role_permission` VALUES (4, 2, 1);
INSERT INTO `role_permission` VALUES (5, 2, 2);
INSERT INTO `role_permission` VALUES (6, 2, 3);
INSERT INTO `role_permission` VALUES (7, 2, 4);

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `salt` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小强', 'd022646351048ac0ba397d12dfafa304', NULL, '');
INSERT INTO `user` VALUES (2, '小明', '5927c5d64d94a5786f90003aa26d0159', NULL, NULL);
INSERT INTO `user` VALUES (3, 'jack', 'd022646351048ac0ba397d12dfafa304', NULL, NULL);

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `remarks` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 3, 1, '小强是editor');
INSERT INTO `user_role` VALUES (2, 1, 3, 'jack是admin');
INSERT INTO `user_role` VALUES (3, 2, 3, 'jack是root');
INSERT INTO `user_role` VALUES (4, 3, 3, 'jack是editor');
INSERT INTO `user_role` VALUES (5, 1, 2, '小明是admin');
INSERT INTO `user_role` VALUES (8, 1, 1, '小强是admin');
INSERT INTO `user_role` VALUES (10, 2, 1, '小强是root');

SET FOREIGN_KEY_CHECKS = 1;