WL#11540: Support 2 active passwords per user account

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

When changing e.g. the replication slave password on a large set of replicated
mysql servers it's necessary to support at least for a certain period both the
old and the new password as valid ones.
This worklog is about adding an extra option to ALTER USER/SET PASSWORD to keep
the old password and then adding another ALTER USER command to retire the old
password. 
FR1: An authentication plugin that uses mysql.user table as a password store 
     (i.e. plugins that set flag AUTH_FLAG_USES_INTERNAL_STORAGE on), 
     should support 2 active passwords for user accounts.

FR2: It should be possible to specify preference to retain current password
through SQL.
  FR2.1: In case of ALTER USER, extra clause - RETAIN CURRENT PASSWORD
         should be added in auth_option sub-clauses

  auth_option: {

    IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string'] [RETAIN CURRENT
PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'hash_string' [RETAIN CURRENT PASSWORD]
  }

    Note: Since new clause RETAIN CURRENT PASSWORD is part of auth_option, 
          preference applies to individual user account in an ALTER USER
          statement.
  FR2.2: In case of SET PASSWORD, extra clause RETAIN CURRENT PASSWORD
         should be added

  SET PASSWORD = 'auth_string' [REPLACE 'current_auth_string'] [RETAIN CURRENT
PASSWORD]
  SET PASSWORD FOR  = 'auth_string' [REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]

FR3: It should be possible to specify preference to discard old password through 
     SQL.
  FR3.1: ALTER USER will support a new clause - DISCARD OLD PASSWORD for
         auth_option sub-clause.

  auth_option: {

    IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string'] [RETAIN CURRENT
PASSWORD]
  | DISCARD OLD PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'hash_string' [RETAIN CURRENT PASSWORD]
  }


FR4: Privilege Requirement for FR2 and FR3:
     FR 4.1 CREATE USER privilege allows user to specify
            RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for self
            and other accounts.
     FR 4.2 A new dynamic privilege - APPLICATION_PASSWORD_ADMIN should be
            added. Users with the new dynamic privilege shall be able to use
            RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for self.

FR5: A new column - user_attributes should be introduced to store extra
     credential in mysql.user table. It should be updated as a part of execution
     mentioned in FR2 and FR3 above. New column should be of type JSON so that 
     it can be extended to cover other attributes too.

FR6: User management DDLs should affect extra credential in following manner:
  FR6.1: Changing authentication plugin through ALTER USER should discard the
         extra credential.
  FR6.2: Renaming a user account should not have any impact on extra credential.
  FR6.3: Dropping a user account should discard extra credential too.

FR7: Logging requirements:
  FR 7.1: In an HA environment, if new clauses mentioned in FR2 and FR3 are 
          specified, they should be replicated to other nodes to make sure that 
          behavior is consistent across all nodes. Hence, binary log should
          contain new clauses whenever specified through
          ALTER USER/SET PASSWORD.
  FR 7.2: General log, slow query log and audit log should contain new
          clauses whenever specified through ALTER USER/SET PASSWORD.
  FR 7.3: There should not be any change in behavior/extra clauses for
          SHOW CREATE USER.
  FR 7.4: Whenever second password is used, authentication plugin should log
          a message of type INFORMATION in error log.

FR8: Information about additional credential should be passed to plugin through 
     MYSQL_SERVER_AUTH_INFO structure. Due to this change, major version for 
     authentication plugin API should be bumped.

FR 9: Empty password handling should be in following manner
  FR9.1: An empty password can not be set as second password.
  FR9.2: Setting current password as empty discards second password too.

NFR1: As a part of upgrade, new column mysql.user.user_attributes should be 
      created. However, server should be able to start and support at least
      the upgrade even if the column is missing.
Definition:
1. Primary password : Credential stored in mysql.user.authentication_string column
2. Additional password : Credential stored in mysql.user.user_attribute

HLS1: A new column will be introduced in mysql.user table.
      ALTER TABLE user ADD User_attributes JSON DEFAULT NULL AFTER
Password_require_current;
HLS 1.1: Additional credential, wherever applicable will be stored as:
         {"additional_credential":"value"}

HLS2: ACL_USER will be updated to store additional password in memory.

HLS3: ALTER USER and SET PASSWORD will support RETAIN CURRENT PASSWORD while 
      changing user's password.
      - If additional password does not exist for the user, one will be added in
        in-memory structures and mysql.user table.
      - If additional password already exists for the user, same will be 
        discarded and user's current password will be saved in
        mysql.user.user_attributes column.

HLS4: ALTER USER will support DISCARD CURRENT PASSWORD
      - If additional password does not exist, this is a no-op.
      - If additional password exists, same will be discarded from in-memory
        structures and mysql.user.user_attributes column.

HLS5: ALTER USER rewriter will make sure that new clauses are part of various 
      logs.

HLS6: There will not be any change in SHOW CREATE USER output.

HLS7: Authentication plugin is responsible for performing authentication using
      additional password.
      - Server structure MPVIO_EXT holds copy of ACL_USER object. Since extra 
        password information is part of ACL_USER, it will be available to 
        authentication module through MYSQL_SERVER_AUTH_INFO structure as 
        mentioned in HLS8 below.

HLS8: MYSQL_SERVER_AUTH_INFO will contain additional password.
      struct MYSQL_SERVER_AUTH_INFO {
        ...
        ...
        /**
          Additional password
        */
        const char *additional_auth_string;

        /**
          Length of additional_auth_string;
        */
        unsigned long additional_auth_string_length;
      };

      Due to this change, authentication plugin API's major version will be 
      bumped.

HLS9: Plugins that set flag AUTH_FLAG_USES_INTERNAL_STORAGE on, will support
      2 active passwords. At present, following plugins sets 
      AUTH_FLAG_USES_INTERNAL_STORAGE to on.
      - mysql_native_password
      - sha256_password
      - caching_sha2_password

      In future, if and when a new authentication plugin is introduced that
      sets AUTH_FLAG_USES_INTERNAL_STORAGE to on, it should also support 2
      active passwords.

HLS10: Authentication plugin will first perform authentication using primary 
      password. If it fails, it will perform authentication using additional
      password if available.
      
HLS11: In case of caching_sha2_password, following will be the sequence
       - Fast authentication using primary credential
       - Fast authentication using additional credential
       - Full authentication against primary credential
       - Full authentication against additional credential
       HLS11.1: Change in credential for caching_sha2_password user will
                remove cached entry for credential even if
                RETAIN CURRENT PASSWORD is used because primary password
                has changed.
       HLS11.2: Use of DISCARD OLD PASSWORD will remove cached entry for
                additional password in case of caching_sha2_password.