12月 7, 2021 | Nix.Huang

产生证书

#约定-key.pem秘钥，-req.pem证书签名请求，*-cert.pem为证书
#参数私钥和证书认证请求(client-req.pem,certificate signing request
#有些地方将这个文件命名为client.csr)
openssl req -newkey rsa:2048 -days 3600 \ -nodes -keyout client-key.pem -out client-req.pem

#使用ca-key.pem，根据ca-key.pem产生client-cert.pem证书
openssl x509 -req -in client-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

#修改用户需要验证issuer(证书发行主体)和subject（主体）
alter user 'test'@'%' require issuer='/CN=MySQL_Server_8.0.27_Auto_Generated_CA_Certificate' and subject='/CN=win-client';
#指定私钥和证书登录--ssl-key and --ssl-cert
mysql.exe -uroot -p --ssl-key=D:/certs/client-key.pem --ssl-cert=D:/certs/client-cert.pem -h 192.168.228.135

#将openssl产生的证书导出为pkcs12格式
openssl pkcs12 -export -passout pass:Root@123 -in client-cert.pem -inkey client-key.pem -out client-keystore.p12

将ca.pem和client-keystore.p12导入java keystore

keytool -importcert -alias MySQLCACert -file ca.pem \
-keystore truststore -storepass Root@123
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \
-name "mysqlclient" -passout pass:Root@123 -out client-keystore.p12
keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 \
-srcstorepass Root@123 -destkeystore keystore -deststoretype \
pkcs12 -deststorepass Root@123

#trustStore 信任的证书，keyStore 自己的证书

-Djavax.net.ssl.trustStore=path_to_truststore_file
-Djavax.net.ssl.trustStorePassword=Root@123
-Djavax.net.ssl.keyStore=path_to_keystore_file
-Djavax.net.ssl.keyStorePassword=Root@123

或者链接属性

trustCertificateKeyStoreUrl=file:path_to_truststore_file
trustCertificateKeyStorePassword=Root@123
clientCertificateKeyStoreUrl=file:path_to_truststore_file
clientCertificateKeyStorePassword=Root@123

java代码片段

public class App {
    static final String DB_URL = "jdbc:mysql://192.168.228.135/employees?sslMode=VERIFY_CA&allowPublicKeyRetrieval=false";
    static final String USER = "test";
    static final String PASS = "Root@123";
    static final String QUERY = "select count(*) from departments";

    public static void main(String[] args) throws SQLException {

        System.setProperty("javax.net.ssl.trustStore","D:/certs/truststore");
        System.setProperty("javax.net.ssl.trustStorePassword","Root@123");


        System.setProperty("javax.net.ssl.keyStore","D:/certs/keystore");
        System.setProperty("javax.net.ssl.keyStorePassword","Root@123");

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(QUERY);
            rs.next();
           System.out.println(rs.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(conn!= null)conn.close();
        }
    }
}

public class App {

static final String DB_URL = "jdbc:mysql://192.168.228.135/employees?sslMode=VERIFY_CA&allowPublicKeyRetrieval=false";

static final String USER = "test";

static final String PASS = "Root@123";

static final String QUERY = "select count(*) from departments";

public static void main(String[] args) throws SQLException {

System.setProperty("javax.net.ssl.trustStore","D:/certs/truststore");

System.setProperty("javax.net.ssl.trustStorePassword","Root@123");

System.setProperty("javax.net.ssl.keyStore","D:/certs/keystore");

System.setProperty("javax.net.ssl.keyStorePassword","Root@123");

Connection conn = null;

try {

conn = DriverManager.getConnection(DB_URL, USER, PASS);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(QUERY);

rs.next();

System.out.println(rs.getInt(1));

} catch (SQLException e) {

e.printStackTrace();

} finally {

if(conn!= null)conn.close();

}

参考文档

creating-ssl-files-using-openssl
connector-j-reference-using-ssl.html

Posted in: database | Tags: MySQL

知行合一，止于至善

mysql 指定客户端证书

产生证书

将ca.pem和client-keystore.p12导入java keystore

java代码片段

参考文档

站内搜索