This tutorial will offer a different solution for using MySQL database as session management. This is a more practical solution than the one that was duplicated all over Google result pages.
Introduction
A secure web application using form authentication requires a session. For my own project, I have always used the default session from Spring Boot. It was either storing the session in memory or in some temporary files. This is a very limited solution. And it works great only when I host the application on a single server. If I need to make this more scalable, like I want to have three web servers to share the same session data, I need to use something better than the default configuration.
From my experience with ASP.NET, I know there is a way to store session data in the database. With Spring Boot, Spring MVC, I imagine that I can do something similar. Spring Framework is very similar to ASP.NET if and only if you can see through the differences. The hard part is figuring out how the configuration works.
I searched online with the keywords "Spring Session MySQL", the listing shows the same tutorial appears in multiple search result pages. I was going to duplicate the same approach for this tutorial. However, upon close examination of the configuration, I decided not to. You are in for a treat. This tutorial will offer a different solution. I think this is a more practical one than the one that was duplicated all over the Google result pages. You can decide if this is the case.
The Application Architecture
For this tutorial, I intended to create a simple sample application just to make the point. To me, the session data should be kept in a separated database than the application specific data. You can mix the two in the same database, but they serve two different purposes and putting them in the same database just seemed wrong. We don't know how much the data will be in and out of the session, and we don't know how much the data will be in and out for the application. And we don't know what level of security or protection is needed for the two sets of data. There are other considerations about the two sets of data. The point is, it is best to divide them to two different databases.
When I search for how to use MySQL DB for session storage, the tutorial that came up used one data source. Everything is done in configuration file application.properties. I researched deeper, and just couldn't find any better way to have two data sources. What is worse is that multiple tutorials used exactly the same set of code. I researched a little more and was lucky enough to find a solution. I am sharing it here so everyone can benefit.
As stated before, the sample application has a login, once user successfully logged in, the index page will load all the rows of the table in the application database. The session is created and can be queried in the session database after user successfully logged in. I am reusing the same database design from my previous tutorial (UTF-8 encoding for web application). The data to be displayed would be an UUID as the row ID, the title (a short string), and content (a long string). The idea here is that once user can log in, then session is successfully created. The session data will have data on when user logged in and when user will be forced log out. While user logged in, the data can be loaded from a different database, and display on the index page. It is super simple, and the point is I want to demo how the configuration is done so this works.
Database Design
Let me start with the database design. For this sample application, there are two databases, one is for the session data, and one for the application specific data. The database design for the application data is very simple, it is exactly the same as the previous tutorial. The more important one is the session database. It is vital that the database is created before the application executes. I will explain why when we get to the application configuration code.
Let's take a look at the design for the session database. In order to create this database, I have to:
- create the database schema
- create the database tables
The first step is something that we have some freedom. I can define the database schema name, the user who can access it, and the password for the user. Because the data is stored in alpha numeric characters, I don't need to use UTF-8 encoding for it. Here is my SQL script for defining this database:
DROP DATABASE IF EXISTS `sessiondb`;
DROP USER IF EXISTS 'sndbuser'@'localhost';
CREATE DATABASE `sessiondb`;
CREATE USER 'sndbuser'@'localhost' IDENTIFIED BY '123$Test$321';
GRANT ALL PRIVILEGES ON `sessiondb`.* TO 'sndbuser'@'localhost';
FLUSH PRIVILEGES;
This is a simple script, the first two lines are used to drop the database schema and the user associated with this database. Then the next two lines will create the database schema and the user. The fifth line gives all privileges of the database to the user. The last line will flush the changes so that they take effect immediately.
Next, I need to create the tables that actually store the session data. The tables have to be specific names with specific columns. This is something that was provided by Spring Framework. You can get the script from a specific jar file. For this sample application, I was using 2.3.1 version of Spring Boot and associated Spring dependencies. So the script I had to extract is from spring-session-jdbc-2.3.1.RELEASE.jar. If you need a different version, just look for spring-session-jdbc-<your target version>.RELEASE.jar. You can use an archive manager to browse the internal structure of this jar, and you will find a lot of database scripts, they have the name of "schema-<database type>.sql". The one I need is called "schema-mysql.sql". I can just extract the file and executes against my database scheme in MySQL to create the tables. Here is what it looks like:
CREATE TABLE SPRING_SESSION (
PRIMARY_ID CHAR(36) NOT NULL,
SESSION_ID CHAR(36) NOT NULL,
CREATION_TIME BIGINT NOT NULL,
LAST_ACCESS_TIME BIGINT NOT NULL,
MAX_INACTIVE_INTERVAL INT NOT NULL,
EXPIRY_TIME BIGINT NOT NULL,
PRINCIPAL_NAME VARCHAR(100),
CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);
CREATE TABLE SPRING_SESSION_ATTRIBUTES (
SESSION_PRIMARY_ID CHAR(36) NOT NULL,
ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
ATTRIBUTE_BYTES BLOB NOT NULL,
CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) _
REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
In order to create these tables, I have to add the following to the top of the above script:
use `sessiondb`;
Here is my db schema for the application data. As I have mentioned before, I was reusing the same database from my previous tutorial:
DROP DATABASE IF EXISTS `utf8testdb`;
DROP USER IF EXISTS 'utf8tdbuser'@'localhost';
CREATE DATABASE `utf8testdb`;
CREATE USER 'utf8tdbuser'@'localhost' IDENTIFIED BY '123$Test$321';
GRANT ALL PRIVILEGES ON `utf8testdb`.* TO 'utf8tdbuser'@'localhost';
FLUSH PRIVILEGES;
ALTER DATABASE `utf8testdb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
This is my data table for the application:
USE `utf8testdb`;
DROP TABLE IF EXISTS `testcontent`;
CREATE TABLE `testcontent` (
`id` VARCHAR(34) NOT NULL PRIMARY KEY,
`subject` VARCHAR(512) NOT NULL,
`content` MEDIUMTEXT NOT NULL
);
The next section will be the configuration code for the application. And I will explain why I have to create the session database first.
Application Configuration
In this section, I will show you how the application is configured for start-up so that it can use the session and the application databases correctly. Let's start with the application's main entry:
package org.hanbo.boot.app;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class App
{
public static void main(String[] args)
{
SpringApplication.run(App.class, args);
}
}
This is standard main entry for a Spring Boot application. Next, I need to setup the security configuration for form authentication. I took that from one of my early ThymeLeaf tutorials on Spring Security. Here is how this configuration looks:
package org.hanbo.boot.app.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.
builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.method.configuration.
EnableGlobalMethodSecurity;
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.web.access.AccessDeniedHandler;
import org.springframework.security.web.authentication.
SavedRequestAwareAuthenticationSuccessHandler;
import org.hanbo.boot.app.security.UserAuthenticationService;
@Configuration
@EnableWebSecurity
@EnableGlobalMethodSecurity(securedEnabled = true, prePostEnabled = true)
public class WebAppSecurityConfig extends WebSecurityConfigurerAdapter
{
@Autowired
private UserAuthenticationService authenticationProvider;
@Autowired
private AccessDeniedHandler accessDeniedHandler;
@Override
protected void configure(HttpSecurity http) throws Exception
{
http
.authorizeRequests()
.antMatchers("/assets/**", "/public/**").permitAll()
.anyRequest().authenticated()
.and()
.formLogin()
.loginPage("/login")
.permitAll()
.usernameParameter("username")
.passwordParameter("userpass")
.successHandler(new SavedRequestAwareAuthenticationSuccessHandler())
.defaultSuccessUrl("/secure/index", true).failureUrl("/public/authFailed")
.and()
.logout().logoutSuccessUrl("/public/logout")
.permitAll()
.and()
.exceptionHandling().accessDeniedHandler(accessDeniedHandler);
}
@Override
protected void configure(AuthenticationManagerBuilder authMgrBuilder)
throws Exception
{
authMgrBuilder.authenticationProvider(authenticationProvider);
}
}
I can demo the session lifecycle in many ways, but using Spring Security to do it would be most practical. If you can successfully run this application up, then log in with the test user, this means the session configuration is correct. You can even query the session database to see the data stored in it.
Here is how I set up the test user for access of the secured index page:
package org.hanbo.boot.app.security;
import java.util.ArrayList;
import java.util.List;
import org.springframework.security.authentication.AuthenticationProvider;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.AuthenticationException;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.stereotype.Service;
@Service
public class UserAuthenticationService
implements AuthenticationProvider
{
@Override
public Authentication authenticate(Authentication auth) throws AuthenticationException
{
Authentication retVal = null;
List<GrantedAuthority> grantedAuths = new ArrayList<GrantedAuthority>();
if (auth != null)
{
String name = auth.getName();
String password = auth.getCredentials().toString();
System.out.println("name: " + name);
System.out.println("password: " + password);
if (name.equals("user1") && password.equals("user12345"))
{
grantedAuths.add(new SimpleGrantedAuthority("ROLE_USER"));
retVal = new UsernamePasswordAuthenticationToken(
name, "", grantedAuths
);
System.out.println("grant User");
}
}
else
{
System.out.println("invalid login");
retVal = new UsernamePasswordAuthenticationToken(
null, null, grantedAuths
);
System.out.println("bad Login");
}
return retVal;
}
@Override
public boolean supports(Class<?> tokenType)
{
return tokenType.equals(UsernamePasswordAuthenticationToken.class);
}
}
So far, I have not shown the session setup or the database connection configuration. The configuration of setting data sources is the most important part. And they can be put together, this is how:
package org.hanbo.boot.app.config;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.session.jdbc.config.annotation.SpringSessionDataSource;
import org.springframework.session.jdbc.config.annotation.web.http.EnableJdbcHttpSession;
@EnableJdbcHttpSession
@Configuration
public class DataAccessConfiguration
{
@Value("${db.jdbc.driver}")
private String dbJdbcDriver;
@Value("${db.conn.string}")
private String dbConnString;
@Value("${db.access.username}")
private String dbAccessUserName;
@Value("${db.access.password}")
private String dbAccessPassword;
@Value("${db.access.validity.query}")
private String dbAccessValityQuery;
@Value("${db.session.conn.string}")
private String dbSesnConnString;
@Value("${db.session.access.username}")
private String dbSesnAccessUserName;
@Value("${db.session.access.password}")
private String dbSesnAccessPassword;
@Value("${db.session.access.validity.query}")
private String dbSesnAccessValityQuery;
@Bean
public DataSource contentDataSource()
{
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(dbJdbcDriver);
dataSource.setUrl(dbConnString);
dataSource.setUsername(dbAccessUserName);
dataSource.setPassword(dbAccessPassword);
dataSource.setMaxIdle(4);
dataSource.setMaxTotal(20);
dataSource.setInitialSize(4);
dataSource.setMaxWaitMillis(900000);
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(dbAccessValityQuery);
return dataSource;
}
@SpringSessionDataSource
@Bean
public DataSource sessionDataSource()
{
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(dbJdbcDriver);
dataSource.setUrl(dbSesnConnString);
dataSource.setUsername(dbSesnAccessUserName);
dataSource.setPassword(dbSesnAccessPassword);
dataSource.setMaxIdle(4);
dataSource.setMaxTotal(20);
dataSource.setInitialSize(4);
dataSource.setMaxWaitMillis(900000);
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(dbSesnAccessValityQuery);
return dataSource;
}
@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate()
{
NamedParameterJdbcTemplate retVal
= new NamedParameterJdbcTemplate(contentDataSource());
return retVal;
}
@Bean
public DataSourceTransactionManager txnManager()
{
DataSourceTransactionManager txnManager
= new DataSourceTransactionManager(contentDataSource());
return txnManager;
}
}
For this class, I used two annotations, one is @EnableJdbcHttpSession. The other is @Configuration. When this class is loaded, it will be registered in the Spring IOC container as a class that provides configurations, and it will also enable the application to use database and JDBC to manage application session:
...
@EnableJdbcHttpSession
@Configuration
public class DataAccessConfiguration
{
...
}
I need two databases for this application, I have to somehow provide two different data sources (I will show how data sources can be defined a little further down). This is the reason I have to use the annotation @EnableJdbcHttpSession, and not the application properties file to configure database usage for session management. This is also the reason I have to setup the session database manually, not using application properties file to take care of the database creation. I told you I will explain why I have to create the database before hand.
As you can see, there are a lot of private string properties. These are properties that are injected from the application properties file. They represent two different database connections:
@Value("${db.jdbc.driver}")
private String dbJdbcDriver;
@Value("${db.conn.string}")
private String dbConnString;
@Value("${db.access.username}")
private String dbAccessUserName;
@Value("${db.access.password}")
private String dbAccessPassword;
@Value("${db.access.validity.query}")
private String dbAccessValityQuery;
@Value("${db.session.conn.string}")
private String dbSesnConnString;
@Value("${db.session.access.username}")
private String dbSesnAccessUserName;
@Value("${db.session.access.password}")
private String dbSesnAccessPassword;
@Value("${db.session.access.validity.query}")
private String dbSesnAccessValityQuery;
Here is what the application.properties file looks like, and you can see how these values are injected into these private properties:
db.jdbc.driver=com.mysql.cj.jdbc.Driver
db.conn.string=jdbc:mysql://localhost:3306/utf8testdb?useUnicode=true&
useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&
serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false
db.access.username=utf8tdbuser
db.access.password=123$Test$321
db.access.validity.query=SELECT 1
db.session.conn.string=jdbc:mysql://localhost:3306/sessiondb?useUnicode=true&
useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&
allowPublicKeyRetrieval=true&useSSL=false
db.session.access.username=sndbuser
db.session.access.password=123$Test$321
db.session.access.validity.query=SELECT 1
Next, I want to define the data sources. The data source object contains the connection information and can be used to create a SQL connection to the database. This is how I define the one for the application database:
@Bean
public DataSource contentDataSource()
{
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(dbJdbcDriver);
dataSource.setUrl(dbConnString);
dataSource.setUsername(dbAccessUserName);
dataSource.setPassword(dbAccessPassword);
dataSource.setMaxIdle(4);
dataSource.setMaxTotal(20);
dataSource.setInitialSize(4);
dataSource.setMaxWaitMillis(900000);
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(dbAccessValityQuery);
return dataSource;
}
Here comes the most important part of the application, defining another special data source, for the session database:
@SpringSessionDataSource
@Bean
public DataSource sessionDataSource()
{
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(dbJdbcDriver);
dataSource.setUrl(dbSesnConnString);
dataSource.setUsername(dbSesnAccessUserName);
dataSource.setPassword(dbSesnAccessPassword);
dataSource.setMaxIdle(4);
dataSource.setMaxTotal(20);
dataSource.setInitialSize(4);
dataSource.setMaxWaitMillis(900000);
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(dbSesnAccessValityQuery);
return dataSource;
}
You must have got it now. The annotation @SpringSessionDataSource set it only used by the session management to connect to database. It is how I defined two data sources. I found this in the Spring Boot official documentation site. It was a lucky find. Then again, I can find pretty much anything that I need. Once I figure out this, all the technical problems are solved. The last two methods are defining a JDBC template object and a transaction manager object:
@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate()
{
NamedParameterJdbcTemplate retVal
= new NamedParameterJdbcTemplate(contentDataSource());
return retVal;
}
@Bean
public DataSourceTransactionManager txnManager()
{
DataSourceTransactionManager txnManager
= new DataSourceTransactionManager(contentDataSource());
return txnManager;
}
So far, the session configuration is completed. I needed a controller and a request handler to demonstrate the use of session along with Spring Security:
package org.hanbo.boot.app.controllers;
import java.util.ArrayList;
import java.util.List;
import org.hanbo.boot.app.models.PostDataModel;
import org.hanbo.boot.app.services.PostDataService;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
@Controller
public class SecuredPageController
{
private PostDataService _dataSvc;
public SecuredPageController(
PostDataService svc)
{
_dataSvc = svc;
}
@PreAuthorize("hasRole('USER')")
@RequestMapping(value="/secure/index", method = RequestMethod.GET)
public ModelAndView index1()
{
List<PostDataModel> allPosts = _dataSvc.getAllPostData();
if (allPosts == null)
{
allPosts = new ArrayList<PostDataModel>();
}
System.out.println(allPosts.size());
ModelAndView retVal = new ModelAndView();
retVal.setViewName("indexPage");
retVal.addObject("allPosts", allPosts);
return retVal;
}
}
This is a very simple MVC controller. The only method in this class handles requests that will display the secure index page. It will use an injected service object that loads data from the application database and return the list of the data loaded to the index page. Note that the method is marked with Spring Security annotation @PreAuthorize("hasRole('USER')"). It means that any user with the role of "USER" can access this page.
In order to make the above MVC controller work, I have defined a model class and service class. I won't show the model class because it is pretty simple. Here is the implementation class of the service:
package org.hanbo.boot.app.services;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.hanbo.boot.app.models.PostDataModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class PostDataServiceImpl implements PostDataService
{
private final String sql_insertPostData =
"INSERT INTO `testcontent` (id, subject, content) VALUES (:id, :title, :content);";
private final String sql_queryAllPosts =
"SELECT id, subject, content FROM `testcontent` LIMIT 1000;";
@Autowired
private NamedParameterJdbcTemplate sqlDao;
@Override
@Transactional
public String savePostData(PostDataModel dataToSave)
{
if (dataToSave != null)
{
String title = dataToSave.getTitle();
if (title == null || title.isEmpty())
{
throw new RuntimeException("Title is NULL or empty");
}
String content = dataToSave.getContent();
if (content == null || content.isEmpty())
{
throw new RuntimeException("Content is NULL or empty");
}
Map<String, Object> parameters = new HashMap<String, Object>();
String postId = generateId();
parameters.put("id", postId);
parameters.put("title", dataToSave.getTitle());
parameters.put("content", dataToSave.getContent());
int updateCount = sqlDao.update(sql_insertPostData, parameters);
if (updateCount > 0)
{
return postId;
}
}
return "";
}
@Override
public List<PostDataModel> getAllPostData()
{
List<PostDataModel> retVal = new ArrayList<PostDataModel>();
retVal = sqlDao.query(sql_queryAllPosts,
(MapSqlParameterSource)null,
(rs) -> {
List<PostDataModel> foundObjs = new ArrayList<PostDataModel>();
if (rs != null)
{
while (rs.next())
{
PostDataModel postToAdd = new PostDataModel();
postToAdd.setPostId(rs.getString("id"));
postToAdd.setTitle(rs.getString("subject"));
postToAdd.setContent(rs.getString("content"));
foundObjs.add(postToAdd);
}
}
return foundObjs;
});
return retVal;
}
private static String generateId()
{
UUID uuid = UUID.randomUUID();
String retVal = uuid.toString().replaceAll("-", "");
return retVal;
}
}
The reason I show this is that it uses the JDBC template object for database access. It is the application database that this JDBC template object can access. When user logged in and can use this service, the session database would have rows represent this session.
Finally, I want to show the Maven POM file, then to the testing of this sample application.
Maven POM File
The only thing I want to point out in the Maven POM file is the dependencies. I added two new dependencies, one is spring-session-jdbc, and the other is spring-boot-starter-data-jpa. spring-session-jdbc is the dependency that is required to use database for session management. I don't think I needed the other dependency, you can try to remove it, then run the application and see if it works or not. This is the whole section of the dependencies for the POM file:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.session</groupId>
<artifactId>spring-session-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.thymeleaf.extras</groupId>
<artifactId>thymeleaf-extras-springsecurity5</artifactId>
<version>3.0.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.5.0</version>
</dependency>
</dependencies>
How to Test the Sample Application
After getting the source files as a zip file, please rename all the *.sj files to *.js files.
Then, at the base directory of the project (where the POM file) is located, run the following command:
mvn clean install
After successfully build of the project, you can use the following command to run the application:
java -jar target/hanbo-spring-session-mysql-sample-1.0.0.jar
Assuming that the application runs successfully, you can get to the log in page by going to the following URL:
http://localhost:8080
There is only one user available for login, the user name is "user1", and the password is "user12345". If you can use this user to login, and see the index page display, then you know the session is working. If there is any issue with the application configuration, you will most likely see an error page display error code 500 (internal server error).
If you log in successfully, and have executed the insert script for the application database, you will see this:
The secure index page will display a list of posts from the application database, so please use the script "inserts.sql" (in the sub folder DB under the project base folder) to add these data rows to the only table of the application database.
Once you successfully logged in to the application, you can examine the session database and see your session data:
There are two data tables for the Spring Session management. The above one shows the query result from the table "SPRING_SESSION". Here is the query results from the second table "SPRING_SESSION_ATTRIBUTES;":
For this sample application, I provided the logout functionality. When the user logs out, and queries the session data tables, you will see this:
And this:
That's right! Once you logged out, the session tables are cleared out of the associated session. If the database has multiple sessions, then the one cleared out would be only the one that user logged out.
Summary
This is a fairly complicated tutorial. The sample application has quite a few moving parts. What I have to make it work are the following:
- Create the session database and tables.
- Create the application database.
- Insert any needed application data. The session database should be empty initially.
- Add the configurations of Spring Security and Spring JDBC (especially with a second data source for session access).
- Add MVC controller, services class and data access class. On the MVC controller, use the
@PreAuthorize annotations to enforce user log in.
The key point is that I can manually create the session database first, then use two data sources, one is dedicated to session access. All it takes is an annotation @SpringSessionDataSource. The rest are all the same. This is yet another one of those technical problems that seems tough, but there is always a simple solution available.
Just want to mention one last thing, I looked into using Google OpenID authorization for authentication, and decided not to use it. The setup is not very hard, however due to the fact that I have to use Google based APIs and had to expose a lot of configuration using my own Google access, the security risk is too high. And there is no functional need to use Google OpenID. So for this year, I will not offer a tutorial on how to configure OpenID with Spring Security.
I hope you enjoy this tutorial. I had fun creating it. More tutorials are coming. Stay tuned!
History
- 27th April, 2021 - Initial draft
