Kotlin – Spring Security JDBC Authentication – SpringBoot + MySQL + Bootstrap

In the tutorial, JavaSampleApproach will show you how to create a Kotlin Spring Security JDBC Authentication with SpringBoot + MySQL + Bootstrap.

I. Technologies

– Kotlin 1.2.20
– Apache Maven 3.5.2
– Spring Tool Suite – Version 3.9.0.RELEASE
– Spring Boot – 1.5.10.RELEASE
– Bootstrap
– MySQL

II. Goal

We create a Kotlin MVC Web Application as below:

Kotlin Spring Security JDBC Authentication - project structure

With 5 urls:

– ‘/’: access with everyone.

Kotlin Spring Security JDBC Authentication - home pagepng

– ‘/user’: must authenticate and be accessed with user ROLE {USER, ADMIN}

Kotlin Spring Security JDBC Authentication - users

– ‘/admin’: accessed by user with role Admin

Kotlin Spring Security JDBC Authentication - admin

– ‘/login’: login page

Kotlin Spring Security JDBC Authentication - login

– ‘/403’: HTTP Error 403 Forbidden

Kotlin Spring Security JDBC Authentication - access-denied

We create 2 MySQL tables for 2 users (username/password):
– jack/jack has 2 roles {USER, ADMIN}
– peter/peter has 1 role USER

Kotlin Spring Security JDBC Authentication - tables

-> jack/jack can access both pages {user.html, admin.html}. While peter/peter just accesses 1 page user.html.

III. Implementation

– Create Kotlin Spring Boot project
– Create Controller
– Create View Pages
– Configure Database
– Configure WebSecurity

1. Create Kotlin Spring Boot project

Use SpringToolSuite to create a Kotlin SpringBoot project with below dependencies:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

2. Create Controller

package com.javasampleapproach.kotlin.springsecurity.controller

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
class WebController {
   
    @RequestMapping(value="/")
    fun home(): String{
    	return "home"
    }
   
    @RequestMapping(value="/user")
    fun welcome(): String{
        return "user"
    }
  
    @RequestMapping(value="/admin")
    fun admin(): String{
        return "admin"
    }
   
    @RequestMapping(value="/login")
    fun login(): String{
        return "login"
    }
   
    @RequestMapping(value="/403")
    fun Error403(): String{
        return "403"
    }
}

3. Create View Pages

home.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
	xmlns:th="http://www.thymeleaf.org"
	xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
<head>
<title>Security with Spring Boot</title>
</head>
<body>
	<h1>Hello, This is Home page!</h1>
	<a style="color: blue" th:href="@{/user}">User Page</a>
	<br />
	<a style="color: blue" th:href="@{/admin}">Admin Page</a>
</body>
</html>

user.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
	xmlns:th="http://www.thymeleaf.org"
	xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
<head>
<title>Welcome Security with Spring Boot!</title>
</head>
<body>
	<h1>Hello, the page is for Users!</h1>
	<a style="color: blue" th:href="@{/}">Home</a>
	<form th:action="@{/logout}" method="post">
		<input type="submit" value="Sign Out" />
	</form>
</body>
</html>

admin.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
	xmlns:th="http://www.thymeleaf.org"
	xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
<head>
<title>Welcome Security with Spring Boot!</title>
</head>
<body>
	<h1>Hello, the page is for Admin!</h1>
	<a style="color: blue" th:href="@{/}">Home</a>
	<form th:action="@{/logout}" method="post">
		<input type="submit" value="Sign Out" />
	</form>
</body>

login.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
	xmlns:th="http://www.thymeleaf.org"
	xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
<head>
	<title>Welcome Security with Spring Boot!</title>
	<meta charset="utf-8"/>
	<meta name="viewport" content="width=device-width, initial-scale=1"/>
	<link rel="stylesheet"
		href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"/>
	<script
		src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
	<script
		src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body class="container" style="margin:50px">
	<div class="row col-sm-6" 
				style="border: 1px ridge #003312; padding:20px; float: none; margin: 0 auto;">
		<h5 class="text-center" style="font-size: 25px">Sign In</h5>
		<div th:if="${param.error}">
			<p style="color: red">UserName or PassWord is wrong. Please
				check again!</p>
	
		</div>
		<div th:if="${param.logout}">
			<h1 style="color: blue">Logged out.</h1>
		</div>
		<form th:action="@{/login}" method="post">
			<div class="form-group">
				<label for="username">User Name: </label>
				<input type="text" class="form-control" id="username" placeholder="Enter UserName" name="username"/>
			</div>
			<div class="form-group">
				<label for="password">Password: </label>
				<input type="password" class="form-control" id="password" placeholder="Enter Password" name="password"/>
			</div>
			<button type="submit" class="btn btn-primary btn-block">Submit</button>
		</form>
	</div>
</body>
</html>

403.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
	xmlns:th="http://www.thymeleaf.org"
	xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
<head>
<title>Security with Spring Boot</title>
</head>
<body>
	<h1>Access is Denied!</h1>
	<a style="color: blue" th:href="@{/}">Home</a>
	<form th:action="@{/logout}" method="post">
		<input type="submit" value="Sign Out" />
	</form>
</body>
</html>

4. Configure Database Tables

Open application.properties, configure database properties:

spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=12345

Create user table with 3 columns:

CREATE  TABLE testdb.users (
  username VARCHAR(20) NOT NULL ,
  password VARCHAR(20) NOT NULL ,
  enabled TINYINT NOT NULL DEFAULT 1 ,
  PRIMARY KEY (username));

Create user_roles table:

CREATE TABLE testdb.user_roles (
  user_role_id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(20) NOT NULL,
  role varchar(20) NOT NULL,
  PRIMARY KEY (user_role_id),
  UNIQUE KEY uni_username_role (role,username),
  KEY fk_username_idx (username),
  CONSTRAINT fk_username FOREIGN KEY (username) REFERENCES testdb.users (username));

Insert data to 2 tables:

INSERT INTO testdb.users(username,password,enabled) VALUES ('jack','jack', true);
INSERT INTO testdb.users(username,password,enabled) VALUES ('peter','peter', true);
 
INSERT INTO testdb.user_roles (username, role) VALUES ('jack', 'ROLE_USER');
INSERT INTO testdb.user_roles (username, role) VALUES ('jack', 'ROLE_ADMIN');
INSERT INTO testdb.user_roles (username, role) VALUES ('peter', 'ROLE_USER');

5. Configure WebSecurity

Create a SecurityConfig class that extends WebSecurityConfigurerAdapter. Then override method configAuthentication(AuthenticationManagerBuilder auth) to setup SQL queries for users & roles. And override configure(HttpSecurity http) to customize http requests.

package com.javasampleapproach.kotlin.springsecurity.config

import javax.sql.DataSource;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
 
@Configuration
@EnableAutoConfiguration
class SecurityConfig: WebSecurityConfigurerAdapter() {
 
	@Autowired
	lateinit var dataSource: DataSource
 
	@Autowired
	fun configAuthentication(auth: AuthenticationManagerBuilder){
		auth.jdbcAuthentication().dataSource(dataSource)
				.usersByUsernameQuery("select username,password, enabled from users where username=?")
				.authoritiesByUsernameQuery("select username, role from user_roles where username=?")
	}
 
	override fun configure(http: HttpSecurity){
		http.authorizeRequests().antMatchers("/").permitAll().antMatchers("/admin").hasRole("ADMIN")
				.anyRequest().authenticated().and().formLogin().loginPage("/login").permitAll().and().logout()
				.permitAll()
		
		http.exceptionHandling().accessDeniedPage("/403")
	}
}

IV. SourceCode

KotlinSpringSecurityJDBCAuthentication

0 0 votes
Article Rating
Subscribe
Notify of
guest
392 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments