WL#6982: Make plugin column in mysql.user non-null
Affects: Server-5.7 — Status: Complete
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.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.