MySQL Bugs: #68858: Need variable for --default-authentication-plugin

Bug #68858	Need variable for --default-authentication-plugin
Submitted:	3 Apr 2013 18:42	Modified:	27 Jan 2015 15:06
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Server 5.6 introduces the ability to set the default authentication plugin, but this is not exposed (or manageable) via variables:

mysql> show global variables like '%auth%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | ON    |
+---------------+-------+
1 row in set (0.02 sec)

This needs to be at least exposed, ideally allowing manipulation so that the behavior can be changed without downtime.

How to repeat:
See above

Suggested fix:
Add global variable default_authentication_plugin.  Allow it to be modified.

Yes. Running a precompiled version on Linux, there doesn't appear to be a way to enable this. I have tried putting default-authentication-plugin=sha256_password in my.cnf and manually enabling it by adding --default-authentication-plugin=sha256_password to the startup options in /etc/initi.d/mysql. A quick look at the running processes shows that the command IS being executed with this option:

# ps ax|grep mysql
13802 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe --mysqld=mysqld --user=mysql --pid-file=/var/run/mysql/mysqld.pid --socket=/var/run/mysql/mysql.sock --default-authentication-plugin=sha256_password --datadir=/var/lib/mysql
14311 ?        Sl     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr//usr/lib64/mysql/plugin --user=mysql --default-authentication-plugin=sha256_password --log-error=/var/lib/mysql/lynyrd.nickhq.com.err --pid-file=/var/run/mysql/mysqld.pid --socket=/var/run/mysql/mysql.sock --port=3306

But when I look at old_passwords, it is still "0". Documentation indicates that --default-authentication-plugin=sha256_password should have changed it to "2".

mysql> show variables like '%password%'\G
*************************** 1. row ***************************
Variable_name: disconnect_on_expired_password
        Value: ON
*************************** 2. row ***************************
Variable_name: old_passwords
        Value: 0
*************************** 3. row ***************************
Variable_name: report_password
        Value: 
*************************** 4. row ***************************
Variable_name: sha256_password_private_key_path
        Value: rsa-mysql-server.pem
*************************** 5. row ***************************
Variable_name: sha256_password_public_key_path
        Value: rsa-mysql-server.pub

mysql> SHOW STATUS LIKE 'rsa_public_key'\G
*************************** 1. row ***************************
Variable_name: Rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDYlaqsKCcb8kGPuZN2TPiAkEHf
3NQoMOCP1UdUheNl6gl79Lzouwh9ChxqwylDwO4ly9fsgFKLuwVyR2YENuoZjWnw
Qw1wU8OJBZNGMBjQQAW0HV34jF/UkomfEiTVrxCbVI/XWJwfKYB84LK8sMrk6nwp
irQ4Dl3C2/FyGa5y9QIDAQAB
-----END PUBLIC KEY-----

Version is 5.6.12.

Actually, this might be a different bug. old_passwords may be 0 when it's supposed to be 2, but when I created a new user it set the plugin column in the mysql.user table to "sha256_password" and it stored a hashed password in the mysql.user.authentication_string column instead of the mysql.user.Password column.

So --default-authentication-plugin=sha256_password DID enable it, and it DID set old_passwords to 2, but showing the variables false indicates that old_passwords is 0.

It's easily possible to expose this read-only with a daemon plugin:

mysql [(none)] > show global variables like '%default_auth%';
+--------------------------------------------+-----------------------+
| Variable_name                              | Value                 |
+--------------------------------------------+-----------------------+
| def_auth_var_default_authentication_plugin | mysql_native_password |
+--------------------------------------------+-----------------------+
1 row in set (0.00 sec)

Expiremental plugin which exposes default authentication plugin as readonly variable. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Add-daemon-plugin-for-Bug-68858.patch (text/x-diff), 3.71 KiB.

Hi Daniël,

Thank you for contributing this plugin !
However, since this is a server variable, we're IMHO better off making it a server status var (and not a plugin).

This was fixed in MySQL 5.7.2.

See also BUG#73833.