Bug #52596 ssl_cipher_list not set or returned for "SHOW STATUS LIKE 'Ssl_cipher_list'"
Submitted: 5 Apr 2010 14:57 Modified: 19 Apr 2011 16:24
Reporter: Leandro Morgado Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.37sp1, 5.5.2-m2 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[5 Apr 2010 14:57] Leandro Morgado
Description:
If I set ssl-cipher in my.cnf, I would expect the server to limit the types of ciphers clients can connect with. This is reflected in the STATUS variable: ssl_cipher_list

This worked fine in the tests I did with 5.0.72  (64bit CentOS 5.4). I then tested, 5.1.37sp1 5.5.2-m2 on the same server and saw that ssl-cipher is ignored. 

This may be a related with bug:

  http://bugs.mysql.com/bug.php?id=30593

The original bug report said:

"The query "SHOW STATUS LIKE 'Ssl_cipher_list'" should return a list of available ciphers if client is connected via SSL - but it does only return the empty list."

How to repeat:
Setup the server with the following my.cnf options:

[mysqld]
...
ssl-ca=/home/user/sandboxes/msb_5_0_72/newcerts/ca-cert.pem
ssl-cert=/home/user/sandboxes/msb_5_0_72/newcerts/server-cert.pem
ssl-key=/home/user/sandboxes/msb_5_0_72/newcerts/server-key.pem
ssl-cipher=AES128-SHA

I will attach a tarball with the certificates in the next post.

Create a SSL user with:

mysql> GRANT ALL ON *.* TO foo@localhost IDENTIFIED BY 'msandbox' REQUIRE SSL;

Then connect with (adjust certificate path below if necessary):

shell> mysql --ssl-ca=newcerts/ca-cert.pem --ssl-cert=newcerts/client-cert.pem --ssl-key=newcerts/client-key.pem -ufoo -pmsandbox

On the working versions I get: 
=======================================================
[user@www msb_5_0_72]$ grep ssl-cipher my.sandbox.cnf 
ssl-cipher=AES128-SHA

[user@www msb_5_0_72]$ ./my sql --ssl-ca=newcerts/ca-cert.pem --ssl-cert=newcerts/client-cert.pem --ssl-key=newcerts/client-key.pem -ufoo -pmsandbox
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.72-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {foo} ((none)) > SHOW STATUS LIKE "ssl_cipher%"\G
*************************** 1. row ***************************
Variable_name: Ssl_cipher
        Value: AES128-SHA
*************************** 2. row ***************************
Variable_name: Ssl_cipher_list
        Value: AES128-SHA
2 rows in set (0.00 sec)
=======================================================

On 5.1.37sp1 and 5.5.2-m2, they don't seem to work:
=======================================================
[user@www msb_5_1_37]$ grep ssl-cipher my.sandbox.cnf 
ssl-cipher=AES128-SHA
[user@www msb_5_1_37]$ ./my sql --ssl-ca=newcerts/ca-cert.pem --ssl-cert=newcerts/client-cert.pem --ssl-key=newcerts/client-key.pem -ufoo -pmsandbox
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37sp1-enterprise-gpl-pro-log MySQL Enterprise Server - Pro Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {foo} ((none)) > SHOW STATUS LIKE "ssl_cipher%"\G
*************************** 1. row ***************************
Variable_name: Ssl_cipher
        Value: DHE-RSA-AES256-SHA
*************************** 2. row ***************************
Variable_name: Ssl_cipher_list
        Value: DHE-RSA-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:AES128-SHA:AES256-RMD:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA
2 rows in set (0.00 sec)

[user@www msb_5_5_2]$ grep ssl-cipher my.sandbox.cnf ssl-cipher=RC4-MD5
[user@www msb_5_5_2]$ ./my sql --ssl-ca=newcerts/ca-cert.pem --ssl-cert=newcerts/client-cert.pem --ssl-key=newcerts/client-key.pem -ufoo -pmsandbox
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.2-m2 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {foo} ((none)) > SHOW STATUS LIKE "ssl_cipher%"\G
*************************** 1. row ***************************
Variable_name: Ssl_cipher
        Value: DHE-RSA-AES256-SHA
*************************** 2. row ***************************
Variable_name: Ssl_cipher_list
        Value: DHE-RSA-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:AES128-SHA:AES256-RMD:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA
2 rows in set (0.00 sec)
=======================================================

Suggested fix:
Make ssl-cipher limit the types of accepted ciphers and have ssl_cipher and ssl_cipher_list updated accordingly.
[10 Jul 2010 5:42] Shane Bester
bug #55144 is a duplicate of this
[19 Apr 2011 16:24] Paul Dubois
Noted in 5.1.57, 5.5.11, 5.6.3 changelogs.

For a client connected using SSL, the Ssl_cipher_list status variable
was empty and did not show the possible cipher types. 

CHANGESET - http://lists.mysql.com/commits/132818