RBAC权限管理系统设计

￡神魔★判官ぃ 2023-02-17 02:35 70阅读 0赞

## 1 创建数据库 ##

docker run \
    --name security_db \
    -e MYSQL_ROOT_HOST=%.%.%.% \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -p 6969:3306  \
    -di mysql:8.0.18

jdbc:mysql://ip:6969/?serverTimezone=UTC

CREATE DATABASE security_db;
    USE security_db;

--------------------

# 2 设计表  #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70][]

CREATE TABLE `sys_user`
    (
        `id`          VARCHAR(64)  NOT NULL COMMENT '用户ID',
        `username`    varchar(20)  NOT NULL COMMENT '用户名',
        `password`    varchar(100) NOT NULL COMMENT '密码',
        `salt`        varchar(20) DEFAULT NULL COMMENT '盐加密（为null说明是明文存储，需要升级为密文）',
        `status`      tinyint     DEFAULT 1 COMMENT '状态（0禁用、1正常）',
        `create_time` TIMESTAMP   DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
        `update_time` TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        PRIMARY KEY (`id`),
        UNIQUE KEY `uk_user_username` (`username`)
    ) DEFAULT CHARSET = utf8mb4 COMMENT ='用户表';
    CREATE TABLE `sys_role`
    (
        `id`          VARCHAR(64) NOT NULL COMMENT '角色ID',
        `role_name`   varchar(20)  DEFAULT NULL COMMENT '角色名',
        `role_desc`   varchar(250) DEFAULT NULL COMMENT '角色说明',
        `create_time` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
        `update_time` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        PRIMARY KEY (`id`)
    ) DEFAULT CHARSET = utf8mb4 COMMENT ='角色表';
    CREATE TABLE `sys_permission`
    (
        `id`          VARCHAR(64) NOT NULL COMMENT '权限ID',
        `perm_tag`    varchar(50)  DEFAULT NULL COMMENT '权限标识符',
        `perm_desc`   varchar(250) DEFAULT NULL COMMENT '权限说明',
        `create_time` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
        `update_time` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        PRIMARY KEY (`id`)
    ) DEFAULT CHARSET = utf8mb4 COMMENT ='权限表';
    CREATE TABLE `sys_user_role`
    (
        `user_id`     VARCHAR(64) NOT NULL COMMENT '用户ID',
        `role_id`     VARCHAR(64) NOT NULL COMMENT '角色ID',
        `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
        `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        PRIMARY KEY (`user_id`, `role_id`),
        CONSTRAINT `fk_userRole_userId` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
        CONSTRAINT `fk_userRole_roleId` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) DEFAULT CHARSET = utf8mb4 COMMENT ='用户角色表';
    CREATE TABLE `sys_role_permission`
    (
        `role_id`     VARCHAR(64) NOT NULL COMMENT '角色ID',
        `perm_id`     VARCHAR(64) NOT NULL COMMENT '权限ID',
        `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
        `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
        PRIMARY KEY (`role_id`, `perm_id`),
        CONSTRAINT `fk_rolePermission_roleId` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
        CONSTRAINT `fk_rolePermission_permId` FOREIGN KEY (`perm_id`) REFERENCES `sys_permission` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) DEFAULT CHARSET = utf8mb4 COMMENT ='角色权限表';

-- ----------------------------
    -- 用户表 123456
    -- ----------------------------
    INSERT INTO `sys_user`(id, username, salt, password)
    VALUES ('1', '张三', 'e3f9fdf61f5d42bc84db', 'u0h5xlqUh44ccogtzsP0t7L1yAe2mi3OxmtDyLyvZ0c='),
           ('2', '歆瑶', 'e3f9fdf61f5d42bc84db', 'u0h5xlqUh44ccogtzsP0t7L1yAe2mi3OxmtDyLyvZ0c=');
    -- ----------------------------
    -- 角色表
    -- ----------------------------
    INSERT INTO `sys_role`(id, role_name, role_desc)
    VALUES ('100', '辅导员', '超级管理员，拥有所有权限'),
           ('200', '班长', '查询权限');
    -- ----------------------------
    -- 权限表
    -- ----------------------------
    INSERT INTO `sys_permission`(id, perm_tag, perm_desc)
    VALUES ('1000', 'student:add', '增加学生权限'),
           ('2000', 'student:delete', '删除学生权限'),
           ('3000', 'student:update', '修改学生权限'),
           ('4000', 'student:list', '查看学生列表权限'),
           ('5000', 'student:select', '查看学生权限');

-- ----------------------------
    -- 角色权限表
    -- ----------------------------
    -- 辅导员拥有所有权限
    insert into sys_role_permission(role_id, perm_id)
    select r.id rID, p.id pID
    from sys_role r,
         sys_permission p
    where r.role_name = '辅导员';
    -- 班长拥有查询权限
    insert into sys_role_permission(role_id, perm_id)
    select r.id rID, p.id pID
    from sys_role r,
         sys_permission p
    where r.role_name = '班长'
      and (p.perm_tag = 'student:select' or p.perm_tag = 'student:list');

-- ----------------------------
    -- 用户角色表
    -- ----------------------------
    -- 歆瑶是辅导员
    insert into sys_user_role(user_id, role_id)
    select u.id uID, r.id rID
    from sys_user u
             join sys_role r on r.role_name = '辅导员' and u.username = '歆瑶';
    -- 张三是班长
    insert into sys_user_role(user_id, role_id)
    select u.id uID, r.id rID
    from sys_user u
             join sys_role r on r.role_name = '班长' and u.username = '张三';

select u.id        userId,
           u.username  username,
           u.password  password,
           r.id        roleId,
           r.role_name roleName,
           r.role_desc roleDesc,
           p.id        permId,
           p.perm_tag  permTag,
           p.perm_desc permDesc
    from sys_user u
             join sys_user_role ur on ur.user_id = u.id
             join sys_role r on r.id = ur.role_id
             join sys_role_permission rp on rp.role_id = r.id
             join sys_permission p on p.id = rp.perm_id
    where username = '歆瑶'

## ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1][] ##

--------------------

## 3 MyBatis多表联查 ##

>  *  MyBatis 多表操作（一对一）：[https://blog.csdn.net/qq\_40794973/article/details/89600870][https_blog.csdn.net_qq_40794973_article_details_89600870]
>  *  MyBatis 多表操作（一对多）：[https://blog.csdn.net/qq\_40794973/article/details/89603924][https_blog.csdn.net_qq_40794973_article_details_89603924]
>  *  Mybatis中的collection和association：[https://blog.csdn.net/qq\_40794973/article/details/106638913][https_blog.csdn.net_qq_40794973_article_details_106638913]

/**
     * 用户 角色 权限 关联表
     */
    public interface UserRolePermissionMapper {
        /**
         * 通过用户名返回详细的用户信息（用户 角色 权限）
         *
         * @param username 用户名
         */
        User getUserDetailByUsername(String username);
        /**
         * 插入用户和角色关系
         *
         * @param userId 用户id
         * @param roleId 角色id
         */
        void insertUserRoleRelation(@Param("userId") String userId, @Param("roleId") String roleId);
        /**
         * 插入角色和权限关系
         *
         * @param roleId 角色id
         * @param permId 权限id
         */
        void insertRolePermissionRelation(@Param("roleId") String roleId, @Param("permId") String permId);
    }

<resultMap type="cn.yuanyu.studentapi.entity.User" id="userDetailresultMap">
        
        <id property="id" column="userId"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="salt" column="salt"/>
        <result property="status" column="status"/>
        
        <collection property="roles" ofType="cn.yuanyu.studentapi.entity.Role">
            <id property="id" column="roleId"/>
            <result property="roleName" column="roleName"/>
            <result property="roleDesc" column="roleDesc"/>
            
            <collection property="permissions" ofType="cn.yuanyu.studentapi.entity.Permission">
                <id property="id" column="permId"/>
                <result property="permTag" column="permTag"/>
                <result property="permDesc" column="permDesc"/>
            </collection>
        </collection>
    </resultMap>
    <select id="getUserDetailByUsername" resultMap="userDetailresultMap">
        select u.id        userId,
               u.salt      salt,
               u.status    status,
               u.username  username,
               u.password  password,
               r.id        roleId,
               r.role_name roleName,
               r.role_desc roleDesc,
               p.id        permId,
               p.perm_tag  permTag,
               p.perm_desc permDesc
        from sys_user u
                 join sys_user_role ur on ur.user_id = u.id
                 join sys_role r on r.id = ur.role_id
                 join sys_role_permission rp on rp.role_id = r.id
                 join sys_permission p on p.id = rp.perm_id
        where username = #{username};
    </select>

<insert id="insertUserRoleRelation">
        insert into sys_user_role (user_id, role_id)
        values (#{userId}, #{roleId})
    </insert>
    <insert id="insertRolePermissionRelation">
        insert into sys_role_permission (role_id, perm_id)
        values (#{roleId}, #{permId})
    </insert>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200610133558629.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200610153408592.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_40794973_article_details_89600870]: https://blog.csdn.net/qq_40794973/article/details/89600870
[https_blog.csdn.net_qq_40794973_article_details_89603924]: https://blog.csdn.net/qq_40794973/article/details/89603924
[https_blog.csdn.net_qq_40794973_article_details_106638913]: https://blog.csdn.net/qq_40794973/article/details/106638913