MySQL Bugs: #69889: old_passwords changes depending on login authentication type

Bug #69889	old_passwords changes depending on login authentication type
Submitted:	1 Aug 2013 4:30	Modified:	21 Jul 2014 17:43
Reporter:	Alfie John	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Linux (Wheezy)
Assigned to:		CPU Architecture:	Any
Tags:	old_passwords

Description:
The documentation (http://dev.mysql.com/doc/refman/5.6/en/password-hashing.html) says:

"The way in which the server generates password hashes for connected clients is affected by the width of the Password column and by the old_passwords system variable. A 4.1 or later server generates long hashes only if certain conditions are met..."

I think what's happening is the complete opposite - old_password is set depending on the currently logged in user's password type.

How to repeat:
If I login with a pre-4.1.1 password, "SELECT @@old_password" = 1.

If I login with a mysql_native_password, "SELECT @@old_password" = 0.

Suggested fix:
Allow old_password to be set from the command line and the config file (see #69885), and then only use that value regardless of the currently logged in user's password type.

Ok, so it looks like it CAN be set from the config file, but only if you login with a non-pre-4.1.1 account.

I think this is a bug.

Thank you for the report.

Verified as described.

I am sorry, was too fast and did a typo when tested. Actually bug is not repeatable:

mysql> create user bug69889@'%' identified with  mysql_old_password;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on test.* to bug69889 identified by 'foo';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> \q
Bye

$ mysqlmtr -ubug69889 -pfoo --skip-secure-auth 
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 2
Server version: 5.6.21-debug-log Source distribution

Copyright (c) 2000, 2014, 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.

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select password('foo');
+------------------+
| password('foo')  |
+------------------+
| 7c786c222596437b |
+------------------+
1 row in set (0.00 sec)

mysql> set @@old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select password('foo');
+-------------------------------------------+
| password('foo')                           |
+-------------------------------------------+
| *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+-------------------------------------------+
1 row in set (0.00 sec)