Spring Security:配置(AuthenticationManagerBuilder 身份验证) [英] Spring Security: configure(AuthenticationManagerBuilder auth)

问题描述

我有一个实体 User 具有以下属性:

I have an entity User with attributes:

• id
• 用户名
• 密码
• 名字
• 姓氏
• 用户角色

• id
• username
• password
• firstname
• lastname
• userrole

属性userrole是enum的类型，没有Set，所以1个用户角色/用户.

Attribute userrole is type of enum and no Set, so 1 userrole/user.

现在我想做一个 JDBC 身份验证.

Now I want to do a JDBC authentication.

现在我有:

auth
    .jdbcAuthentication()
        .dataSource(dataSource)
        .usersByUsernameQuery("select username, password from user where username=?")
        .authoritiesByUsernameQuery("select username, userrole from user where username=?");

但这不起作用.

.usersByUsernameQuery("...") 和 .authoritiesByUsernameQuery("..."); 中的查询应该如何制定?

How should the query in .usersByUsernameQuery("...") and .authoritiesByUsernameQuery("..."); be formulated?

错误信息:

引起:org.h2.jdbc.JdbcSQLException:参数columnIndex"不允许值3"参数columnIndex"的无效值3"[90008-192]在 org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.192.jar:1.4.192]在 org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.192.jar:1.4.192]在 org.h2.message.DbException.getInvalidValueException(DbException.java:228) ~[h2-1.4.192.jar:1.4.192]在 org.h2.jdbc.JdbcResultSet.checkColumnIndex(JdbcResultSet.java:3172) ~[h2-1.4.192.jar:1.4.192]在 org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3200) ~[h2-1.4.192.jar:1.4.192]在 org.h2.jdbc.JdbcResultSet.getBoolean(JdbcResultSet.java:541) ~[h2-1.4.192.jar:1.4.192]在 org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:223) ~[spring-security-core-4.1.3.RELEASE.jar:4.1.3.RELEASE]在 org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:218) ~[spring-security-core-4.1.3.RELEASE.jar:4.1.3.RELEASE]在 org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]在 org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]在 org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:697) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]在 org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE]...省略了 65 个常用帧

Caused by: org.h2.jdbc.JdbcSQLException: Not allowed value "3" for parameter "columnIndex" Invalid value "3" for parameter "columnIndex" [90008-192] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.192.jar:1.4.192] at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.192.jar:1.4.192] at org.h2.message.DbException.getInvalidValueException(DbException.java:228) ~[h2-1.4.192.jar:1.4.192] at org.h2.jdbc.JdbcResultSet.checkColumnIndex(JdbcResultSet.java:3172) ~[h2-1.4.192.jar:1.4.192] at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3200) ~[h2-1.4.192.jar:1.4.192] at org.h2.jdbc.JdbcResultSet.getBoolean(JdbcResultSet.java:541) ~[h2-1.4.192.jar:1.4.192] at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:223) ~[spring-security-core-4.1.3.RELEASE.jar:4.1.3.RELEASE] at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl$1.mapRow(JdbcDaoImpl.java:218) ~[spring-security-core-4.1.3.RELEASE.jar:4.1.3.RELEASE] at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE] at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE] at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:697) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.3.RELEASE.jar:4.3.3.RELEASE] ... 65 common frames omitted

推荐答案

用户的查询需要3个参数，见Spring 安全参考:

The query for the user needs 3 parameters, see Spring Security Reference:

• users-by-username-query 用于查询用户名、密码和给定用户名的启用状态的 SQL 语句.默认为

• users-by-username-query An SQL statement to query a username, password, and enabled status given a username. The default is

select username, password, enabled from users where username = ?

这篇关于Spring Security:配置(AuthenticationManagerBuilder 身份验证)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！