mysql jdbc url ssl_java – MySQL JDBC over SSL问题

如何让MySQL JDBC通过SSL工作(使用X509证书验证)？

我有自己创建的证书,如MySQL手册中所述,使用SSL进行安全连接,具体来说：

# Create CA certificate

shell> openssl genrsa 2048 > ca-key.pem

shell> openssl req -new -x509 -nodes -days 1000 \n -key ca-key.pem > ca-cert.pem

# Create server certificate

shell> openssl req -newkey rsa:2048 -days 1000 \n -nodes -keyout server-key.pem > server-req.pem

shell> openssl x509 -req -in server-req.pem -days 1000 \n -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Create client certificate

shell> openssl req -newkey rsa:2048 -days 1000 \n -nodes -keyout client-key.pem > client-req.pem

shell> openssl x509 -req -in client-req.pem -days 1000 \n -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

发出GRANT ALL ON *.* to vic @ localhost IDENTIFIED BY’12345’REQUIRE X509;我可以通过命令行连接到MySQL：

mysql -u vic -p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem mysql

...

mysql> SHOW STATUS LIKE 'Ssl_cipher';

+---------------+--------------------+

| Variable_name | Value |

+---------------+--------------------+

| Ssl_cipher | DHE-RSA-AES256-SHA |

+---------------+--------------------+

但是,当我尝试运行Java测试时,我得到auth失败：用户’vic’@’localhost’的访问被拒绝(使用密码：YES).代码如下：

public class Launcher {

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

StringBuffer sb = new StringBuffer("jdbc:mysql://localhost/bt?useSSL=true&");

sb.append("user=vic&password=12345&");

sb.append("clientCertificateKeyStorePassword=123456&");

sb.append("clientCertificateKeyStoreType=JKS&");

sb.append("clientCertificateKeyStoreUrl=file:///home/vic/tmp/client-keystore&");

sb.append("trustCertificateKeyStorePassword=123456&");

sb.append("trustCertificateKeyStoreType=JKS&");

sb.append("trustCertificateKeyStoreUrl=file:///home/vic/tmp/ca-keystore");

Class.forName("com.mysql.jdbc.Driver");

Connection c = DriverManager.getConnection(sb.toString());

Statement st = c.createStatement();

ResultSet rs = st.executeQuery("SELECT * FROM test_table");

while (rs.next()) {

System.out.println(rs.getInt("id"));

}

rs.close(); st.close(); c.close();

}

}

以下是我准备Java密钥库文件的方法：

keytool -import -alias mysqlServerCACert -file ca-cert.pem -keystore ca-keystore

keytool -import -file client-cert.pem -keystore client-keystore -alias client-key

更新如果我使用’root’用户而不是’vic’,我可以通过JDBC连接SSL.然后是代码

Statement st = c.createStatement();

ResultSet rs = st.executeQuery("SHOW STATUS LIKE 'Ssl_cipher';");

while (rs.next()) {

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

System.out.println(rs.getString(2));

}

版画

Ssl_cipher

DHE-RSA-AES128-SHA

但我不能在生产中使用root,我想知道为什么JDBC使用AES128,而命令行mysql客户端使用AES256.

UPDATE2在root用户表的root用户表中将ssl_type更改为X509后,请求客户端的完全身份验证,我获得了与root相同的行为 – 无法通过JDBC登录.

UPDATE3如果我在GRANT语句中使用REQUIRE SSL而不是REQUIRE X509,则代码可以正常工作.是否可以使X509工作？