Till now, we are using the in-memory data for studying purpose.
DemoSecurityConfig.java:

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
//        add our users for in memory authentication
        UserBuilder users = User.withDefaultPasswordEncoder();

        auth.inMemoryAuthentication()
                .withUser(users.username("john").password("test123").roles("EMPLOYEE"))
                .withUser(users.username("mary").password("test123").roles("EMPLOYEE","MANAGER"))
                .withUser(users.username("susan").password("test123").roles("EMPLOYEE","ADMIN"));
    }

And now, we will try to add database access.

Database Support in Spring Security

Spring Security can read user account info from database
By default, you have to follow Spring Security’s predefined table schemas
Development Process

Develop SQL Script to set up databse tables
Spring Security has a default database schema, so you need to provide two tables, one call users and one call authorities. And you have to use these exact table names, and also these tables need to have these columns: username, password and enabled for users, and authorities is the username and authority(authority is the same thing as roles).

CREATE TABLE `users` (
    `username` varchar(50) NOT NULL,
    `password` varchar(50) NOT NULL,
    `enabled` tinyint(1) NOT NULL,

    PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In Spring Security 5, passwords are stored using a specific format:{id}encodedPassword

INSERT INTO `users`
VALUES
('john','{noop}test123',1),
('mary','{noop}test123',1),
('susan','{noop}test123',1);

Note: the password is “test123” while the {noop} let Spring Security know the passwords are stored as plain text{noop}

    CREATE TABLE `authorities` (
    `username` varchar(50) NOT NULL,
    `authority` varchar(50) NOT NULL,

    UNIQUE KEY `authorities_idx_1` (`username`,`authority`),

    CONSTRAINT `authorities_ibfk_1`
    FOREIGN KEY (`username`)
    REFERENCES `users`(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `authorities`
VALUES
('john','ROLE_EMPLOYEE'),
('mary','ROLE_EMPLOYEE'),
('mary','ROLE_MANAGER'),
('susan','ROLE_EMPLOYEE'),
('susan','ROLE_ADMIN');

Note: Internally Spring Security uses “ROLE_” prefix

Add database support to Maven POM file
pom.xml:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.45</version>
 </dependency>

 <dependency>
   <groupId>com.mchange</groupId>
   <artifactId>c3p0</artifactId>
   <version>0.9.5.2</version>
 </dependency>

Create JDBC properties file
src/main/resources/persistence-mysql.properties:

#
# JDBC connection properties
#
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_security_demo_plaintext?useSSL=false
jdbc.user=root
jdbc.password=pwd

#
# Connection poll properties
#
connection.pool.initialPoolSize=5
connection.pool.minPoolSize=5
connection.pool.maxPoolSize=20
connection.pool.maxIdleTime=3000

Define DataSource in Spring Configuration
DemoAppConfig.java:

@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.xliu.springsecurity.demo")
// src/main/resources files are automatically copied to classpath during Mavan build
@PropertySource("classpath:persistence-mysql.properties")
public class DemoAppConfig {
    @Autowired
private Environment env;

private Logger logger = Logger.getLogger(getClass().getName());

@Bean
public DataSource securityDataSource() {
//        create connection poll
    ComboPooledDataSource securityDataSource = new ComboPooledDataSource();
//        set the jdbc driver
    try {
        securityDataSource.setDriverClass(env.getProperty("jdbc.driver"));
    } catch (PropertyVetoException e) {
        throw new RuntimeException(e);
    }
    logger.info(">>>> jdbc.url=" + env.getProperty("jdbc.url"));
    logger.info(">>>> jdbc.user=" + env.getProperty("jdbc.user"));
//        set database connection props
    securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
    securityDataSource.setUser(env.getProperty("jdbc.user"));
    securityDataSource.setPassword(env.getProperty("jdbc.password"));
//        set connection pool props
    securityDataSource.setInitialPoolSize(Integer.parseInt(env.getProperty("connection.pool.initialPoolSize")));
    securityDataSource.setMinPoolSize(Integer.parseInt(env.getProperty("connection.pool.minPoolSize")));
    securityDataSource.setMaxPoolSize(Integer.parseInt(env.getProperty("connection.pool.maxPoolSize")));
    securityDataSource.setMaxIdleTime(Integer.parseInt(env.getProperty("connection.pool.maxIdleTime")));

    return securityDataSource;
}

Environment is a Spring helper class to hold the data that was read from the properties file

Update Spring Security Configuration to use JDBC
DemoSecurityConfig.java:

@Configuration
@EnableWebSecurity
public class DemoSecurityConfig extends WebSecurityConfigurerAdapter {
//    Inject our data source that we just configured
    @Autowired
    private DataSource securityDataSource;
    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
//        Tell Spring Security to use JDBC authentication with our data source
        auth.jdbcAuthentication().dataSource(securityDataSource);

Here we no longer use hard-coding users.

Database Support in Spring Security

Development Process