MySQL  8.0.11
Source Code Documentation
Connection Phase

The Connection Phase performs these tasks:

  • exchange the capabilities of client and server
  • setup SSL communication channel if requested
  • authenticate the client against the server

It starts with the client connect()ing to the server which may send a ERR packet and finish the handshake or send a Initial Handshake Packet which the client answers with a Handshake Response Packet. At this stage client can request SSL connection, in which case an SSL communication channel is established before client sends its authentication response.

Note
In case the server sent a ERR packet as first packet it will happen before the client and server negotiated any capabilities. Therefore the ERR packet will not contain the SQL-state.

After initial handshake, server informs client about the method to be used for authentication (unless it was already established during the handshake) and the authentication exchange continues until server either accepts connection by sending an OK_Packet or rejects it with ERR_Packet.

Initial Handshake

The initial handshake starts with the server sending the Protocol::Handshake packet. After this, optionally, the client can request an SSL connection to be established with the Protocol::SSLRequest: packet and then the client sends the Protocol::HandshakeResponse: packet.

Plain Handshake

  1. Server sending Protocol::Handshake.
  2. Client replying with Protocol::HandshakeResponse:

SSL Handshake

  1. Server sending Protocol::Handshake
  2. Client replying with Protocol::SSLRequest:
  3. The usual SSL exchange leading to establishing SSL connection
  4. Client sends Protocol::HandshakeResponse:

Capability Negotiation

To permit an old client to connect to newer servers, the Protocol::Handshake contains

the MySQL Server version the server's Capabilities Flags

The client should only announce the capabilities in the Protocol::HandshakeResponse: that it has in common with the server.

They can agree on: use of status flags use of SQL states for error codes authentication methods SSL Support Compression

Determining Authentication Method

Method used for authentication is tied to the user account and stored in the plugin column of mysql.user table. Client informs about the user account it wants to log into in the Protocol::HandshakeResponse: packet. Only then server can look up the mysql.user table and find the authentication method to be used.

However, to save round-trips, server and client start authentication exchange already in the initial handshake using an optimistic guess of the authentication method to be used.

Server uses its default authentication method default_auth_plugin to produce initial authentication data payload and sends it to the client inside Protocol::Handshake, together with the name of the method used.

Client can include in the Protocol::HandshakeResponse: packet its reply to the authentication data sent by the server.

When including authentication reply in the Protocol::HandshakeResponse:, client is not obligated to use the same authentication method that was used by the server in the Protocol::Handshake packet. The name of the authentication method used by the client is stored in the packet. If the guessed authentication method used either by the client or the server in the initial handshake was not correct, server informs client which authentication method should be used using Protocol::AuthSwitchRequest:. See section Authentication Method Mismatch for more details.

Up to MySQL 4.0 the MySQL protocol only supported the Old Password Authentication. In MySQL 4.1 the Native Authentication method was added and in MySQL 5.5 arbitrtary authentication methods can be implemented by means of authentication plugins.

If the client or server do no support pluggable authentication (i.e. CLIENT_PLUGIN_AUTH capability flag is not set) then authentication method used is inherited from client and server capabilities as follows: The method used is Old Password Authentication if CLIENT_PROTOCOL_41 or CLIENT_SECURE_CONNECTION are not set. The method used is Native Authentication if both CLIENT_PROTOCOL_41 and CLIENT_SECURE_CONNECTION are set, but CLIENT_PLUGIN_AUTH is not set.

Authentication Phase Fast Path

Assume the client wants to log in via user account U and that user account is defined to use authentication method server_method. The fast authentication path is used when:

  • the server used server_method to generate authentication data in the Protocol::Handshake packet.
  • the client used a client_authentication_method in Protocol::HandshakeResponse: that is compatible with the server_method used by the server.

In that case the first round of authentication has been already commenced during the hanshake. Now, depending on the authentication method server_method, further authentication can be exchanged until the server either accepts or refuses the authentication.

Successful Authentication

A successful fast authentication path looks as follows:

  1. The client connects to the server
  2. The server sends Protocol::Handshake
  3. The client respons with Protocol::HandshakeResponse:
  4. Client and server possibly exchange further packets as required by the server authentication method for the user account the client is trying to authenticate against.
  5. The server responds with an OK_Packet

The packets the server sends in step 4 are a Protocol::AuthMoreData: packet prefixed with 0x01 to distinguish them from ERR_Packet and OK_Packet

Note
Many authentication methods, including the mysql_native_password method consist of a single challenge-response exchange. In that case no extra packet are exchanged in step 4 and the server sends an OK_Packet directly after receiving the Protocol::HandshakeResponse: packet (provided the authentication was successful).

Authentication Fails

It goes exactly like Successful Authentication , but if the server decides that it won't authenticate the user it replies with an ERR_Packet instead of OK_Packet.

  1. The client connects to the server
  2. The server sends Protocol::Handshake
  3. The client respons with Protocol::HandshakeResponse:
  4. Client and server possibly exchange further packets as required by the server authentication method for the user account the client is trying to authenticate against.
  5. The server responds with an ERR_Packet

Again, the Protocol::AuthMoreData: packets sent by the server during step 4 start with 0x01 byte and thus can never be confused with the ERR_Packet.

Authentication Method Mismatch

Assume that client wants to log in as user U and that user account uses authentication method M. If:

  1. Server's default method used to generate authentication payload for Protocol::Handshake was different from M or
  2. Method used by the client to generate authentication reply in Protocol::HandshakeResponse: was not compatible with M

then there is an authentication method mismatch and authentication exchange must be restarted using the correct authentication method.

Note
  1. The mismatch can happen even if client and server used compatible authentication methods in the intial handshake, but the method the server used was different from the method required by the user account.
  2. In the 4.1-5.7 server and client the default authentication method is Native Authentication.
  3. In 8.0 server and client the default authentication method is Caching_sha2_password information.
  4. The client and the server can change their default authentication method via the --default-auth option.
  5. A sensibe thing to do for a client would be to see the server's default authentication method announced in the Protocol::Handshake packet and infer the authentication method from it instead of using the client default authentication method when producing Protocol::HandshakeResponse:. But since there can be one to many server to client plugins and the clients generally do not know the mapping from server authentication methods to client authentication methods this is not implemented in the client mysql library.

If authentication method missmatch happens, server sends to client the Protocol::AuthSwitchRequest: which contains the name of the client authentication method to be used and the first authentication payload generated by the new method. Client should switch to the requested authentication method and continue the exchange as dictated by that method.

If the client does not know the requested method it should disconnect.

Authentication Method Change

  1. The client connects to the server
  2. The server sends Protocol::Handshake
  3. The client respons with Protocol::HandshakeResponse:
  4. The server sends the Protocol::AuthSwitchRequest: to tell the client that it needs to switch to a new authentication method.
  5. Client and server possibly exchange further packets as required by the server authentication method for the user account the client is trying to authenticate against.
  6. The server responds with an OK_Packet or rejects with ERR_Packet

Insufficient Client Capabilities

Server will reject with ERR_Packet if it discovers that client capabilities are not sufficient to complete authentication. This can happen in the following situations:

In either of these cases authentication phase will look as follows:

  1. The client connects to the server
  2. The server sends Protocol::Handshake
  3. The client respons with Protocol::HandshakeResponse:
  4. The server recognizes that the client does not have enough capabilities to handle the required authentication method, sends ERR_Packet and closes the connection.

New Authentication Method Not Known by Client

Even if client supports external authentication (CLIENT_PLUGIN_AUTH flag is set) the new authentication method indicated in Protocol::AuthSwitchRequest: might not be known to it. In that case the client simply disconnects.

  1. The client connects to the server
  2. The server sends Protocol::Handshake
  3. The client respons with Protocol::HandshakeResponse:
  4. The server sends the Protocol::AuthSwitchRequest: to tell the client that it needs to switch to a new authentication method.
  5. client discovers that it does not know the authentication method requested by the server - it disconnects.

Non-CLIENT_PLUGIN_AUTH Clients

Note
This can only happen on pre-8.0 servers. 8.0 has the Old Password Authentication removed.

The only situation where server will request authentication method change from a client which does not set CLIENT_PLUGIN_AUTH flag is when the following conditions hold:

  1. The client uses Old Password Authentication for the Protocol::HandshakeResponse: packet.
  2. The client supports secure authentication (CLIENT_SECURE_CONNECTION is set)
  3. Server's default authentication method is Native Authentication

In this case server sends Protocol::OldAuthSwitchRequest:. This packet does not contain a new authenticartion method name because it's implicitly assumed to be Native Authentication and it does not contain authentication data. Client replies with Protocol::HandshakeResponse320. To generate a password hash the client should re-use the random bytes sent by the server in the Protocol::Handshake.

Authentication After COM_CHANGE_USER Command

During Command Phase a client can send a COM_CHANGE_USER command which will trigger authenticating into a new account via a full authentication handshake.

Similarly to the Connection Phase the server may reply with a ERR_Packet or OK_Packet for the usual fast-path or with Protocol::AuthSwitchRequest: containing the authentication method to be used for the new account and the first authentication data payload to be consumed by the client. Further handshake continues as usual, as defined by the authentication method of the new account. Eventually the server will accept the new account with OK_Packet or it will reject the change with an ERR_Packet and disconnect.

  1. The client sends COM_CHANGE_USER packet
  2. The server responds with the Protocol::AuthSwitchRequest: which initiates authentication handshake using the correct authentication method
  3. Client and server exchange further packets as required by the authentication method for the new account
  4. The server responds with OK_Packet and returns to command phase or ERR_Packet and closes the connection.

COM_CHANGE_USER and Non-CLIENT_PLUGIN_AUTH Clients

Clients which do not support pluggable authentication can send COM_CHANGE_USER command for accounts which use Native Authentication or Old Password Authentication. In this case it is assumed that server has already sent the authentication challenge - the same which was sent when the client connected for the first time - and client's reply to that challenge, i.e. the hash of the new password, should be sent in the auth_response field of COM_CHANGE_USER.

  1. The client sends COM_CHANGE_USER packet with authentication response (hash of the password) for Native Authentication (post 4.1 clients) or Old Password Authentication (pre 4.1 clients) method.
  2. The server responds with an OK_Packet and returns to Command Phase or with an ERR_Packet and closes the connection.

As during normal connection, it is also possible that a post 4.1 client which does not support pluggable authentication connects to an account which uses Old Password Authentication In that case server will send Protocol::OldAuthSwitchRequest: and expect the client to reply with Protocol::HandshakeResponse320

  1. The client sends COM_CHANGE_USER packet with response for Native Authentication
  2. The server replies with Protocol::OldAuthSwitchRequest: (0xFE byte)
  3. The client sends response again, this time in the form required by Old Password Authentication
  4. The server responds with an OK_Packet and returns to Command Phase or an ERR_Packet and disconnects
See also
Capabilities Flags Connection Phase Packets Authenticatrion Methods