问题描述
我正在使用 Spring 安全性运行 1.1.4.RELEASE spring-boot 应用程序.我有基本的 Spring 用户和权限表和实体:
I am running a 1.1.4.RELEASE spring-boot app using Spring security. I have the basic Spring Users and Authorities table and entities:
@Entity
@Table(name = "USERS")
public class User implements UserDetails {
@Id
private String username;
@Column(nullable = false)
private String password;
private boolean enabled;
private boolean accountNonExpired;
private boolean accountNonLocked;
private boolean credentialsNonExpired;
private String companyName;
@OneToMany(mappedBy = "username", fetch = FetchType.EAGER, orphanRemoval = true)
@Cascade({org.hibernate.annotations.CascadeType.ALL})
private Set<Authorities> roles = new HashSet<Authorities>();
/// getters and setters
}
@Entity
public class Authorities {
@Id
private String username;
private String authority;
/// getters and setters
}
我有一个仓库:
public interface UserRepository extends CrudRepository<User, String> {
User findUserByUsername(String username);
void delete(String username);
List<User> findAll();
}
我用作:
@PreAuthorize("hasRole('ADMIN')")
@Transactional
@RequestMapping(value = "/deleteUser/{username}", method = RequestMethod.POST)
public String deleteUser(@PathVariable String username, Model model) {
customUserDetailsService.delete(username);
**the following line throws the exception**
model.addAttribute(UPLOAD, customUserDetailsService.findAllUsers());
return "admin";
}
当我尝试删除用户时,出现错误:
When I try and delete a user, I get an error:
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["IX_AUTH_USERNAME: PUBLIC.AUTHORITIES FOREIGN KEY(USERNAME) REFERENCES PUBLIC.USERS(USERNAME) ('testUser')"; SQL statement:
delete from USERS where username=? [23503-172]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
我认为我的映射是错误的,但老实说我不知道在哪里.
I think my mapping is wrong, but honestly I don't see where.
更新:根据出色的反馈,我现在有以下几点:
UPDATE:Based on the excellent feedback, I now have the following:
@Entity
@Table(name = "USERS")
public class User implements UserDetails {
@Id
private String username;
@Column(nullable = false)
private String password;
private boolean enabled;
private boolean accountNonExpired;
private boolean accountNonLocked;
private boolean credentialsNonExpired;
private String companyName;
@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="Authorities", joinColumns=@JoinColumn(name="username"))
@Column(name="authority")
private Set<String> roles = new HashSet<String>();
// getters & setters
}
@Repository
public interface UserRepository extends CrudRepository<User, String> {
User findUserByUsername(String username);
void delete(String username);
List<User> findAll();
}
我通过以下方式使用:
@Controller
@PreAuthorize("hasRole('Admin')")
@RequestMapping("/admin")
public class AdminController {
@PreAuthorize("hasRole('ADMIN')")
@Transactional
@RequestMapping(value = "/deleteUser/{username}", method = RequestMethod.POST)
public String deleteUser(@PathVariable String username, Model model) {
customUserDetailsService.delete(username);
updateModelWithAllUsers(model);
model.addAttribute("user", new User());
return "admin";
}
private void updateModelWithAllUsers(Model model) {
model.addAttribute(USERLIST, customUserDetailsService.findAllUsers());
}
..
}
和
@Component
public class CustomUserDetailsService implements UserDetailsService {
public void delete(String username) {
userRepository.delete(username);
}
..
}
我正在使用 H2,我在 liquibase 中创建和播种:
I am using H2, which I create and seed in liquibase as:
<createTable tableName="Users">
<column name="username" type="VARCHAR_IGNORECASE(50)"/>
<column name="password" type="VARCHAR_IGNORECASE(500)"/>
<column name="companyName" type="VARCHAR_IGNORECASE(500)"/>
<column name="enabled" type="boolean"/>
<column name="accountNonExpired" type="boolean"/>
<column name="accountNonLocked" type="boolean"/>
<column name="credentialsNonExpired" type="boolean"/>
</createTable>
<createTable tableName="Authorities">
<column name="username" type="VARCHAR(50)">
<constraints primaryKey="true" nullable="false"
foreignKeyName="ix_auth_username" references="Users(username)" deleteCascade="true"/>
</column>
<column name="authority" type="VARCHAR_IGNORECASE(500)"/>
</createTable>
所以这似乎工作正常.边缘条件是,如果我创建一个用户,删除然后创建一个具有相同用户名的用户,我会得到一个异常:
So this appears to work fine. The edge condition is that if I create a user, delete and then create a user with the same username I get an exception:
Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY_KEY_A ON PUBLIC.AUTHORITIES(USERNAME)"; SQL statement:
insert into Authorities (username, authority) values (?, ?) [23505-172]
推荐答案
删除 Authorities
类.请记住,实体与表不同.在 User 类中使用以下映射:
Remove the Authorities
class. Remember, entities are not the same as tables. Use the following mapping in User class:
@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="authorities", joinColumns=@JoinColumn(name="username"))
@Column(name="authority")
private Set<String> authorities;
此外,您可以在外键上添加ON DELETE CASCADE
.
Additionally, you can add ON DELETE CASCADE
on the foreign key.
这篇关于spring security 删除用户失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!