JDBC authentication with H2 Database in Spring Security

In this article, we are going to see how to do authentication of spring security with JDBC and H2 Database.

JDBC is an application programming interface (API) of java that handles database connectivity and issuing queries and commands to retrieve the data from the database.

H2 database is an embedded database available in Spring Boot. H2 database is an in-memory database that stores the data temporarily and that is available when the application starts and will be lost once the application stopped or restarted.

In this article, we see how to do authentication with JDBC and H2 Database in Spring Security. Before implementing this we have to do basic authentication and authorization of spring security.

I explained the basic authentication and authorization already in another article. Please click this link Spring Security Basic Authentication and Authorization to know more about that.

The article linked above explained how authentication and authorization work with the hardcoded user details in in-memory. But here let we see with the H2 database.

H2 database is more likely with the original database like Oracle, MySql, and so on. But the only difference is it is an in-memory database so the data are not stored permanently.

Spring Boot Application with JDBC and H2 Database Configuration

Now let's create the spring boot application and configure the JDBC and H2 database which is available in the classpath of the application.

1. Please go to the website https://start.spring.io/
2. On the website choose the project as Maven Project and language like java and fill in the project metadata information.
3. Add the below dependencies in the Add Dependencies section.
4. Spring Web, Spring Security, Spring Boot DevTools, Spring Boot Actuator, JDBC API, and H2 Database
5. Finally, click generate button to generate the project.

Now open the application with any of the IDE and do the changes. Here I used eclipse as an IDE so I open the generated project in an eclipse and do the configuration.

First, we have to create the controller with some endpoints (APIs) that are accessible by the roles of ADMIN and USER.

HomeController

package com.tipstocode.springsecurityjdbc;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;


@RestController
public class HomeController {
	
	@GetMapping("/")
	public String login(){
		return "Welcome to TipsToCode Site!";
	}
	
	@GetMapping("/user")
	public String user(){
		return "Welcome user to TipsToCode Site!";
	}
	
	@GetMapping("/admin")
	public String admin(){
		return "Welcome admin to TipsToCode Site!";
	}

}

In the above controller the URL "/" is accessible by all the users irrespective of roles and the URL "/user" is accessible by ADMIN and USER and the URL "/admin" is accessible only by the ADMIN role of users.

Now we have to configure the authentication part which does the authentication with the user details from the H2 Database and configure the authorization with some roles and their corresponding access of endpoints.

The below snippet shows the configuration of JDBC authentication with the H2 database and with some basic authorization.

package com.tipstocode.springsecurityjdbc;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.crypto.password.NoOpPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter{
	
	@Autowired
	DataSource dataSource;
	
	@Override
	protected void configure(AuthenticationManagerBuilder auth) throws Exception{
	
		//default schema to create users
		auth.jdbcAuthentication()
		.dataSource(dataSource)
		.withDefaultSchema()
		.withUser(
				User.withUsername("user")
				.password("password")
				.roles("USER")
		)
		.withUser(
				User.withUsername("admin")
				.password("password")
				.roles("ADMIN")
		);		
	}
	
	@Bean
	public PasswordEncoder getPasswordEncoder(){
		return NoOpPasswordEncoder.getInstance();
	}
	
	@Override
	protected void configure(HttpSecurity http) throws Exception{
		
		http.authorizeRequests()
			.antMatchers("/admin").hasRole("ADMIN")
			.antMatchers("/user").hasAnyRole("USER", "ADMIN")
			.antMatchers("/").permitAll()
			.and().formLogin();
	}
}

Now let me explain the configuration done in the above snippet SecurityConfig.java. First, we have to auto-wire the Datasource bean which points to any database like H2 or Oracle, or MySQL.

@Autowired
DataSource dataSource;

In this article, this DataSource points to the H2 database automatically because we added H2 database as a dependency in the application. Spring boot identifies this H2 at runtime which is available in the classpath and configures the DataSource automatically to authenticate the users.

Actually, by default, this DataSource points to any of the embedded databases like H2, HSQL, or Derby if no other databases are configured in the classpath or application.properties file.

Now we have to configure the method configure(AuthenticationManagerBuilder auth) which takes authentication as an input to do the authentication.

Now, let's configure the JDBC authentication and pass this DataSource to authenticate the users. The below code does this.

auth.jdbcAuthentication().dataSource(dataSource);

Now the application knows this Datasource belongs to H2 Database. But this Datasource should have the user details right!. Then only the application reads the user details to authenticate the user.

Normally the user details come from the database tables. But here we configured H2 in-memory database. So the user data has to be hardcoded in the application itself or the user data comes from the files.

Spring security looks into the two tables for doing authentication. user table and authorities table. Now we have to hard code these two table information in the application itself by using Spring Security default schema.

Configure Users with Authorities by using Default Schema of Spring Security

This spring security default schema "withDefaultSchema()" populates the users and authorities tables with the hardcoded user details and binds those details to the Datasource to authenticate the users. The below snippet does that.

auth.jdbcAuthentication()
		.dataSource(dataSource)
		.withDefaultSchema()
		.withUser(
				User.withUsername("user")
				.password("password")
				.roles("USER")
		)
		.withUser(
				User.withUsername("admin")
				.password("password")
				.roles("ADMIN")
		);	

apart from these I configured authorization and used "NoOpPasswordEncoder" to authenticate the users with clear text passwords without hashing. The below snippet does that.

@Bean
	public PasswordEncoder getPasswordEncoder(){
		return NoOpPasswordEncoder.getInstance();
	}
	
	@Override
	protected void configure(HttpSecurity http) throws Exception{
		
		http.authorizeRequests()
			.antMatchers("/admin").hasRole("ADMIN")
			.antMatchers("/user").hasAnyRole("USER", "ADMIN")
			.antMatchers("/").permitAll()
			.and().formLogin();
	}

Now, let's start the application and test it. Once the application started successfully then open the browser and put this URL "http://localhost:8080/". You will see the message "Welcome to TipsToCode Site!". This URL is accessible by all users.

Now, let's test this URL "http://localhost:8080/user" in the browser then you will see the login screen to authenticate the user. you have to put any one of the credentials which are mentioned in the security configuration to log in.

Here we configured username as "user" and password as "password". Put the same in the browser and do login. Once the user is authenticated successfully then you will see the message "Welcome user to TipsToCode Site!".

In the same way, you can test this ADMIN URL "http://localhost:8080/admin" by putting admin credentials.

Now hope you understood JDBC with H2 Database authentication in Spring Security.

Populate Database and User details from SQL Files using the default schema

Now we have to create some .sql files to populate the database and populate the user details by using default schema like what exactly does with the real databases.

Here we have to create two files named "schema.sql" and "data.sql" in the resources folder. This "schema.sql" file is having the schema of users and authorities tables. This "data.sql" file is having the queries to insert the data into the users and authorities tables.

schema.sql File

create table users(
	username varchar_ignorecase(50) not null primary key,
	password varchar_ignorecase(50) 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);

The above is the default schema of users and authorities tables in Spring Security. Here we are using the H2 database, so this default schema exactly matched with the H2 database.

The above schema for the table creation varies for the different databases. Like if you are using MySql or Oracle then the above syntax varies. If you want to know the exact schema for all the databases then please click this link Security Database Schema.

Spring boot application uses this schema to set up the database and tables when starts the application. Now we have to create data.sql file which is having the data of users and authorities tables.

data.sql File

INSERT INTO users (username, password, enabled)
values('user','password', true);

INSERT INTO users (username, password, enabled)
values('admin','password', true);

INSERT INTO authorities(username, authority)
values('user','ROLE_USER');

INSERT INTO authorities(username, authority)
values('admin','ROLE_ADMIN');

In the above file, we are inserting two users in the name of 'user' and 'admin' and we are inserting two corresponding authorities 'ROLE_USER' and 'ROLE_ADMIN' in the authorities table.

When startup the application spring boot populates these users and authorities tables data and makes that available for authentication.

Now how the application knows that to read the user information from the files. Yes, Spring Boot knows that if the files exist in the resources folder then it automatically reads the user information from that files.

But we have to modify slightly in the security configuration of the spring boot. The security configuration file with the corresponding changes below.

package com.tipstocode.springsecurityjdbc;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.crypto.password.NoOpPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter{
	
	@Autowired
	DataSource dataSource;
	
	@Override
	protected void configure(AuthenticationManagerBuilder auth) throws Exception{
	
		//default schema to create users
		auth.jdbcAuthentication()
		.dataSource(dataSource);
				
	}	
	@Bean
	public PasswordEncoder getPasswordEncoder(){
		return NoOpPasswordEncoder.getInstance();
	}	
	@Override
	protected void configure(HttpSecurity http) throws Exception{
		
		http.authorizeRequests()
			.antMatchers("/admin").hasRole("ADMIN")
			.antMatchers("/user").hasAnyRole("USER", "ADMIN")
			.antMatchers("/").permitAll()
			.and().formLogin();
	}
}

That's it!. Everything finished. Now the spring security reads the user information from the files to authenticate the users.

Please note that the schema we wrote in the shcema.sql file exactly matches with the default schema of Spring Boot Security schema. We have to keep the tables name as it is in the default schema.

Now the question will come like will I change the table name? or will I put my custom name for the table in the schema file?. like the user table into the user_info table. Yes, you can put that. Spring Security does that too for you.

If you are using the custom names in tables then you have to tell spring security to read the tables with that custom name. For that spring security provides the methods called "usersByUsernameQuery" and "authoritiesByUsernameQuery" to write our own schema to fetch the user information from the files.

The security configuration of spring security changes like below. Please use this to tell spring security to read the data from the files.

package com.tipstocode.springsecurityjdbc;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.crypto.password.NoOpPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter{
	
	@Autowired
	DataSource dataSource;
	
	@Override
	protected void configure(AuthenticationManagerBuilder auth) throws Exception{	
		//default schema to create users
		auth.jdbcAuthentication()
		.dataSource(dataSource)
		.usersByUsernameQuery("select username,password,enabled from users where username = ?")
		.authoritiesByUsernameQuery("select username,authority from authorities where username = ?");				
	}	
	@Bean
	public PasswordEncoder getPasswordEncoder(){
		return NoOpPasswordEncoder.getInstance();
	}	
	@Override
	protected void configure(HttpSecurity http) throws Exception{
		
		http.authorizeRequests()
			.antMatchers("/admin").hasRole("ADMIN")
			.antMatchers("/user").hasAnyRole("USER", "ADMIN")
			.antMatchers("/").permitAll()
			.and().formLogin();
	}
}

You can put any name for the tables in the schema.sql file and the same you have to put that in the queries "usersByUsernameQuery" and "authoritiesByUsernameQuery".

For example, if you are using the name 'user_info' for the table 'user' then you have to use the same name with the query in the security configuration file. The query will be like below.

usersByUsernameQuery("select username,password,enabled from user_info where username = ?")

In the same way, you have to use your name on the authorities table too.

Hope in this article you understood how the spring security authentication happens with H2 Database using JDBC. In the next article, we will see how the authentication happens with the MySql database by using JPA.

Thanks. Keep Reading!.