Spring Security（二）基于数据库认证鉴权解决方案

项目介绍

Spring Boot + Spring Security + mysql，基于数据库的认证鉴权实现；

代码地址:https://github.com/xdouya/Spring-Security-demo/tree/master/02-jdbc-security

项目目录结构：

项目构建

数据表创建及用户数据导入

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `username` varchar(50) NOT NULL,
  `password` varchar(500) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB;

INSERT  IGNORE INTO `users` VALUES ('admin','{bcrypt}$2a$10$SAqQq0WEQYRA4etZpRa6e.Kew0sKKtC/ahFrSZXS1iHsy5EhZqLsa',1),('user','{bcrypt}$2a$10$SAqQq0WEQYRA4etZpRa6e.Kew0sKKtC/ahFrSZXS1iHsy5EhZqLsa',1),('vip','{bcrypt}$2a$10$SAqQq0WEQYRA4etZpRa6e.Kew0sKKtC/ahFrSZXS1iHsy5EhZqLsa',1);

DROP TABLE IF EXISTS `authorities`;
CREATE TABLE `authorities` (
  `username` varchar(50) NOT NULL,
  `authority` varchar(50) NOT NULL,
  UNIQUE KEY `ix_auth_username` (`username`,`authority`),
  CONSTRAINT `fk_authorities_users` FOREIGN KEY (`username`) REFERENCES `users` (`username`)
) ENGINE=InnoDB;

INSERT  IGNORE INTO `authorities` VALUES ('admin','ROLE_admin'),('user','ROLE_user'),('vip','ROLE_vip');

Maven依赖

• pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <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-devtools</artifactId>
        <scope>runtime</scope>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework.security</groupId>
        <artifactId>spring-security-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

项目构建

• application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.0.103:3306/security_sso?useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: '088114'

• SecurityConfig

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    private DataSource dataSource;

    @Autowired
    public SecurityConfig(DataSource dataSource){
        this.dataSource = dataSource;
    }

    /**
     * 配置拦截器保护请求
     */
    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
                .antMatchers("/admin/**").hasRole("admin")
                .antMatchers("/vip/**").hasRole("vip")
                .antMatchers("/user/**").hasRole("user")
                .anyRequest().authenticated()
                .and().formLogin()
                .and().httpBasic();
    }

    /**
     * UserDetailsService 用户名，密码，以及其他属性的查找，Spring Security提供内存以及JDBC实现
     */
    @Override
    @Bean
    public UserDetailsService userDetailsService() {
        JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager(dataSource);
        jdbcUserDetailsManager.setDataSource(dataSource);
        return jdbcUserDetailsManager;
    }

    /**
     * 根据自动匹配密码编码器
     * @return PasswordEncoder
     */
    @Bean
    public PasswordEncoder passwordEncoder(){
        return PasswordEncoderFactories.createDelegatingPasswordEncoder();
    }
}

• 上述配置中配置了一个JdbcUserDetailsManager去数据库查询用户信息；JdbcUserDetailsManager继承了JdbcDaoImpl接口，定义了一些默认的对用户进行增删改查的sql语句，并实现了UserDetailManager接口；
  
• JdbcDaoImpl中实现了UserDetalService方法中的loadUserByUsername查询用户信息的方法，并使用的是图中内嵌的sql语句，也就是为什么我们要创建user表以及authorities表，以及为什么表中的需要哪些字段；

• 判断角色是由拥有访问权限是在RoleVoter类中进行判断的，从源码中可以得知，该类定义了一个ROLE_的前缀，在角色验证时，会自动加上这个ROLE前缀
  
• HelloController

@RestController
public class HelloController {

    @GetMapping("/")
    public String hello(){
        return "hello, welcome";
    }

    @GetMapping("/admin/hello")
    public String helloAdmin(){
        return "hello admin";
    }

    @GetMapping("/vip/hello")
    public String helloVip(){
        return "hello vip";
    }

    @GetMapping("/user/hello")
    public String helloUser(){
        return "hello user";
    }
}

• JdbcApplication

@SpringBootApplication
public class JdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(JdbcApplication.class, args);
    }

}

项目测试

​ 使用不同账号（账号admin或者user或者vip， 密码088114）访问http://localhost:8080/admin/hello、http://localhost:8080/user/hello、http://localhost:8080/vip/hello 来查看给个角色访问时的授权情况；

拓展

​ 使用自动的JdbcUserDetailsManager实现基于数据库的认证能够满足一些简单认证鉴权需求，但是对于复杂认证鉴权需求，可能需要我们自定义数据的查询方式；

​ 下一节介绍Spring Security（三）自定义查询过程的解决方案