数据库设计与登录认证

（基于jdk17+Springboot3+Spring Security）

一、数据库设计以及表结构

sys_dept 部门表

(id为主键 code是唯一索引 parent_id父节点id)

CREATE TABLE `sys_dept`  (
                             `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                             `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '部门名称',
                             `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门编号',
                             `parent_id` bigint NOT NULL DEFAULT 0 COMMENT '父节点id',
                             `tree_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '父节点id路径',
                             `sort` smallint NULL DEFAULT 0 COMMENT '显示顺序',
                             `status` tinyint NOT NULL DEFAULT 1 COMMENT '状态(1-正常 0-禁用)',
                             `create_by` bigint NULL DEFAULT NULL COMMENT '创建人ID',
                             `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                             `update_by` bigint NULL DEFAULT NULL COMMENT '修改人ID',
                             `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                             `is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT '逻辑删除标识(1-已删除 0-未删除)',
                             PRIMARY KEY (`id`) USING BTREE,
                             UNIQUE INDEX `uk_code`(`code` ASC) USING BTREE COMMENT '部门编号唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '部门表' ROW_FORMAT = DYNAMIC;

sys_user 用户表

(id为主键 username为唯一索引 dept_id与部门进行关联)

CREATE TABLE `sys_user`  (
                             `id` int NOT NULL AUTO_INCREMENT,
                             `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
                             `nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',
                             `gender` tinyint(1) NULL DEFAULT 1 COMMENT '性别((1-男 2-女 0-保密)',
                             `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
                             `dept_id` int NULL DEFAULT NULL COMMENT '部门ID',
                             `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户头像',
                             `mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系方式',
                             `status` tinyint(1) NULL DEFAULT 1 COMMENT '状态((1-正常 0-禁用)',
                             `email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户邮箱',
                             `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                             `create_by` bigint NULL DEFAULT NULL COMMENT '创建人ID',
                             `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                             `update_by` bigint NULL DEFAULT NULL COMMENT '修改人ID',
                             `is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '逻辑删除标识(0-未删除 1-已删除)',
                             PRIMARY KEY (`id`) USING BTREE,
                             UNIQUE INDEX `login_name`(`username` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 288 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;

sys_role 角色表

(id为主键 code为唯一索引 name也用于唯一索引并指定了数据权限data_scope )

CREATE TABLE `sys_role`  (
                             `id` bigint NOT NULL AUTO_INCREMENT,
                             `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '角色名称',
                             `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色编码',
                             `sort` int NULL DEFAULT NULL COMMENT '显示顺序',
                             `status` tinyint(1) NULL DEFAULT 1 COMMENT '角色状态(1-正常 0-停用)',
                             `data_scope` tinyint NULL DEFAULT NULL COMMENT '数据权限(0-所有数据 1-部门及子部门数据 2-本部门数据3-本人数据)',
                             `create_by` bigint NULL DEFAULT NULL COMMENT '创建人 ID',
                             `create_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                             `update_by` bigint NULL DEFAULT NULL COMMENT '更新人ID',
                             `update_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                             `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标识(0-未删除 1-已删除)',
                             PRIMARY KEY (`id`) USING BTREE,
                             UNIQUE INDEX `uk_name`(`name` ASC) USING BTREE COMMENT '角色名称唯一索引',
                             UNIQUE INDEX `uk_code`(`code` ASC) USING BTREE COMMENT '角色编码唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 128 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色表' ROW_FORMAT = DYNAMIC;

sys_menu 菜单表

(id 为主键 parent_id是父菜单 tree_path树结构 perm权限标识 )

CREATE TABLE `sys_menu`  (
                             `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
                             `parent_id` bigint NOT NULL COMMENT '父菜单ID',
                             `tree_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '父节点ID路径',
                             `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '菜单名称',
                             `type` tinyint NOT NULL COMMENT '菜单类型（1-菜单 2-目录 3-外链 4-按钮）',
                             `route_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '路由名称（Vue Router 中用于命名路由）',
                             `route_path` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '路由路径（Vue Router 中定义的 URL 路径）',
                             `component` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '组件路径（组件页面完整路径，相对于 src/views/，缺省后缀 .vue）',
                             `perm` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '【按钮】权限标识',
                             `always_show` tinyint NULL DEFAULT NULL COMMENT '【目录】只有一个子路由是否始终显示（1-是 0-否）',
                             `keep_alive` tinyint NULL DEFAULT NULL COMMENT '【菜单】是否开启页面缓存（1-是 0-否）',
                             `visible` tinyint(1) NOT NULL DEFAULT 1 COMMENT '显示状态（1-显示 0-隐藏）',
                             `sort` int NULL DEFAULT 0 COMMENT '排序',
                             `icon` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '菜单图标',
                             `redirect` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '跳转路径',
                             `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                             `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
                             `params` json NULL COMMENT '路由参数',
                             PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 117 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '菜单管理' ROW_FORMAT = DYNAMIC;

sys_user_role 用户与角色关联表

(使用了联合主键 以及 联合唯一索引 保证用户不会被重复赋予相同的角色，提高查询效率)

CREATE TABLE `sys_user_role`  (
                                  `user_id` bigint NOT NULL COMMENT '用户ID',
                                  `role_id` bigint NOT NULL COMMENT '角色ID',
                                  PRIMARY KEY (`user_id`, `role_id`) USING BTREE,
                                  UNIQUE INDEX `uk_userid_roleid`(`user_id` ASC, `role_id` ASC) USING BTREE COMMENT '用户角色唯一索引'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户和角色关联表' ROW_FORMAT = DYNAMIC;

sys_role_menu 角色与菜单关联表

(使用了联合唯一索引 提高插入性能)

CREATE TABLE `sys_role_menu`  (
                                  `role_id` bigint NOT NULL COMMENT '角色ID',
                                  `menu_id` bigint NOT NULL COMMENT '菜单ID',
                                  UNIQUE INDEX `uk_roleid_menuid`(`role_id` ASC, `menu_id` ASC) USING BTREE COMMENT '角色菜单唯一索引'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色和菜单关联表' ROW_FORMAT = DYNAMIC;

二、导入工具类依赖

hutool 5.8.27
mysql 8.0.28
druid 1.2.23
mybatis-plus 3.5.5
mapstruct 1.5.5.Final
easyexcel 3.2.1
redisson 3.30.0
knife4j 4.5.0

三、编写配置文件

包括数据源、jackson序列化时区和格式、redis配置、spring缓存配置、mybatis-plus基本配置、security自定义配置、阿里云oss配置、openAPI配置、验证码配置等

四、业务流程实现细节

定义统一的后端通用接口Result、定义对应的响应码、定义统一的分页响应结构

（一）基于SpringSecurity的用户认证以及授权

编写SpringSecurity配置类，配置密码编码器、认证管理器以及过滤器链。

①通过application.yml配置SpringSecurity放行的url

# 安全配置
security:
jwt:
  # JWT 秘钥
  key: zidingyi1111111
  # JWT 有效期(单位：秒)
  ttl: 7200
ignore-urls:
  - /v3/api-docs/**
  - /doc.html
  - /swagger-resources/**
  - /webjars/**
  - /doc.html
  - /swagger-ui/**
  - /swagger-ui.html
  - /api/v1/auth/captcha

②通过@ConfigurationProperties(prefix="security")读取配置文件(属性名一致列表使用List接收)

③放行配置

@Bean
public WebSecurityCustomizer webSecurityCustomizer() {
    return (web) -> {
        if (CollectionUtil.isNotEmpty(securityProperties.getIgnoreUrls())) {
            web.ignoring().requestMatchers(securityProperties.getIgnoreUrls().toArray(new String[0]));
        }
    };
}

④配置过滤器链

@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
    http.authorizeHttpRequests(requestMatcherRegistry ->
    //配置请求授权规则  登录放行
               requestMatcherRegistry.requestMatchers(SecurityConstants.LOGIN_PATH).permitAll()
                            .anyRequest().authenticated()
            )
            //配置异常处理
            .exceptionHandling(httpSecurityExceptionHandlingConfigurer ->
                    httpSecurityExceptionHandlingConfigurer
                            .authenticationEntryPoint(authenticationEntryPoint)
                            .accessDeniedHandler(accessDeniedHandler)
            )
            //配置会话管理 设置为无状态
            .sessionManagement(configurer -> configurer.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
            //禁止CSRF保护
            .csrf(AbstractHttpConfigurer::disable)
            //禁止页面框架的跨站请求
            .headers(headers -> headers.frameOptions(HeadersConfigurer.FrameOptionsConfig::disable));

    // 在UsernamePasswordAuthenticationFilter之前添加验证码验证过滤器
    http.addFilterBefore(new CaptchaValidationFilter(redisTemplate, codeGenerator), UsernamePasswordAuthenticationFilter.class);
    // 在UsernamePasswordAuthenticationFilter之前添加JWT验证过滤器
    http.addFilterBefore(new JwtValidationFilter(redisTemplate,securityProperties.getJwt().getKey()), UsernamePasswordAuthenticationFilter.class);
    return http.build();
}

两个异常处理:

一个是认证失败（404、用户名或密码错误、token无效或已过期)，实现AuthenticationEntryPoint接口重写commence方法；

另一个是访问未授权实现AccessDeniedHandler接口重写handler方法。

两个自定义过滤器:

验证码校验过滤器:生成验证码后，在登录时进行判断。从redis中取出验证码进行判断。验证不通过直接返回错误数据。

Jwttoken验证过滤器:验证消息头Authorization中存储的token是否有效。需要注意用户注销登录后,对应的token如果没有过期，就将token的jwt_id存入redis进行黑名单设置。直到过期才会删除。所以在这里要判断token是否在黑名单内。如果有效,将token解析成Authentication对象进行上下文设置。

实现两个接口，实体类需要实现UserDetails,封装角色以及权限以及数据权限。角色使用 Collection<SimpleGrantedAuthority> authorities进行封装,权限使用 Set<String> perms进行封装。添加dataScope属性用以判断该用户的角色所拥有的最大数据权限。

public SysUserDetails(UserAuthInfo user) {
    this.userId = user.getUserId();
    Set<String> roles = user.getRoles();
    Set<SimpleGrantedAuthority> authorities;
    if (CollectionUtil.isNotEmpty(roles)) {
        authorities = roles.stream()
         // SpringSecurity标准化 标识角色
                .map(role -> new SimpleGrantedAuthority("ROLE_" + role))
                .collect(Collectors.toSet());
    } else {
        authorities = Collections.EMPTY_SET;
    }
    this.authorities = authorities;
    this.username = user.getUsername();
    this.password = user.getPassword();
    this.enabled = ObjectUtil.equal(user.getStatus(), 1);
    this.perms = user.getPerms();
    this.deptId = user.getDeptId();
    this.dataScope = user.getDataScope();
}

实现UserDetailsService并重写loadUserByUsername方法,在认证管理器autheticationManager中，它会遍历一系统的AutheticationProvider，直到找到一个可以处理UsernamePasswordAutheticationToken的provider。DaoAutheticationProvider会调用实现了UserDetailsService的loadUserByUsername方法。

登录接口实现逻辑

通过验证码过滤器查询redis缓存校验验证码是否正确
controller层接收用户输入的账号密码
service层创建认证令牌对象UsernamePasswordAutheticationToken,并将传入的账号密码封装进去。
- 执行用户认证 注入AutheticationManger对象,将autheticationToken传入并执行autheticate方法
- 创建UserDetailsService的实现类,重写loadUserByUsername方法。在方法中通过用户名查询用户授权对象
  - 通过sys_user表、sys_user_role和sys_role关联,一对多查询查询code角色标识,封装成Set
  - 再通过sys_role、sys_role_menu以及sys_menu关联，多对多查询权限,封装成Set
  - 获取该用户所在角色中最大数据权限,对数据权限进行赋值(值越小权限越大)
- 返回用户授权对象,判断是否为空。不为空将用户授权对象封装到UserDetails中，为空抛出异常
认证成功后,利用hutool的JWTUtil生成token。包括用户id、用户名、部门id、数据权限、角色code集合、过期时间、以及生成一个JWT_ID（用以后续黑名单验证）
将认证信息存入SpringSecurity上下文中,并返回jwt令牌

部分代码 SysUserServiceImpl

/**
 * 登录
 *
 * @param username 用户名
 * @param password 密码
 * @return 登录结果
 */
@Override
public LoginResult login(String username, String password) {
    // 创建认证令牌对象
    UsernamePasswordAuthenticationToken authenticationToken =
            new UsernamePasswordAuthenticationToken(username.toLowerCase().trim(), password);
    // 执行用户认证
    Authentication authentication = authenticationManager.authenticate(authenticationToken);
    // 认证成功后生成JWT令牌
    String accessToken = JwtUtils.createToken(authentication);
    // 将认证信息存入Security上下文
    SecurityContextHolder.getContext().setAuthentication(authentication);
    // 返回包含JWT令牌的登录结果
    return LoginResult.builder()
            .tokenType("Bearer")
            .accessToken(accessToken)
            .build();
}
@Override
public UserAuthInfo getUserAuthInfo(String username) {
    UserAuthInfo userAuthInfo = this.baseMapper.getUserAuthInfo(username);
    if (userAuthInfo != null) {
        Set<String> roles = userAuthInfo.getRoles();
        if (CollectionUtil.isNotEmpty(roles)) {
            Set<String> perms = roleMenuService.getRolePermsByRoleCodes(roles);
            userAuthInfo.setPerms(perms);
        }

        // 获取最大范围的数据权限
        Integer dataScope = roleService.getMaximumDataScope(roles);
        userAuthInfo.setDataScope(dataScope);
    }
    return userAuthInfo;
}

SysUserDetailsService

@Service
@RequiredArgsConstructor
@Slf4j
public class SysUserDetailsService implements UserDetailsService {

    private final SysUserService sysUserService;

    /**
     * 根据用户名获取用户信息
     *
     * @param username 用户名
     * @return 用户信息
     * @throws UsernameNotFoundException 用户名未找到异常
     */
    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        try {
            UserAuthInfo userAuthInfo = sysUserService.getUserAuthInfo(username);
            if (userAuthInfo == null) {
                throw new UsernameNotFoundException(username);
            }
            return new SysUserDetails(userAuthInfo);
        } catch (Exception e) {
            e.printStackTrace();
            // 记录异常日志
            log.error("认证异常:{}", e.getMessage());
            // 抛出异常
            throw e;
        }
    }
}

（二）基于Redis以及SpringSecurity的@PreAuthorize注解实现权限控制

初始化项目时，将所有角色编码code对应的权限集合查询出来，存入redis中

@PostConstruct
public void initRolePermsCache() {
    log.info("初始化权限缓存... ");
    refreshRolePermsCache();
}
/**
 * 刷新权限缓存
 */
@Override
public void refreshRolePermsCache() {
    // 清理权限缓存
    redisTemplate.opsForHash().delete(SecurityConstants.ROLE_PERMS_PREFIX, "*");

    List<RolePermsBO> list = this.baseMapper.getRolePermsList(null);
    if (CollectionUtil.isNotEmpty(list)) {
        list.forEach(item -> {
            String roleCode = item.getRoleCode();
            Set<String> perms = item.getPerms();
            if (CollectionUtil.isNotEmpty(perms)) {
                redisTemplate.opsForHash().put(SecurityConstants.ROLE_PERMS_PREFIX, roleCode, perms);
            }
        });
    }
}

当有方法新增修改删除角色(code以及status)、权限(菜单的权限标识)时，对redis缓存中的数据进行更新或重新加载

例如:

/**
 * 保存角色
 *
 * @param roleForm 角色表单数据
 * @return {@link Boolean}
 */
@Override
public boolean saveRole(RoleForm roleForm) {
   //获取表单传入roleId
    Long roleId = roleForm.getId();

    // 编辑角色时，判断角色是否存在
    SysRole oldRole = null;
    if (roleId != null) {
    //编辑之前查询旧的角色信息
        oldRole = this.getById(roleId);
        Assert.isTrue(oldRole != null, "角色不存在");
    }

    String roleCode = roleForm.getCode();
    //无论是新增还是修改 都要求code和name是唯一的
    long count = this.count(new LambdaQueryWrapper<SysRole>()
            //修改时 查询除了自己之外的
            .ne(roleId != null, SysRole::getId, roleId)
            .and(wrapper ->
                    wrapper.eq(SysRole::getCode, roleCode).or().eq(SysRole::getName, roleForm.getName())
            ));
    Assert.isTrue(count == 0, "角色名称或角色编码已存在，请修改后重试！");

    // 实体转换
    SysRole role = roleConverter.toEntity(roleForm);
   //新增或修改 操作数据库
    boolean result = this.saveOrUpdate(role);
    if (result) {
        // 判断角色编码或状态是否修改，修改了则刷新权限缓存
        if (oldRole != null
                && (
                !StrUtil.equals(oldRole.getCode(), roleCode) ||
                        !ObjectUtil.equals(oldRole.getStatus(), roleForm.getStatus())
        )) {
        //角色状态或角色编码改变时触发
            roleMenuService.refreshRolePermsCache(oldRole.getCode(), roleCode);
        }
    }
    return result;
}

定义PermissionService类添加hasPerm权限

@Component("ss")
@RequiredArgsConstructor
@Slf4j
public class PermissionService {

    private final RedisTemplate<String, Object> redisTemplate;

    /**
     * 判断当前登录用户是否拥有操作权限
     *
     * @param requiredPerm 所需权限
     * @return 是否有权限
     */
    public boolean hasPerm(String requiredPerm) {

        if (StrUtil.isBlank(requiredPerm)) {
            return false;
        }
        // 超级管理员放行
        if (SecurityUtils.isRoot()) {
            return true;
        }

        // 获取当前登录用户的角色编码集合
        Set<String> roleCodes = SecurityUtils.getRoles();
        if (CollectionUtil.isEmpty(roleCodes)) {
            return false;
        }

        // 获取当前登录用户的所有角色的权限列表
        Set<String> rolePerms = this.getRolePermsFormCache(roleCodes);
        if (CollectionUtil.isEmpty(rolePerms)) {
            return false;
        }
        // 判断当前登录用户的所有角色的权限列表中是否包含所需权限
        boolean hasPermission = rolePerms.stream()
                .anyMatch(rolePerm ->
                        // 匹配权限，支持通配符(* 等)
                        PatternMatchUtils.simpleMatch(rolePerm, requiredPerm)
                );

        if (!hasPermission) {
            log.error("用户无操作权限");
        }
        return hasPermission;
    }


    /**
     * 从缓存中获取角色权限列表
     *
     * @param roleCodes 角色编码集合
     * @return 角色权限列表
     */
    public Set<String> getRolePermsFormCache(Set<String> roleCodes) {
        // 检查输入是否为空
        if (CollectionUtil.isEmpty(roleCodes)) {
            return Collections.emptySet();
        }

        Set<String> perms = new HashSet<>();
        // 从缓存中一次性获取所有角色的权限
        Collection<Object> roleCodesAsObjects = new ArrayList<>(roleCodes);
        List<Object> rolePermsList = redisTemplate.opsForHash().multiGet(SecurityConstants.ROLE_PERMS_PREFIX, roleCodesAsObjects);

        for (Object rolePermsObj : rolePermsList) {
            if (rolePermsObj instanceof Set) {
                @SuppressWarnings("unchecked")
                Set<String> rolePerms = (Set<String>) rolePermsObj;
                perms.addAll(rolePerms);
            }
        }

        return perms;
    }

}

添加注解生效@PreAuthorize("@ss.hasPerm('sys:dept:add')")

从零学习一个基于Springboot的权限管理系统(一）数据库设计