Spring Security实现多SQL表身份认证方案咨询

针对你需要从独立的乘客表和员工表实现身份认证的需求，以下是几种可行的实现方案，结合Spring Data JPA和Spring Security完成：

方案一：自定义UserDetailsService（最直接方案） #

通过自定义UserDetailsService，整合两个实体的Repository，根据用户名依次查询乘客表和员工表，封装成Spring Security需要的UserDetails对象。

步骤1：更新表结构，添加认证必要字段 #

给乘客表和员工表添加用户名、密码、启用状态字段：

-- 更新乘客表
ALTER TABLE passengers 
ADD COLUMN username VARCHAR(50) UNIQUE NOT NULL,
ADD COLUMN password VARCHAR(255) NOT NULL,
ADD COLUMN enabled BOOLEAN NOT NULL DEFAULT TRUE;

-- 更新员工表
ALTER TABLE employees 
ADD COLUMN username VARCHAR(50) UNIQUE NOT NULL,
ADD COLUMN password VARCHAR(255) NOT NULL,
ADD COLUMN enabled BOOLEAN NOT NULL DEFAULT TRUE;

步骤2：更新实体类 #

在Passenger和Employee实体中添加对应字段：

// Passenger实体示例
@Entity
@Table(name = "passengers")
public class Passenger {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "passenger_id")
    private Integer id;
    
    // 原有字段...
    private String name;
    private String passportNumber;
    private String nationality;
    private String contactDetails;
    
    // 新增认证字段
    @Column(unique = true, nullable = false)
    private String username;
    
    @Column(nullable = false)
    private String password;
    
    @Column(nullable = false)
    private boolean enabled = true;

    // Getter、Setter、构造方法
}

// Employee实体示例
@Entity
@Table(name = "employees")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "employee_id")
    private Integer id;
    
    // 原有字段...
    private String name;
    private String role;
    private String contactInfo;
    private Integer airportId;
    
    // 新增认证字段
    @Column(unique = true, nullable = false)
    private String username;
    
    @Column(nullable = false)
    private String password;
    
    @Column(nullable = false)
    private boolean enabled = true;

    // Getter、Setter、构造方法
}

步骤3：定义Repository接口 #

添加根据用户名查询的方法：

public interface PassengerRepository extends JpaRepository<Passenger, Integer> {
    Optional<Passenger> findByUsername(String username);
}

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    Optional<Employee> findByUsername(String username);
}

步骤4：自定义UserDetailsService #

实现UserDetailsService接口，整合两个Repository的查询逻辑：

@Service
public class CustomUserDetailsService implements UserDetailsService {

    private final PassengerRepository passengerRepository;
    private final EmployeeRepository employeeRepository;

    // 构造注入
    public CustomUserDetailsService(PassengerRepository passengerRepository, EmployeeRepository employeeRepository) {
        this.passengerRepository = passengerRepository;
        this.employeeRepository = employeeRepository;
    }

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        // 优先查询乘客表
        Optional<Passenger> passengerOpt = passengerRepository.findByUsername(username);
        if (passengerOpt.isPresent()) {
            Passenger passenger = passengerOpt.get();
            return User.builder()
                    .username(passenger.getUsername())
                    .password(passenger.getPassword())
                    .enabled(passenger.isEnabled())
                    .authorities("ROLE_PASSENGER") // 乘客默认角色
                    .build();
        }

        // 查询员工表
        Optional<Employee> employeeOpt = employeeRepository.findByUsername(username);
        if (employeeOpt.isPresent()) {
            Employee employee = employeeOpt.get();
            // 为员工角色添加ROLE_前缀（Spring Security规范）
            String authority = "ROLE_" + employee.getRole().toUpperCase();
            return User.builder()
                    .username(employee.getUsername())
                    .password(employee.getPassword())
                    .enabled(employee.isEnabled())
                    .authorities(authority)
                    .build();
        }

        throw new UsernameNotFoundException("用户不存在：" + username);
    }
}

步骤5：配置Spring Security #

替换原有JdbcUserDetailsManager，使用自定义的UserDetailsService，并添加密码编码器：

@Configuration
public class SecurityConfig {

    // 密码编码器（生产环境推荐BCrypt）
    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

    @Bean
    public AuthenticationManager authenticationManager(AuthenticationConfiguration authConfig) throws Exception {
        return authConfig.getAuthenticationManager();
    }

    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http.authorizeHttpRequests(configurer ->
                configurer
                        // 乘客权限控制
                        .requestMatchers(HttpMethod.GET, "/api/passengers/**").hasRole("PASSENGER")
                        .requestMatchers(HttpMethod.POST, "/api/passengers").hasRole("PASSENGER")
                        // 员工权限控制
                        .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")
                        // 其他所有接口需要认证
                        .anyRequest().authenticated()
        );

        // 启用HTTP Basic认证
        http.httpBasic(Customizer.withDefaults());

        // 禁用CSRF（适合无状态REST API）
        http.csrf(csrf -> csrf.disable());

        return http.build();
    }
}

方案二：多AuthenticationProvider（高扩展性方案） #

如果两种用户类型的认证逻辑差异较大（比如未来可能添加不同的认证方式），可以定义多个AuthenticationProvider，分别处理乘客和员工的认证。

核心代码示例 #

@Configuration
public class SecurityConfig {

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

    // 乘客认证Provider
    @Bean
    public AuthenticationProvider passengerAuthProvider(PassengerRepository repo, PasswordEncoder encoder) {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        provider.setUserDetailsService(username -> {
            Passenger passenger = repo.findByUsername(username)
                    .orElseThrow(() -> new UsernameNotFoundException("乘客不存在"));
            return User.builder()
                    .username(passenger.getUsername())
                    .password(passenger.getPassword())
                    .enabled(passenger.isEnabled())
                    .authorities("ROLE_PASSENGER")
                    .build();
        });
        provider.setPasswordEncoder(encoder);
        return provider;
    }

    // 员工认证Provider
    @Bean
    public AuthenticationProvider employeeAuthProvider(EmployeeRepository repo, PasswordEncoder encoder) {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        provider.setUserDetailsService(username -> {
            Employee employee = repo.findByUsername(username)
                    .orElseThrow(() -> new UsernameNotFoundException("员工不存在"));
            return User.builder()
                    .username(employee.getUsername())
                    .password(employee.getPassword())
                    .enabled(employee.isEnabled())
                    .authorities("ROLE_" + employee.getRole().toUpperCase())
                    .build();
        });
        provider.setPasswordEncoder(encoder);
        return provider;
    }

    // 注册多个Provider到AuthenticationManager
    @Bean
    public AuthenticationManager authenticationManager(AuthenticationConfiguration authConfig,
                                                       AuthenticationProvider passengerProvider,
                                                       AuthenticationProvider employeeProvider) throws Exception {
        AuthenticationManagerBuilder builder = authConfig.getAuthenticationManagerBuilder();
        builder.authenticationProvider(passengerProvider);
        builder.authenticationProvider(employeeProvider);
        return builder.build();
    }

    // SecurityFilterChain配置同方案一
    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        // ... 同方案一代码
    }
}

方案三：统一用户表+关联表（规范扩展性方案） #

如果希望统一管理认证信息，避免重复字段，可以创建一个通用的users表存储认证数据，再通过关联关系绑定乘客和员工的专属数据，利用JPA懒加载优化跨表查询性能。

核心表结构 #

-- 通用用户表（存储认证信息）
CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    user_type VARCHAR(20) NOT NULL -- 标记用户类型：PASSENGER/EMPLOYEE
);

-- 更新乘客表，添加关联字段
ALTER TABLE passengers ADD COLUMN user_id INT UNIQUE NOT NULL;
ALTER TABLE passengers ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

-- 更新员工表，添加关联字段
ALTER TABLE employees ADD COLUMN user_id INT UNIQUE NOT NULL;
ALTER TABLE employees ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

核心实体关联 #

// User实体
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Integer id;
    
    private String username;
    private String password;
    private boolean enabled;
    private String userType;

    // 关联乘客（懒加载，仅当需要时查询）
    @OneToOne(mappedBy = "user", fetch = FetchType.LAZY)
    private Passenger passenger;

    // 关联员工（懒加载）
    @OneToOne(mappedBy = "user", fetch = FetchType.LAZY)
    private Employee employee;

    // Getter、Setter
}

// Passenger实体添加关联
@Entity
@Table(name = "passengers")
public class Passenger {
    // 原有字段...
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", referencedColumnName = "user_id")
    private User user;

    // Getter、Setter
}

这种方案下，认证时只需查询users表，获取用户专属数据时通过JPA的懒加载关联查询，不会影响性能，适合未来扩展更多用户类型的场景。

最佳实践建议 #

• 快速实现选方案一：无需修改原有表结构，代码逻辑简单直接，适合当前需求。
• 高扩展性选方案二：如果未来可能添加更多用户类型或不同认证方式，多Provider的设计更灵活。
• 长期规范选方案三：统一认证数据，避免重复字段，JPA懒加载解决跨表查询的性能顾虑，符合领域模型设计规范。

内容的提问来源于stack exchange，提问作者Florian Saqipi