Springboot整合SpringSecurity 05-使用JDBC实现认证和授权

Springboot整合SpringSecurity 05-使用JDBC实现认证和授权

前面的章节中，我们都是使用的InMemoryUserDetailsManager内存保存的用户信息，但是通常我们开发中常用的一般都是数据库来保存数据。
所以本章我们使用JDBC来实现认证和授权。

本系列的按顺序写的，如果对于某些代码不清楚，请看下前面的几篇文章。
Springboot整合SpringSecurity 01-使用入门
Springboot整合SpringSecurity 02-使用自定义登陆页面
Springboot整合SpringSecurity 03-访问权限控制
Springboot整合SpringSecurity 04-启用登出logout功能
Springboot整合SpringSecurity 05-使用JDBC实现认证和授权
Springboot整合SpringSecurity 06-登陆扩展之自定义登陆验证逻辑
Springboot整合SpringSecurity 07-方法访问权限控制

1.添加jdbc相关依赖和配置。

1.1 在pom.xml中添加数据库支持。

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.9</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
    </dependencies>

我们使用mysql数据库来进行本次教程，同时使用mybatis和druid连接池。

1.2 在application.yml中添加数据库和mybatis相关配置

server:
  port: 10022
  servlet:
    context-path: /security
spring:
  mvc:
    view:
      suffix: .html
      prefix: templates/
    static-path-pattern: /static/**
  thymeleaf:
    prefix: classpath:/templates/

druid:
  url: jdbc:mysql://127.0.0.1:3306/security?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&useSSL=false           # 数据库连接地址
  driver-class: com.mysql.jdbc.Driver
  username: root
  password: root
  initialSize: 5
  min-idle: 5
  max-active: 100
  max-wait: 60000
  test-on-borrow: false
  test-on-return: false
  filters: stat,slf4j
  time-between-eviction-runs-millis: 60000
  minEvictableIdleTimeMillis: 30000
  test-while-idle: true
  poolPreparedStatements: true
  maxPoolPreparedStatementPerConnectionSize: 20
  connectionProperties: druid.stat.slowSqlMillis=5000
mybatis:
  mapper-locations: classpath:mapper/**
  type-aliases-package: com.demo.spring.security.model

我们指定了mybatis的mapper.xml的文件位置和取别名的类的包路径。

1.3 添加druid连接池的配置。

先创建druid的配置实体类

@ConfigurationProperties(prefix = "druid")
public class DruidProperties {
    private String url;
    private String username;
    private String password;
    private String driverClass;

    private int     maxActive;
    private int     maxWait;
    private int     minIdle;
    private int     initialSize;
    private boolean testOnBorrow;
    private String filters;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private boolean testWhileIdle;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private Properties connectionProperties;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

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

    public String getDriverClass() {
        return driverClass;
    }

    public void setDriverClass(String driverClass) {
        this.driverClass = driverClass;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public int getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public int getInitialSize() {
        return initialSize;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public String getFilters() {
        return filters;
    }

    public void setFilters(String filters) {
        this.filters = filters;
    }

    public int getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public int getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public boolean isTestWhileIdle() {
        return testWhileIdle;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public boolean isTestOnReturn() {
        return testOnReturn;
    }

    public void setTestOnReturn(boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public boolean isPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public void setPoolPreparedStatements(boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public int getMaxPoolPreparedStatementPerConnectionSize() {
        return maxPoolPreparedStatementPerConnectionSize;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public Properties getConnectionProperties() {
        return connectionProperties;
    }

    public void setConnectionProperties(Properties connectionProperties) {
        this.connectionProperties = connectionProperties;
    }
}

在创建druid的配置类

/**
 * datasource configuration class.
 *
 * @author flw
 */
@Configuration
@EnableConfigurationProperties(DruidProperties.class)
@ConditionalOnClass(DruidDataSource.class)
@ConditionalOnProperty(prefix = "druid", name = "url")
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
public class DruidAutoConfiguration {

    @Autowired
    private DruidProperties properties;

    @Bean
    public DataSource dataSource(StatFilter statFilter, LogFilter logFilter) {
        DruidDataSource dataSource = initDataSource();
        List<Filter> list = new ArrayList<Filter>();
        list.add(statFilter);
        list.add(logFilter);
        dataSource.setProxyFilters(list);
        dataSource.setTestOnBorrow(properties.isTestOnBorrow());
        try {
            dataSource.setFilters(properties.getFilters());
            dataSource.init();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return dataSource;
    }

    @Bean
    public StatFilter statFilter() {
        StatFilter statFilter = new StatFilter();
        statFilter.setMergeSql(false);
        return statFilter;
    }

    @Bean
    public LogFilter slf4jLogFilter() {
        Slf4jLogFilter slf4jLogFilter = new Slf4jLogFilter();
        slf4jLogFilter.setStatementExecutableSqlLogEnable(true);
        slf4jLogFilter.setResultSetLogEnabled(false);
        slf4jLogFilter.setConnectionLogEnabled(false);
        slf4jLogFilter.setStatementParameterClearLogEnable(false);
        slf4jLogFilter.setStatementCreateAfterLogEnabled(false);
        slf4jLogFilter.setStatementCloseAfterLogEnabled(false);
        slf4jLogFilter.setStatementParameterSetLogEnabled(false);
        slf4jLogFilter.setStatementPrepareAfterLogEnabled(false);
        slf4jLogFilter.setStatementLogEnabled(false);
        return slf4jLogFilter;
    }

    private DruidDataSource initDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(properties.getUrl());
        dataSource.setUsername(properties.getUsername());
        dataSource.setPassword(properties.getPassword());
        dataSource.setDriverClassName(properties.getDriverClass());
        if (properties.getInitialSize() > 0) {
            dataSource.setInitialSize(properties.getInitialSize());
        }
        if (properties.getMinIdle() > 0) {
            dataSource.setMinIdle(properties.getMinIdle());
        }
        if (properties.getMaxActive() > 0) {
            dataSource.setMaxActive(properties.getMaxActive());
        }
        if (properties.getMaxWait() > 0) {
            dataSource.setMaxWait(properties.getMaxWait());
        }
        if (properties.getTimeBetweenEvictionRunsMillis() > 0) {
            dataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
        }
        if (properties.getMinEvictableIdleTimeMillis() > 0) {
            dataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
        }
        if (properties.getMaxPoolPreparedStatementPerConnectionSize() > 0) {
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
        }
        dataSource.setTestWhileIdle(properties.isTestWhileIdle());
        dataSource.setTestOnReturn(properties.isTestOnReturn());
        dataSource.setTestOnBorrow(properties.isTestOnBorrow());
        dataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
        dataSource.setConnectProperties(properties.getConnectionProperties());
        return dataSource;
    }
}

这样基本的配置就完成了。

2. 创建数据库和相应的表

从application.yml里面我们知道我们使用的security这个数据库，所以我们需要先创建一个security数据库。
然后再数据库里面添加表和初始数据。

CREATE TABLE t_user(
	id BIGINT PRIMARY KEY auto_increment,
	username VARCHAR(50),
	password VARCHAR(100)
);
INSERT INTO T_USER (username, `password`) 
VALUES ('user','userpwd'),('admin','adminpwd'),('dba','dbapwd');

create TABLE t_role(
	id BIGINT PRIMARY KEY auto_increment,
	role VARCHAR(10)
);

insert into t_role (role) VALUES ('ROLE_ADMIN'),('ROLE_USER'),('ROLE_DBA');

create TABLE t_user_role_rel (
	id BIGINT PRIMARY KEY auto_increment,
	user_id BIGINT not null,
	role_id BIGINT not null
);

INSERT INTO t_user_role_rel (user_id,role_id) VALUES (1,2),(2,1),(3,2),(3,3);

上面的数据很简单。其实还是对应的我们之前的章节中的那些测试用户和权限信息：
一共创建了三个用户：
user： 账号user，密码userpwd，权限为ROLE_USER
admin： 账号admin，密码adminpwd，权限为ROLE_ADMIN
dba： 账号dba，密码dbapwd，权限为ROLE_DBA,ROLE_USER

注意：这里的role我们跟之前的内存形式的多加了ROLE_前缀。这是因为之前的role都是通过springsecurity的api赋值过去的，他会自行帮我们加上这个前缀。
但是现在我们使用的是自己的数据库里面读取出来的权限，然后封装到自己的实体类中。所以这时候需要我们自己手动添加这个ROLE_前缀。

3. 创建相关实体类

/**
 * @author flw
 */
public class UserVo implements UserDetails {

    private long id;

    private String username;

    private String password;

    private Collection<? extends GrantedAuthority> authorities;

    public long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

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


    /**
     * 账号是否失效，返回false账号失效，不可用。
     *
     * @return
     */
    public boolean isAccountNonExpired() {
        return true;
    }

    /**
     * 账号是否被锁，返回false，账号被锁，不可用
     * @return
     */
    public boolean isAccountNonLocked() {
        return true;
    }

    /**
     * 账号认证是否过期，返回false，过期，不可用
     * @return
     */
    public boolean isCredentialsNonExpired() {
        return true;
    }

    /**
     * 账号是否可用。返回false不可用
     * @return
     */
    public boolean isEnabled() {
        return true;
    }

    /**
     * 返回用户的权限集合。
     * @return
     */
    public Collection<? extends GrantedAuthority> getAuthorities() {
        return authorities;
    }

    public void setAuthorities(Collection<? extends GrantedAuthority> authorities) {
        this.authorities = authorities;
    }
}

首先我们需要一个实现UserDetails的实体类，UserDetails就是SpringSecurity的认证实体的统一接口，所以我们必须要实现，上面注释的五个方法是UserDetails的方法，具体作用注释上面已经说明了。

/**
 * @author flw
 */
public class MyGrantedAuthority implements GrantedAuthority {

    private String authority;

    @Override
    public String getAuthority() {
        return authority;
    }

    public void setAuthority(String authority) {
        this.authority = authority;
    }
}

然后创建一个role的实体类，这个里面就是装着我们的权限。

4. 创建相关mapper

public interface UserMapper {

    /**
     * 根据账号查询用户。
     *
     * @param username
     * @return
     */
    UserVo selectUserByUsername(String username);

    /**
     * 根据用户查找权限
     *
     * @param id
     * @return
     */
    List<MyGrantedAuthority> selectUserAuthorities(long id);
}

再mapper目录下面新建一个User.xml的mapper

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC
        "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.demo.spring.security.mapper.UserMapper">

    <select id="selectUserByUsername" parameterType="string" resultType="userVo">
		SELECT id, username, password FROM T_USER
		WHERE username = #{username}
	</select>

    <select id="selectUserAuthorities" parameterType="long" resultType="myGrantedAuthority">
        SELECT role authority FROM T_ROLE
        WHERE id IN (
          SELECT role_id FROM T_USER_ROLE_REL WHERE user_id = #{userId}
        )
    </select>
</mapper>

5. 创建UserDetailsService实现

security的用户信息就是通过这个接口获取的。从前面的内存获取用户数据里面应该也能看出来。
现在我们创建一个自己的UserDetailsService来实现获取用户信息的逻辑。

@Service
public class UserService implements UserDetailsService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        UserVo vo = userMapper.selectUserByUsername(username);
        if (vo != null) {
            List<MyGrantedAuthority> authorities = userMapper.selectUserAuthorities(vo.getId());
            vo.setAuthorities(authorities);
        }
        return vo == null ? null
                : User.withUserDetails(vo)
                .passwordEncoder(PasswordEncoderFactories.createDelegatingPasswordEncoder()::encode)
                .build();
    }
}

注意：SpringSecurity默认是需要passwordEncoder的，所以这里我仿照之前内存方式里面创建用户的方式在获取到用户信息后在使用User来封装了一次。

6. 修改WebSecurityConfig配置

@EnableWebSecurity
@MapperScan("com.demo.spring.security.mapper")
public class WebSecurityConfig extends WebSecurityConfigurerAdapter  {

    @Autowired
    private UserDetailsService userService;

//    @Bean
//    @Override
//    public UserDetailsService userDetailsService() {
//        InMemoryUserDetailsManager manager = new InMemoryUserDetailsManager();
//        manager.createUser(User.withDefaultPasswordEncoder().username("user")
//                .password("user").roles("USER").build());
//        manager.createUser(User.withDefaultPasswordEncoder().username("admin")
//                .password("admin").roles("ADMIN").build());
//        manager.createUser(User.withDefaultPasswordEncoder().username("dba")
//                .password("dba").roles("DBA","USER").build());
//        return manager;
//    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.userDetailsService(userService)
                .authorizeRequests()
                .antMatchers("/static/**", "/common/**","/login/**").permitAll()
                .antMatchers("/admin/**").hasRole("ADMIN")
                .antMatchers("/user/**").access("hasRole('USER') and hasRole('DBA')")
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .loginPage("/login")
//                .authenticationDetailsSource()
                .successHandler(new MyAuthenticationSuccessHandler())
                .permitAll()
                .and()
                .logout()
//                .logoutUrl("/my/logout")
//                .logoutSuccessUrl("/my/index")
//                .logoutSuccessHandler(null)
                .invalidateHttpSession(true)
//                .addLogoutHandler(null)
                .deleteCookies("testCookie", "testCookie2")
                .permitAll();
    }
}

@MapperScan(“com.demo.spring.security.mapper”)： 这是mybatis的配置，指定mapper的接口的包路径。
http.userDetailsService(userService)： 然后我注释掉了之前内存方式的userDetailsService，同时把我们自定义的userDetailsService赋值给了HttpSecurity。

7. 启动项目开始测试

完成了前面的改造，我们的数据库支持就实现了。下面我们启动项目，开始测试。
首先到登陆页面使用admin/adminpwd登陆

http://localhost:10022/security/login

登陆成功并跳转到hello.html页面。
然后我们去/admin/hello页面，看是否有admin权限。

http://localhost:10022/security/admin/hello

结果可以正常访问，说明admin是具有ADMIN权限的。
然后访问/user/hello

http://localhost:10022/security/user/hello

结果返回403。说明一切正常。