WL#11057: Make caching_sha2_password default authentication mechanism

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

WL#9591 introduces new authentication plugin - caching_sha2_password. This worklog switches default authentication for server/client to caching_sha2_password.

Functional Requirements

  • FR#1: Default value for plugin column in mysql.user table shall be changed to 'caching_sha2_password'.
  • FR#2: Default authentication plugin for MySQL server shall be changed from mysql_native_password to caching_sha2_password.
  • FR#3: Default authentication plugin for libmysql shall be changed from myql_native_password to caching_sha2_password.
  • FR#4: Two new columns shall be added to table mysql.slave_master_info.
    • FR#4.1 : Column Public_key_path would store path to a file containing public key of master.
    • FR#4.2 : Column Master_public_key would store preference to fetch public key from master
  • FR#5: It should be possible to specify path to master's public key file and preference to fetch master key through CHANGE MASTER statement.
  • FR#6: root@localhost shall be defined to use caching_sha2_password by --initialize.
  • FR#7: It should be possible to consult the path to master's public key file and preference for fetching public key from master through either SHOW SLAVE STATUS or querying performance_schema.replication_connection_configuration table.
  • FR#8: Group Replication should support a new plugin variables to specify public key file information
  • FR#9: A new client API should be introduced to clear cached public key information in MySQL client library

Non-functional Requirements

  • NFR#1: As a part of upgrade, no change should be done to a valid user account's authentication plugin.
  • HLS#1: Default value of plugin column in mysql.user table will be changed to 'caching_sha2_password'.
  • HLS#2: Default value for system variable : --default-authentication-plugin will be changed from 'mysql_native_password' to 'caching_sha2_password'.
  • HLS#3: Default plugin for libmysql will be changed from native_password_client_plugin to caching_sha2_password_client_plugin.
  • HLS#4: In mysql.slave_master_info table, two new columns will be added after column Tls_version.
    • Name of the column : Master_public_key_path
      • Type of the column : TEXT
      • Character set : utf8
      • Collation : utf8_bin
      • Value : Path to master's public key path
    • Name of the column : Get_master_public_key
      • Type of the column : Boolean
  • HLS#5: During upgrade, columns, Master_public_key_path and Get_master_public_key will be added after Tls_version.
# If the order of column Public_key_path and Get_master_public_key is wrong,
# same will correct the order in slave_master_info table.
ALTER TABLE slave_master_info
 MODIFY COLUMN Master_public_key_path TEXT CHARACTER SET utf8 COLLATE utf8_bin
 COMMENT 'The file containing public key of master server.'
 AFTER Tls_version;
ALTER TABLE slave_master_info
 MODIFY COLUMN Get_master_public_key BOOLEAN NOT NULL
 COMMENT 'Preference to obtain public key from master.'
 AFTER Master_public_key_path
  • HLS#6: CHANGE MASTER sql will be extended to support two new clauses.
  MASTER_PUBLIC_KEY_PATH = ''
  Get_master_public_key = ON | OFF
  • HLS#7:
    • Class Master_info::Master_info() will be modified to have a new member variables to have information about master's public key : master_public_key_path and preference to obtain master public key
    • If master_public_key_path is not empty, while establishing connection with master through libmysql, MYSQL_SERVER_PUBLIC_KEY option will be set with the value of master_public_key_path.
    • If master_public_key_path is set, we will not obtain public key from master even if Get_master_public_key is set to ON
  • HLS#8:
    • SHOW SLAVE STATUS will show information about Master_public_key_path and Get_master_public_key.
    • Two new column will be added in performance_schema.replication_connection_configuration.
      • Name : Master_public_key_path
        • Type : VARCHAR(512).
      • Name : Get_master_public_key
        • Type : Boolean
  • HLS#9:
    • Two new options will be added to Group replication plugin
      • group_replication_recovery_public_key_path : To accept public key file information
      • group_replication_recovery_get_public_key : To accept preference about fetching public key from master
  • HLS#10:
    • A new client API : mysql_reset_server_public_key() will be introduced to clear cached copy of server public key from client library.
  • Notes about design
    • With respect to caching_sha2_password, requirement to provide server's public key or use of TLS material is required at following occasions.
      • First login after user creation
      • First login after FLUSH PRIVILEGES
      • First login after password change
      • First login after RENAME USER
    • Other than above mentioned cases, public key or TLS material is not required.
    • Above mentioned conditions apply to slave user when user is used for traditional replication or group replication.