MySQL資料庫創建用戶帶SSL認證,並且有SUBJECT和ISSUER的時候,報錯[Note] X509 subject mismatch:解決

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: 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.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 '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.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/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.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日志,看報錯信息然後依據報錯信息一步步調試,都可以確保功能測試通過。

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。