-- 用戶表
CREATE TABLE `user` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,   -- BCrypt加密
  `enabled` tinyint DEFAULT 1
);
-- 角色表
CREATE TABLE `role` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `role_code` varchar(50) NOT NULL   -- ROLE_ADMIN, ROLE_USER
);
-- 權(quán)限表（細(xì)粒度權(quán)限）
CREATE TABLE `permission` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `perm_code` varchar(100) NOT NULL  -- user:add, user:delete, order:view
);
-- 用戶-角色關(guān)聯(lián)
CREATE TABLE `user_role` (
  `user_id` int,
  `role_id` int
);
-- 角色-權(quán)限關(guān)聯(lián)
CREATE TABLE `role_permission` (
  `role_id` int,
  `perm_id` int
);

-- 用戶 admin / 123456（實(shí)際用BCrypt）
INSERT INTO `user` VALUES (1, 'admin', '$2a$10$NkMwpJ7UoWjQlqWjQlqWjQ', 1);
-- 角色
INSERT INTO `role` VALUES (1, 'ROLE_ADMIN'), (2, 'ROLE_USER');
-- 權(quán)限
INSERT INTO `permission` VALUES (1, 'user:list'), (2, 'user:delete');
-- 管理員擁有所有角色和權(quán)限
INSERT INTO `user_role` VALUES (1,1);
INSERT INTO `role_permission` VALUES (1,1),(1,2);

public class User {
    private Integer id;
    private String username;
    private String password;
    private Boolean enabled;
    // getter/setter
}

@Mapper
public interface UserMapper {
    @Select("SELECT id, username, password, enabled FROM user WHERE username = #{username}")
    User findByUsername(String username);
    @Select("SELECT r.role_code FROM user_role ur " +
            "JOIN role r ON ur.role_id = r.id " +
            "WHERE ur.user_id = #{userId}")
    List<String> findRolesByUserId(Integer userId);
    @Select("SELECT p.perm_code FROM user_role ur " +
            "JOIN role_permission rp ON ur.role_id = rp.role_id " +
            "JOIN permission p ON rp.perm_id = p.id " +
            "WHERE ur.user_id = #{userId}")
    List<String> findPermissionsByUserId(Integer userId);
}

@Service
public class CustomUserDetailsService implements UserDetailsService {
    @Autowired
    private UserMapper userMapper;
    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        // 1. 查用戶
        User user = userMapper.findByUsername(username);
        if (user == null) {
            throw new UsernameNotFoundException("用戶不存在: " + username);
        }
        // 2. 查角色（ROLE_xxx）
        List<String> roles = userMapper.findRolesByUserId(user.getId());
        // 3. 查權(quán)限（user:add 等）
        List<String> permissions = userMapper.findPermissionsByUserId(user.getId());
        // 4. 合并權(quán)限 + 角色
        //    注意：Spring Security 中角色需要加 ROLE_ 前綴
        Set<GrantedAuthority> authorities = new HashSet<>();
        for (String role : roles) {
            // 如果role已經(jīng)是 ROLE_ADMIN 格式，直接加；否則自動(dòng)補(bǔ)
            if (role.startsWith("ROLE_")) {
                authorities.add(new SimpleGrantedAuthority(role));
            } else {
                authorities.add(new SimpleGrantedAuthority("ROLE_" + role));
            }
        }
        for (String perm : permissions) {
            authorities.add(new SimpleGrantedAuthority(perm));
        }
        // 5. 返回 UserDetails
        return new org.springframework.security.core.userdetails.User(
            user.getUsername(),
            user.getPassword(),
            user.getEnabled(),      // 是否啟用
            true,                   // 賬號(hào)未過(guò)期
            true,                   // 憑證未過(guò)期
            true,                   // 賬號(hào)未鎖定
            authorities
        );
    }
}

@Configuration
@EnableWebSecurity
public class SecurityConfig {
    @Autowired
    private CustomUserDetailsService userDetailsService;
    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();  // 密碼加密
    }
    @Bean
    public AuthenticationManager authManager(HttpSecurity http) throws Exception {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        provider.setUserDetailsService(userDetailsService);
        provider.setPasswordEncoder(passwordEncoder());
        return http.getSharedObject(AuthenticationManagerBuilder.class)
                   .authenticationProvider(provider)
                   .build();
    }
    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http
            .authorizeHttpRequests(auth -> auth
                // 公開(kāi)接口
                .requestMatchers("/login", "/register").permitAll()
                // 權(quán)限控制：需要具體權(quán)限
                .requestMatchers("/user/list").hasAuthority("user:list")
                .requestMatchers("/user/delete").hasAuthority("user:delete")
                // 角色控制
                .requestMatchers("/admin/**").hasRole("ADMIN")
                // 其他請(qǐng)求需要認(rèn)證
                .anyRequest().authenticated()
            )
            .formLogin(form -> form
                .loginPage("/login")
                .defaultSuccessUrl("/home")
                .permitAll()
            )
            .logout(LogoutConfigurer::permitAll)
            .csrf(csrf -> csrf.disable());  // 根據(jù)實(shí)際情況開(kāi)啟
        return http.build();
    }
}

@RestController
public class UserController {
    @GetMapping("/user/list")
    public String listUsers() {
        return "用戶列表（需要 user:list 權(quán)限）";
    }
    @GetMapping("/user/delete")
    public String deleteUser() {
        return "刪除用戶（需要 user:delete 權(quán)限）";
    }
    @GetMapping("/current-permissions")
    public Authentication getCurrentAuth() {
        return SecurityContextHolder.getContext().getAuthentication();
    }
}

// 修改權(quán)限后，重新加載
SecurityContextHolder.getContext().setAuthentication(updatedAuth);

@PreAuthorize("hasAuthority('user:delete')")
@DeleteMapping("/user/{id}")
public String delete(@PathVariable Long id) {
    return "刪除成功";
}

@EnableGlobalMethodSecurity(prePostEnabled = true)

<resultMap id="UserWithAuth" type="com.example.User">
    <id column="id" property="id"/>
    <collection property="roles" ofType="string" select="..."/>
</resultMap>