In this blog post, we’ll determine a MySQL connection using SSL… or not.
Since MySQL 5.7.5 the server generates SSL certificates (see auto_generate_certs) by default if compiled with SSL, or uses mysql_ssl_rsa_setup if compiled with YaSSL.
But how can we check to see if our MySQL client connection uses SSL?
When using an interactive client, it’s easy! You have two options:
1. Check the status(s):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> s -------------- mysql Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using EditLine wrapper Connection id: 7 Current database: Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.11-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 36 min 33 sec |
As you can see, for that connection, we are indeed using SSL: SSL: Cipher in use is DHE-RSA-AES256-SHA
2. Use the status variables Ssl_version and Ssl_cipher:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> show status like 'Ssl_version'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Ssl_version | TLSv1.1 | +---------------+---------+ mysql> show status like 'Ssl_cipher'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+ |
But is there a way to verify this on all the connections? For example, if we have some applications connected to our database server, how do we verify which connections are using SSL and which are not?
Yes, there is a solution: Performance_Schema!
This is how:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> SELECT sbt.variable_value AS tls_version, t2.variable_value AS cipher, processlist_user AS user, processlist_host AS host FROM performance_schema.status_by_thread AS sbt JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id WHERE sbt.variable_name = 'Ssl_version' and t2.variable_name = 'Ssl_cipher' ORDER BY tls_version; +-------------+--------------------+------+-----------+ | tls_version | cipher | user | host | +-------------+--------------------+------+-----------+ | | | root | localhost | | TLSv1 | DHE-RSA-AES256-SHA | root | localhost | | TLSv1.1 | DHE-RSA-AES256-SHA | root | localhost | +-------------+--------------------+------+-----------+ |
That’s it. Isn’t that easy? 😉
Actually there are three ways, the sys schema comes with a view like above already packaged:
https://dev.mysql.com/doc/refman/5.7/en/sys-session-ssl-status.html
https://github.com/mysql/mysql-sys/blob/master/views/p_s/session_ssl_status.sql
Hi Mark,
Thank you for your quick reply, indeed sys schema is very useful, hopefully it’s now included by default 😉
Thank you again to have pointed it here.
Yep, there by default since 5.7.7..
Hi Frederic,
That “s” in the MySQL command line doesn’t work on any of the servers that we have tried. What could be the problem? Can you confirm it is really “s”?
Fadi,
The “s” is actually preceded by a backslash, which often gets mangled in HTML/blog authoring software. Here’s a link to the manual page which describes this command for mysql client, among others:
https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html
I am on RDS using my ‘system’ account mysql version 5.7.19. When I try your query or the sys.session_ssl_status I get an empty list. I also come up empty with the base tables, they show no rows.
my grants look like this from the show grants statement:
Grants for my-user@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO ‘my-user’@’%’ WITH GRANT OPTION
my 56 compatibility variable looks like this. | show_compatibility_56 | OFF |
Suggestions on how to get this to work or why it is not working?