java - 如何更改H2数据库的密码加密？

Question

我的 Spring Boot 项目有这个配置文件:

@Configuration
@EnableWebSecurity
@EnableGlobalMethodSecurity(prePostEnabled = true)
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
    @Autowired
    private UserService userService;

    @Autowired
    private PasswordEncoder passwordEncoder;

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

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
                .authorizeRequests()
                .antMatchers("/", "/registration", "/activate/*").permitAll()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .loginPage("/login")
                .permitAll()
                .and()
                .logout()
                .permitAll();
    }

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userService)
                .passwordEncoder(passwordEncoder);
    }
}

我还有 data.sql 文件，但我需要对这些密码进行编码:

insert into users (email, username, password, is_enabled)
values ('admin@gmail.com', 'admin', 'admin', true),
       ('user@gmail.com', 'user', 'password', true),
       ('user2@gmail.com', 'user2', 'password', true);

insert into user_role (user_id, roles)
values (100000, 'ADMIN'),
       (100000, 'USER'),
       (100001, 'USER'),
       (100002, 'USER');

对于“PostgreSQL”，我可以这样设置编码:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

UPDATE users SET password = crypt(password, gen_salt('bf', 8));

但它不适用于 H2 数据库。如何修复它并对密码进行编码？

Answer 1

SQL 没有任何可移植的方式来实现此目的，但某些数据库有自己的功能。在H2中你可以使用HASH生成密码的哈希值。

UPDATE USERS SET PASSWORD = HASH('SHA256', PASSWORD, 1000);

SELECT * FROM USERS WHERE USERNAME = ? AND IS_ENABLED AND PASSWORD = HASH('SHA256', ?, 1000);

您还可以使用SECURE_RAND函数来生成盐以将其与密码连接起来，但是您必须将盐单独存储在其自己的列中或带有分隔符的同一列中(或使用散列函数的已知长度)。

UPDATE USERS SET PASSWORD = (@S := SECURE_RAND(16)) || HASH('SHA256', @S || PASSWORD, 1000);

SELECT * FROM USERS WHERE USERNAME = 'user'
    AND HASH('SHA256', SUBSTRING(PASSWORD FROM 1 FOR 32) || 'password',  1000)
        = SUBSTRING(PASSWORD FROM 33);

您还可以编写用户定义的函数来模拟 PostgreSQL 中的函数。

更可靠的解决方案是使用 Java 代码，它不依赖于您当前使用的数据库。

final int keyLength = 256 / 8;
final String algorithm = "PBKDF2WithHmacSHA256";
final int saltLength = 16;
final int numIterations = 1000;
SecureRandom sr = SecureRandom.getInstanceStrong();
SecretKeyFactory skf = SecretKeyFactory.getInstance(algorithm);
// Encode
String password = "test";
byte[] salt = sr.generateSeed(saltLength);
byte[] encodedPassword = skf
        .generateSecret(new PBEKeySpec(password.toCharArray(), salt, numIterations, keyLength * 8))
        .getEncoded();
byte[] passwordHash = Arrays.copyOf(salt, saltLength + keyLength);
System.arraycopy(encodedPassword, 0, passwordHash, saltLength, keyLength);
// Check
String password2 = "test";
salt = Arrays.copyOf(passwordHash, saltLength);
byte[] encodedPassword2 = skf
        .generateSecret(new PBEKeySpec(password2.toCharArray(), salt, numIterations, keyLength * 8))
        .getEncoded();
// Always test all bytes to prevent timing attack
int bits = 0;
for (int i = 0; i < keyLength; i++) {
    bits |= passwordHash[i + saltLength] ^ encodedPassword2[i];
}
boolean valid = bits == 0;