使用 spring-security: WebSecurityConfigurerAdapter: auth.jdbcAuthentication().usersByUsernameQuery: 查询字符串应该是什么样的? [英] Using spring-security: WebSecurityConfigurerAdapter: auth.jdbcAuthentication().usersByUsernameQuery: what should the query string look like?

问题描述

使用带有两个表的 postgresql db 来验证登录...(用户和权限)

Using postgresql db with two tables to authenticate login ...(users and authorities)

CREATE TABLE users
(
   username character(50) NOT NULL,
   password character(50) NOT NULL,
   enabled boolean NOT NULL,
   CONSTRAINT users_pkey PRIMARY KEY (username)
)

CREATE TABLE authorities
(
    username character(50) NOT NULL,
    authority character(50) NOT NULL,
    CONSTRAINT fk_authorities_users FOREIGN KEY (username)
       REFERENCES users (username) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)

当我尝试执行以下操作时，我做错了什么?

What am I doing wrong when I try to do the following?

registerAuthentication(AuthenticationManagerBuilder auth){

auth.
      jdbcAuthentication()
            .dataSource(dataSource)
            .usersByUsernameQuery("select username,password,enabled 
                 from users where username = ?")
            .authoritiesByUsernameQuery("select username,authority from 
                 authorities where username = ?");

}

谢谢

推荐答案

试试这个:

auth.jdbcAuthentication()                
    .dataSource(dataSource)                
    .usersByUsernameQuery("select username as principal, password as credentials, true from users where username = ?")               
    .authoritiesByUsernameQuery("select username as principal, authority as role from authorities where username = ?")                
    .rolePrefix("ROLE_");

也许这个源代码可以帮助你:https://github.com/spring-projects/spring-security-javaconfig/blob/master/spring-security-javaconfig/src/test/groovy/org/springframework/security/config/annotation/authentication/NamespaceJdbcUserServiceTests.groovy

May be this source code can help you: https://github.com/spring-projects/spring-security-javaconfig/blob/master/spring-security-javaconfig/src/test/groovy/org/springframework/security/config/annotation/authentication/NamespaceJdbcUserServiceTests.groovy

这篇关于使用 spring-security: WebSecurityConfigurerAdapter: auth.jdbcAuthentication().usersByUsernameQuery: 查询字符串应该是什么样的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！