WL#9271: Mysqlx authentication of users using SHA256-auth-plugin

Affects: Server-8.0   —   Status: Complete


X Plugin has implemented two types of authentication: "plain" and "mysql41".
Both types use 'mysql_native_password' plugin entries in `mysql`.`user` table
(MySQL Server account types). This worklog is about supporting MySQL Server
accounts that use 'sha256_password' plugin. 

Authentication against 'sha256_password' transmits clear-text-pasword which is
computed in MySQL Server and salt/hash is compared to data stores in in `user`
table. This enforces that X Plugin must allow this type of account to be
considered only when "TLS" is enabled with X Plugin "plain" authentication.

Mapping of X Plugin authentication types to MySQL Server account "types":

|X Plugin authentication method  |MySQL Server auth method    |Requires TLS |
|plain                           |mysql_mysql_native_password |yes          |
|plain                           |sha256_password             |yes          |
|mysql41                         |mysql_mysql_native_password |no           |


'sha256_password' plugin entries can't be used in/like "MySQL41" authentication
or "SCRAM-SHA256", because of the algorithm chosen in sha256_password.
The algorithm uses [CRYPT-SHA] which isn't as dynamic as [PBKDF2]. It has
constant "salt" which leads to that the same "scramble" is always transferred
between client and server (it could be said that "scramble" is the "new
password"). The 'sha256_password' defines the password hashing and storage
format, also the usage with plain text authentication protocol and a encrypted
authentication protocol (classic protocol). X Plugin is going to reuse only the
hash storage and use it on  plain text authentication (X Protocol).

[CRYPT-SHA] is good for storing the hash in database but not for authentication
exchange on the wire.

[SCRAM-SHA-256] - https://tools.ietf.org/html/rfc7677
[SCRAM] - https://tools.ietf.org/html/rfc5802
[PBKDF2] - https://en.wikipedia.org/wiki/PBKDF2
[CRYPT-SHA] - https://www.akkadia.org/drepper/SHA-crypt.txt
[sha256_password] -
Functional Requirements

1. User must be able to trigger authentication using "plain" method against
   MySQL Server account identified by 'sha256_password' at new connection or
   session reset

Generic Authentication Related Requirements

1. Authentication against MySQL account identified by 'sha256_password' must
   fail when the hash-algorithm doesn't generate same hash as the one
   associated with the user.

2. Authentication against MySQL account identified by 'sha256_password' must
   fail when the user is locked

3. Authentication against MySQL account identified by 'sha256_password' must
   fail when the user doesn't have `super` privilege and server works in
   offline mode

4. Authentication against MySQL account identified by 'sha256_password' must
   fail when the password expired which isn't supported by client and server
   system variable `disconnect_on_expired_password` is set to `true`

5. Authentication against MySQL account identified by 'sha256_password' must
   fail when `ssl/tls` options set on this account aren't fulfilled

6. Authentication against MySQL account identified by 'sha256_password' must be
   successful non of 1 to 5 is fulfilled

7. User need to be informed that he uses account which password expired with
   `Notice` when the authentication was successful


Please consider that "plain" methods requires that connection uses TLS or its
type is considered secure like UNIX socket connections. With this old
requirement we do not need to take into the account system variable

New functionality considers only secure connections, which means that the
connection need to have TLS activated (by sending "CapabilitiesSet" with
capability "tls" set) or the connection need to be made through UNIX socket.

New authentication behaviour must be triggered by already existing flow of
setting up session, when the authentication mechanism is set to "plain"
(presented bellow).

Client -> Server: AuthenticateStart([mech_name="plain", ...])
  Server --> Client: AuthenticateContinue
  Client --> Server: AuthenticateContinue
  Server --> Client: Frame([type=Warning, ...])
  Server --> Client: AuthenticateOk
  Server --> Client: Error

After reception of `AuthenticateStart`, X Plugin must try to validate the user
based on rows in `mysql`.`user` table, which must have in column `plugin` 
either "mysql_native_password" or "sha256_password" (new authentication method).

Interface specification


When X Plugin finds a matching user entry in 'mysql'.'user' table (`host`,
`user`, `plugin` columns) it should try to authenticate the user by using same
crypto-function as 'sha256_password` plugin does. Crypto function is implemented
according [crypt-sha] algorithm and requires algorithm iteration count and salt.
Both parameters are saved in `authentication_string` column with the expected
result (hash).

When the expected hash matches the one calculated from the clear-text-password,
X Plugin must create a security context with corresponds to the user with all
his access rights.

Error and Warnings

Currently X Plugin maps all error codes that it gets while authenticating
to `ER_ACCESS_DENIED_ERROR`. Same behaviour should be preserved in case of MySQL
Server account which use 'sha256_password' plugin.

|Error name             |Error code   |
|ER_ACCESS_DENIED_ERROR |1045         |
X Plugin authentication methods

X Plugin implements two authentication methods "plain" and "mysql41". The
classes that implements those are following "Sasl_mysql41_auth",
"Sasl_plain_auth". They assume that database stores the HASH in form of sha1 and
pass corresponding verification function to "Sql_data_context::authenticate"
(Service session wrapper).

Service session and authentication

"Sql_data_context" class is responsible for handling of data context, which also
handles the "switch" of security context from one user to another. The service
session API doesn't support account password verification, thus "authenticate"
method must builds a SQL query and get account informations (stored in
"mysql"."user" table). If `user` and `host` columns match with the user
that tries to connect then hash verification function is being called with data
from column `authentication_string`.

Code impact

The hashing function takes only `authentication_string` from the `user` table,
still it needs to take `plugin` entry and based on those parameters compare the
user-hash and `authentication_string`. Thus function should be removed and
replaced by following interface:

  enum Account_type {
    Account_native = 1,
    Account_sha256 = 2

  class Account_verification_interface {
    virtual std::vector get_supported_accounts() = 0;
    virtual bool verify_authentication_string(
      const std::string &hash,
      const Account_type account_type) = 0;

'Sql_data_context::authenticate' should check if the type of account is
supported by selected authentication method and compare the data from `plugin`
column with `vector` of supported `Account_types`. If the type is supported the
the code should proceed with hash verification by calling

The implementation of `Account_verification_interface` must consider that the
current X Plugin authentication mechanism ('plain', 'mysql41) can use specific
MySQL Server authentication plugins. For example 'plain' must support
"authentication_string" stored by 'mysql_native_password' and 'sha256_password'