Bug #86664 Reopen of #78033 - CREATE USER accepts plugin and hash, but misplaces the hash

Description:
Since I can't reopen an already closed bug, I'm creating a new item to follow up on https://bugs.mysql.com/bug.php?id=78033

This is a functional issue, but I primarily view it as a security gap since it allows for the creation of passwordless accounts without warning.

Original description by Jesper wisborg Krogh:

###

CREATE USER in 5.5 and later supports the syntax:

CREATE USER user_specification IDENTIFIED WITH auth_plugin AS 'hash_string'

However in MySQL 5.5 and 5.6 the hash_string is ignored and the user created without a password.

Issue verified in 5.5.8, 5.5.44, 5.6.10, and 5.6.26. Works in 5.7.8 and 5.7.9.

How to repeat:
Original steps by Jesper wisborg Krogh and extended by me are below. The steps are reproducible in 5.6.31+ without password_validation plugin.

###

mysql> SELECT PASSWORD('my_n0t_v3ry_secur3_passw0rd');
+-------------------------------------------+
| PASSWORD('my_n0t_v3ry_secur3_passw0rd')   |
+-------------------------------------------+
| *FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44 |
+-------------------------------------------+
1 row in set (0.00 sec)

-- Notice no warnings when creating the user
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password AS '*FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44';
Query OK, 0 rows affected (0.07 sec)

-- The password hash is placed in authentication_string column, and not password column (where it'll be pulled from at authentication stage)
mysql> SELECT User, Host, Plugin, Password, Authentication_string FROM mysql.user WHERE User = 'myuser' AND host = 'localhost';
+--------+-----------+-----------------------+----------+-------------------------------------------+
| User   | Host      | Plugin                | Password | Authentication_string                     |
+--------+-----------+-----------------------+----------+-------------------------------------------+
| myuser | localhost | mysql_native_password |          | *FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44 |
+--------+-----------+-----------------------+----------+-------------------------------------------+
1 row in set (0.00 sec)

# mysql -umyuser -e 'select "logged in!" as result'
+------------+
| result     |
+------------+
| logged in! |
+------------+

Suggested fix:
The fix provided in https://github.com/mysql/mysql-server/commit/8a2aefcd72d72b9c6a9ecd5d3aa75da7d746e761 is not enough. It neither resolves the original issue, nor closes the rather serious security gap. Imagine a situation:

- A database is being migrated from 5.6 to 5.7,
- The migration hits an issue and the DBA is forced to migrate back to 5.6 via replication,
- Due to this bug, user accounts replicated to 5.6 are passwordless and the DBA may not even become aware of the issue.

The real solution would mean making the engine behavior consistent: password-setting statements (GRANT/CREATE/SET PASSWORD) and authentication mechanisms should operate on the same columns. In other words, the current bug is that password-setting commands for "mysql_native_password" plugin operate on authentication_string, while authentication stage for the same plugin looks for the hash in "password" field.

Thank you for the report.

-- 5.6.36

rm -rf 86664
scripts/mysql_install_db --basedir=$PWD --datadir=$PWD/86664 -v
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/86664 --core-file --socket=/tmp/mysql_ushastry.sock   --port=5636 --log-error=$PWD/86664/log.err  2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.36: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 MySQL Community Server (GPL)

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

root@localhost [(none)]> SELECT PASSWORD('my_n0t_v3ry_secur3_passw0rd');
+-------------------------------------------+
| PASSWORD('my_n0t_v3ry_secur3_passw0rd')   |
+-------------------------------------------+
| *FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> CREATE USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password AS '*FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> SELECT User, Host, Plugin, Password FROM mysql.user WHERE User = 'myuser' AND host = 'localhost';
+--------+-----------+-----------------------+----------+
| User   | Host      | Plugin                | Password |
+--------+-----------+-----------------------+----------+
| myuser | localhost | mysql_native_password |          |
+--------+-----------+-----------------------+----------+
1 row in set (0.00 sec)

root@localhost [(none)]> SELECT User, Host, Plugin, Password, Authentication_string FROM mysql.user WHERE User = 'myuser' AND host = 'localhost';
+--------+-----------+-----------------------+----------+-------------------------------------------+
| User   | Host      | Plugin                | Password | Authentication_string                     |
+--------+-----------+-----------------------+----------+-------------------------------------------+
| myuser | localhost | mysql_native_password |          | *FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44 |
+--------+-----------+-----------------------+----------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.36: bin/mysql -umyuser -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 MySQL Community Server (GPL)

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

myuser@localhost [(none)]> show grants;
+--------------------------------------------+
| Grants for myuser@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

What you're describing here is the documented behavior:(https://dev.mysql.com/doc/refman/5.6/en/create-user.html) says:
"With IDENTIFIED WITH, the server assigns the specified plugin and the account has no password. If the optional AS 'hash_string' clause is also given, the string is stored as is in the authentication_string column (it is assumed to be already hashed in the format required by the plugin)." 

There was a logical discrepancy in how 5.5 and 5.6 were handling the two columns in mysql.user. This is fixed in 5.7 since it required changes in behavior that are not acceptable in a GA release. And mysql_upgrade should properly upgrade the system tables too. 
I would suggest you consider upgrading your installation to 5.7.