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:
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.6.12 OS:Linux (Wheezy)
Assigned to: CPU Architecture:Any
Tags: old_passwords

[1 Aug 2013 4:30] Alfie John
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.
[1 Aug 2013 23:11] Alfie John
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.
[21 Jul 2014 17:39] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Jul 2014 17:43] Sveta Smirnova
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

$ 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

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)