| Bug #68957 | JDBC SSL connection access denied exception | ||
|---|---|---|---|
| Submitted: | 14 Apr 2013 20:09 | Modified: | 26 Jun 2018 13:06 |
| Reporter: | Mohamed Nabil | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J Documentation | Severity: | S2 (Serious) |
| Version: | Connector J 5.1.23 | OS: | Any (JDBC ) |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
| Tags: | jdbc ssl access denied error exception jca jsse x509 | ||
[15 Apr 2013 9:07]
Alexander Soklakov
Hi Mohamed, Thank you for the report. Please provide additional info: - server version, - default-authentication-plugin value (if any), - openssl version
[15 Apr 2013 11:11]
Mohamed Nabil
Hi Alexander,
Thank you for the response.
required information:
--------------------------------------------------------------------------------
- server version:
> mysqld --version
mysqld Ver 5.6.10 for Win32 on x86 (MySQL Community Server (GPL))
> mysql --version
mysql Ver 14.14 Distrib 5.6.10, for Win32 (x86)
--------------------------------------------------------------------------------
- openssl version: ==> version comes with Tomcat 7.0.39 tomcat-native-1.1.27-win32-bin.zip
> openssl version
OpenSSL 1.0.1d 5 Feb 2013
--------------------------------------------------------------------------------
- default-authentication-plugin value (if any):
mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_TYPE='AUTHENTICATION'\G
*************************** 1. row ***************************
PLUGIN_NAME: mysql_native_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Native MySQL authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
*************************** 2. row ***************************
PLUGIN_NAME: mysql_old_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Old MySQL-4.0 authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
*************************** 3. row ***************************
PLUGIN_NAME: sha256_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Oracle
PLUGIN_DESCRIPTION: SHA256 password authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
3 rows in set (0.01 sec)
Also,
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.10, for Win32 (x86)
Connection id: 1
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Using delimiter: ;
Server version: 5.6.10-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 50050
Uptime: 3 hours 15 min 27 sec
Threads: 2 Questions: 36 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.003
--------------
Also,
mysql> SHOW STATUS LIKE 'ssl%';
+--------------------------------+--------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------+
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | DHE-RSA-AES256-SHA |
| Ssl_cipher_list | DHE-RSA-AES256-SHA |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 500 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | Apr 6 14:40:34 2023 GMT |
| Ssl_server_not_before | Apr 8 14:40:34 2013 GMT |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | TLSv1 |
+--------------------------------+--------------------------+
25 rows in set (0.00 sec)
[15 Apr 2013 12:09]
Alexander Soklakov
Thanks Mohamed, It's still unclear what plugin used for this user, please provide results of the next query: select Host, Password, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string from mysql.user where user='user_name'
[15 Apr 2013 13:13]
Mohamed Nabil
Thank you: ------------------------- select Host, Password, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string from mysql.user where user='user_name' ------------------------- Host Password ssl_type ssl_cipher x509_issuer x509_subject plugin authentication_string -------------------------------------------------------------------------------------------------------------------------- 127.0.0.1 *3894... SPECIFIED NULL BLOB NULL mysql_native_password localhost *3894... SPECIFIED NULL BLOB NULL mysql_native_password x509_issuer BLOB is => /C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=Name/emailAddress=email
[16 Apr 2013 10:05]
Mohamed Nabil
Hi Alexander,
I've made some trials and finally get to these configurations at end, I even tried ""wrong"" certificates, issuer and subject to be sure that mysql tool is working properly and yes mysql tool refused connection as expected, and when ""corrected"" certificates issuer and subject every thing worked fine, then the user_name is completely and correctly set up!!!
I've even copy/past issuer and subject by calling
> openssl x509 -text -in client-cert.pem
> openssl x509 -noout -in client-cert.pem -issuer
> openssl x509 -noout -in client-cert.pem -subject
----------------------------------------------------------------
** Problem still exists
** com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 608 milliseconds ago. The last packet sent successfully to the server was 593 milliseconds ago.
------------------------------------------------------------------
User Creation and Privileges
--------------------------------
use mysql;
DROP USER 'user_name'@'localhost';
DROP USER 'user_name'@'127.0.0.1';
flush privileges;
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'pass_word';
GRANT ALL PRIVILEGES ON algadwal.* TO 'user_name'@'localhost' IDENTIFIED BY 'pass_word' REQUIRE X509;
GRANT ALL PRIVILEGES ON algadwal.* TO 'user_name'@'localhost' IDENTIFIED BY 'pass_word' REQUIRE
ISSUER '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=CA Cert/emailAddress=email'
AND SUBJECT '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=Client Cert/emailAddress=emails'
AND CIPHER 'DHE-RSA-AES256-SHA';
CREATE USER 'user_name'@'127.0.0.1' IDENTIFIED BY 'pass_word';
GRANT ALL PRIVILEGES ON algadwal.* TO 'user_name'@'127.0.0.1' IDENTIFIED BY 'pass_word' REQUIRE X509;
GRANT ALL PRIVILEGES ON algadwal.* TO 'user_name'@'127.0.0.1' IDENTIFIED BY 'pass_word' REQUIRE
ISSUER '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=CA Cert/emailAddress=email'
AND SUBJECT '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=Client Cert/emailAddress=emails'
AND CIPHER 'DHE-RSA-AES256-SHA';
# ssl_type is 'SPECIFIED' after executing above quries
# just updated it by hand to be sure it is X509 correctly
# even though mysq client work fine with both 'SPECIFIED' or 'X509'
#update mysql.user set ssl_type='X509' where user='user_name';
flush privileges;
select * from user;
----------------------------------------------------------------------------------
Keytool and Openssl ==> using same client certificates at [client] in my.cnf file
--------------------
echo " >>>>>> Import CA to Key store"
%KEY_TOOL% -import -alias mysqlServerCACert -file %WORK_DIR%/ca-cert.pem -keystore %TRST_STORE% -storepass pass_word -storetype JKS
@REM THIS
@echo " >>>>>> Change Cert to DER format "
%WORK_DIR%/openssl.exe x509 -outform DER -in %WORK_DIR%/client-cert.pem -out %WORK_DIR%/client.cert
@REM AND THIS
@REM echo " >>>>>> Add Cert to Key Store"
%KEY_TOOL% -import -file %WORK_DIR%/client.cert -keystore %KEY_STORE% -alias mysqlClientCertificate -storepass pass_word -storetype JKS
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
JDBC program
-------------------
mysqlDS = new MysqlDataSource();
mysqlDS.setUseSSL(true);
mysqlDS.setRequireSSL(true);
mysqlDS.setClientCertificateKeyStoreUrl("file:/D:/mysql/cert/keystore");
mysqlDS.setClientCertificateKeyStorePassword("password");
mysqlDS.setTrustCertificateKeyStoreUrl("file:/D:/mysql/cert/truststore");
mysqlDS.setTrustCertificateKeyStorePassword("password");
mysqlDS.setServerName("DATABSE_SERVER_IP");
mysqlDS.setPort(DATABSE_SERVER_PORT);
mysqlDS.setDatabaseName("DATABSE_NAME");
connection = mysqlDS.getConnection("DATABSE_USER_NAME", "DATABSE_USER_PASSWORD");
[16 Apr 2013 15:01]
Mohamed Nabil
I;ve JDK 7u_17 When Tracking JSSE out put I discovered Case #1 below and that some TLS cipher suites are unavailable including "TLS_DHE_RSA_WITH_AES_256_CBC_SHA" the suite represents Mysql 5.6 cipher suite "DHE-RSA-AES256-SHA" Documentation states that ** 5.5. Connecting Securely Using SSL ** For SSL support to work, you must have the following: ** A JDK that includes JSSE (Java Secure Sockets Extension), like JDK-1.4.1 or newer JDK 7 includes JSSE but after updating JCE I got Case #2 I've updated it from http://www.oracle.com/technetwork/java/javase/downloads/index.html and replaced local_policy.jar & US_export_policy.jar Now I've Case #2 below but now getting java.sql.SQLException: Access denied for user 'user_name'@'localhost' (using password: YES) ## Still Investigating !! --------------------------------------------------------------------- Case #1 before updating JCE --------------------------------------------------------------------- Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA256 Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256 --------------------------------------------------------------------- Case #1 after updating JCE --------------------------------------------------------------------- Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256
[16 Apr 2013 20:15]
Mohamed Nabil
What I can't resolve is this, how mysql client accepts user defined as X509, Issuer & Subject from command line successfully and rejects the same user if connected over JDBC I think that JDBC shall have same behavior as mysql client command line tool, right? or other wise different behaviors are considered an error in either mysql client or JDBC, right? also openssl x509 -text -in ca-cert.pem openssl x509 -text -in client-cert.pem return same Authority Key Identifier & Subject Key Identifier as returned AuthorityKeyIdentifier & SubjectKeyIdentifier by %KEY_TOOL% -list -v -keystore truststore %KEY_TOOL% -list -v -keystore keystore
[16 Apr 2013 20:27]
Mohamed Nabil
Validate User Definition and Certificates are correct by using MySQL server 5.6.10 and Mysql Workbench version 5.2.47CE
Attachment: Mysql_5.6.10_Workbench_5.2.47CE_Test_SSL.png (image/png, text), 72.71 KiB.
[16 Apr 2013 20:37]
Mohamed Nabil
Reruning Query ------------------------- Host Password ssl_type ssl_cipher x509_issuer x509_subject plugin authentication_string -------------------------------------------------------------------------------------------------------------------------- localhost *6BB4... SPECIFIED BLOB BLOB BLOB mysql_native_password 127.0.0.1 *6BB4... SPECIFIED BLOB BLOB BLOB mysql_native_password ::1 *6BB4... SPECIFIED BLOB BLOB BLOB mysql_native_password ------------ ** BLOBs: ------------ ssl_cipher => DHE-RSA-AES256-SHA x509_issuer => '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=CA Cert/emailAddress=email' x509_subject => '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=Client Cert/emailAddress=emails'
[17 Apr 2013 8:48]
Mohamed Nabil
I can (( guess, only guess since no server log message can explain the situation )) that JDBC doesn't connect user privileged with X509, Issuer, Subject parameters 1st- developer goes for MySQL documentation securing MySQL server user access with SSL and choose X509 with Issuer, Subject parameters for maximum security 2n- developer goes for Connector/J documentation to use SSL as described on documentation, that doesn't mention what type of SSL a user must be defiened at first place! 3rd- developer assumes that JDBC will connect his X509, Issuer, Subject user, since all clients must have similar behavior on similar situations. 4th- the reality that JDBC connect users granted REQUIRES SSL and doesn't connect users with REQUIER X509, but this is left to developer to conclude by his own self! I would appreciate if Connector J documentation updates with this situation to save developers time.
[17 Apr 2013 9:04]
Alexander Soklakov
Hi Mohamed, Cooking keystore for openssl certs is a bit tricky. You can follow these references for more details: http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-using-ssl.html http://softteco.blogspot.ru/2010/05/create-java-keystore-file-from-existing.html I was able to connect after this: keytool -import -alias mysqlCACert -file ca-cert.pem -keystore truststore -storepass 111111 openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -certfile client-cert.pem -name "Name" -out client.p12 keytool -importkeystore -srckeystore client.p12 -srcstoretype pkcs12 -destkeystore keystore Java code: MysqlDataSource mysqlDS = new MysqlDataSource(); mysqlDS.setUseSSL(true); mysqlDS.setRequireSSL(true); mysqlDS.setClientCertificateKeyStoreUrl("file:/home/say/4/keystore"); mysqlDS.setClientCertificateKeyStorePassword("222222"); mysqlDS.setTrustCertificateKeyStoreUrl("file:/home/say/4/truststore"); mysqlDS.setTrustCertificateKeyStorePassword("111111"); mysqlDS.setServerName("localhost"); mysqlDS.setPort(3306); mysqlDS.setDatabaseName("test"); Connection connection = mysqlDS.getConnection("user_name", "password"); System.out.println(connection.getClass().getName()); ResultSet rs = connection.createStatement().executeQuery("select current_user()"); if (rs.next()) { System.out.println(rs.getString(1)); } it's output: com.mysql.jdbc.ConnectionImpl user_name@localhost So it's not a c/J bug, but please report that you are able to resolve the problem.
[17 Apr 2013 10:27]
Mohamed Nabil
Hi Alexander,
Now, different clients have same behavior against same server
-----------
** Output:
-----------
URL jdbc:mysql://127.0.0.1:50050/database_name
Driver Version mysql-connector-java-5.1.23 ( Revision: ${bzr.revision-id} )
Connected...
--------------
I do report that I am able to resolve the problem
I do admit that it's not a c/J bug :)
Hope a documentation update is made to reflect this openssl certs keystore tricky Cooking for both REQUIRE SSL and REQUIRE X509 user types
I've posted a comment about JCE update because I'm sure as surfing the web that many brother developers around web can't figure out how to resolve the cause of exception Communications link failure I hope if this note written in documentation too
http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-using-ssl.html#c12626
Thank you
Mohammad
[17 Apr 2013 11:43]
Alexander Soklakov
Thanks Mohammad! The fastest way to change documentation is to put another one comment here http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-using-ssl.html, you know, all MySQL documentation contains the half of valuable info in comments. Of course it's not because it should be so. Just it is. But if you want the main doc to be updated you should fill the bug (feature request) in Connector/J * Documentation category, or change category of this report.
[17 Apr 2013 13:28]
Mohamed Nabil
Thanks Alexander comment posted http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-using-ssl.html
[1 Jan 2018 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[26 Jun 2018 13:06]
Alexander Soklakov
Documentation was fixed some time ago under other task, see https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html So, I close this report.
Description: JDBC can't establish SSL connection, throws exception java.sql.SQLException: Access denied for user 'user_name'@'localhost' (using password: YES) Mysql SSL certificates are installed correctly: mysql client can connect user with X509 privileges, and also mysql client refuse to connect user with X509 privileges after removing SSL configurations from [client] section at my.cnf file ********************************** ** Create User for SSL connection ********************************** CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password'; grant all privileges on database.* to 'user_name'@'localhost' WITH GRANT OPTION; grant all privileges on database.* to 'user_name'@'localhost' REQUIRE X509; grant all privileges on database.* to 'user_name'@'localhost' REQUIRE ISSUER '/C=FI/ST=State/L=City/O=Organization/OU=Organization Unit/CN=Name/emailAddress=emails'; flush privileges; ********************************** ** configuration file my.cnf ********************************** [client] ssl=ON ssl-ca=D:/mysq/cert/ca-cert.pem ssl-cert=D:/mysql/cert/client-cert.pem ssl-key=D:/mysql/cert/client-key.pem ********************************** ** key trust / key store ********************************** echo " >>>>> Import CA to trust store " keytool -import -alias mysqlServerCACert -file D:/mysql/cert/ca-cert.pem -keystore D:/mysql/cert/truststore echo " >>>>> Change Cert to DER format " openssl.exe x509 -outform DER -in D:/mysql/cert/client-cert.pem -out D:/mysql/cert/client.cert echo " >>>>> Add Cert to Key Store " keytool -import -file D:/mysql/cert/client.cert -alias mysqlClientCertificate -keystore D:/mysql/cert/keystore How to repeat: mysqlDS = new MysqlDataSource(); //Remove the comment to get the exception /* mysqlDS.setUseSSL(true); mysqlDS.setRequireSSL(true); mysqlDS.setClientCertificateKeyStoreUrl("file:/D:/mysql/cert/keystore"); mysqlDS.setClientCertificateKeyStorePassword("password"); mysqlDS.setTrustCertificateKeyStoreUrl("file:/D:/mysql/cert/truststore"); mysqlDS.setTrustCertificateKeyStorePassword("password"); mysqlDS.setServerName("DATABSE_SERVER_IP"); mysqlDS.setPort(DATABSE_SERVER_PORT); mysqlDS.setDatabaseName("DATABSE_NAME"); */ connection = mysqlDS.getConnection("DATABSE_USER_NAME", "DATABSE_USER_PASSWORD"); Suggested fix: No suggested fix since there is no verbose error returned to client in order to trace problem roots