How can I verify I'm using SSL to connect to mysql?

Question

I have configured my server to allow SSL, and have modified my client ~/.my.cnf so I use SSL:

[client]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=~/certs/ca-cert.pem

When I log in with my client and view the status, it lists a cipher on the SSL line:

mysql> \s
--------------
SSL:            Cipher in use is DHE-RSA-AES256-SHA

Without installing something like wireshark to verify that the connection is secure, can I assume that I'm connecting via SSL based on this information?

Answer 1

From the client, just run status. If this connection is using SSL, you'll get something interesting in the SSL row.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

Connection id:      12
Current database:
Current user:       replicator@domU-12-31-39-10-54-BD.compute-1.internal
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.30-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     boston.hugskeep.wstudent.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         44 min 49 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.005
--------------

mysql>

If this connection is not using SSL, you'll get:

SSL:            Not in use

You can also use:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

But I think the first is more attractive, and sure easier to type.

Answer 2

When it comes to MySQL user authentication, you need to use a MySQL user that has SSL defined

mysql> select column_name,column_type from information_schema.columns
    -> where table_schema='mysql' and table_name='user'
    -> and column_name like 'ssl%';
+-------------+-----------------------------------+
| column_name | column_type                       |
+-------------+-----------------------------------+
| ssl_type    | enum('','ANY','X509','SPECIFIED') |
| ssl_cipher  | blob                              |
+-------------+-----------------------------------+
2 rows in set (0.01 sec)

mysql>

These columns should have values in them for any user allowed to login via SSL

If you login with a specific user, please run this query

SELECT
    A.*,B.ssl_type,
    IFNULL(B.ssl_cipher,'Undefined') ssl_cipher
FROM
(
    SELECT
        SUBSTR(cu,1,LOCATE('@',cu) - 1) user,
        SUBSTR(cu,LOCATE('@',cu) + 1) host
    FROM
        (SELECT CURRENT_USER() cu) AA
) A
INNER JOIN mysql.user B USING (user,host);

This will quickly tell you if you are connecting as an SSL MySQL client or not.

If a particular user needs SSL defined, use the GRANT command to defined SSL options.