MySQL Bugs: #79269: Failure of auth_socket authentication with sha256

Bug #79269	Failure of auth_socket authentication with sha256_password as default
Submitted:	13 Nov 2015 13:18	Modified:	24 Jan 2018 21:13
Reporter:	Ceri Williams	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S3 (Non-critical)
Version:	5.6, 5.7, 8.0.0-dmr, 9.0.0-dmr, 5.7.19, 8.0.4-rc	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	auth_socket, authentication, sha256_password

Description:
Setting default-authentication-plugin=sha256_password forces authentication failure for accounts using auth_socket.

How to repeat:
- Enable auth_socket plugin
- Create a user who identfies using auth_socket
- Connect to MySQL - OK
- Set default-authentication-plugin=sha256_password in my.cnf and restart MySQL
- Connect to MySQL - ERROR 2012 (HY000): Error in server handshake

Suggested fix:
Bypass the check as the user doesn't enter a password

On 5.7 the message seems to have changed to:

ERROR 2061 (HY000): Authentication plugin 'sha256_password' reported error: Authentication requires SSL encryption

Updated affected versions

Updated affected versions.

This issue also appears with caching_sha2_password.

9.0.0-dmr:

shell> ./mysqld --plugin-load-add=auth_socket.so

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

mysql> \q

As OS root user, login without a password to check to make sure auth_socket is working:
shell> /mysql -u root

mysql> \q
shell> >/mysqladmin -u root shutdown

shell> ./mysqld --default_authentication_plugin=caching_sha2_password --plugin-load-add=auth_socket.so

shell> ./mysql -u root
ERROR 2012 (HY000): Error in server handshake

Hello Ceri Williams,

Thank you for the report.
Please accept our sincere apologies for missing out on this for so long.
Verified as described.

Regards,
Umesh

-- 5.7.19

rm -rf 88063
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/88063 -v
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/88063 --plugin-load-add=auth_socket.so --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/88063/log.err 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

root@localhost [(none)]> create user 'umshastr'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> grant all on *.* TO 'umshastr'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> ALTER USER 'umshastr'@'localhost' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>

root@localhost [(none)]> select * from mysql.user where user='umshastr'\G
.
.
                plugin: auth_socket
 authentication_string:
      password_expired: N
 password_last_changed: 2017-10-12 08:16:57
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

root@localhost [(none)]>\q

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysql -uumshastr
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

umshastr@localhost [(none)]> show grants;
+-------------------------------------------------------+
| Grants for umshastr@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'umshastr'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

-- Stop mysqld

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysqladmin -uroot -S /tmp/mysql.sock shutdown
[1]+  Done                    bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/88063 --plugin-load-add=auth_socket.so --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/88063/log.err 2>&1

-- restart with

bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/88063 --default-authentication-plugin=sha256_password --plugin-load-add=auth_socket.so --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/88063/log.err 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysql -uumshastr
ERROR 2012 (HY000): Error in server handshake
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19:

After reading that the default_authentication_plugin is now changing to become caching_sha2_password (https://mysqlserverteam.com/the-mysql-8-0-4-release-candidate-is-available/) I have just downloaded and tested on the RC.

Compiled with:

version="$(basename "$(pwd)")"
prefix="/home/ceri/opt/mysql/${version}"
boost="./$(find boost/ -maxdepth 1 -type d -not -name boost)"

cmake . -DBUILD_CONFIG=mysql_release \
 -DCMAKE_INSTALL_PREFIX:PATH="${prefix}" \
 -DMYSQL_DATADIR:PATH="${prefix}/data" \
 -DWITH_SSL:STRING=system \
 -DWITH_ARCHIVE_STORAGE_ENGINE:BOOL=OFF \
 -DWITH_EMBEDDED_SERVER:BOOL=OFF \
 -DWITH_EXTRA_CHARSETS:STRING="" \
 -DWITH_FEDERATED_STORAGE_ENGINE:BOOL=OFF \
 -DWITH_BLACKHOLE_STORAGE_ENGINE:BOOL=OFF \
 -DWITH_BOOST="${boost}"

I then created a single-instance using MySQL Sandbox and restored the default back to caching_sha2_password, followed by:

mysql [localhost] {root} ((none)) > show global variables like 'default_auth%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql [localhost] {root} ((none)) > install plugin auth_socket soname 'auth_socket.so';
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {root} ((none)) > create user ceri@localhost identified with auth_socket;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {root} ((none)) > grant all on *.* to ceri@localhost;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} ((none)) > show grants for ceri@localhost\G
*************************** 1. row ***************************
Grants for ceri@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `ceri`@`localhost`
*************************** 2. row ***************************
Grants for ceri@localhost: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `ceri`@`localhost`
2 rows in set (0.00 sec)

This still fails, but with a mysterious "Unknown MySQL error":

 ⇒ /home/ceri/opt/mysql/mysql-8.0.4-rc/bin/mysql --defaults-file=/home/ceri/sandbox/mysql-8.0.4-rc/my.sandbox.cnf -uceri
ERROR 2000 (HY000): Unknown MySQL error

https://www.percona.com/blog/2018/01/24/mysql-8-0-4-rc-beware-auth_socket-users/

Just to follow up on the reference to the caching_sha2_password - the issue is just made apparent as it is the default, but making the sha2_password the default would be enough, so the issue looks to be in the same place.

mysql [localhost] {root} ((none)) > show global variables like 'default_authentication_plugin';
+-------------------------------+-----------------+
| Variable_name                 | Value           |
+-------------------------------+-----------------+
| default_authentication_plugin | sha256_password |
+-------------------------------+-----------------+
1 row in set (0.01 sec)

mysql [localhost] {root} ((none)) > \q
Bye
 ⇒ ./use -u ceri
ERROR 2000 (HY000): Unknown MySQL error