What's the point of using client certificate if mysql server doesn't verify the clients?

Question

By default mysql server creates these 6 certificates in /var/mysql/lib:

-rw------- 1 mysql mysql 1676 Aug 3 00:16 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 3 00:16 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 3 00:16 client-cert.pem
-rw------- 1 mysql mysql 1676 Aug 3 00:16 client-key.pem
-rw------- 1 mysql mysql 1680 Aug 3 00:16 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Aug 3 00:16 public_key.pem
-rw-r--r-- 1 root root 1172 Aug 6 12:20 server-cert.pem
-rw------- 1 mysql mysql 1676 Aug 3 00:16 server-key.pem

As mentioned in https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/creating-ssl-rsa-files-using-mysql.html , public_key.pem and private_key.pem are used in:

These key-pair files enable secure password exchange using RSA over unencrypted connections for accounts authenticated by the sha256_password plugin;

If I understand correctly, the other 6 certificates are used for mutual tls verification.

I tested it with DataGrip and there are 3 options: Require, Verifa CA and Full Verification. (commandline interface e.g. mysqlsh has corresponding options)

• Require has nothing to with certificates, it starts a tls encryption with server as ordinary https does.
• Verify CA verifies the ca.pem, which you get from the server /var/lib/ca.pem.
• Full Verification is the client verifying not only the ca.pem, but also server-cert.pem and server-key.pem.
  ( you need to re-generate a server-cert.pem for this work. Because the auto-generated server-cert.pem 's Subject: field doesn't match the server ip/domain)

enter image description here

None of the options reuire specifying client cert and key. So what's the usage of proving client-key.pem and client-cert.pem in the client? I don't find an option like "verify the client" in mysql server. So it's merely one way verification and client certs are completely useless? I don't get it.

Answer 1

You must have missed this in the documentation: when you create a user, you can specify various TLS authentication options. For example,

CREATE USER 'rick'@'%' REQUIRE SUBJECT 'CN=Rick' AND ISSUER '...'

• Rick: Yes, the linked documentaion explains how client certs work. I tried CREATE USER 'rick'@'%' IDENTIFIED BY 'password' require X509; in the X509 section and had some experiments with my client. It matches my expectation. Thanks!