java MySQL JDBC over SSL 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4663061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL JDBC over SSL problem
提问by Victor Sorokin
How one can make MySQL JDBC work over SSL (with X509 certificates validation)?
如何让 MySQL JDBC 通过 SSL 工作（使用 X509 证书验证）？
I've got self-created certificates as described in MySQL manual, in Using SSL for Secure Connections, specifically:
我有 MySQL 手册中描述的自创证书，在Using SSL for Secure Connections，特别是：
# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 \
-key ca-key.pem > ca-cert.pem
# Create server certificate
shell> openssl req -newkey rsa:2048 -days 1000 \
-nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 1000 \
-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 \
-nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
After issuing GRANT ALL ON *.* TO vic@localhost IDENTIFIED BY '12345' REQUIRE X509;I am able to connect to MySQL over command-line:
发出后，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 |
+---------------+--------------------+
However, when I try to run Java test, I get auth failure: Access denied for user 'vic'@'localhost' (using password: YES). Code follows:
但是，当我尝试运行 Java 测试时，出现 auth failure: Access denied for user 'vic'@'localhost' (using password: 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();
}
}
And here's how I prepared Java keystore files:
这是我准备 Java 密钥库文件的方法：
keytool -import -alias mysqlServerCACert -file ca-cert.pem -keystore ca-keystore
keytool -import -file client-cert.pem -keystore client-keystore -alias client-key
UPDATEI am able to connect over SSL via JDBC if I use 'root' user instead of 'vic'. Then following code
更新如果我使用“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));
}
prints
印刷
Ssl_cipher
DHE-RSA-AES128-SHA
But I can't use root in production, and I wonder why JDBC uses AES128, whereas command-line mysql client uses AES256.
但是我不能在生产中使用 root，我想知道为什么 JDBC 使用 AES128，而命令行 mysql 客户端使用 AES256。
UPDATE2After I changed ssl_typeto X509in usertable for root@localhost, requesting full auth of client, I get the same behavior for root as for vic -- can't login via JDBC.
UPDATE2在我更改ssl_type为X509in usertable for 后root@localhost，请求客户端的完全身份验证，我得到与 vic 相同的 root 行为 - 无法通过 JDBC 登录。
UPDATE3If I use REQUIRE SSLinstead of REQUIRE X509in GRANTstatement, code works. Is it possible to make X509 work?
UPDATE3如果我使用REQUIRE SSL而不是REQUIRE X509inGRANT语句，则代码有效。可以让X509工作吗？
回答by sehrope
Support for self-signed certificates was recently added to the MariaDB JDBC driver (which also works for connecting to MySQL). The latest version (1.1.3 as of writing this) also allows you to directly specify the server certificate at runtime so that you do not need to configure key stores or import certificates in advance.
最近在 MariaDB JDBC 驱动程序中添加了对自签名证书的支持（它也适用于连接到 MySQL）。最新版本（撰写本文时为 1.1.3）还允许您在运行时直接指定服务器证书，这样您就无需提前配置密钥库或导入证书。
The two properties to set are useSSLand serverSslCert. The latter can be either the certificate itself (a String value) or a path to a file that contains the certificate (either full path or classpath relative):
要设置的两个属性是useSSL和serverSslCert。后者可以是证书本身（字符串值）或包含证书的文件的路径（完整路径或相对类路径）：
String url = "jdbc:mysql://" + host + ":" + port + "/" + database;
Properties info = new Properties();
info.setProperty("user", username);
info.setProperty("password", password);
info.setProperty("useSSL", "true");
info.setProperty("serverSslCert", "classpath:server.crt");
Connection conn = DriverManager.getConnection(url, info);
For a full working example of how to connect see here: https://github.com/properssl/java-jdbc-mariadb
有关如何连接的完整工作示例，请参见此处：https: //github.com/properssl/java-jdbc-mariadb
回答by Victor Sorokin
I've settled on using REQUIRE SSL. If someone knows how to make X509 work with JDBC, this info would be appreciated.
我已经决定使用REQUIRE SSL. 如果有人知道如何使 X509 与 JDBC 一起工作，此信息将不胜感激。
回答by Mark Matten
I can connect without even specifying the client certificate properties when the user has been configurated with only REQUIRES SSL.
当用户只配置了 REQUIRES SSL 时，我甚至可以在不指定客户端证书属性的情况下进行连接。
回答by mohr_michael_a
Seems the MariaDB driver folks have a test class that walks through all the scenarios including 'REQUIRES X509'.
似乎 MariaDB 驱动程序人员有一个测试类，可以遍历所有场景，包括“REQUIRES X509”。
