I'm relatively new to Spring Boot and I've been exploring its authentication features. I've noticed that Spring Boot offers convenient out-of-the-box authentication, provided that usernames and roles are provided within users and users_roles tables. However, I'm facing a challenge in implementing authentication across two different user types, which must be separate and stored on different tables since they hold different kinds of data SQL tables in my Spring Boot application.
Here is the DDL for these two tables (I understand there's no password field or username field in these tables they could be added later, I just want a bigger picture solution.) This is the passengers table this is meant to be regular authenticated users.
CREATE TABLE IF NOT EXISTS passengers (
passenger_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
passport_number VARCHAR(15) UNIQUE NOT NULL,
nationality VARCHAR(255) NOT NULL,
contact_details VARCHAR(255)
);
This other one is the employees table and it is meant to have admin authorities.
CREATE TABLE IF NOT EXISTS employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
role VARCHAR(100) NOT NULL,
contact_info VARCHAR(255),
airport_id INT,
FOREIGN KEY (airport_id) REFERENCES airports(airport_id)
);
So here's a basic example that I worked on just for demonstration purposes (this doesn't apply to the roles I need in the project). So here I do need to fetch different users from two different tables. I don't know what is best practice for a scenario like this.
@Configuration
public class DemoSecurityConfig {
// add support for JDBC ... no more hardcoded users :-)
@Bean
public UserDetailsManager userDetailsManager(DataSource dataSource) {
JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager(dataSource);
// define query to retrieve a user by username
jdbcUserDetailsManager.setUsersByUsernameQuery(
"select user_id, pw, active from members where user_id=?");
// define query to retrieve the authorities/roles by username
jdbcUserDetailsManager.setAuthoritiesByUsernameQuery(
"select user_id, role from roles where user_id=?");
return jdbcUserDetailsManager;
}
@Bean
public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
http.authorizeHttpRequests(configurer ->
configurer
.requestMatchers(HttpMethod.GET, "/api/employees").hasRole("EMPLOYEE")
.requestMatchers(HttpMethod.GET, "/api/employees/**").hasRole("EMPLOYEE")
.requestMatchers(HttpMethod.POST, "/api/employees").hasRole("MANAGER")
.requestMatchers(HttpMethod.PUT, "/api/employees").hasRole("MANAGER")
.requestMatchers(HttpMethod.DELETE, "/api/employees/**").hasRole("ADMIN")
);
// use HTTP Basic authentication
http.httpBasic(Customizer.withDefaults());
// disable Cross Site Request Forgery (CSRF)
// in general, not required for stateless REST APIs that use POST, PUT, DELETE and/or PATCH
http.csrf(csrf -> csrf.disable());
return http.build();
}
}
I have been thinking about maintaining only the required fields in another user table and user_roles table for both tables. But then I think I'll run into another problem where I'll need the data for one type of user and have to fetch from one or the other table.
We are using Spring Data JPA and have defined entities for these tables. So we have repositories that extend the JPARepositoryInterface. Could someone please provide guidance or a potential solution for implementing authentication that spans multiple SQL tables in this context? Any examples or pointers would be greatly appreciated.
Thank you in advance!
In real world, its quite possible where different business users needs to be authenticated from different sources. Let's go through the solution:
You can register two different SecurityFilterChain for each entry points. I suggest you to understand Spring Security Architecture. FilterChainProxy will decide which SecurityFilterChain will be used based on securityMatcher.
Similarly, you can create two different UserDetailsService for each datasource.
SecurityConfiguration
@Configuration
public class SecurityConfiguration {
private final PassengerUserDetailsService passengerUserDetailsService;
private final EmployeeUserDetailsService employeeUserDetailsService;
private final PasswordEncoder passwordEncoder;
public SecurityConfiguration(PassengerUserDetailsService passengerUserDetailsService,
EmployeeUserDetailsService employeeUserDetailsService,
PasswordEncoder passwordEncoder) {
this.passengerUserDetailsService = passengerUserDetailsService;
this.employeeUserDetailsService = employeeUserDetailsService;
this.passwordEncoder = passwordEncoder;
}
@Bean
@Order(0)
public SecurityFilterChain filterChain0(HttpSecurity httpSecurity) throws Exception {
httpSecurity
.securityMatcher("/passenger/**") // this will decide which securityFilterChain will be used
.authorizeHttpRequests(
auth -> auth.
requestMatchers(HttpMethod.GET, "/passenger/**").hasRole("PASSENGER")
)
.httpBasic(Customizer.withDefaults())
.csrf(CsrfConfigurer::disable)
.userDetailsService(passengerUserDetailsService);
return httpSecurity.build();
}
@Bean
@Order(1)
public SecurityFilterChain filterChain1(HttpSecurity httpSecurity) throws Exception {
httpSecurity
.securityMatcher("/employee/**")
.authorizeHttpRequests(
auth -> auth.
requestMatchers(HttpMethod.GET, "/employee/**").hasRole("EMPLOYEE")
)
.httpBasic(Customizer.withDefaults())
.csrf(CsrfConfigurer::disable)
.userDetailsService(employeeUserDetailsService);
return httpSecurity.build();
}
}
PassengerUserDetailsService
@Service
public class PassengerUserDetailsService implements UserDetailsService {
private final PasswordEncoder passwordEncoder;
PassengerUserDetailsService(PasswordEncoder passwordEncoder) {
this.passwordEncoder = passwordEncoder;
}
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
User.UserBuilder builder = User.builder();
builder.username(username);
builder.password(passwordEncoder.encode(username));
switch (username) {
case "passenger":
builder.roles("PASSENGER");
break;
default:
throw new UsernameNotFoundException("User not found.");
}
return builder.build();
}
}
EmployeeUserDetailsService
@Service
public class EmployeeUserDetailsService implements UserDetailsService {
private final PasswordEncoder passwordEncoder;
EmployeeUserDetailsService(PasswordEncoder passwordEncoder) {
this.passwordEncoder = passwordEncoder;
}
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
User.UserBuilder builder = User.builder();
builder.username(username);
builder.password(passwordEncoder.encode(username));
switch (username) {
case "employee":
builder.roles("EMPLOYEE");
break;
default:
throw new UsernameNotFoundException("User not found.");
}
return builder.build();
}
}
I have mocked users in each UserDetailsService, which you can fetch from your respective datasource. You can refer to class org.springframework.security.authentication.dao.DaoAuthenticationProvider for your reference.
You don't need to follow spring's default ddl to define your schema. You can always use your existing schema and use custom queries using UserDetailsService. They are provided to quick start your project without manual configuration.