WL#6982: Make plugin column in mysql.user non-null

Since the introduction of the post-4.1 authentication method,
the choice of authentication method was performed on the length
of the password hash alone, thus enabling easy change of the
authentication methods through a password change.

This has become a problem with the introduction of other
authentication methods in 5.5. We needed a special value (empty)
of the authentication plugin column and a set of special cases
throughout the code to support the backward compatible behavior.

Since in 5.6 the pre-4.1 password hash is to be deprecated this
worklog aims to remove this backward compatible layer and simplify
the authentication code by removing the special cases.

Now the native authentication methods will be subject to the same
rules as the other authentication methods.

User Documentation
==================

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html
http://dev.mysql.com/doc/refman/5.7/en/server-system-
variables.html#sysvar_default_authentication_plugin
http://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html
http://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

List of Requirements:

1) When a row is entered in mysql.user table manually, plugin field cannot be
   null. If the plugin field is blank(empty string ''), a warning reporting the
   same for that user must be thrown after FLUSH PRIVILEGES or on restarting
   the server.

2) When mysql_install_db is run, all the users, including 'root', must have
   their plugin field filled. The default value is 'mysql_native_password'. If
   the default is mentioned explicitly using --default-authentication-plugin,
   the value given here must be reflected in plugin field.

3) When mysql_install_db is used with --random-passwords option, it has to be
   checked that the password hash generated is according to the plugin value.
   If plugin value is mysql_native_password, the hash length must be 41 and if
   the plugin value is sha256_password, authentication_string field must be
   updated.

4) mysql_upgrade must properly update the empty plugin values according to
   the length of exiting password.

The modifications required for each of the requirements, is listed below:

1) Make plugin column in mysql.user table a non-null:

The plugin column will be made non null and the default value will be
'mysql_native_password' as we intend to discontinue supporting blank plugin
values.

2) Change mysql_install_db to fill plugin value when creating root users:

The plugin given along with the option '--default-authentication-plugin'
is to be filled in the plugin value of all the users. If this option is not
used, 'mysql_native_password' must be filled in the field.

3) Upgrade script must identify all accounts with empty plugin and fill
   required plugin value:

Plugin field must be filled(in case it is blank) during mysql_upgrade
according to the length of existing password hash.

If length of hash = 0 or 41, plugin = mysql_native_password.
If length of hash = 16, plugin = mysql_old_password.

Along with this, the plugin field must be made NON NULL and the default must
be set as 'mysql_native_password'.

4) FLUSH PRIVILEGES must throw a warning when it encounters a user with blank
   plugin value. The user details are not put in memory cache and that user
   account becomes useless(cannot be logged in with) until the plugin field is
   updated properly. The same is the case on restarting the server too.

5) A read only global server variable @@default_authentication_plugin is to be
   added to the list of variables.

Once the worklog is implemented, before running mysql_upgrade, the server needs
to be started with --skip-grant-tables option. After the upgrade, the server
should be restarted, this time without --skip-grant-tables, for better security.

1) Make plugin column in mysql.user table a non-null:

In scripts/mysql_system_tables.sql, replace

plugin char(64) DEFAULT ''

with

plugin char(64) DEFAULT 'mysql_native_password' NOT NULL

in the 'CREATE TABLE IF NOT EXISTS user ...' statement.

2) Change mysql_install_db to fill plugin value when creating root users:

scripts/mysql_system_tables_data.sql is used for populating mysql.user table.
While inserting the rows corresponding to 'root', instead of
inserting a blank value for plugin, the new variable
@@default_authentication_plugin, must be used.

3) Upgrade script must identify all accounts with empty plugin and fill
   required plugin value:

The following statements is to be added to the file
scripts/mysql_system_tables_fix.sql

ALTER TABLE user ADD plugin char(64) DEFAULT 'mysql_native_password' NOT NULL,
ADD authentication_string TEXT;
UPDATE user SET plugin=IF(length(password) = 16, 'mysql_old_password',
'mysql_native_password') WHERE plugin = '';
ALTER TABLE user MODIFY plugin char(64) DEFAULT
'mysql_native_password' NOT NULL;

4) FLUSH PRIVILEGES must throw a warning when it encounters a user with blank
   plugin value.

The function validate_user_plugin_records() checks if all the users have a valid
plugin on running FLUSH PRIVILEGES. Right now it checks if the plugin name is
non empty and a warning is thrown only when invalid non blank names are used.
The same warning must be thrown for a blank name too.