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:
Category:MySQL Server: Options Severity:S2 (Serious)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[3 Apr 2013 18:42] Todd Farmer
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.
[14 Aug 2013 4:54] Nicholas Williams
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
*************************** 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-----
-----END PUBLIC KEY-----

Version is 5.6.12.
[14 Aug 2013 5:02] Nicholas Williams
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.
[16 Nov 2014 13:55] Daniël van Eeden
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)
[16 Nov 2014 13:56] Daniël van Eeden
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.

[19 Nov 2014 13:17] Georgi Kodinov
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).
[27 Jan 2015 15:06] Jon Stephens
This was fixed in MySQL 5.7.2.

See also BUG#73833.