WebSecurityConfigurerAdapter中通过httpBasic和jdbcAuthentication进行用户验证的问题 [英] Problem with user validation by httpBasic and jdbcAuthentication in WebSecurityConfigurerAdapter

问题描述

我检查了大多数stackoverflow解决方案，以及一些官方文章，例如:

解决方案

单引号查询 username ='?'会引起问题.

修改

  @Autowired公共无效configureGlobal(AuthenticationManagerBuilder auth)引发异常{auth.jdbcAuthentication().dataSource(数据源).usersByUsernameQuery(选择用户名，密码，true"+来自apiclient，其中username ='?'").authoritiesByUsernameQuery(选择用户名，角色"+来自apiclient，其中username ='?'")；} 

到

 <代码> @Autowired公共无效configureGlobal(AuthenticationManagerBuilder auth)引发异常{auth.jdbcAuthentication().dataSource(数据源).usersByUsernameQuery(选择用户名，密码，true"+来自apiclient，其中username =?").authoritiesByUsernameQuery(选择用户名，角色"+来自apiclient，其中username =?")；} 

查看从github链接共享的代码后，
在您的项目中，您尚未启用级别为DEBUG的日志.

启用调试日志后，我注意到了

  DEBUG-执行准备好的SQL语句[选择apiclient中的用户名，密码和true，其中username ='?']调试-从数据源获取JDBC连接...调试-数据源的缓存SQL错误代码[com.zaxxer.hikari.HikariDataSource@3bcd426c]:数据库产品名称为'H2'调试-无法转换错误代码为'90008'的SQLException，现在将尝试回退转换器...调试-访问被拒绝(用户为匿名用户)；重定向到身份验证入口点org.springframework.security.access.AccessDeniedException:访问被拒绝在org.springframework.security.access.vote.AffirmativeBased.decide(AffirmativeBased.java:84) 

您的查询无法获取usersByUsername(由于查询中使用单引号)，并且由于异常而未触发AuthorityByUsername查询，导致弹簧安全性将用户视为ROLE_ANONYMOUS并因此被视为401(未授权)

如果要在STS/Eclipse控制台中查看日志.
1.在src/main/resources下创建logback.xml文件
2.复制以下代码

 <?xml version ="1.0" encoding ="UTF-8"?><配置>< appender name ="ALL_ROLLING_FILE"class ="ch.qos.logback.core.rolling.RollingFileAppender">< rollingPolicyclass ="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">< fileNamePattern> .. \ logs \ SMTH_Project.％d {yyyy-MM-dd_HH}.％i.log.zip</fileNamePattern>< timeBasedFileNamingAndTriggeringPolicyclass ="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">< maxFileSize> 100MB</maxFileSize></timeBasedFileNamingAndTriggeringPolicy>< MaxHistory> 30</MaxHistory></rollingPolicy><编码器>< pattern>％d {yyyy MM dd HH:mm:ss:SSS} [％-40thread]％-5level {5}-％msg％n</pattern></encoder></appender>< appender name ="ASYNC"class ="ch.qos.logback.classic.AsyncAppender">< appender-ref ref ="ALL_ROLLING_FILE"/>< queueSize> 1000000</queueSize>< discardingThreshold> 0</discardingThreshold></appender>< appender name ="STDOUT" class ="ch.qos.logback.core.ConsoleAppender"><编码器>< pattern>％-5level {5}-％msg％n</pattern></encoder></appender>< root level ="DEBUG">< appender-ref ref ="STDOUT"/></root></configuration> 

I checked the most of solutions from stackoverflow and also few official articles like: spring.io i have finished small webapi based on SpringBoot and i want change security level from inMemoryAuthentication to jdbcAuthentication and i think my code is correct and i have no idea why still i have 401 response code in Postman.

Here is my code:

1.POM DEPENDENCIES

<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-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.security</groupId>
        <artifactId>spring-security-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

2. DOMAIN CLASS API CLIENT

@Entity
@Table(name = "apiclient")
public class ApiClient {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @NotNull
    @Column(name = "username")
    @Size(max = 50)
    private String username;

    @NotNull
    @Column(name = "password")
    @Size(max = 65)
    private String password;

    @NotNull
    @Column(name = "role")
    @Size(max = 15)
    private String role;

    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;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "ApiClient{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", role='" + role + '\'' +
                '}';
    }
}

3. REST CONTROLLER

@RestController
public class HelloController {

    @RequestMapping(path = "/user")
    public String showUserMsg() {
        return "User has logged in!!!";
    }

    @RequestMapping(path = "/admin")
    public String showAdminMsg() {
        return "Admin has logged in!!!";
    }
}

4. SECURITY CLASS

@Configuration
@EnableWebSecurity
public class SpringSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.httpBasic();
        http.authorizeRequests()
                .antMatchers("/admin/**").hasRole("ADMIN")
                .antMatchers("/user/**").hasRole("USER")
                .antMatchers("/h2-console/**").permitAll()
                .anyRequest().authenticated();

        http.csrf().ignoringAntMatchers("/h2-console/**");
        http.headers().frameOptions().disable();
    }

    @Autowired
    public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().dataSource(dataSource)
                .usersByUsernameQuery("select username, password, true"
                        + " from apiclient where username='?'")
                .authoritiesByUsernameQuery("select username, role"
                        + " from apiclient where username='?'");
    }

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

5. COMMAND LINE RUNNER OF AUTO USER REGISTRATION

public class SampleDataLoader implements CommandLineRunner {

    @PersistenceContext
    EntityManager entityManager;

    @Autowired
    private PasswordEncoder passwordEncoder;

    @Override
    @Transactional
    public void run(String... args) throws Exception {

        ApiClient user = new ApiClient();
        user.setRole("USER");
        user.setPassword(passwordEncoder.encode("TESTPAS123"));
        user.setUsername("USER");
        entityManager.persist(user);
        entityManager.flush();

        System.out.println(user);

        ApiClient admin = new ApiClient();
        admin.setRole("ADMIN");
        admin.setPassword(passwordEncoder.encode("TESTPAS123"));
        admin.setUsername("ADMIN");
        entityManager.persist(admin);
        entityManager.flush();

        System.out.println(admin);
    }
}

After start springboot application h2 database look like:

解决方案

Query with single quote username='?' causing problem.

Modify

 @Autowired
 public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception 
 {
     auth.jdbcAuthentication().dataSource(dataSource)
             .usersByUsernameQuery("select username, password, true"
                 + " from apiclient where username='?'")
         .authoritiesByUsernameQuery("select username, role"
                 + " from apiclient where username='?'");
}

to

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
    auth.jdbcAuthentication()
            .dataSource(dataSource)
            .usersByUsernameQuery("select username, password, true"
                    + " from apiclient where username=?")
            .authoritiesByUsernameQuery("select username, role"
                    + " from apiclient where username=?");
}

After looking at your code shared from github link,
In your project you have not enabled logs with level DEBUG.

After enabling DEBUG logs i noticed

DEBUG - Executing prepared SQL statement [select username, password, true from apiclient where username='?'] 
DEBUG - Fetching JDBC Connection from DataSource 
...
DEBUG - Caching SQL error codes for DataSource [com.zaxxer.hikari.HikariDataSource@3bcd426c]: database product name is 'H2' 
DEBUG - Unable to translate SQLException with Error code '90008', will now try the fallback translator

...

DEBUG - Access is denied (user is anonymous); redirecting to authentication entry point 
org.springframework.security.access.AccessDeniedException: Access is denied
    at org.springframework.security.access.vote.AffirmativeBased.decide(AffirmativeBased.java:84)

Your query was failing to get usersByUsername (because of single quote in query) and authoritiesByUsername query was not fired due to exception, resulting spring-security to treat user as ROLE_ANONYMOUS and hence 401(Unauthorized)

If you want to see logs in STS/Eclipse console.
1. create logback.xml file under src/main/resources
2. Copy below code

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

<configuration>
    <appender name="ALL_ROLLING_FILE"
        class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy
            class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>..\logs\SMTH_Project.%d{yyyy-MM-dd_HH}.%i.log.zip</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy
                class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>100MB</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>%d{yyyy MM dd HH:mm:ss:SSS} [%-40thread] %-5level{5} - %msg %n</pattern>
        </encoder>
    </appender>

    <appender name="ASYNC"
        class="ch.qos.logback.classic.AsyncAppender">
        <appender-ref ref="ALL_ROLLING_FILE" />
        <queueSize>1000000</queueSize>
        <discardingThreshold>0</discardingThreshold>
    </appender>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%-5level{5} - %msg %n</pattern>
        </encoder>
    </appender>

    <root level="DEBUG">
        <appender-ref ref="STDOUT" />
    </root>
</configuration>

这篇关于WebSecurityConfigurerAdapter中通过httpBasic和jdbcAuthentication进行用户验证的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！