Bug #76074 users with old-password=1 changed to mysql_native_password after upgrade
Submitted: 27 Feb 2015 7:32 Modified: 7 Apr 2015 14:46
Reporter: Ramana Yeruva Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.6 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 2015 7:32] Ramana Yeruva
Description:
start 5.6 server with old-password=1,skip-secure-auth and change root@localhost password,observe plugn column is empty and password set
make sure root@localhost login works fine
create super user who can execute upgrade and shutdown operations
now upgrade to wl8350,and check login with root@localhost and observe it can't and no workaround for this user to login

How to repeat:
5.6.22:steps:
../scripts/mysql_install_db --no-defaults -uroot --basedir=../ --datadir=./data
./mysqld --no-defaults -uroot --basedir=../ --datadir=./data --gdb --ssl-ca=$SSL/cacert.pem --ssl-cert=$SSL/server-cert.pem --ssl-key=$SSL/server-key.pem --loose-sha256_password_private_key_path=$SSL/rsa_private_key.pem --loose-sha256_password_public_key_path=$SSL/rsa_public_key.pem --old_passwords=1 --skip-secure-auth &

[lroot@vitro77 bin]# ./mysql --socket=/tmp/mysql.sock -uroot
mysql> select user,host,plugin,authentication_string from mysql.user;
+------+-----------+--------+-----------------------+
| user | host      | plugin | authentication_string |
+------+-----------+--------+-----------------------+
| root | localhost |        |                       |
| root | vitro77   |        |                       |
| root | 127.0.0.1 |        |                       |
| root | ::1       |        |                       |
|      | localhost |        | NULL                  |
|      | vitro77   |        | NULL                  |
+------+-----------+--------+-----------------------+
6 rows in set (0.00 sec)
mysql> drop user ''@localhost;drop user ''@vitro77;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select user,password,host,plugin,authentication_string from mysql.user;
+------+----------+-----------+--------+-----------------------+
| user | password | host      | plugin | authentication_string |
+------+----------+-----------+--------+-----------------------+
| root |          | localhost |        |                       |
| root |          | vitro77   |        |                       |
| root |          | 127.0.0.1 |        |                       |
| root |          | ::1       |        |                       |
+------+----------+-----------+--------+-----------------------+
4 rows in set (0.00 sec)
mysql> set password = password('test');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead
1 row in set (0.00 sec)

mysql> select user,password,host,plugin,authentication_string from mysql.user;
+------+------------------+-----------+--------+-----------------------+
| user | password         | host      | plugin | authentication_string |
+------+------------------+-----------+--------+-----------------------+
| root | 378b243e220ca493 | localhost |        |                       |<--plugin column is empty which is expected
| root |                  | vitro77   |        |                       |
| root |                  | 127.0.0.1 |        |                       |
| root |                  | ::1       |        |                       |
+------+------------------+-----------+--------+-----------------------+
4 rows in set (0.00 sec)
mysql> create user ramana@localhost identified by '';
Query OK, 0 rows affected (0.00 sec)
mysql> set password for ramana@localhost = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to ramana@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,password,host,plugin,authentication_string from mysql.user;
+--------+-------------------------------------------+-----------+-----------------------+-----------------------+
| user   | password                                  | host      | plugin                | authentication_string |
+--------+-------------------------------------------+-----------+-----------------------+-----------------------+
| root   | 378b243e220ca493                          | localhost |                       |                       |
| root   |                                           | vitro77   |                       |                       |
| root   |                                           | 127.0.0.1 |                       |                       |
| root   |                                           | ::1       |                       |                       |
| ramana | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | localhost | mysql_native_password |                       |
+--------+-------------------------------------------+-----------+-----------------------+-----------------------+
5 rows in set (0.00 sec)

[lroot@vitro77 bin]# ./mysql -uroot -ptest --socket=/tmp/mysql.sock --skip-secure_auth<--this connection is successful
mysql> 

[lroot@vitro77 bin]# ./mysqladmin -uroot -ptest --socket=/tmp/mysql.sock --skip-secure_auth shut<--shutdown 5.6.22 server
Warning: Using a password on the command line interface can be insecure.

wl8350 for upgrade:
[lroot@vitro77 bin]# ./mysqld --no-defaults -uroot --basedir=../ --datadir=/export/home/tmp/ramana/5.6.22/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/bin/data --gdb --ssl-ca=$SSL/cacert.pem --ssl-cert=$SSL/server-cert.pem --ssl-key=$SSL/server-key.pem --loose-sha256_password_private_key_path=$SSL/rsa_private_key.pem --loose-sha256_password_public_key_path=$SSL/rsa_public_key.pem &

[lroot@vitro77 bin]# ./mysql_upgrade --socket=/tmp/mysql.sock -uramana -ptestmysql_upgrade: <--observe execution success with below warning
mysql_upgrade: [Warning] 1642: Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.

./mysqladmin --socket=/tmp/mysql.sock -uramana -ptest shut<--shutdown wl8350 server

./mysqld --no-defaults -uroot --basedir=../ --datadir=/export/home/tmp/ramana/5.6.22/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/bin/data --gdb --ssl-ca=$SSL/cacert.pem --ssl-cert=$SSL/server-cert.pem --ssl-key=$SSL/server-key.pem --loose-sha256_password_private_key_path=$SSL/rsa_private_key.pem --loose-sha256_password_public_key_path=$SSL/rsa_public_key.pem &<--restarting server

[lroot@vitro77 bin]# ./mysql -uramana -ptest --socket=/tmp/mysql.sock 
mysql> select user,host,plugin,authentication_string from mysql.user;
+--------+-----------+-----------------------+-------------------------------------------+
| user   | host      | plugin                | authentication_string                     |
+--------+-----------+-----------------------+-------------------------------------------+
| root   | localhost | mysql_native_password |                                           |<--plugin changed from blank to mysql_native_password
| root   | vitro77   | mysql_native_password |                                           |
| root   | 127.0.0.1 | mysql_native_password |                                           |
| root   | ::1       | mysql_native_password |                                           |
| ramana | localhost | mysql_native_password | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+--------+-----------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

./mysql -uroot -proot --socket=/tmp/mysql.sock<--it fails,
[7 Apr 2015 14:46] Paul Dubois
Noted in 5.7.7, 5.8.0.

For upgrades from MySQL 5.6 to 5.7 that involve moving mysql.user 
table passwords from the Password column to the authentication_string
column, mysql_upgrade neglected to handle rows with an empty plugin
value and a pre-4.1 password hash.