Database Support in Spring Security ~ Learning help you to achieve your biggest Goals

Database Support in Spring Security

The implementation of this tutorial can be found in the GitHub project – this is an Eclipse based project, so it should be easy to import and run as it is.

- Spring Security can read user account info from database
- By Default,You have to follow Spring Security's predefined table schemas.

- Also you can customize the table schemas.
- In Spring security 5,password are stored using a specific format.

{id}encoded Password

where id is encoding algorithm.

{noop}=password stored as plain text.

I have used Oracle 11g database .Using below script,you can create table and records .

CREATE TABLE users ( username varchar(50) NOT NULL,
password varchar(50) NOT NULL,
enabled NUMBER(1, 0) NOT NULL,
primary key (username)
)

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

Create table authorities(
username varchar2(50) NOT NULL,
authority varchar2(50) NOT NULL,
CONSTRAINT authorities_idx_1 UNIQUE (username,authority),
Constraint authorities_ibfk_1 FOREIGN KEY (username)
References users ( username)
);

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

Step 2:Add database support to maven POM file.

JDBC driver
DB connection pool
com.mechange
c3p0
0.9.5.2

NOTE:Please place ojdbc6.jar in the tomcat lib.

Step 3:Create JDBC properties file

#JDBC connection properties

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.user=SYSTEM
jdbc.password=oracle

##Connection pool properties

connection.pool.initialPoolSize=5
connection.pool.minPoolSize=5
connection.pool.maxPoolSize=20
connection.pool.maxIdleTime=3000

step 4:
Define DataSource in Spring Configuration

@Configuration
@EnableWebMvc
@ComponentScan(basePackages="com.demo")
@Properties(classpath:persistence-mysql.properties")
or
@PropertySource("file:///C:/Learning/SPRING/SpringSecurityJDBC/src/main/resources/persistence-mysql.properties")

DemoAppConfig.java

package com.demo.config;

import java.beans.PropertyVetoException;

import java.util.logging.Logger;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Value;

//import org.codehaus.plexus.component.annotations.Component;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.PropertySource;

import org.springframework.core.env.Environment;

import org.springframework.web.servlet.ViewResolver;

import org.springframework.web.servlet.config.annotation.EnableWebMvc;

import org.springframework.web.servlet.view.InternalResourceViewResolver;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import com.mchange.v2.c3p0.DriverManagerDataSource;

@Configuration

@EnableWebMvc

@ComponentScan(basePackages = "com.demo")

@PropertySource("file:///C:/Learning/SPRING/SpringSecurityJDBC/src/main/resources/persistence-mysql.properties")

public class DemoAppConfig {

       @Value("${jdbc.driver}")

       private String jdbcdriver;

       @Value("${jdbc.url}")

       private String jdbcUrl;

       @Value("${jdbc.user}")

       private String jdbcusername;

       @Value("${jdbc.password}")

       private String jdbcpassword;

       // set up variable to hold the properties

       @Autowired

       private Environment env;

       // set up a logger for diagnostics

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

       @Bean

       public DataSource securityDataSource() {

             System.out.println("-------- Oracle JDBC Connection Testing ------------");

             System.out.println("JDBC Driver Found");

             logger.info(">>jdbc url1" + jdbcdriver);

             logger.info(">>jdbc url1" + jdbcUrl);

             logger.info(">>jdbc user" + jdbcusername);

             logger.info(">>jdbc password" + jdbcpassword);

             DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();

             driverManagerDataSource.setDriverClass(jdbcdriver);

             driverManagerDataSource.setJdbcUrl(jdbcUrl);

             driverManagerDataSource.setUser(jdbcusername);

             driverManagerDataSource.setPassword(jdbcpassword);

             return driverManagerDataSource;

       }

       // need a helper method

       // read environment property and convert into int

       private int getIntProperty(String propName) {

             String propVal = env.getProperty(propName);

             // now convert to int

             int intPropVal = Integer.parseInt(propVal);

             return intPropVal;

       }

       // define a bean for ViewResolver

       @Bean

       public ViewResolver viewResolver() {

             InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();

             viewResolver.setPrefix("/WEB-INF/view/");

             viewResolver.setSuffix(".jsp");

             return viewResolver;

       }

}

DemoSecurityConfig.java

package com.demo.config;

import javax.sql.DataSource;

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.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.core.userdetails.User;

import org.springframework.security.core.userdetails.User.UserBuilder;

@Configuration

@EnableWebSecurity

public class DemoSecurityConfig extends WebSecurityConfigurerAdapter {

               // add a reference to our security data source

               @Autowired

               private DataSource securityDataSource;

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

                              */

                              auth.jdbcAuthentication().dataSource(securityDataSource);

               }

               // custom login page

               @Override

               protected void configure(HttpSecurity http) throws Exception {

                              http.authorizeRequests()

                                                            // .anyRequest().authenticated()

                                                            .antMatchers("/").hasRole("EMPLOYEE").antMatchers("/leaders/**").hasRole("MANAGER")

                                                            .antMatchers("/systems/**").hasRole("ADMIN").and().formLogin().loginPage("/showMyLoginPage")

                                                            .loginProcessingUrl("/authenticateTheUser").permitAll().and().logout().permitAll().and()

                                                            .exceptionHandling().accessDeniedPage("/access-denied");

               }

}

Password storage
The best practice is store passwords in an encrypted format.

{bcrypt}encodedpassword

{bcrypt}- 8chars
encodedpassword - 60chars

noop -Plain text passwords
bcrypt- Bcrypt password hashing

Password column must be at least 68 chars wide.

ALTER TABLE users MODIFY password varchar2(100) ;

NOTE: MODIFY DDL for password field,length should be 68

Spring security recommends using the popular
bcrypt algorithm

Bcrypt

- Performs one-way encrypted hashing
- Adds a random salt to the password for additional protection
- Includes support to defeat brute force attacks.

Bcrypt culculator

Run SQL script that contains encrypted passwords.

Password in plain text is test123

INSERT INTO users VALUES ('devyan','{bcrypt}$2a$10$rxICygsfezBEUTQaPVvITOBw7l4lQEM9pqu5q.D.P6SE7ZysRIeUK',1);

INSERT INTO authorities VALUES ('devyan','ROLE_ADMIN');
INSERT INTO authorities VALUES ('devyan','ROLE_MANAGER');

Learning help you to achieve your biggest Goals

Thursday, January 3, 2019