DEV Community

Akshay Kumar
Akshay Kumar

Posted on • Edited on

Spring Security Part 2 : Getting started with jdbc authentication

#security #springboot #spring #java


Kudos đź‘Ź to reach till here.
In the previous article, we were implementing in-memory authentication in Spring security. But what if we need to connect to a datasource instead of an in-memory.
In this article, we’ll fo throught JDBC authentication.

Most of the concept is same as before, the only difference is setting up a jdbc authentication in the configure method.

Below is the code —

@EnableWebSecurity public class SecurityConfig extends WebSecurityConfigurerAdapter { @Override protected void configure (AuthenticationManagerBuilder auth) { auth.jdbcAuthentication(); } @Override protected void configure (HttpSecurity http) { http.authorizeRequests() .antMatchers("/admin").hasRole("ADMIN"). .antMatchers("/user").hasAnyRole("ADMIN","USER"). .antMatchers("/**").permitAll(). .hasAnyRole(); } }
Enter fullscreen mode Exit fullscreen mode

Now, we need to setup our authentication to tell Spring Security to lookup the user and password in the database. To do so, we need a bean for data source. By default, Spring security provides DataSource class which we can autowire in our project.

@Autowired DataSource datasource; @Override protected void configure (AuthenticationManagerBuilder auth) { auth.jdbcAuthentication() .dataSource(dataSource); }
Enter fullscreen mode Exit fullscreen mode

But how does the spring knows the configuration of the data source.
We must configure it somewhere or else if we’re using H2 which is in-memory database, then spring by default creates a datasource.

For now, lets assume we have H2 in our class path.

@Autowired DataSource datasource; @Override protected void configure (AuthenticationManagerBuilder auth) { auth.jdbcAuthentication() .dataSource(dataSource); .dataSource(dataSource) .withDefaultSchema() .withUser( user.withUserName("user") .password("pass") .roles("USER") .withUserName("admin") .password("pass") .roles("ADMIN") ); }
Enter fullscreen mode Exit fullscreen mode

In the above code, we’re using a default schema and creating the users.
When a client sends a request along with a password, the authentication configuration creates these 2 users and checks do the user in request matches to these. Hence authenticating the request.

Now, what if we already have a schema or we don’t want to use the default schema and you don’t want to put user creation logic in the code?
Create your own schema in resources -> schema.sql

schema.sql :

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);
Enter fullscreen mode Exit fullscreen mode

data.sql :

INSERT INTO users (username, password, enabled) VALUES ('user', 'pass', true), ('admin', 'pass', true); INSERT INTO authorities (username, authority) VALUES ('user', 'ROLE_USER'), ('admin', 'ROLE_ADMIN'); Important notes:
Enter fullscreen mode Exit fullscreen mode

The password should ideally be encoded (BCrypt is recommended) rather than stored in plain text like this
Spring Security expects the authorities/roles to be prefixed with “ROLE_” by default
The enabled column must be set to true for the users to be able to authenticate
Below is the complete code for configuration class:

@EnableWebSecurity public class SecurityConfig extends WebSecurityConfigurerAdapter { @Autowired DataSource dataSource; @Override protected void configure(AuthenticationManagerBuilder auth) throws Exception { auth.jdbcAuthentication() .dataSource(dataSource) .passwordEncoder(passwordEncoder()); } @Override protected void configure (HttpSecurity http) { http.authorizeRequests() .antMatchers("/admin").hasRole("ADMIN"). .antMatchers("/user").hasAnyRole("ADMIN","USER"). .antMatchers("/**").permitAll(). .hasAnyRole(); } @Bean public PasswordEncoder passwordEncoder() { return new BCryptPasswordEncoder(); } }
Enter fullscreen mode Exit fullscreen mode

When you autowire DataSource and use it in auth.jdbcAuthentication(), Spring Security automatically uses this DataSource to query the database for user authentication.

By default, it looks for tables: users (with username, password, enabled columns) , authorities (with username, authority columns)

If your schema is different, you can customize the queries.

auth.jdbcAuthentication() .dataSource(dataSource) .usersByUsernameQuery("Select username, password, enables" + "from custom_users where username = ?") .authoritiesByUsernameQuery("Select username, authority " + "from authorities where username = ?");
Enter fullscreen mode Exit fullscreen mode

If you’re using MySQL, you need to define the DataSource in application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/spring_security_db spring.datasource.username=root spring.datasource.password=password spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
Enter fullscreen mode Exit fullscreen mode

Great đź‘Ź

You’ve now got a good idea about spring security and jdbc authentication.

Next, let’s dive deep into JWT and implementing it using Spring security.
Thanks for reading.

Top comments (0)