This is a continuation of our earlier article Introduction to Spring Security 5. In the previous article we configured a simple form login using in-memory authentication using basic mechanisms of Spring Security.
So it’s clear that every application needs to allow registration of new users and disabling of older ones. It’s not practical to release a new version of your application every time you want to do such a change.
So how do we go about this? One of the best solutions is to have a database and maintain user data in a table. Let’s see how that’s done in the next section.
We have to maintain two different things: the users and the authorities - or roles. There is a subtle difference between these two, but in this post they are almost synonymous.
The first table we need is the users table. By definition, you’ll need to store the username and password. The docs also say you’ll have to store a flag if the user is enabled or not.
Here is the SQL (running on MySQL) for our table:
create table users
(
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(100) not null,
`password` varchar(100) not null,
`enabled` boolean not null,
PRIMARY KEY (`id`),
UNIQUE KEY `username_unique` (`username`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
You can modify it as per your liking, separate it further or even add more fields. Also, I have made the username unique. I probably could have removed the id field, but I prefer to have such a field usually.
The following is the next table, and we’ll use it to store the authorities - or roles - assigned to each user.
create table user_authorities
(
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(11) unsigned NOT NULL,
`authority` varchar(50) not null,
PRIMARY KEY (`id`),
UNIQUE KEY `username_authorities_unique` (`user_id`, `authority`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
The above table only contains a reference to the user table and an authority. Again, my preference is to keep an id. I also made user_id and authority unique to avoid duplicate records.
Now, these two tables need to be connected to each other. This is done by a foreign key on user_authorities, so it references the user table.
ALTER TABLE `user_authorities`
ADD CONSTRAINT `fk_authorities`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
That’s it. Our user tables are in place. Please create it in the schema of your choice and let’s get back to our application.
It’s sometimes unreal how easy Spring Boot makes it, for us to perform some tasks. Just by adding these lines of configuration to application.properties, Spring Boot already knows that it should create a DataSource object.
spring.datasource.url=jdbc:mysql://localhost/db
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=dbuser
spring.datasource.password=dbpassword
In case you are not using Spring Boot, you can add this Java configuration into your Configuration class:
@Bean
public DataSource createDataSource() {
SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}
PS: You’ll usually want to use a database connection pool, like Hikari.
Once you have that configuration in place, a DataSource type of object is available to be autowired.
Again, it is usually a good idea to extend from WebSecurityConfigurer.
Just like in the preceding blog post, I added some authorization rules to my configuration.
@EnableWebSecurity
public class JdbcSecurityConfiguration extends WebSecurityConfigurerAdapter {
@Override
protected void configure(HttpSecurity http) throws Exception {
http.formLogin();
http.authorizeRequests()
.antMatchers("/devs/*").hasAnyRole("boss", "dev")
.antMatchers("/boss/*").hasRole("boss")
.antMatchers("/").permitAll();
}
}
Now let’s inform Spring that we need to change the way we authenticate users. Previously, this was done “in-memory”.
First, we need access to our datasource. So it’s wise to @Autowire the datasource bean. In addition, let’s use BCryptEncoding as well. Since we want our users to register themselves, it may be a good idea to make the password encoder a Spring bean too. That way we can share the same algorithm in our application for the registration code as well.
@EnableWebSecurity
public class JdbcSecurityConfiguration extends WebSecurityConfigurerAdapter {
@Autowired
private DataSource dataSource;
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}
@Override
protected void configure(HttpSecurity http) throws Exception { ... }
}
Now it’s time to reimplement the authentication process. Here comes the obvious part. First we get hold of the builder, Then, instead of “inMemoryAuthentication” we use “jdbcAuthentication”. Where should we connect to? That’s next, the dataSource() method. And finally, we call the passwordEncoder, so the right password algorithm is used.
@EnableWebSecurity
public class JdbcSecurityConfiguration extends WebSecurityConfigurerAdapter {
@Autowired
private DataSource dataSource;
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth
.jdbcAuthentication()
.dataSource(dataSource)
.passwordEncoder(passwordEncoder());
}
@Bean
public PasswordEncoder passwordEncoder() {
return new BCryptPasswordEncoder();
}
@Override
protected void configure(HttpSecurity http) throws Exception { ... }
}
But that’s not all, unfortunately. Because everybody creates tables a little bit differently, we should tell Spring Security how to get hold of the user and role information. Two more methods need to be called, and they need to contain SQL statements.
@EnableWebSecurity
public class JdbcSecurityConfiguration extends WebSecurityConfigurerAdapter {
...
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth
.jdbcAuthentication()
.dataSource(dataSource)
.passwordEncoder(passwordEncoder())
.usersByUsernameQuery(
"SELECT username, password, enabled from users where username = ?")
.authoritiesByUsernameQuery(
"SELECT u.username, a.authority " +
"FROM user_authorities a, users u " +
"WHERE u.username = ? " +
"AND u.id = a.user_id"
);
}
...
}
The first statement “usersByUsernameQuery” only selects the user from our user database. The ? is the placeholder where the username will be added later by Spring Security.
The second statement “authoritiesByUsernameQuery” selects the username and the authority for it.
It’s recommended to return exactly those fields from the database. No matter from what tables you get them.
That’s it - Spring Security is connected to a database.
Registering a user should now be a half-way trivial job. Basically you only need to read a web form and insert a new user into the user database - done.
An example of how this could look is below. The only trick is to use the same password encoder to store a password as you have to read it. We ensure this, by making our PasswordEncoder a bean.
The below example even uses JPA entities since they are nice to read. But nothing prevents a developer to use plain JDBC here as well.
@Controller
public class RegisterController {
@Autowired
PasswordEncoder passwordEncoder;
@Autowired
private UserRepository userRepository;
@PostMapping("/register")
public String doRegister(@ModelAttribute UserDto userDto) {
String encodedPassword = passwordEncoder.encode(userDto.getPassword1());
User user = new User();
user.setEnabled(Boolean.TRUE);
user.setPassword(encodedPassword);
user.setUsername(userDto.getUsername());
UserAuthority boardAuthority = new UserAuthority();
boardAuthority.setAuthority("BOARD");
boardAuthority.setUser(user);
user.getAuthorities().add(boardAuthority);
userRepository.save(user);
return "register-success";
}
}
We connected our login form to the database. There are more advanced scenarios when it comes to authentication. How about using Basic Auth or JWT? Read on to learn more about Spring Security with Basic Auth and JWT.