java - jdbcAuthentication() 而不是 inMemoryAuthentication() 不提供访问权限 - Spring Security 和 Spring Data JPA

Question

我只是使用 spring mvc、gradle、spring security、spring data jpa 创建简单的应用程序。现在我想测试一下 spring security 是如何工作的，但是我有一个问题。首先，我会向您展示一些代码，然后我会提及我的问题。

结构:

Person.java

package com.test.business;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "person")
public class Person {

  @Id
  @Column(name = "id")
  private int id;
  @Column(name = "name")
  private String name;
  @Column(name = "password")
  private String password;
  @Column(name = "role")
  private String role;

  public Person(){
  }

  public Person(int id, String name, String password, String role) {
    this.id = id;
    this.name = name;
    this.password = password;
    this.role = role;
  }
  //setters and getters
}

PersonController.java

package com.test.controller;

import com.test.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class PersonController {

  @Autowired
  private PersonService personService;

  @GetMapping(value="/")
  @ResponseBody
  public String printWelcome() {
    return "home";
  }

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

  @GetMapping(value="/user")
  @ResponseBody
  public String user() {
    return "user";
  }
}

MyWebInitializer.java

package com.test.config;

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class MyWebInitializer extends
    AbstractAnnotationConfigDispatcherServletInitializer {

  @Override
  protected Class<?>[] getRootConfigClasses() {
    return new Class[] { RootConfig.class, SecurityConfig.class };
  }

  @Override
  protected Class<?>[] getServletConfigClasses() {
    return new Class[] { WebConfig.class };
  }

  @Override
  protected String[] getServletMappings() {
    return new String[] { "/" };
  }

}

SecurityWebInitializer.java

package com.test.config;

import org.springframework.security.web.context.AbstractSecurityWebApplicationInitializer;

public class SecurityWebInitializer
    extends AbstractSecurityWebApplicationInitializer {

}

RootConfig.java

package com.test.config;

import java.util.Properties;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaRepositories( basePackages = {"com.test.repository"})
@PropertySource(value = { "classpath:application.properties" })
@EnableTransactionManagement
@Import({ SecurityConfig.class })
@ComponentScan(basePackages = {"com.test.service", "com.test.repository", "com.test.controller", "com.test.business"})
public class RootConfig {

  @Autowired
  private Environment environment;

  @Autowired
  private DataSource dataSource;

  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(environment.getRequiredProperty("jdbc.driverClassName"));
    dataSource.setUrl(environment.getRequiredProperty("jdbc.url"));
    dataSource.setUsername(environment.getRequiredProperty("jdbc.username"));
    dataSource.setPassword(environment.getRequiredProperty("jdbc.password"));

    return dataSource;
  }

  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setDatabase(Database.POSTGRESQL);
    vendorAdapter.setGenerateDdl(true);
    vendorAdapter.setShowSql(true);

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.test.business");
    factory.setDataSource(dataSource());
    factory.setJpaProperties(jpaProperties());

    return factory;
  }

  private Properties jpaProperties() {
    Properties properties = new Properties();
    properties.put("hibernate.dialect", environment.getRequiredProperty("hibernate.dialect"));
    properties.put("hibernate.show_sql", environment.getRequiredProperty("hibernate.show_sql"));
    properties.put("hibernate.format_sql", environment.getRequiredProperty("hibernate.format_sql"));
    return properties;
  }
  @Bean
  public PlatformTransactionManager transactionManager() {

    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory().getObject());
    return txManager;
  }
}

WebConfig.java

package com.test.config;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.ViewResolverRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@EnableWebMvc
@Configuration
@ComponentScan({ "com.test.controller" })
public class WebConfig implements WebMvcConfigurer {

  @Override
  public void addResourceHandlers(ResourceHandlerRegistry registry) {
    registry.addResourceHandler("/resources/**")
        .addResourceLocations("/resources/");
  }

  @Override
  public void configureViewResolvers(ViewResolverRegistry registry) {
    registry.jsp().prefix("/WEB-INF/views/").suffix(".jsp");
  }

}

SecurityConfig.java

package com.test.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

  @Autowired
  private DataSource dataSource;

  @Override
  protected void configure(AuthenticationManagerBuilder auth) throws Exception {

    auth.jdbcAuthentication().dataSource(dataSource)
        .usersByUsernameQuery("select name, password"
            + " from person where name=?")
        .authoritiesByUsernameQuery("select name, role"
            + "from person where name=?");
  }

  @Override
  protected void configure(HttpSecurity http) throws Exception {

    http.authorizeRequests().antMatchers("/admin").hasRole("ADMIN")
        .and()
        .httpBasic(); // Authenticate users with HTTP basic authentication
  }
}

在数据库中记录为 JSON:

{
    "id": 1,
    "name": "test1",
    "password": "test1",
    "role": "ADMIN"
}

问题是什么？查看 SecurityConfig.java。有 jdbcAuthentication()。当我尝试访问/admin 时，浏览器会要求我输入用户名和密码。不幸的是，当我这样做时什么也没有发生，浏览器会一次又一次地询问。

我更改了一点我的代码。在 SecurityConfig.java 而不是 jdbcAuthentication() 我使用了 inMemoryAuthentication() 所以它看起来像:

SecurityConfig.java

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

  @Autowired
  private DataSource dataSource;

  @Override
  protected void configure(AuthenticationManagerBuilder auth) throws Exception {

    auth.inMemoryAuthentication().withUser("user").password("password").roles("ADMIN");
  }

  @Override
  protected void configure(HttpSecurity http) throws Exception {

    http.authorizeRequests().antMatchers("/admin").hasRole("ADMIN")
        .and()
        .httpBasic(); // Authenticate users with HTTP basic authentication
  }
}

现在我尝试访问/admin。浏览器要求我输入用户名和密码，当我这样做时，我将获得对/admin 的访问权限。这是为什么？为什么我不能使用 jdbcAuthentication() 获得访问权限？你能给我一些建议吗？

Answer 1

我猜你的查询有误

auth.jdbcAuthentication().dataSource(dataSource)
    .usersByUsernameQuery("select name, password"
        + " from person where name=?")
    .authoritiesByUsernameQuery("select name, role"
        + "from person where name=?");

jdbcAuthentication 期望

• 对于 users-by-username-query :username、password 和 enabled
• 对于 authorities-by-username-query username 和 role

所以这对你来说应该有效:

auth.jdbcAuthentication().dataSource(dataSource)
    .usersByUsernameQuery("select name as username, password, true"
        + " from person where name=?")
    .authoritiesByUsernameQuery("select name as username, role"
        + " from person where name=?");