MySQL创建用户带SSL认证,并且有SUBJECT和ISSUER的时

页面导航:首页 > 数据库 > Mysql > MySQL创建用户带SSL认证,并且有SUBJECT和ISSUER的时

MySQL创建用户带SSL认证,并且有SUBJECT和ISSUER的时

来源: 作者: 时间:2016-02-18 10:28 【

MySQL创建用户带SSL认证,并且有SUBJECT和ISSUER的时候,报错[Note] X509 subject mismatch:解决1 简单的SSL是OK的:用简单的SSL的验证,分配帐号mysql GRANT ALL PRIVILEGES ON test.* TO #39;test...
MySQL创建用户带SSL认证,并且有SUBJECT和ISSUER的时候,报错[Note] X509 subject mismatch:解决
 
1 简单的SSL是OK的:
用简单的SSL的验证,分配帐号
> GRANT ALL PRIVILEGES ON test.* TO 'test'@%· IDENTIFIED BY 'test'REQUIRE SSL;

 

然后在客户端登陆:
[[email protected] 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,  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=, 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=/CN="Xxxxxxxxc Xxxx, In
  c CA"/OU=XX Online/Pogo.com/O="Xxxxxxxxc Xxxx, Inc."/L=Redwood City/S=California/C=US';

 

在客户端登陆:
[[email protected] 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=/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='

 

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

 

 
客户端登陆mysql db server,依然报错如下:
[[email protected] 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=' ;   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=' ;

 

 
 然后在客户端登陆
[[email protected] 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日志,看报错信息然后依据报错信息一步步调试,都可以确保功能测试通过。
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<