MySQL创建用户带SSL认证，并且有SUBJECT和ISSUER的时候，报错[No_MySQL

MySQL创建用户带SSL认证，并且有SUBJECT和ISSUER的时候，报错[Note] X509 subject mismatch:解决

1 简单的SSL是OK的:

用简单的SSL的验证，分配帐号

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

然后在客户端登陆:

[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

缺陷，任何创建的ssl的key，只要匹配ca-cert.pem和client-cert.pem和client-key.pem3者之间匹配上，就可以用ssl登陆上db服务器，

就算这个client的key是否与server的可以一致，只要cliet的3个pem之间一致就可以通过ssl的方式登陆db server，这就有安全隐患。

所以我们需要加上subject和issuer来验证client和server端的key一致。

2 同事发给我的ssl的信息如下，我需要用已经生成的这2个来创建用户：

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

-- 但是加上subject和issuer的时候，就抱错如下：

先创建用户：

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

在客户端登陆：

[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日志保错如下：

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 看到client端的issuer和server端的issuer mismatch，所以为了测试成功，直接修改grant语句吧，再次进行测试，如下，drop user然后再grant帐号

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

客户端登陆mysql db server,依然报错如下：

[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.pemERROR 1045 (28000): Access denied for user 'test'@'XXnprdmydbctl.XXo.abn-iad.XX.com' (using password: YES)再check error日志  130722  9:29:15 [Note] X509 subject mismatch:   should be '/CN=nuc-bbbmysql-client.nucleus.XX.com/OU=XX Online/Pogo.com/O="Xxxxxxxxc Xxxx, Inc."/S=California/C=US'   but is '/C=US/ST=California/O=Xxxxxxxxc Xxxx, Inc./OU=XX Online/Pogo.com/CN=nuc-bbbmysql-client.nucleus.XX.com'

4 看到client与server的subject不一致，所以直接将提示error中的subject里面的替换下，再测试

 drop user，然后grant user；   drop user 'test'@'%';  GRANT all privileges ON *.* TO 'test'@'%'  IDENTIFIED BY 'test'  REQUIRE SUBJECT '/C=US/ST=California/O=Xxxxxxxxc Xxxx, Inc./OU=XX Online/Pogo.com/CN=nuc-bbbmysql-client.nucleus.XX.com'  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' ;   drop user 'test'@'%';  GRANT all privileges ON *.* TO 'test'@'%'  IDENTIFIED BY 'test'  REQUIRE SUBJECT '/C=US/ST=California/O=Xxxxxxxxc Xxxx, Inc./OU=XX Online/Pogo.com/CN=nuc-bbbmysql-client.nucleus.XX.com'  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' ;

然后在客户端登陆

[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。

然后觉得同事给我的subject和issuer有问题，跟同事在server端创建的server key有出入，

最后检查问题出在windown环境和linux环境之间的差异，同事给的一些参数是window下的，所以linux下不识别，比如email参数等。

不过这些也没有关系，我们只要关注error日志，看报错信息然后依据报错信息一步步调试，都可以确保功能测试通过。