Spring security基于数据库中账户密码认证

Spring Security是Spring框架中提供的一个安全权限框架。它将认证（Authentication）和授权（Authorization）抽象为一个独立的模块，可以快速地将安全性集成到应用程序中。Spring Security可以基于多种认证方式，包括基于数据库中账户密码的认证。

基于数据库中账户密码认证的Spring Security攻略如下：

1.配置Spring Security

在Spring配置文件中，添加Spring Security的依赖和配置信息，以启用Spring Security的安全性：

<dependency>
    <groupId>org.springframework.security</groupId>
    <artifactId>spring-security-web</artifactId>
    <version>${spring-security.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework.security</groupId>
    <artifactId>spring-security-config</artifactId>
    <version>${spring-security.version}</version>
</dependency>

<security:http auto-config="true">
    <security:intercept-url pattern="/**" access="ROLE_USER" />
    <security:form-login login-page="/login" default-target-url="/home"
        authentication-failure-url="/login?error" />
    <security:logout logout-success-url="/login?logout" />
</security:http>    

<security:authentication-manager>
    <security:authentication-provider>
        <security:jdbc-user-service data-source-ref="dataSource"
            users-by-username-query="SELECT username, password, enabled FROM users WHERE username=?"
            authorities-by-username-query="SELECT u.username, a.authority FROM users u, authorities a WHERE u.username=? AND u.id=a.user_id" />
    </security:authentication-provider>
</security:authentication-manager>

在上面的代码中，<security:http>元素配置了应用程序的安全性，包括拦截URL规则、登录页面和默认路径等。<security:authentication-manager>元素配置了应用程序的认证方式，使用JDBC的用户服务实现，查询用户的账户、密码和角色等信息。

2.创建数据表

在数据库中创建用户表和授权表，用于存储用户的账户、密码和角色等信息：

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(100) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (id)
);

CREATE TABLE authorities (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  authority VARCHAR(50) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_authorities_users FOREIGN KEY (user_id) REFERENCES users(id)
);

3.插入测试数据

插入测试数据，用于测试用户的认证和授权：

INSERT INTO users (username, password, enabled)
VALUES ('user1', '$2a$10$7l6V5AwMiVbvVn7oKVfYJ.LkNuaSmETcYliJOO1QX/0QFzuPzQSpC', true);

INSERT INTO authorities (user_id, authority)
VALUES (1, 'ROLE_USER');

在上面的代码中，$2a$10$7l6V5AwMiVbvVn7oKVfYJ.LkNuaSmETcYliJOO1QX/0QFzuPzQSpC是加密后的密码，使用BCrypt密码哈希算法生成。

4.基于数据库中账户密码认证的测试

使用新创建的账户测试基于数据库中账户密码认证的Spring Security，以下是基于Spring Boot的一个示例：

@Controller
public class HelloController {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @GetMapping("/")
    public String index() {
        return "index";
    }

    @GetMapping("/home")
    public String home() {
        return "home";
    }

    @GetMapping("/login")
    public String login() {
        return "login";
    }

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

}

@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    DataSource dataSource;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().dataSource(dataSource);
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
            .antMatchers("/", "/home").permitAll()
            .antMatchers("/admin").hasRole("ADMIN")
            .anyRequest().authenticated()
            .and()
            .formLogin()
            .loginPage("/login")
            .defaultSuccessUrl("/home")
            .permitAll()
            .and()
            .logout()
            .logoutSuccessUrl("/login?logout")
            .permitAll()
            .and()
            .exceptionHandling().accessDeniedPage("/403");
    }

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

}

在以上代码中，创建了一个URL映射的控制器，并定义了认证和授权的配置类。
接下来，我们使用以下代码进行测试：

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment=WebEnvironment.RANDOM_PORT)
public class SecurityTest {

    @LocalServerPort
    private int port;

    TestRestTemplate restTemplate = new TestRestTemplate();

    HttpHeaders headers = new HttpHeaders();

    @Test
    public void test() throws JSONException {
        HttpEntity<String> request = new HttpEntity<>(headers);

        ResponseEntity<String> response = restTemplate.exchange(
          "http://localhost:" + port + "/", HttpMethod.GET, request, String.class);

        assertEquals(HttpStatus.OK, response.getStatusCode());

        response = restTemplate.exchange(
          "http://localhost:" + port + "/home", HttpMethod.GET, request, String.class);

        assertEquals(HttpStatus.OK, response.getStatusCode());

        response = restTemplate.exchange(
          "http://localhost:" + port + "/admin", HttpMethod.GET, request, String.class);

        assertEquals(HttpStatus.FORBIDDEN, response.getStatusCode());

        response = restTemplate.exchange(
          "http://localhost:" + port + "/login", HttpMethod.GET, request, String.class);

        assertEquals(HttpStatus.OK, response.getStatusCode());

        headers.add("Cookie", response.getHeaders().get("Set-Cookie").get(0));

        MultiValueMap<String, String> map = new LinkedMultiValueMap<>();
        map.add("username", "user1");
        map.add("password", "password");

        request = new HttpEntity<>(map, headers);

        response = restTemplate.exchange(
          "http://localhost:" + port + "/login", HttpMethod.POST, request, String.class);

        assertEquals(HttpStatus.FOUND, response.getStatusCode());

        response = restTemplate.exchange(
          "http://localhost:" + port + "/admin", HttpMethod.GET, request, String.class);

        assertEquals(HttpStatus.FORBIDDEN, response.getStatusCode());

        response = restTemplate.exchange(
          "http://localhost:" + port + "/login?logout", HttpMethod.GET, request, String.class);

        assertEquals(HttpStatus.OK, response.getStatusCode());
    }

}

再次强调，以上示例为一个简单的示例，实际应用中应该更加完善和严谨。