使用Spring Security和自定义数据库的Spring Boot [英] Spring boot with Spring Security and custom database
问题描述
对不起，我的英语不好。。
Sorry in advance for my bad english..
由于我更改了数据库配置，因此无法成功登录我的应用程序。
我正在使用Spring安全性。在进行更改之前，一切正常。
Since I have changed my database configuration I don't succeed to log me on my application. I am using Spring security. Before making the changes everything worked.
我有两个实体：
I have two entities :
- User.java
- UserRole.java
User.java
package betizy.models;
//imports
@Entity
@Table(name = "use_user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="USE_ID")
private Long id;
@NotNull
@Column(name = "USE_USERNAME")
private String username;
@NotNull
@Column(name = "USE_PASSWORD")
private String password;
@NotNull
@Column(name = "USE_EMAIL")
private String email;
//getters and setters
}
UserRole.java
package betizy.models;
//imports
@Entity
@Table(name = "usr_user_role")
public class UserRole {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="USR_ID")
private Long id;
@ManyToOne
@JoinColumn(name = "USR_USE_ID")
private User user;
@NotNull
@Column(name = "USR_ROLE")
private String role;
//getters and setters
}
login.html
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<head>
<title>Spring Security Example </title>
<script src="/webjars/angularjs/1.4.9/angular.js"></script>
<script src="webjars/jquery/2.0.3/jquery.min.js"></script>
<link rel="stylesheet" href="/webjars/bootstrap/3.3.6/css/bootstrap.css">
<script src="/js/index.js"></script>
</head>
<body ng-app="Betizy">
<div header></div>
<div th:if="${param.error}">
Invalid username and password.
</div>
<div th:if="${param.logout}">
You have been logged out.
</div>
<form th:action="@{/login}" method="post">
<div><label> User Name : <input type="text" name="username" required/> </label></div>
<div><label> Password: <input type="password" name="password" required/> </label></div>
<div><input type="submit" value="Sign In"/></div>
</form>
<div>To open a new account click <a href="/register">here</a>. </div>
<div footer></div>
</body>
</html>
SecurityConfig.java
package betizy.security;
//imports
@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
@Autowired
DataSource dataSource;
@Autowired
public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication().dataSource(dataSource)
//.passwordEncoder(passwordEncoder())
.usersByUsernameQuery(
"select * from use_user where use_user.use_username=?")
.authoritiesByUsernameQuery(
"select * from usr_user_role inner join use_user on use_user.use_id = usr_user_role.usr_use_id where use_user.use_username=?");
}
@Bean
public PasswordEncoder passwordEncoder(){
PasswordEncoder encoder = new BCryptPasswordEncoder();
return encoder;
}
@Override
protected void configure(HttpSecurity http) throws Exception {
http.authorizeRequests()
//.antMatchers("/hello").access("hasRole('ROLE_ADMIN')")
.antMatchers("/", "/register", "/user/create", "/webjars/**", "/js/**").permitAll()
.anyRequest().authenticated()
.and()
.formLogin().loginPage("/login").permitAll()
.usernameParameter("username").passwordParameter("password")
.and()
.logout().permitAll()
.and()
.exceptionHandling().accessDeniedPage("/403")
.and()
.csrf().disable();
}
}
I我认为问题在于我的 User 实体字段的名称或我在 SecurityConfig.java 中的两个查询，但是我知道该如何解决我的问题。
我必须保留我的数据库配置（字段名称）。
I think problem is with name of my User entity fields or my two queries in SecurityConfig.java, but I have any idea how can I do to solve my problem.
I must keep my database configuration (name of fields).
在此先感谢您的帮助！ ：）
Thank you in advance for your help ! :)
编辑
进行两次更改都可以使用，但它不是一个很好的数据库。我将发布两个数据库之间的差异以及 SecurityConfig.java
With two changes all works but it is not the good database. I will post differences between the two databases and differences in SecurityConfig.java
First base (it is works)
User table
User Role table
具有 SecurityConfig。 java
@Autowired
public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication().dataSource(dataSource)
.usersByUsernameQuery(
"select username,password, enabled from users where username=?")
.authoritiesByUsernameQuery(
"select username, role from user_roles where username=?");
}
第二个不起作用。我无法发布链接，但是您在 User.java 和 UserRole.java
Second it doesn't work. I can't post links but you have the perfect description above in User.java and UserRole.java
使用 SecurityConfig.java
@Autowired
public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication().dataSource(dataSource)
.usersByUsernameQuery(
"select use_username, use_password, use_email from use_user where use_username=?")
.authoritiesByUsernameQuery(
"select use_username, usr_role from usr_user_role, use_user where use_id = usr_use_id and use_username=?");
}
推荐答案
请更改SQL以返回列名按顺序而不是 * ;
Please change the SQL to return the column names in order instead of *;
<!-- change to your own column name, return the columns in order-->
select <username,password,enabled> from use_user where use_user.use_username=?
和 AUTHORITIES SQL
<!-- change to your own column name, the authority must be second column -->
select <username,authority> from usr_user_role inner join use_user on use_user.use_id = usr_user_role.usr_use_id where use_user.use_username=?
更多详细信息，请参见此处，确定，这是代码，我认为它更容易
More detail see here, OK, here is the code, I think it's more easy to understand why you should return the columns in order.
protected List<UserDetails> loadUsersByUsername(String username) {
return getJdbcTemplate().query(this.usersByUsernameQuery,
new String[] { username }, new RowMapper<UserDetails>() {
@Override
public UserDetails mapRow(ResultSet rs, int rowNum)
throws SQLException {
// get user info
String username = rs.getString(1);
String password = rs.getString(2);
boolean enabled = rs.getBoolean(3);
return new User(username, password, enabled, true, true, true,
AuthorityUtils.NO_AUTHORITIES);
}
});
}
protected List<GrantedAuthority> loadUserAuthorities(String username) {
return getJdbcTemplate().query(this.authoritiesByUsernameQuery,
new String[] { username }, new RowMapper<GrantedAuthority>() {
@Override
public GrantedAuthority mapRow(ResultSet rs, int rowNum)
throws SQLException {
//get GrantedAuthority
String roleName = JdbcDaoImpl.this.rolePrefix + rs.getString(2);
return new SimpleGrantedAuthority(roleName);
}
});
}
这篇关于使用Spring Security和自定义数据库的Spring Boot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
![用户表](\"https://i.stack.imgur.com/Q0wya.png\"){kind=link}