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.pem Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25139 Server version: 5.5.25a-log MySQL XX RelXXse Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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=US issuer: [email protected], 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 '/[email protected]/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.pem ERROR 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 '[email protected]/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/[email protected]'
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/[email protected]' ;
客戶端登陸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.pem ERROR 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/[email protected]' ; 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/[email protected]' ;
然後在客戶端登陸
[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.pem Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25289 Server version: 5.5.25a-log MySQL XX RelXXse Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clXXr the current input statement. mysql> mysql> mysql> mysql> mysql> exit Bye
OK，i did it。 然後覺得同事給我的subject和issuer有問題，跟同事在server端創建的server key有出入， 最後檢查問題出在windown環境和linux環境之間的差異，同事給的一些參數是window下的，所以linux下不識別，比如email參數等。 不過這些也沒有關系，我們只要關注error日志，看報錯信息然後依據報錯信息一步步調試，都可以確保功能測試通過。
