Spring Security + JPA user schema

Question

As per Spring documentation if you need to manage spring security via database you should have some standard schema of tables. for example.

create table users(
    username varchar(256) not null primary key,
    password varchar(256) not null,
    enabled boolean not null
);

create table authorities (
    username varchar(256) not null,
    authority varchar(256) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

The problem I am facing is following. 1) Not able to understand how could I achieve such schema of tables using JPA?

I have tried something as follows.

@Entity
@Table(name="USERS")
public class UsersPersistence extends Users implements Serializable{

    private static final long serialVersionUID = 1009548075747154488L;

    public UsersPersistence() {
        super();
    }

    public UsersPersistence(long id, String userName, String password, boolean enabled) {
        super(id, userName,password,enabled);
    }

    @Id
    @GeneratedValue
    @Column(name="id")
    @Override
    public long getId() {
        return super.getId();
    }

    @Column(name="username", nullable=false)
    @Override
    public String getUserName() {
        return super.getUserName();
    }

    @Column(name="password", nullable=false)
    @Override
    public String getPassword() {
        return super.getPassword();
    }

    @Column(name="enabled", nullable=false)
    @Override
    public boolean isEnabled() {
        return super.isEnabled();
    }

}

This table created as per requirement stated in Spring documentation schema. Problem in understanding is when i am trying to assign a foreign key on username in authorities table.Since JPA assign the foreign key's via the id of parent table (primary key Table) Or may be i do not know how to assign it.

Following is the JPA class which create problem :-

@Entity
@Table(name="AUTHORITIES")
public class AuthoritiesPersistence extends Authorities implements Serializable{

    private static final long serialVersionUID = 1L;

    public AuthoritiesPersistence() {
        super();
    }

    public AuthoritiesPersistence(long id, UsersPersistence userName, String authority) {
        super(id,userName,authority);
    }

    @Id
    @GeneratedValue
    @Column(name="id")
    @Override
    public long getId() {
        return super.getId();
    }

    @Override
    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="username", nullable=false)
    public UsersPersistence  getUserName() {
        return (UsersPersistence) super.getUserName();
    }

    @Column(name="authority", nullable=false)
    @Override
    public String getAuthority() {
        return super.getAuthority();
    }

}

This table is created successfully but Spring security authentication is not able to recognize the username because JPA uses the foreign key id than the actual user name.

Any help would be appreciable. I am really stuck in creating a foreign key which will be based on the username rather than the id. Thanks

Answer 1

I was able to map those tables using the following class definition:

@Entity
@Table(name = "users")
public class User {

    @Id
    @Column(name = "username")
    private String username;
    @Column(name = "password")
    private String password;
    @Column
    private boolean enabled;
    @Column
    private String firstName;
    @ElementCollection
    @JoinTable(name = "authorities", joinColumns = {@JoinColumn(name = "email")})
    @Column(name = "authority")
    private Set<String> roles;

    public User() {
    }

    public Serializable getId() {
        return username;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public boolean isEnabled() {
        return enabled;
    }

    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Set<String> getRoles() {
        return roles;
    }

    public void setRoles(Set<String> roles) {
        this.roles = roles;
    }

}

Answer 2

There are a couple of ways you could handle this:

• You could write your own AuthenticationProvider using Hibernate for DB access
• You could use the <jdbc-user-service> and overwrite the SQL queries as mentioned by @zagyi. (http://static.springsource.org/spring-security/site/docs/current/reference/appendix-namespace.html#nsa-jdbc-user-service)
• Or you can create the your schema to fit the standard <jdbc-user-service>

To use the approach you are interested in you have to know that aside from having the fields username, password and enabled spring security expects the username to be a unique identifier. This means that you can use the username property of your entity as Id for your DB and Hibernate. If you don't want to do this a way of approaching this is to set a table wihch defines the authorites using an ID/Name and the authority. And then to set up the use a jointable to map them to the users. Some untested examplecode: Role:

@Entity
@DynamicUpdate
@Table(name ="authorities")
public class Authority{


    private String authority;

    @Id
    @Column(name="authority")
    public String getAuthority() {
        return authority;
    }

User:

@Entity
@DynamicUpdate
@Table(name = "users", uniqueConstraints={ @UniqueConstraint(columnNames={"username"})})
public class User {
private String username;
private List<Authority> authorities;
@Type(type = "numeric_boolean")
    private boolean enabled;


        @Id
    @Column(name="username")
    public String getUsername() {
        return username;
    }

        @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
       name = "authorities",
       joinColumns = @JoinColumn(name = "username"), 
       inverseJoinColumns = @JoinColumn(name = "rolename")
     )
    public List<Authority> getauthorities() {
        return authorities;
    }

        @Column(name="ENABLED")
    public boolean isEnabled() {
        return enabled;
    }

When the base is running you can add properties for internal use as u like.

Answer 3

I have figure out the Alternative which is "Configuring the JdbcUserDetailsManager to use custom SQL queries" at least i can create my tables via JPA and Can hope " users-by-username-query and authorities-by-username-query " would do my work indeed. To achieve it I have to add following schema .

create table custom_user_authorities (
    id bigint identity,
    user bigint not null,
    authority varchar(256) not null,
);

this schema have id(which will be auto-incremented) which will definitely work for JPA.

Answer 4

You only have to stick to the schema given in Spring Security reference docs, when using a default JdbcDaoImpl as UserDetailsService implementation, which is the case if you have the <jdbc-user-service> tag in your security configuration. Even then it is possible to override the default SQL queries it uses to fetch users and authorities (refer to the namespace appendix).

However if you manage user accounts using hibernate, it would make sense to write your own UserDetailsService implementation, instead of trying to create JPA entities that result in the specific schema required by the JdbcDaoImpl.

The documentation states as well:

If your application does use an ORM tool, you might prefer to write a custom UserDetailsService to reuse the mapping files you've probably already created.