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,
