Spring Security Database-backed Form Login

Learn to configure the JDBC-based form login security that fetches the username, password and roles from the database. We have already learned to configure the various options of form login security in the linked post.

1. Role of UserDetailsService in Authentication

If we refer to the Spring security architecture, we can see that verifying the user-supplied authentication tokens (username/password, LDAP, OTP or any other technique) is handled by the UserDetailsService implementation that we plug into AuthenticationProvider.

The primary responsibility of UserDetailsService is to find a user by its username from the cache or underlying storage. After the UserDetails has been loaded, the provider matches the stored password with user-supplied password using the configured password encoder.

UserDetails loadUserByUsername(String username) throws UsernameNotFoundException;

This flow helps us to plugin any user details store by implementing and injecting the appropriate UserDetailsService implementation without changing any other part of the spring security configuration.

2. Configuring JdbcUserDetailsManager with Default Schema

To store and retrieve the username and passwords from a SQL database, we use JdbcUserDetailsManager class. It connects to the database directly through JDBC. Note that JdbcUserDetailsManager extends JdbcDaoImpl, and JdbcDaoImpl implements the UserDetailsService interface.

In the following example, auth.jdbcAuthentication() provides reference to JdbcUserDetailsManagerConfigurer that helps in setting up schema and JDBC authentication and allows easily adding users to the database used for authentication.

Using the withDefaultSchema() method, we intend to use the default database schema. The default schema file is in the file users.ddl. The file location is given in the constant JdbcDaoImpl.DEFAULT_USER_SCHEMA_DDL_LOCATION.

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

  @Autowired
  private DataSource dataSource;

  @Override
  public void configure(AuthenticationManagerBuilder auth)
      throws Exception {
    
    auth.jdbcAuthentication()
        .dataSource(dataSource)
        .withDefaultSchema()
        /*.withUser(User.withUsername("user")
            .password(passwordEncoder().encode("password"))
            .roles("USER"))*/
        ;
  }

  @Bean
  public PasswordEncoder passwordEncoder() {
    return new BCryptPasswordEncoder();
  }

  @Override
  protected void configure(final HttpSecurity http) throws Exception {

    //@formatter:off
    http.authorizeRequests()
        .antMatchers("/login").permitAll()
        .antMatchers("/**").hasAnyRole("USER", "ADMIN")
        .antMatchers("/admin/**").hasAnyRole("ADMIN")
        .and()
          .formLogin()
          .loginPage("/login")
          .loginProcessingUrl("/process-login")
          .defaultSuccessUrl("/home")
          .failureUrl("/login?error=true")
          .permitAll()
        .and()
          .logout()
          .logoutSuccessUrl("/login?logout=true")
          .invalidateHttpSession(true)
          .deleteCookies("JSESSIONID")
          .permitAll()
        .and()
          .csrf()
          .disable();
    //@formatter:on
  }

  @Override
  public void configure(WebSecurity web) {
    web.ignoring()
        .antMatchers("/resources/**", "/static/**");
  }
}

Note that DataSource is configured by Spring in various ways. For example, we can add the src/main/resources/META-INF/persistence.xml and provide database connectivity details.

<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://xmlns.jcp.org/xml/ns/persistence"
  xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
  version="2.1">
  <persistence-unit name="MySQLDB">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="jakarta.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="jakarta.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test"/>
      <property name="jakarta.persistence.jdbc.user" value="dbuser"/>
      <property name="jakarta.persistence.jdbc.password" value="dbpass"/>
      <property name="hibernate.show_sql" value="true"/>
      <property name="hibernate.format_sql" value="true"/>
      <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
    </properties>
  </persistence-unit>
</persistence>

3. Configuring JdbcUserDetailsManager with Custom Schema

The default schema is good but it may not fit in all usecases. Sometimes we need to support additional columns or different columns for authentication purposes.

For example, we may be asking the users to login by email id and password. Or we may be asking the users to provide additional domain name information. There can be many such scenarios.

In these cases, we need to provide the custom schema in file schema.sql. Any initial data we may need to insert into the tables, we can place the SQL INSERT/UPDATE queries in data.sql file. Place both files in the application’s /resources folder.

create table users(
  username varchar_ignorecase(50) not null primary key,
  password varchar_ignorecase(500) not null,
  enabled boolean not null
);

create table authorities (
  username varchar_ignorecase(50) not null,
  authority varchar_ignorecase(50) not null,
  constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);
insert into users (username, password, enabled)
  values ('user',
    '$2a$10$dXJ3SW6G7P50lGmMkkmwe.20cQQubK3.HZWzG3YB1tlRy.fqvM/BG',
    1);

insert into authorities (username, authority)
  values ('user', 'ROLE_USER');

insert into users (username, password, enabled)
  values ('admin',
    '$2a$10$dXJ3SW6G7P50lGmMkkmwe.20cQQubK3.HZWzG3YB1tlRy.fqvM/BG',
    1);

insert into authorities (username, authority)
  values ('admin', 'ROLE_ADMIN');
insert into authorities (username, authority)
  values ('admin', 'ROLE_USER');

Now we need to plug in the JdbcUserDetailsManager instance to Spring security that is automatically used the AuthenticationProvider.

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

  @Autowired
  private DataSource dataSource;

  @Bean
  public UserDetailsService jdbcUserDetailsService(DataSource dataSource) {
    return new JdbcUserDetailsManager(dataSource);
  }

  @Bean
  public PasswordEncoder passwordEncoder() {
    return new BCryptPasswordEncoder();
  }

  @Override
  protected void configure(final HttpSecurity http) throws Exception {

    //@formatter:off
    http.authorizeRequests()
        .antMatchers("/login").permitAll()
        .antMatchers("/**").hasAnyRole("USER", "ADMIN")
        .antMatchers("/admin/**").hasAnyRole("ADMIN")
        .and()
          .formLogin()
          .loginPage("/login")
          .loginProcessingUrl("/process-login")
          .defaultSuccessUrl("/home")
          .failureUrl("/login?error=true")
          .permitAll()
        .and()
          .logout()
          .logoutSuccessUrl("/login?logout=true")
          .invalidateHttpSession(true)
          .deleteCookies("JSESSIONID")
          .permitAll()
        .and()
          .csrf()
          .disable();
    //@formatter:on
  }

  @Override
  public void configure(WebSecurity web) {
    web.ignoring()
        .antMatchers("/resources/**", "/static/**");
  }
}

Now if we login into the application with credentials user:password or admin:password, we will be able to authenticate successfully.

Note if we are using different column names and we want to customize the SQL queries used by UserDetailService.loadUserByUsername() then we can configure the SQL queries right with JdbcUserDetailsManager.

@Bean
public UserDetailsService jdbcUserDetailsService(DataSource dataSource) {
  String usersByUsernameQuery = "select username, password, enabled from tbl_users where username = ?";
  String authsByUserQuery = "select username, authority from tbl_authorities where username = ?";
      
  JdbcUserDetailsManager users = new JdbcUserDetailsManager(dataSource);

  userDetailsManager.setUsersByUsernameQuery(usersByUsernameQuery);
  userDetailsManager.setAuthoritiesByUsernameQuery(authsByUserQuery);

  return users;
}

4. Testing Form Login with In-Memory Database

For unit testing the login form functionality, we should not be connecting with the application’s physical database. We should be injecting an in-memory database and use a few dummy users to test the login form security.

One good way is to create the test configuration and override the DataSource bean so that it connects to in-memory database in place of the real database. We can also create a few users based on requirements.

In the following example, we are using the application’s primary security configuration and autowiring the PasswordEncoder and UserDetailsService into test configuration. Additionally, we override the DataSource bean and AuthenticationManagerBuilder for adding a few users.

@Configuration
@Order(101)
class TestConfiguration extends WebSecurityConfigurerAdapter {

  @Autowired
  PasswordEncoder passwordEncoder;

  @Autowired
  UserDetailsService userDetailsService;

  @Bean
  public DataSource dataSource() {
    return new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.H2)
        .addScript(JdbcDaoImpl.DEFAULT_USER_SCHEMA_DDL_LOCATION)
        .build();
  }

  @Override
  protected void configure(AuthenticationManagerBuilder auth) throws Exception {

    JdbcUserDetailsManager mgr = (JdbcUserDetailsManager) userDetailsService;

    var user = User.withUsername("user")
        .password(passwordEncoder.encode("password"))
        .roles("USER")
        .build();

    var admin = User.withUsername("admin")
        .password(passwordEncoder.encode("password"))
        .roles("ADMIN", "USER")
        .build();

    mgr.createUser(user);
    mgr.createUser(admin);

    auth.userDetailsService(mgr)
        .passwordEncoder(passwordEncoder);
  }
}

Now we can test the login form functionality using this TestConfiguration class. In the following tests, we are using the formLogin() method to process the authentication with provided user details and testing the result with authenticated() and unauthenticated() methods.

@ExtendWith({SpringTestContextExtension.class})
public class LoginFormWithInMemoryDatabaseTest {

  public final SpringTestContext spring = new SpringTestContext(this);

  @Autowired
  private MockMvc mvc;

  @BeforeEach
  public void setup() {
    spring.register(TestConfiguration.class, SecurityConfig.class).autowire();
  }

  @Test
  void contextLoads() throws Exception {
  }

  @Test
  void verifyUserIsUnauthenticated_WhenNotLoggedIn() throws Exception {
    mvc.perform(MockMvcRequestBuilders.get("/home"))
        .andExpect(unauthenticated());
  }

  @Test
  void verifyUserIsUnAuthenticated_WhenSubmitInCorrectDetails() throws Exception {
    mvc
        .perform(formLogin("/process-login")
            .user("user")
            .password("wrong-password"))
        .andExpect(unauthenticated());
  }

  @Test
  void verifyUserIsUnAuthenticated_WhenSubmitCorrectDetails() throws
      Exception {
    mvc
        .perform(formLogin("/process-login")
            .user("user")
            .password("password"))
        .andExpect(authenticated().withRoles("USER"));

  }

  @Test
  void verifyUserRoleIsAdmin_WhenSubmitAdminCredentials() throws Exception {
    mvc
        .perform(formLogin("/process-login")
            .user("admin")
            .password("password"))
        .andExpect(authenticated().withRoles("ADMIN", "USER"));
  }
}

5. Conclusion

In this tutorial, we learned to configure the Login form with JDBC based authentication in an application secured by spring security. We learned to configure the JDBC authentication with default schema and with custom schema.

We also learned to test the login form authentication using spring security test related classes.

Happy Learning !!

Source Code on Github

Was this post helpful?

Join 7000+ Awesome Developers

Get the latest updates from industry, awesome resources, blog updates and much more.

* We do not spam !!

Leave a Comment

HowToDoInJava

A blog about Java and related technologies, the best practices, algorithms, and interview questions.