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
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.