How does Spring Security store user data into the database?

Up to now, we haven't connected to the database. In real projects, in most cases, we design our own permission database. However, Spring Security also provides us with a permission database designed by itself. Let's see what's going on first! Let's learn this simple one first, and then we'll look at the complex one.

1.UserDetailService

Spring Security supports a variety of different data sources. These different data sources will eventually be encapsulated into instances of UserDetailsService. In a real project, we create a class to implement the UserDetailsService interface. In addition to our own encapsulation, we can also use the UserDetailsService instance provided by the system by default, For example, in memory user details manager introduced in the previous article.

Let's take a look at the implementation classes of UserDetailsService:

It can be seen that among several implementation classes that can be used directly, in addition to InMemoryUserDetailsManager, there is also a JdbcUserDetailsManager. Using JdbcUserDetailsManager allows us to connect the database with Spring Security through JDBC.

2.JdbcUserDetailsManager

JdbcUserDetailsManager provides a database model, which is saved in the following location:

org/springframework/security/core/userdetails/jdbc/users.ddl

The script stored here is as follows:

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);

As you can see, there is a data type varchar in the script_ Ignorecase is actually created for HSQLDB database, and MySQL we use does not support this data type. Therefore, we need to manually adjust the data type to varchar_ Just change ignorecase to varchar.

After modification, create the database and execute the completed script.

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);

After executing the SQL script, we can see that two tables have been created: users and authorities.

• The users table stores the basic information of users, including user name, user password and whether the account is available.
• The user's role is saved in the authorities.
• authorities and users are associated through username.

After the configuration is completed, we will replace the user data provided through InMemoryUserDetailsManager in the previous article with JdbcUserDetailsManager, as follows:

    @Resource
    DataSource dataSource;
    @Override
    @Bean
    protected UserDetailsService userDetailsService() {
        JdbcUserDetailsManager manager = new JdbcUserDetailsManager(dataSource);
        if (!manager.userExists("summer")) {
            manager.createUser(User.withUsername("summer").password("123").roles("admin").build());
        }
        if (!manager.userExists("wavefar")) {
            manager.createUser(User.withUsername("wavefar").password("123").roles("user").build());
        }
        return manager;
    }

The meaning of this configuration is as follows:

1. First, build an instance of JdbcUserDetailsManager.
2. Add a DataSource object to the JdbcUserDetailsManager instance.
3. Call the userExists method to determine whether the user exists. If it does not exist, create a new user (because this code will be executed every time the project is started, so add a judgment to avoid creating users repeatedly).
4. The user creation method is basically the same as that in inmemoryuserdetails manager.

The createUser or userExists methods here actually call the written SQL to judge. We can see from its source code (part):

public class JdbcUserDetailsManager extends JdbcDaoImpl implements UserDetailsManager,GroupManager {
 public static final String DEF_USER_EXISTS_SQL = "select username from users where username = ?";

 private String userExistsSql = DEF_USER_EXISTS_SQL;

 public boolean userExists(String username) {
  List<String> users = getJdbcTemplate().queryForList(userExistsSql,
    new String[] { username }, String.class);

  if (users.size() > 1) {
   throw new IncorrectResultSizeDataAccessException(
     "More than one user found with name '" + username + "'", 1);
  }

  return users.size() == 1;
 }
}

From this source code, we can see that the execution logic of userExists method is actually to call JdbcTemplate to execute the predefined SQL script, and then judge whether the user exists. Other judgment methods are similar, so I won't repeat them.

3. Database support

Through the previous code, you can see that database support is required here, so we add the following two dependencies to the project:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Then in application Configure the following database connections in properties:

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/security?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai

Once the configuration is complete, you can start the project. After the project is started successfully, we can see that two users are automatically added to the database, and both users are configured with roles. As shown below:

4. Test

Then we can test it. http://127.0.0.1:8080/login.html Enter the account and password to get the correct response result. Note that if we put the database users If the enabled field is changed to 0, the account cannot be accessed. The bottom layer of Spring Security still helps us do a lot of functions.

{
    "status": 200,
    "msg": "Login succeeded!",
    "obj": {
        "password": null,
        "username": "summer",
        "authorities": [
            {
                "authority": "ROLE_admin"
            }
        ],
        "accountNonExpired": true,
        "accountNonLocked": true,
        "credentialsNonExpired": true,
        "enabled": true
    }
}