Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 39 ยท Authentication Mechanisms

JDBC User Stores and Custom Schemas

Store users in a database with JdbcUserDetailsManager, learn its default schema, then write your own queries for a custom users table with authorities and lockout fields.

Real users live in a database. Spring Security gives you JdbcUserDetailsManager, a ready-made UserDetailsService that reads two tables, plus the option to change every SQL statement it uses, or to skip it and write your own UserDetailsService on top of JPA. This lesson explains the default schema, how to adapt it, and the queries that matter for performance and safety.

The real Spring classes are read-only here. The SQL and JDBC code, however, runs: the Java runner on this site has an in-memory H2 database on its classpath, so you can try the default schema and the exact queries the manager issues.

The default schema

JdbcUserDetailsManager expects two tables:

SQL
create table users(
    username varchar(50) not null primary key,
    password varchar(500) not null,
    enabled boolean not null
);
create table authorities(
    username varchar(50) not null,
    authority varchar(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username, authority);

and uses these statements by default:

SQL
select username, password, enabled from users where username = ?
select username, authority from authorities where username = ?

Boot can create the schema and a first user for you in development; Spring Security ships the script as org/springframework/security/core/userdetails/jdbc/users.ddl. For production, create tables with your normal migration tool (Flyway, Liquibase) instead.

Java
@Bean
UserDetailsManager users(DataSource dataSource) {
    return new JdbcUserDetailsManager(dataSource);
}

Besides loadUserByUsername, JdbcUserDetailsManager also implements UserDetailsManager: createUser, updateUser, deleteUser, changePassword, userExists. That is enough for a simple admin screen.

Running the default queries

The program below creates the schema in H2, inserts users with prefixed password hashes (placeholders here), and runs the same two lookups that the manager performs. Run it to see the shape of the result.

Java Default users/authorities schema in H2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Note the use of PreparedStatement with a ? placeholder. The username comes from the login form, which is attacker-controlled input. Concatenating it into SQL is the classic injection hole; Spring's manager always uses placeholders, and so must any query you write yourself.

Using your own tables

Most applications already have a app_user table with an email column, a status and perhaps a failed-login counter. Do not rename your schema to fit the framework. Change the queries instead:

Java
@Bean
UserDetailsManager users(DataSource ds) {
    JdbcUserDetailsManager m = new JdbcUserDetailsManager(ds);
    m.setUsersByUsernameQuery(
        "select email, password_hash, status = 'ACTIVE' from app_user where lower(email) = lower(?)");
    m.setAuthoritiesByUsernameQuery(
        "select u.email, p.code from app_user u "
      + "join user_role ur on ur.user_id = u.id "
      + "join role_permission rp on rp.role_id = ur.role_id "
      + "join permission p on p.id = rp.permission_id where lower(u.email) = lower(?)");
    return m;
}

The contract is by column position, not name: the users query must return (username, password, enabled) and, optionally, three more booleans for the account flags; the authorities query must return (username, authority). Verify with a test that loads one user.

If you also want groups (roles that contain authorities) there is a third query, setGroupAuthoritiesByUsernameQuery, and enableGroups(true). For anything more complicated than this, a custom UserDetailsService backed by a repository is easier to evolve than three long SQL strings.

Joining a normalized role model

The second TryIt uses a realistic schema: roles that contain permissions, and users with several roles. The authorities query flattens it into the rows Spring expects. It also demonstrates two production details: case-insensitive lookup and an index on the lookup column.

Java A custom schema flattened into authorities

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Observe that Alice receives both role names (as ROLE_...) and permission codes in one flat list. That list becomes her GrantedAuthority collection, which the authorization rules then test with hasRole and hasAuthority (see roles and authorities).

Production concerns

  • Index the lookup column and match its collation to the query (for lower(email) use a case-insensitive column type or collation, or a functional index where the database supports one; the H2 example uses varchar_ignorecase with a unique constraint). Every login and, with Basic auth, every request runs this query.
  • Cache nothing by accident. A UserDetailsService that caches users keeps suspended accounts logged in. If you must cache, keep the TTL short or evict on change.
  • Limit what you select. Do not select the whole row if it contains large columns or other people's data.
  • Mind the transaction. JdbcUserDetailsManager runs plain JDBC; if you use JPA elsewhere, make sure writes to the user tables are in a transaction.
  • Passwords: the column must be wide enough for prefixed hashes ( hashes are 68 characters; Argon2 ones are longer). The default varchar(500) exists for that reason. Truncated hashes simply never match.
  • Failed-login counters belong in the same transaction as the check, or in a separate event listener on AuthenticationFailureBadCredentialsEvent. See login throttling.

JPA instead of raw SQL

A repository-backed implementation is the same pattern with entities. It is easier to test with an in-memory store, as shown in UserDetailsService:

Java
@Service
class JpaUserDetailsService implements UserDetailsService {

    private final UserRepository users;

    JpaUserDetailsService(UserRepository users) { this.users = users; }

    @Override
    @Transactional(readOnly = true)
    public UserDetails loadUserByUsername(String email) {
        AppUser u = users.findWithPermissionsByEmailIgnoreCase(email)
                .orElseThrow(() -> new UsernameNotFoundException(email));
        return new AppUserPrincipal(u);        // wraps the entity, exposes authorities
    }
}

Use an entity graph or a fetch join for the roles and permissions, otherwise a lazy collection is loaded outside the transaction and fails with LazyInitializationException after the method returns.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which two tables does JdbcUserDetailsManager expect by default?

    The default schema has users(username, password, enabled) and authorities(username, authority).
  2. 2. How does JdbcUserDetailsManager map query results when you replace the SQL?

    The queries must return the expected columns in the expected order, whatever their names.
  3. 3. Why must the user lookup use a PreparedStatement parameter?

    Parameters keep data separate from SQL, which prevents injection.
  4. 4. What is a common cause of valid passwords never matching with a custom schema?

    Hashes with an id prefix are long; a truncated value can never match.

Summary

  • JdbcUserDetailsManager reads users and authorities by default and also manages users (create, update, delete, change password).
  • Replace its queries to fit your own schema; results are matched by column position.
  • Always use parameters, index the login column and size the password column for prefixed hashes.
  • Flatten roles and permissions into one authority list with a query, then test them with hasRole and hasAuthority.
  • Prefer a repository-backed UserDetailsService when the model grows complex.

Next: Remember-me, sessions and fixation