spring security 删除用户失败 [英] spring security deleting users failure

问题描述

我正在使用 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 删除用户失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！