MySQL 8.0.28 Access Denied for users created with REQUIRE cipher '...'

Question

I am attempting to require specific SSL ciphers for users that I create in MySQL following this documentation but whenever I try to connect I get Access Denied. My steps are as follows

1. Start MySQL (docker run --name mysql -p 3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.0.28)
2. Connect as root (mysql -u root -p -h 127.0.0.1 --port mapped_port)
3. Create a user with a required cipher (CREATE USER 'user'@'%' identified by 'root' require cipher 'ECDHE-RSA-AES128-GCM-SHA256';)
4. Attempt to connect as user (mysql -u user -h 127.0.0.1 --port mapped_port -p --ssl-cipher=ECDHE-RSA-AES128-GCM-SHA256 --tls-version=TLSv1.2)

If I attempt to connect with a different cipher I see an exception in the MySQL general log that indicates a cipher mismatch

2022-06-23T11:54:23.432986Z 16 [Note] [MY-010289] [Server] X.509 ciphers mismatch: should be 'ECDHE-RSA-AES128-GCM-SHA256' but is 'ECDHE-RSA-AES256-GCM-SHA384' 2022-06-23T11:54:23.433052Z 16 [Note] [MY-010926] [Server] Access denied for user 'user'@'127.0.0.1' (using password: YES)

but if I connect with the correct cipher I get only get the Access denied

2022-06-23T11:55:41.431244Z 17 [Note] [MY-010926] [Server] Access denied for user 'user'@'127.0.0.1' (using password: YES)

What am I missing that needs to be configured to enable requiring specific ciphers for connections?

Answer 1

This is how I created the user to support your specific cipher:

mysql> CREATE USER 'user'@'%' identified by 'password' require cipher 'ECDHE-RSA-AES128-GCM-SHA256';
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE mysql.user SET ssl_type = 'ANY' WHERE user = 'user';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user,host,ssl_type, ssl_cipher FROM mysql.user;
+------------------+-----------+----------+----------------------------------------------------------+
| user             | host      | ssl_type | ssl_cipher                                               |
+------------------+-----------+----------+----------------------------------------------------------+
| root             | %         |          | 0x                                                       |
| user             | %         | ANY      | 0x45434448452D5253412D4145533132382D47434D2D534841323536 |
| mysql.infoschema | localhost |          | 0x                                                       |
| mysql.session    | localhost |          | 0x                                                       |
| mysql.sys        | localhost |          | 0x                                                       |
| root             | localhost |          | 0x                                                       |
+------------------+-----------+----------+----------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> quit
Bye

Then I was able to log in like this:

root@339394bbea69:/# mysql -uuser -ppassword --ssl-cipher='ECDHE-RSA-AES128-GCM-SHA256'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
root@339394bbea69:/# mysql -uuser -ppassword --ssl-cipher='ECDHE-RSA-AES128-GCM-SHA256' --ssl-mode=REQUIRED
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 clear the current input statement.

You are enforcing ssl connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> \s;
--------------
mysql  Ver 8.0.29 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:      76
Current database:   
Current user:       user@localhost
SSL:            Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.29 MySQL Community Server - GPL
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        /var/run/mysqld/mysqld.sock
Binary data as:     Hexadecimal
Uptime:         3 hours 23 min 50 sec

Threads: 2  Questions: 175  Slow queries: 0  Opens: 300  Flush tables: 3  Open tables: 219  Queries per second avg: 0.014
--------------

ERROR: 
No query specified

mysql> quit
Bye

Finally I tested a wrong cipher or no cipher fails:

root@339394bbea69:/# mysql -uuser -ppassword --ssl-cipher='TLS_AES_256_GCM_SHA384' --ssl-mode=REQUIRED
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: Failed to set ciphers to use
root@339394bbea69:/# mysql -uuser -ppassword                                                          
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
root@339394bbea69:/#

Hope this helps!