When a MySQL User is created with SSL authentication and has SUBJECT and ISSUER, the error [No_MySQL

When a MySQL User is created with SSL authentication and has SUBJECT and ISSUER, the following error occurs: [Note] X509subjectmismatch: resolving bitsCN.com

When a MySQL User is created with SSL authentication and has SUBJECT and ISSUER, the error [Note] X509 subject mismatch: Solved

1 Simple SSL is OK:

Use simple SSL verification to allocate accounts

mysql> GRANT ALL PRIVILEGES ON test.* TO 'test'@%· IDENTIFIED BY 'test'REQUIRE SSL;

Then log on to the client:

[aaaaaaaaaaa@XXnintmydbc000ctl ssl]$   /opt/mysql/product/mysql/bin/mysql -hXXcccmysql.abn-iad.XX.com -utest -ptest --ssl-ca=/home/aaaaaaaaaaa/ssl/ca-cert.pem --ssl-cert=/home/aaaaaaaaaaa/ssl/server-cert.pem --ssl-key=/home/aaaaaaaaaaa/ssl/server-key.pemWelcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 25139Server version: 5.5.25a-log MySQL XX RelXXseCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clXXr the current input statement.mysql> show grants;+--------------------------------------------------------------------------------------------------------------------------------------------+| Grants for test@%                                                                                                                          |+--------------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' REQUIRE SSL WITH GRANT OPTION |+--------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> exit

Defect, any key for creating ssl, as long as it matches a match between the ca-cert.pem and the client-cert.pem and the client-key.pem3, you can use ssl to log on to the db server,

Even if the key of the client is consistent with that of the server, as long as the three pem of the cliet are consistent, you can log on to the db server through ssl, which poses a security risk.

Therefore, we need to add subject and issuer to verify that the client and server have the same key.

2. the ssl information sent to me by my colleagues is as follows. I need to use the generated two to create users:

subject: CN=nuc-bbbmysql-client.nucleus.XX.com, OU=XX Online/Pogo.com, O="Xxxxxxxxc Xxxx, Inc.", S=California, C=USissuer: E=wwtso-ssl-admins@XX.com, CN="Xxxxxxxxc Xxxx, Inc CA", OU=XX Online/Pogo.com, O="Xxxxxxxxc Xxxx, Inc.", L=Redwood City, S=California, C=US

-- When subject and issuer are added, the following error occurs:

Create a user first:

GRANT all privileges ON *.* TO 'sss'@'localhost'  IDENTIFIED BY 'goodsecret'  REQUIRE SSL and SUBJECT '/CN=nuc-bbbmysql-admin.nucleus.XX.com/OU=XX Online/Pogo.com/O="Xxxxxxxxc Xxxx, Inc."/S=California/C=US'  and issuer '/E=wwtso-ssl-admins@XX.com/CN="Xxxxxxxxc Xxxx, In  c CA"/OU=XX Online/Pogo.com/O="Xxxxxxxxc Xxxx, Inc."/L=Redwood City/S=California/C=US';

Log on to the client:

[aaaaaaaaaaa@XXnintmydbc000ctl ssl]$   /opt/mysql/product/mysql/bin/mysql -hXXcccmysql.abn-iad.XX.com -utest -ptest --ssl-ca=/home/aaaaaaaaaaa/ssl/ca-cert.pem --ssl-cert=/home/aaaaaaaaaaa/ssl/server-cert.pem --ssl-key=/home/aaaaaaaaaaa/ssl/server-key.pemERROR 1045 (28000): Access denied for user 'test'@'XXnintmydbc000ctl.abn-iad.XX.com' (using password: YES)

Db server error log saving error:

130722  9:25:04 [Note] X509 issuer mismatch: should be 'E=wwtso-ssl-admins@XX.com/CN="Xxxxxxxxc Xxxx, Inc CA"/OU=XX Online/Pogo.com/O="Xxxxxxxxc Xxxx, Inc."/L=Redwood City/S=California/C=US' but is '/C=US/ST=California/L=Redwood City/O=Xxxxxxxxc Xxxx, Inc./OU=XX Online/Pogo.com/CN=Xxxxxxxxc Xxxx, Inc CA/emailAddress=wwtso-ssl-admins@XX.com'

3. check the issuer mismatch on the client side and the issuer mismatch on the server side. Therefore, modify the grant statement to make the test successful. perform the test again as follows: drop the user and grant the account.

  drop user 'test'@'%';  GRANT all privileges ON *.* TO 'test'@'%'  IDENTIFIED BY 'test'  REQUIRE SUBJECT '/CN=nuc-bbbmysql-client.nucleus.XX.com/OU=XX Online/Pogo.com/O="Xxxxxxxxc Xxxx, Inc."/S=California/C=US'  and issuer '/C=US/ST=California/L=Redwood City/O=Xxxxxxxxc Xxxx, Inc./OU=XX Online/Pogo.com/CN=Xxxxxxxxc Xxxx, Inc CA/emailAddress=wwtso-ssl-admins@XX.com' ;

When the client logs on to the mysql db server, the following error is still reported:

[Ddddmysqlprd @ XXnprdmydbctl client-cert] $/opt/mysql/product/mysql/bin/mysql plugin-utest-ptest -- ssl-ca =/home/ddddddmysqlprd/client-cert/ ca-cert.pem -- ssl-cert =/home/ddddmysqlprd/client-cert/client-cert.pem -- ssl-key =/home/ddddddmysqlprd/client-cert/client-key.pemERROR 1045 (28000): Access denied for user 'test' @ 'xxnprdmydbctl .XXo.abn-iad.XX.com '(using password: YES) check error log 130722 9:29:15 [Note] X509 subject mismatch: shoshould be '/CN = nuc-bbbmysql-client.nucleus us.xx.com/ouw.xx Online/Pogo.com/O= "xxxxxxc Xxxx, Inc. "/S = California/C = US 'but is'/C = US/ST = California/O = Xxxxxxxxc Xxxx, Inc. /OU = XX Online/pogo.com/cnw.nuc-bbbmysql-client.nucleus us.xx.com'

4. the client and the server subject are inconsistent. Therefore, replace the subject in the error message and test again.

Drop user, then grant user; drop user 'test' @ '%'; GRANT all privileges ON *. * TO 'test' @ '%' identified by 'test' require subject '/C = US/ST = California/O = xxxxxxc Xxxx, Inc. /OU = XX Online/pogo.com/cnw.nuc-bbbmysql-client.nucleus.xx.com' and issuer '/C = US/ST = California/L = Redwood City/O = Xxxxxxxxc Xxxx, Inc. /OU = XX Online/pogo.com/cn1_xxxxxxxxc Xxxx, Inc CA/emailAddress = wwtso-ssl-admins@XX.com '; drop user 'test' @' % '; GRANT all privileges ON *. * TO 'test' @ '%' identified by 'test' require subject '/C = US/ST = California/O = xxxxxxc Xxxx, Inc. /OU = XX Online/pogo.com/cnw.nuc-bbbmysql-client.nucleus.xx.com' and issuer '/C = US/ST = California/L = Redwood City/O = Xxxxxxxxc Xxxx, Inc. /OU = XX Online/pogo.com/cnw.xxxxxxxxc Xxxx, Inc CA/emailAddress = wwtso-ssl-admins@XX.com ';

Then log on to the client

[ddddmysqlprd@XXnprdmydbctl client-cert]$   /opt/mysql/product/mysql/bin/mysql -hXXcccmysql.abn-iad.XX.com -utest -ptest --ssl-ca=/home/ddddmysqlprd/client-cert/ca-cert.pem --ssl-cert=/home/ddddmysqlprd/client-cert/client-cert.pem --ssl-key=/home/ddddmysqlprd/client-cert/client-key.pemWelcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 25289Server version: 5.5.25a-log MySQL XX RelXXseCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clXXr the current input statement.mysql> mysql> mysql> mysql> mysql> exitBye

OK, I did it.

Then I felt that my subject and issuer were faulty, and they were different from the server key created by my colleagues on the server,

Finally, check whether the problem lies in the difference between the runtime WN environment and the linux environment. some parameters provided by colleagues are in windows, so they are not identified in linux, such as email parameters.

However, this does not matter. we only need to pay attention to the error log, view the error information, and debug it step by step based on the error information, so that the function test is successful.

BitsCN.com