In the previous article, we have learned about Spring InMemoryUserDetailsManager which internally stores and retrieves the user-related information, Spring Security’s JdbcUserDetailsManager class uses the database to store and retrieve the user information. In this JdbcUserDetailsManager Example, we perform Create, Read, Update, and Delete (CRUD) operations on the user details stored in the database.
All the user request will be filtered and passed to AuthenticationManager, the AuthenticationManager authenticates the users by requesting the user details from the UserDetailsService
The UserDetailsService is responsible for providing the valid user details to the AuthenticationManager, JdbcUserDetailsManager indirectly implements UserDetailsService interface.
Now it is the responsibility of JdbcUserDetailsManager to retrieve the user details from the database and load the UserDetails by calling the loadUserByUsername() method.
Once the UserDetails is loaded via JdbcUserDetailsManager and the authentication is successful, the SecurityContext will be updated and the request will proceed to the DispatcherServlet and so on…
Let’s get started, as a first step we need to create two tables “USERS” and “AUTHORITIES” to hold user information and their corresponding authorities.
Creating table
CREATE TABLE USERS ( USERNAME VARCHAR(50) NOT NULL, PASSWORD VARCHAR(68) NOT NULL, ENABLED TINYINT(1) NOT NULL, PRIMARY KEY(USERNAME) ); INSERT INTO USERS (USERNAME, PASSWORD, ENABLED) VALUES('employee','$2a$10$cRqfrdolNVFW6sAju0eNEOE0VC29aIyXwfsEsY2Fz2axy3MnH8ZGa',1); INSERT INTO USERS (USERNAME, PASSWORD, ENABLED) VALUES('manager','$2a$10$cRqfrdolNVFW6sAju0eNEOE0VC29aIyXwfsEsY2Fz2axy3MnH8ZGa',1); CREATE TABLE AUTHORITIES ( USERNAME VARCHAR(50) NOT NULL, AUTHORITY VARCHAR(68) NOT NULL, FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME) ); INSERT INTO AUTHORITIES VALUES('employee','ROLE_EMPLOYEE'); INSERT INTO AUTHORITIES VALUES('employee','ROLE_USER'); INSERT INTO AUTHORITIES VALUES('manager','ROLE_MANAGER'); INSERT INTO AUTHORITIES VALUES('manager','ROLE_USER');
Note: We need to encode the password with Bcrypt Encryption Algorithm before persisting, In the above SQL “pass” is encrypted as “$2a$10$cRqfrdolNVFW6sAju0eNEOE0VC29aIyXwfsEsY2Fz2axy3MnH8ZGa”
Folder Structure:
- Create a simple Maven Project “SpringJdbcUserDetailsManager” and create a package for our source files “com.javainterviewpoint.config” and “com.javainterviewpoint.controller” under src/main/java
- Now add the following dependency in the POM.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.javainterviewpoint</groupId> <artifactId>SpringJdbcUserDetailsManager</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>SpringJdbcUserDetailsManager Maven Webapp</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>5.1.8.RELEASE</version> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-config</artifactId> <version>5.1.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-web</artifactId> <version>5.1.5.RELEASE</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> <version>2.9.9</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>2.9.9</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.1.8.RELEASE</version> </dependency> </dependencies> <build> <finalName>SpringJdbcUserDetailsManager</finalName> <pluginManagement> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-war-plugin</artifactId> <version>3.2.3</version> <configuration> <failOnMissingWebXml>false</failOnMissingWebXml> </configuration> </plugin> </plugins> </pluginManagement> </build> </project>
- Create the Java class ServletInitializer.java, SpringSecurityConfig.java, SpringConfig.java and SecurityInitializer.java under com.javainterviewpoint.config and EmployeeController.java under com.javainterviewpoint.controller folder.
Spring Security – JdbcUserDetailsManager Example
Spring Configuration
package com.javainterviewpoint.config; import javax.sql.DataSource; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.datasource.DriverManagerDataSource; import org.springframework.web.servlet.config.annotation.EnableWebMvc; @Configuration @EnableWebMvc @ComponentScan(basePackages = {"com.javainterviewpoint"}) public class SpringConfig { @Bean public DataSource getDataSource() { DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/mydb"); dataSource.setUsername("root"); dataSource.setPassword("root"); return dataSource; } }
- @Configuration annotation indicates that this class declares one or more @Bean methods which will be processed by the Spring container to generate bean definitions
- @EnableWebMvc is equivalent to <mvc:annotation-driven />. It enables support for @Controller, @RestController, etc.. annotated classes
- We have defined the DataSource bean which consists of all the connection related details which are needed to connect to the database.
Spring Security Configuration – JdbcUserDetailsManager
package com.javainterviewpoint.config; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Bean; 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.crypto.bcrypt.BCryptPasswordEncoder; import org.springframework.security.crypto.password.PasswordEncoder; import org.springframework.security.provisioning.JdbcUserDetailsManager; @Configuration @EnableWebSecurity public class SecurityConfig extends WebSecurityConfigurerAdapter { @Autowired public DataSource dataSource; @Override protected void configure(AuthenticationManagerBuilder auth) throws Exception { auth.userDetailsService(jdbcUserDetailsManager()).passwordEncoder(passwordEncoder()); } @Override protected void configure(HttpSecurity http) throws Exception { http.authorizeRequests() .antMatchers("/employee/**").hasRole("USER") .antMatchers("/manager/**").hasRole("MANAGER") .anyRequest().authenticated() .and() .httpBasic() .and() .csrf().disable(); } @Bean public JdbcUserDetailsManager jdbcUserDetailsManager() { JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager(); jdbcUserDetailsManager.setDataSource(dataSource); return jdbcUserDetailsManager; } @Bean public PasswordEncoder passwordEncoder() { return new BCryptPasswordEncoder(); } }
- @EnableWebSecurity annotation enables spring security configuration which is defined in WebSecurityConfigurerAdapter
- We have extended WebSecurityConfigurerAdapter, which allows us to override spring’s security default feature. In our example we want all the requests to be authenticated using the custom authentication.
- configure(HttpSecurity http) method configures the HttpSecurity class which authorizes each HTTP request which has been made. In our example ‘/employee/**’ should be allowed for the user with USER role and ‘/manager/**’ should be allowed for the user with MANAGER role.
- authorizeRequests() .antMatchers(“/employee/**”).hasRole(“USER”) .antMatchers(“/manager/**”).hasRole(“MANAGER”) –> All requests to must be authorized or else they should be rejected.
- httpBasic() –> Enables the Basic Authentication
- .csrf().disable() –> Enables CSRF protection
@Override protected void configure(HttpSecurity http) throws Exception { http .authorizeRequests() .antMatchers("/employee/**").hasRole("USER") .antMatchers("/manager/**").hasRole("MANAGER") .anyRequest().authenticated() .and() .httpBasic() .and() .csrf().disable(); }
- configure(AuthenticationManagerBuilder auth) method configures the AuthenticationManagerBuilder class with the valid credentials and the allowed roles. The AuthenticationManagerBuilder class creates the AuthenticationManger which is responsible for authenticating the credentials. In our example, we have used the JdbcUserDetailsManager as the UserDetailsService
- jdbcUserDetailsManager() method connects to the database using the dataSource which we have autowired and retrieves the user details.
@Bean public JdbcUserDetailsManager jdbcUserDetailsManager() { JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager(); jdbcUserDetailsManager.setDataSource(dataSource); return jdbcUserDetailsManager; }
Registering Spring Security Filter
Spring Security will be implemented using DelegatingFilterProxy, in order to register it with the Spring container we will be extending AbstractSecurityWebApplicationInitializer. This will enable Spring to register DelegatingFilterProxy and use the springSecurityFilterChain Filter
package com.javainterviewpoint.config; import org.springframework.security.web.context.AbstractSecurityWebApplicationInitializer; public class SecurityInitializer extends AbstractSecurityWebApplicationInitializer { }
ServletInitializer
From Servlet 3.0 onwards, ServletContext can be programmatically configured and hence web.xml is not required.
We have extended AbstractAnnotationConfigDispatcherServletInitializer class which in turn implements WebApplicationInitializer, the WebApplicationInitializer configures the ServletContext
package com.javainterviewpoint.config; import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer; public class ServletInitializer extends AbstractAnnotationConfigDispatcherServletInitializer { @Override protected Class<?>[] getRootConfigClasses() { return null; } @Override protected Class<?>[] getServletConfigClasses() { return new Class[] {SpringConfig.class}; } @Override protected String[] getServletMappings() { return new String[] {"/"}; } }
EmployeeController
package com.javainterviewpoint.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.security.core.userdetails.User; import org.springframework.security.crypto.password.PasswordEncoder; import org.springframework.security.provisioning.JdbcUserDetailsManager; import org.springframework.web.bind.annotation.DeleteMapping; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.PutMapping; import org.springframework.web.bind.annotation.RestController; @RestController public class EmployeeController { @Autowired JdbcUserDetailsManager jdbcUserDetailsManager; @Autowired PasswordEncoder passwordEncoder; @GetMapping("/user") public String showUser() { return "Welcome User!!"; } @GetMapping("/employee") public String showEmployee() { return "Welcome Employee!!"; } @GetMapping("/manager") public String showManager() { return "Welcome Manager!!"; } @GetMapping("/user/{username}") public String checkIfUserExists(@PathVariable("username") String username) { boolean flag = jdbcUserDetailsManager.userExists(username); if (flag) return "\"" + username + "\" exist in Database"; else return "\"" + username + "\" does not exist in Database"; } @PostMapping("/user/{username}/{password}/{role}") public String createUser(@PathVariable("username") String username, @PathVariable("password") String password, @PathVariable("role") String role) { jdbcUserDetailsManager.createUser( User.withUsername(username).password(passwordEncoder.encode(password)).roles("USER").build()); return checkIfUserExists(username); } @PutMapping("/user/{username}/{password}/{role}") public String updateUser(@PathVariable("username") String username, @PathVariable("password") String password, @PathVariable("role") String role) { jdbcUserDetailsManager.updateUser( User.withUsername(username).password(passwordEncoder.encode(password)).roles("USER").build()); return checkIfUserExists(username); } @DeleteMapping("/user/{username}") public String deleteUser(@PathVariable("username") String username) { jdbcUserDetailsManager.deleteUser(username); return checkIfUserExists(username); } }
We have autowired JdbcUserDetailsManager and PasswordEncoder classes, JdbcUserDetailsManager enables us to create, retrieve, modify and delete the UserDetails and PasswordEncoder is an implementation of BCryptPasswordEncoder which is used to encode the password.
All the request which start with /employee requires USER role and the request which start with /manager requires MANAGER role. checkIfUserExists(), createUser(), updateUser(), deleteUser() methods will help us to make changes to the UserDetails persisted in the database.
Output:
Check if the User Exist or Not
In POSTMAN, select the GET method and hit the URL “http://localhost:8080/SpringJdbcUserDetailsManager/user/employee”
In the Authorization, tab selects the Type as “Basic Auth” and key in the valid username /password [employee/pass (or) manager/pass]. You should get a response like“employee” exist in Database
Now hit “http://localhost:8080/SpringJdbcUserDetailsManager/user/employee222” and the response will be “employee222” does not exist in Database
Create User
POST request on the URL “http://localhost:8080/SpringJdbcUserDetailsManager/user/newuser/pass/USER”
In the Authorization, tab select the Type as “Basic Auth” and key in the valid credentials (employee/pass)
This adds the User “newuser” to the database
Update User
Now let’s update the password for the user “newuser”.
Place a PUT request on the URL “http://localhost:8080/SpringJdbcUserDetailsManager/user/newuser/password/USER”
In the Authorization, tab select the Type as “Basic Auth” and key in the valid credentials (employee/password)
Let’s now try to login with the user newuser and validate whether we are able to access the /employee service as it needs the USER role
Hit on the URL “http://localhost:8080/SpringJdbcUserDetailsManager/employee” with credentials (newuser/password)
Try accessing the /manager service, it should be 403 Forbidden as it requires MANAGER role
Delete User
Let’s delete the user “newuser”
Hit on the URL “http://localhost:8080/SpringJdbcUserDetailsManager/user/newemployee”
JdbcUserDetailsManager to use custom SQL queries
JdbcUserDetailsManager class expects the presence of tables by the name “USERS” and “AUTHORITIES”, as the SQL is hardcoded in the class.
Not in every situation, you will be able to have the table names as “USERS” and “AUTHORITIES”, JdbcUserDetailsManager class provides a solution for that as well.
In the case of Custom Table names, we will be able to change the SQL by using the setters methods provided setUserExistsSql(), setCreateUserSql(), setCreateAuthoritySql(), setUpdateUserSql(), setDeleteUserSql(), setDeleteUserAuthoritiesSql()
Let’s see how to use the custom table names
Create the tables USERS1 and AUTHORITIES1
CREATE TABLE USERS1 ( USERNAME VARCHAR(50) NOT NULL, PASSWORD VARCHAR(68) NOT NULL, ENABLED TINYINT(1) NOT NULL, PRIMARY KEY(USERNAME) ); INSERT INTO USERS1 (USERNAME, PASSWORD, ENABLED) VALUES('employee','$2a$10$cRqfrdolNVFW6sAju0eNEOE0VC29aIyXwfsEsY2Fz2axy3MnH8ZGa',1); INSERT INTO USERS1 (USERNAME, PASSWORD, ENABLED) VALUES('manager','$2a$10$cRqfrdolNVFW6sAju0eNEOE0VC29aIyXwfsEsY2Fz2axy3MnH8ZGa',1); CREATE TABLE AUTHORITIES1 ( USERNAME VARCHAR(50) NOT NULL, AUTHORITY VARCHAR(68) NOT NULL, FOREIGN KEY (USERNAME) REFERENCES USERS1(USERNAME) ); INSERT INTO AUTHORITIES1 VALUES('employee','ROLE_EMPLOYEE'); INSERT INTO AUTHORITIES1 VALUES('employee','ROLE_USER'); INSERT INTO AUTHORITIES1 VALUES('manager','ROLE_MANAGER'); INSERT INTO AUTHORITIES1 VALUES('manager','ROLE_USER');
Now we need to make the below changes to our SecurityConfig file [jdbcUserDetailsManager() method]
package com.javainterviewpoint.config; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Bean; 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.crypto.bcrypt.BCryptPasswordEncoder; import org.springframework.security.crypto.password.PasswordEncoder; import org.springframework.security.provisioning.JdbcUserDetailsManager; @Configuration @EnableWebSecurity public class SecurityConfig extends WebSecurityConfigurerAdapter { @Autowired public DataSource dataSource; @Override protected void configure(AuthenticationManagerBuilder auth) throws Exception { auth.userDetailsService(jdbcUserDetailsManager()).passwordEncoder(passwordEncoder()); } @Override protected void configure(HttpSecurity http) throws Exception { http.authorizeRequests() .antMatchers("/employee/**").hasRole("USER") .antMatchers("/manager/**").hasRole("MANAGER") .anyRequest().authenticated() .and() .httpBasic() .and() .csrf().disable(); } @Bean public JdbcUserDetailsManager jdbcUserDetailsManager() { JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager(); jdbcUserDetailsManager.setDataSource(dataSource); jdbcUserDetailsManager.setUserExistsSql("select username from users1 where username = ?"); jdbcUserDetailsManager.setCreateUserSql("insert into users1 (username, password, enabled) values (?,?,?)"); jdbcUserDetailsManager.setCreateAuthoritySql("insert into authorities1 (username, authority) values (?,?)"); jdbcUserDetailsManager.setUpdateUserSql("update users1 set password = ?, enabled = ? where username = ?"); jdbcUserDetailsManager.setDeleteUserSql("delete from users1 where username = ?"); jdbcUserDetailsManager.setDeleteUserAuthoritiesSql("delete from authorities1 where username = ?"); return jdbcUserDetailsManager; } @Bean public PasswordEncoder passwordEncoder() { return new BCryptPasswordEncoder(); } }
Happy Learning !!
Leave a Reply