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:

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:

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:

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 ] Performs authentication steps (use with --no-auth)

New connection commands with specified authentication: * newsession 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 Create a new connection which is going to be authenticate using MYSQL41 mechanism.

• newsession_memory Create a new connection which is going to be authenticate using SHA256_MEMORY mechanism.

• newsession_plain 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);"