Howto implement Spring Security User/Authorities with Hibernate/JPA2?

Question

I am trying to implement DAOs to work with Spring Security database authentication in Hibernate/JPA2. Spring uses following relations and associations in order to represent user & roles:

repesented as postgresql create query:

CREATE TABLE users
(
  username character varying(50) NOT NULL,
  "password" character varying(50) NOT NULL,
  enabled boolean NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (username)
);
CREATE TABLE authorities
(
  username character varying(50) NOT NULL,
  authority character varying(50) NOT NULL,
  CONSTRAINT fk_authorities_users FOREIGN KEY (username)
      REFERENCES users (username) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Using the on-board implementations of GrantedAuthorities, UserDetailsService and UserDetailsmanager, everything is fine. However, I am not satisfied with the JDBC implementation of Spring and would like to write my own ones. In order to do so, I tried to create a representation of the relations by following business objects:

The user entity:

@Entity
@Table(name = "users", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"})})
public class AppUser implements UserDetails, CredentialsContainer {

    private static final long serialVersionUID = -8275492272371421013L;

    @Id
    @Column(name = "username", nullable = false, unique = true)
    private String username;

    @Column(name = "password", nullable = false)
    @NotNull
    private String password;

    @OneToMany(
            fetch = FetchType.EAGER, cascade = CascadeType.ALL,
            mappedBy = "appUser"
    )
    private Set<AppAuthority> appAuthorities;

    @Column(name = "accountNonExpired")
    private Boolean accountNonExpired;

    @Column(name = "accountNonLocked")
    private Boolean accountNonLocked;

    @Column(name = "credentialsNonExpired")
    private Boolean credentialsNonExpired;

    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "personalinformation_fk", nullable = true)
    @JsonIgnore
    private PersonalInformation personalInformation;

    @Column(name = "enabled", nullable = false)
    @NotNull
    private Boolean enabled;

    public AppUser(
            String username,
            String password,
            boolean enabled,
            boolean accountNonExpired,
            boolean credentialsNonExpired,
            boolean accountNonLocked,
            Collection<? extends AppAuthority> authorities,
            PersonalInformation personalInformation
    ) {
        if (((username == null) || "".equals(username)) || (password == null)) {
            throw new IllegalArgumentException("Cannot pass null or empty values to constructor");
        }

        this.username = username;
        this.password = password;
        this.enabled = enabled;
        this.accountNonExpired = accountNonExpired;
        this.credentialsNonExpired = credentialsNonExpired;
        this.accountNonLocked = accountNonLocked;
        this.appAuthorities = Collections.unmodifiableSet(sortAuthorities(authorities));
        this.personalInformation = personalInformation;
    }

    public AppUser() {
    }

    @JsonIgnore
    public PersonalInformation getPersonalInformation() {
        return personalInformation;
    }

    @JsonIgnore
    public void setPersonalInformation(PersonalInformation personalInformation) {
        this.personalInformation = personalInformation;
    }

    // Getters, setters 'n other stuff

And the authority entity as an implementation of GrantedAuthorities:

@Entity
@Table(name = "authorities", uniqueConstraints = {@UniqueConstraint(columnNames = {"id"})})
public class AppAuthority implements GrantedAuthority, Serializable {
    //~ Instance fields ================================================================================================

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "username", nullable = false)
    private String username;

    @Column(name = "authority", nullable = false)
    private String authority;

    // Here comes the buggy attribute. It is supposed to repesent the
    // association username<->username, but I just don't know how to
    // implement it 
    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "appuser_fk")
    private AppUser appUser;

    //~ Constructors ===================================================================================================

    public AppAuthority(String username, String authority) {
        Assert.hasText(authority,
                "A granted authority textual representation is required");
        this.username = username;
        this.authority = authority;
    }

    public AppAuthority() {
    }

    // Getters 'n setters 'n other stuff

My problem is the @ManyToOne assoc. of AppAuthorities: It is supposed to be "username", but trying and doing so throws an error, because I've got to typify that attribute as String ... while Hibernate expects the associated entity. So what I tryied is actually providing the correct entity and creating the association by @JoinColumn(name = "appuser_fk"). This is, of course, rubbish, because in order to load the User, I will have the foreign key in username, while Hibernate searches for it in appuser_fk, which will always be empty.

So here is my question: any suggestion on how to modify the above metioned code in order to get a correct JPA2 implementation of the data model?

Thanks

Answer 1

There is one more way that decouples the UserDetailsService from JPA/Hibernate.

You can model your User and Authority class as you like and use this while defining userDetailsService in configuration:-

<sec:jdbc-user-service data-source-ref="webDS"
                id="userDetailsService"
                users-by-username-query="SELECT USER_NAME,PASSWORD,TRUE FROM CUSTOMER WHERE USER_NAME=?"
                authorities-by-username-query="SELECT c.USER_NAME,r.ROLE_NAME from CUSTOMER c 
                                          JOIN CUSTOMER_ROLE cr ON c.CUSTOMER_ID = cr.CUSTOMER_ID 
                                          JOIN ROLE r ON cr.ROLE_ID = r.ROLE_ID 
                                          WHERE USER_NAME=?" />

This way you can define fine tuned SQL query to fetch user and roles from your database. All you need to take care of is the table and column name.

Answer 2

You AppAuthority doesn't need username at all. Spring Security can't depend on it because it depends on the GrantedAuthority interface which doesn't have any methods to access username.

But the better practice is to decouple your domain model from Spring Security. When you have a custom UserDetailsService, you don't need to mimic neither Spring Security's default database schema nor its object model. Your UserDetailsService can load your own AppUser and AppAuthority and then create UserDetails and GrantedAuthoritys based on them. This leads to cleaner design with better separation of concerns.

Answer 3

This looks like the classic Hibernate problem of using a domain-specific key. A possible fix would be to create a new primary key field; e.g. userId int for the Users and Authorities entities / tables, remove Authorities.userName, and change Users.userName to a unique secondary key.