WL#10992: xplugin: sha256 challenge response against in-memory sha256-storage

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

Motivation
==========

WL#9591 introduces

* a cache_sha2_password storage in mysql.user
* intentionally expensive, salted, iterated SHA256 hashes in storage
* a in-memory cache which stores unsalted SHA256(SHA256(password)) which is
  stored after successful authentication
* a challenge-response protocol with a NONCE against that cache

As the password is never exchanged in cleartext no TLS is needed to authenticate.



Goal
====

* allow clients to authenticate using a 'MYSQL41'-like SASL method
  (using SHA256 instead of SHA1) over a cleartext channel
* authentication goes against a in-memory dictionary which stores a unsalted 
  sha256(sha256(password)) same as in WL#9591
* the cache gets filled if account has "cache_sha2_password" as auth-plugin and
  user logs in via PLAIN-over-TLS

Bonus Goals
-----------

* the cache MAY also work for the expensive, existing sha256_password entries.

## Caching of Auth Credentials

F1
:  authentication with SHA256_MEMORY MUST fail when cache invalidation doesn't
   work

F2
:  authentication with PLAIN-over-secure-connections MUST store
   user-account, SHA256(SHA256(password)) in a in-memory sha256_cache

F3
:  authentication with PLAIN-over-secure-connections WILL succeed if
   user-account already exists in the cache with the same
   SHA256(SHA256(password))

F4
:  authentication with SHA256_MEMORY  MUST succeed if
   the user-account, sha256(password) matches the entry in the sha256_cache
 
F5
:  authentication with SHA256_MEMORY MUST work over insecure and secure
   connections

F6
:  authentication with SHA256_MEMORY MUST fail if connection is insecure
   and account has any tls_option set

## Protocol Implementation Requirements

PROTO1
:  server MUST not close connection after authentication failure

PROTO2
:  server MUST close connection after multiple authentication failures

PROTO3
:  server MUST support resetting the failed authentication at Connection::Reset

## Edge Cases

NF1
:  there MUST be NO way to access the contents of the cache through SQL

NF2
:  the contents of the cache MUST NOT be persisted

## Cache Invalidation

CI1
:  DROP USER MUST invalidate the cached entry

CI2
:  SET PASSWORD MUST invalidate the user entry in the cache

CI3
:  ALTER USER MUST invalidate the user entry in the cache

CI4
:  RENAME USER MUST invalidate the old entry and a possible new entry
   in the cache

CI5
:  GRANT with IDENTIFIED MUST invalidate the user entry in the cache

CI6
:  FLUSH PRIVILEGES invalidates all entries in the cache

## libmysqlxclient

CL1
:  libmysqlxclient MUST support SHA256_MEMORY authentication method
General
=======

The worklog introduces an authentication that uses cached account informations. The cache is going to contain password hashes of MySQL accounts that already authenticated using clear-text password:

* client send clear-text password
* server authenticates the user
* the hash is calculated:

    HASH=sha256(sha256(PASSWORD))

* server makes an association between the MySQL account and the HASH (caches the user)

Authentication for already cached user, look like:

* server sends back a nonce which consist of 20 bytes (randomly generated, utf8 compatible).
* client calculates the hash using:

    CLIENT_HASH=SHA256(SHA256(SHA256(PASSWORD)),NONCE) XOR SHA256(PASSWORD)

* client sends the CLIENT_HASH to the server
* server gets cached hash using MySQL account data (the cached hash is going to be refereed as SERVER_SHA_SHA_PASSWORD_CACHE)
* server calculates following:

    CALCULATED_SHA_SHA_PASSWORD = SHA256(CLIENT_HASH XOR SHA256(SERVER_SHA_SHA_PASSWORD_CACHE, NONCE))

* authentication is successful when CALCULATED_SHA_SHA_PASSWORD equals SERVER_SHA_SHA_PASSWORD_CACHE

If the MySQL Server account is modified the cached entries for that account must be removed.


Protocol
========

When the client connects, the X Session goes through following stages:

* negotiation
* authentication
* ready

Capabilities
------------

In negotiation stage the client is be able to check which authentication methods are supported by X Plugin. It is done by probing X Plugin capabilities, using following flow:

    client -> server: Mysqlx.Connection.CapabilitiesGet
    client <- server: Mysqlx.Connection.Capabilities(...)

The worklog introduces a new authentication mechanism "SHA256_MEMORY" which must be returned in "authentication.mechanisms" capability:

    Mysqlx.Connection.Capabilities {
      capabilities {
        name: "authentication.mechanisms"
        value {
          type: ARRAY
          array {
            value {
              type: SCALAR
              scalar {
                type: V_STRING
                v_string {
                  value: "MYSQL41"
                }
              }
            }
            value {
              type: SCALAR
              scalar {
                type: V_STRING
                v_string {
                  value: "SHA256_MEMORY"
                }
              }
            }
          }
       }
    }

Authentication
--------------

"SHA256_MEMORY" authentication is going to be performed by flow that is already used for "MYSQL41" authentication method:

    client -> server: Mysqlx.Session.AuthenticateStart(mech_name="SHA256_MEMORY")
    client <- server: Mysqlx.Session.AuthenticateContinue(auth_data=NONCE)
    client -> server: Mysqlx.Session.AuthenticateContinue(auth_data="SCHEMA\0USER\0CLIENT_HASH")
    client <- server: Mysqlx.Session.AuthenticateOk()|Mysqlx.Error()


After clients receives the NONCE, it must calculate the CLIENT_HASH and send it back inside "auth_data" field of AuthenticateContinue message. It is transfered with default schema for the session and user name that is going to be authenticated. Schema, user and CLIENT_HASH are going to be concatenated into a single string, separated with '\0' character. Binary version of CLIENT_HASH may contain '\0' characters. To make it compatible with encoding of "auth_data", it must to be converted to hex-string.

Most authentication attempts are going to interact with the cache, table below shows actions on cache that must be performed when different accounts and methods are used:

|X Method      |TLS  |Server account type   |Cache                         |
|--------------|-----|----------------------|------------------------------|
|PLAIN         |Yes  |native_password       |Add hash and account to cache |
|PLAIN         |Yes  |sha256_password       |Add hash and account to cache |
|PLAIN         |Yes  |caching_sha2_password |Add hash and account to cache |
|MYSQL41       |No   |native_password       |-                             |
|MYSQL41       |Yes  |native_password       |-                             |
|MEMORY_SHA256 |No   |Any                   |Use cache for authentication  |
|MEMORY_SHA256 |Yes  |Any                   |Use cache for authentication  |

Multiple authentications
------------------------

Currently X Plugin closes the connection after first failed authentication:


    ...Connection setup...
    
    alt PLAIN
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="PLAIN", auth_data="schema\0user\0password")
      client <- server : Mysqlx.Error
    else MYSQL41 or SHA256_MEMORY
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="MYSQL41|SHA256_MEMORY")
      client <- server : Mysqlx.Session.AuthenticateContinue(auth_data=NONCE)
      client -> server : Mysqlx.Session.AuthenticateContinue(auth_data="SCHEMA\0USER\0CLIENT_HASH")
      client <- server : Mysqlx.Error
    else UNKNOWN
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="UNKNOWN",...)
      client <- server : Mysqlx.Error
    end
    
    ...Server closes the connection...

Client doesn't know if a given MySQL account was cached in X Plugin, he can only try to authenticate using SHA256_MEMORY method.
After authentication failure the connection must not be dropped to allow a sequence of authentications with different mechanisms (no need for reconnection).

Example 1:

      title Most expected scenario. Account is not cached, SHA256_MEMORY fails. To fill the cache client does PLAIN auth.
      ...Connection setup...
  
      client -> server : Mysqlx.Connection.CapabilitiesSet([tls=true])
      client <- server : Mysqlx.Ok()
      ...TLS ENABLED...
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="SHA256_MEMORY")
      client <- server : Mysqlx.Session.AuthenticateContinue(auth_data=NONCE)
      client -> server : Mysqlx.Session.AuthenticateContinue(auth_data="SCHEMA\0USER\0CLIENT_HASH")
      client <- server : Mysqlx.Error
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="PLAIN", auth_data="schema\0user\0password")
      client <- server : Mysqlx.Session.AuthenticateOk()
      ...Session ready...

Example 2:

      ...Connection setup...
      
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="SHA256_MEMORY")
      client <- server : Mysqlx.Session.AuthenticateContinue(auth_data=NONCE)
      client -> server : Mysqlx.Session.AuthenticateContinue(auth_data="SCHEMA\0USER\0CLIENT_HASH")
      client <- server : Mysqlx.Error
      client -> server : Mysqlx.Connection.CapabilitiesSet([tls=true])
      client <- server : Mysqlx.Ok()
      ...TLS ENABLED...
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="PLAIN", auth_data="schema\0user\0password")
      client <- server : Mysqlx.Session.AuthenticateOk()
      ...Session ready...

Example 3:

      ...Connection setup...
      
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="SHA256_MEMORY")
      client <- server : Mysqlx.Session.AuthenticateContinue(auth_data=NONCE)
      client -> server : Mysqlx.Session.AuthenticateContinue(auth_data="SCHEMA\0USER\0CLIENT_HASH")
      client <- server : Mysqlx.Error
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="MYSQL41")
      client <- server : Mysqlx.Session.AuthenticateContinue(auth_data=NONCE)
      client -> server : Mysqlx.Session.AuthenticateContinue(auth_data="SCHEMA\0USER\0CLIENT_HASH")
      client <- server : Mysqlx.Error
      client -> server : Mysqlx.Connection.CapabilitiesSet([tls=true])
      client <- server : Mysqlx.Ok()
      ...TLS ENABLED...
      client -> server : Mysqlx.Session.AuthenticateStart(mech_name="PLAIN", auth_data="schema\0user\0password")
      client <- server : Mysqlx.Session.AuthenticateOk()
      ...Session ready...

Number of authentication retries should be limited to three (number of all authentication mechanisms supported by X Plugin). 


Error and Warnings
==================

Authentication failure must not introduce any new error codes.


SQL
===

New audit plugin is going to be added. It is going to maintain the cache and remove all entires for accounts that were modified. To installe the plugin user must execute:

    INSTALL PLUGIN mysqlx_cache_cleaner SONAME 'mysqlx.so';


Compatibility
=============

New authentication method is not compatible with old MySQL server, except that its not supported there, old server do not allow authentication sequencing the first tried auth-method there should be either PLAIN or MYSQL41:

|Authentication mech |MySQL 5.7  |before MySQL 8.0.4 |MySQL 8.0.4 and after |
|--------------------|-----------|-------------------|----------------------|
|MYSQL41             |OK         |OK                 |OK                    |
|PLAIN               |OK         |OK                 |OK                    |
|SHA256_MEMORY       |NO         |NO                 |OK                    |

If an new connector is going to send SHA256_MEMORY to old plugin, it must expect following behaviour:

    client->server: Mysqlx.Session.AuthenticateStart(mech="SHA256_MEMORY")
    client<-server: Mysqlx.Error(ER_NOT_SUPPORTED_AUTH_MODE)
    client<-server: TCP:RST

Mysqlxclient must have a configuration option in which is going to use PLAIN or MYSQL41 when its in AUTO mode.
Client
======

MYSQLXCLIENT
------------

"xcl::Mysqlx_option::Authentication_method" configuration option select which authentication mechanism is going to be used during session setup. Still it accepts single string.  To be able to support a authentication sequence the option needs to accept an array of strings, which would represent multiple steps of authentication. Th X Client API needs to be modified:

    namespace xcl {
    ...
    class XSession {
     public:
       /**
        Modify mysqlx options.

        This method may only be called before calling `XSession::connect` 
        method.

        @param option   option to set or modify
        @param value    assign list of string values to the option

        @return Error code with description
          @retval != true     OK
          @retval == true     error occurred
      */
      virtual XError set_mysql_option(const Mysqlx_option option,
          const std::vector<std::string> &values_list) = 0;

New values ("SHA256_MEMORY") needs to be accepted by the configuration option  to use new authentication mechanism. Full list of possible values:

* "AUTO"         - let the library select authentication method/methods (default)

* "SHA256_MEMORY - authentication based on memory-stored credentials

* "MYSQL41"      - do not use plain password send through network

* "PLAIN"        - use plain password for authentication

The behavior of "AUTO" setting needs to check connection properties and use different authentication sequences:

|Connection properties  |Authentication sequence          |
|-----------------------|---------------------------------|
|TCP, TLS               | SHA256_MEMORY, PLAIN, MYSQL41   |
|TCP                    | SHA256_MEMORY, MYSQL41          |
|UNIX socket            | SHA256_MEMORY, PLAIN, MYSQL41   |


MYSQLXTEST
----------

Authentication sequence should be configurable through command line arguments. Sequence of following arguments should trigger the same authentication sequence:

* plain-auth          Use PLAIN text authentication mechanism (already exists)

* cached-auth         Use SHA256_MEMORY authentication mechanism

* mysql41-auth        Use MYSQL41 authentication mechanism

In test language, user must be able to trigger a single authentication using selected mechanism. To execute a sequence of authentication, user must call multiple of following commands:

* login <user>	<pass>	<db>	<mysql41|plain|sha256_memory>]
   Performs authentication steps (use with --no-auth)

New connection commands with specified authentication:
* newsession <name>	<user>	<pass>	<db>
  Create a new connection which is going to be authenticate using sequence of 
  mechanisms (AUTO). Use '-' in place of the user for raw connection.

* newsession_mysql41 <name>	<user>	<pass>	<db>
  Create a new connection which is going to be authenticate using MYSQL41 
  mechanism.

* newsession_memory <name>	<user>	<pass>	<db>
  Create a new connection which is going to be authenticate using SHA256_MEMORY 
   mechanism.

* newsession_plain <name>	<user>	<pass>	<db>
  Create a new connection which is going to be authenticate using PLAIN
  mechanism.


Notes
=====

* The caches maintained by X Plugin and caching_sha2_password are independent. MySQL Server does not provide a mechanism to share the data between them.

* SHA256_MEMORY authentication uses the same flow as MYSQL41 which is implemented by Sasl_mysql41_auth class. Its possible to extract some base class for both.

* NONCE that it utf8 compatible is already generated in caching_sha2_password using call "generate_user_salt(scramble, SCRAMBLE_LENGTH + 1);"