Spring Security连接数据库

在Spring Security整合Spring Boot快速入门中，我们给Spring Security提供的数据源是基于内存的。但实际生产中，这些数据都应该来自数据库。所以接下来就连接数据库实现认证。

1、首先准备数据库、表和数据

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

use user_db;

-- 创建用户表。密码都是123
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint(20) NOT NULL COMMENT '用户id',
  `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `fullname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
  `mobile` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_user` VALUES (1, 'xiaowang', '$2a$10$fkKtthZiXBXPSTmIbkkHj.Zp/HGnekeTnHTeO4Z6pNGHr8DUwgKjK', '1', '1');
INSERT INTO `t_user` VALUES (2, 'xiaoming', '$2a$10$fkKtthZiXBXPSTmIbkkHj.Zp/HGnekeTnHTeO4Z6pNGHr8DUwgKjK', '1', '1');
 
-- 创建角色表
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `role_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unique_role_name`(`role_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_role` VALUES ('1', '管理员', NULL, NULL, NULL, '');
INSERT INTO `t_role` VALUES ('2', '普通用户', NULL, NULL, NULL, '');
 
-- 创建角色关系表
DROP TABLE IF EXISTS `t_user_role`;
CREATE TABLE `t_user_role`  (
  `user_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `role_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `creator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`, `role_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_user_role` VALUES ('1', '1', NULL, NULL);
INSERT INTO `t_user_role` VALUES ('2', '2', NULL, NULL);
 
-- 创建权限表
DROP TABLE IF EXISTS `t_permission`;
CREATE TABLE `t_permission`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '权限标识符',
  `description` varchar(64) 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 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_permission` VALUES ('1', 'p1', '测试资源1', '/r/r1');
INSERT INTO `t_permission` VALUES ('2', 'p2', '测试资源2', '/r/r2');
 
-- 角色权限关系表
DROP TABLE IF EXISTS `t_role_permission`;
CREATE TABLE `t_role_permission`  (
  `role_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `permission_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_role_permission` VALUES ('1', '1');
INSERT INTO `t_role_permission` VALUES ('1', '2');
INSERT INTO `t_role_permission` VALUES ('2', '2');

表与表之间的关系如下：

表与表之间的关系相信大家都能看得懂，我就不多做解释了。

2、在我们入门案例的基础上，增加数据库相关的依赖

 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.22</version>
 </dependency>

 <dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>2.1.3</version>
 </dependency>

 <!-- alibaba的druid数据库连接池 -->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.2.4</version>
 </dependency>

3、编写配置文件

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: root
    # 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

4、自定义UserDetailService
自定义一个UserDetailService，告诉Spring Security如何根据用户名，到数据库中找到用户的数据。

@Component
public class MysqlUserDetailService implements UserDetailsService {
    @Resource
    private UserMapper userMapper;

    // 告诉SpringSecurity，如何根据用户名到数据库中查数据
    @Override
    public UserDetails loadUserByUsername(String name) throws UsernameNotFoundException {
        UserPo user = userMapper.findUserByName(name);
        if (user == null) {
            throw new UsernameNotFoundException("用户不存在");
        }
        // 查找用户的权限
        List<String> permissionList = userMapper.findPermissionByUserId(user.getId());
        String[] permissions = new String[permissionList.size()];
        for (int i = 0; i < permissionList.size(); i++) {
            permissions[i] = permissionList.get(i);
        }

        // 构建UserDetails对象，用户名、密码、权限均来自数据库
        return User.withUsername(user.getUsername()).password(user.getPassword()).authorities(permissions).build();
    }
}

@Mapper
public interface UserMapper {
    @Select("select id,username,password,fullname,mobile from  t_user where username=#{name}")
    UserPo findUserByName(@Param("name") String name);

    @Select("SELECT code FROM t_permission WHERE id IN (" +
            "SELECT permission_id FROM t_role_permission WHERE role_id IN ( SELECT role_id FROM t_user_role WHERE user_id = #{id} ));")
    List<String> findPermissionByUserId(Integer id);
}

测试用例如下：

1. 使用小王账号登录，可以访问所有资源
2. 使用小明账号登录，由于没有p1权限，所以不能访问/r/r1资源，报403错误
3. 如果不登陆，只能访问匿名资源

其实最关键就是去实现UserDetailsService，然后重写loadUserByUsername方法。

至于为什么不使用Spring Security提供的JdbcUserDetailsManager，大家去看看这个类的实现就知道了，这个类已经把查询sql定死了，这就意味着你的表要和它的一模一样，不够灵活，所以选择了自定义。