MySQL JDBC over SSL问题 [英] MySQL JDBC over SSL problem

问题描述

如何使MySQL JDBC通过SSL(带有X509证书验证)工作?

How one can make MySQL JDBC work over SSL (with X509 certificates validation)?

我有Using SSL for Secure Connections中的MySQL手册中所述的自创建证书，具体是:

I've got self-created certificates as described in MySQL manual, in Using SSL for Secure Connections, specifically:

# 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

发出GRANT ALL ON *.* TO vic@localhost IDENTIFIED BY '12345' REQUIRE X509;后，我可以通过命令行连接到MySQL:

After issuing GRANT ALL ON *.* TO vic@localhost IDENTIFIED BY '12345' REQUIRE X509; I am able to connect to MySQL over command-line:

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测试时，出现身份验证失败:Access denied for user 'vic'@'localhost' (using password: YES).代码如下:

However, when I try to run Java test, I get auth failure: Access denied for user 'vic'@'localhost' (using password: YES). Code follows:

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密钥库文件的方式:

And here's how I prepared Java keystore files:

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连接.然后执行以下代码

UPDATE I am able to connect over SSL via JDBC if I use 'root' user instead of 'vic'. Then following code

    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.

But I can't use root in production, and I wonder why JDBC uses AES128, whereas command-line mysql client uses AES256.

UPDATE2 在我将root@localhost的user表中的ssl_type更改为X509之后，请求客户端的完全身份验证，我得到的root行为与vic相同-可以不要通过JDBC登录.

UPDATE2 After I changed ssl_type to X509 in user table for root@localhost, requesting full auth of client, I get the same behavior for root as for vic -- can't login via JDBC.

UPDATE3 如果在GRANT语句中使用REQUIRE SSL而不是REQUIRE X509，则代码有效.是否可以使X509工作?

UPDATE3 If I use REQUIRE SSL instead of REQUIRE X509 in GRANT statement, code works. Is it possible to make X509 work?

推荐答案

最近在MariaDB JDBC驱动程序中添加了对自签名证书的支持(该驱动程序也可用于连接到MySQL).最新版本(在撰写本文时为1.1.3)还允许您在运行时直接指定服务器证书，从而无需预先配置密钥存储或导入证书.

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.

要设置的两个属性是useSSL和serverSslCert.后者可以是证书本身(字符串值)，也可以是包含证书的文件的路径(完整路径或相对于类路径):

The two properties to set are useSSL and 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):

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);

有关如何连接的完整工作示例，请参见此处: https://github.com/propssl/java-jdbc-mariadb

For a full working example of how to connect see here: https://github.com/properssl/java-jdbc-mariadb

这篇关于MySQL JDBC over SSL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！