title: MySQL JDBC over SSL problem
body: <p>How one can make MySQL JDBC work over SSL (with X509 certificates validation)?  </p>
  
  <p>I've got self-created certificates as described in MySQL manual, in <code>Using SSL for Secure Connections</code>, specifically:</p>
  
  <pre><code># Create CA certificate
  shell&gt; openssl genrsa 2048 &gt; ca-key.pem
  shell&gt; openssl req -new -x509 -nodes -days 1000 \
           -key ca-key.pem &gt; ca-cert.pem
  
  # Create server certificate
  shell&gt; openssl req -newkey rsa:2048 -days 1000 \
           -nodes -keyout server-key.pem &gt; server-req.pem
  shell&gt; openssl x509 -req -in server-req.pem -days 1000 \
           -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 &gt; server-cert.pem
  
  # Create client certificate
  shell&gt; openssl req -newkey rsa:2048 -days 1000 \
           -nodes -keyout client-key.pem &gt; client-req.pem
  shell&gt; openssl x509 -req -in client-req.pem -days 1000 \
           -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 &gt; client-cert.pem
  </code></pre>
  
  <p>After issuing <code>GRANT ALL ON *.* TO vic@localhost IDENTIFIED BY '12345' REQUIRE X509;</code> I am able to connect to MySQL over command-line:</p>
  
  <pre><code>mysql -u vic -p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem mysql
  ...
  
  mysql&gt;  SHOW STATUS LIKE 'Ssl_cipher';
  +---------------+--------------------+
  | Variable_name | Value              |
  +---------------+--------------------+
  | Ssl_cipher    | DHE-RSA-AES256-SHA |
  +---------------+--------------------+
  </code></pre>
  
  <p>However, when I try to run Java test, I get auth failure: <code>Access denied for user 'vic'@'localhost' (using password: YES)</code>. Code follows:</p>
  
  <pre><code>public class Launcher {
      public static void main(String[] args) throws DbException, SQLException, ClassNotFoundException {
          StringBuffer sb = new StringBuffer("jdbc:mysql://localhost/bt?useSSL=true&amp;");
          sb.append("user=vic&amp;password=12345&amp;");
          sb.append("clientCertificateKeyStorePassword=123456&amp;");
          sb.append("clientCertificateKeyStoreType=JKS&amp;");
          sb.append("clientCertificateKeyStoreUrl=file:///home/vic/tmp/client-keystore&amp;");
          sb.append("trustCertificateKeyStorePassword=123456&amp;");
          sb.append("trustCertificateKeyStoreType=JKS&amp;");
          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();
      }
  }
  </code></pre>
  
  <p>And here's how I prepared Java keystore files:</p>
  
  <pre><code>keytool -import -alias mysqlServerCACert -file ca-cert.pem -keystore ca-keystore
  keytool -import -file client-cert.pem -keystore client-keystore -alias client-key
  </code></pre>
  
  <p><strong>UPDATE</strong> I am able to connect over SSL via JDBC if I use 'root' user instead of 'vic'. Then following code</p>
  
  <pre><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));
      }
  </code></pre>
  
  <p>prints</p>
  
  <pre><code>Ssl_cipher
  DHE-RSA-AES128-SHA
  </code></pre>
  
  <p>But I can't use root in production, and I wonder why JDBC uses AES128, whereas command-line mysql client uses AES256.</p>
  
  <p><strong>UPDATE2</strong> After I changed <code>ssl_type</code> to <code>X509</code> in <code>user</code> table for <code>root@localhost</code>, requesting full auth of client, I get the same behavior for root as for vic -- can't login via JDBC.</p>
  
  <p><strong>UPDATE3</strong> If I use <code>REQUIRE SSL</code> instead of <code>REQUIRE X509</code> in <code>GRANT</code> statement, code works. Is it possible to make X509 work?</p>