Caching SHA256 first send a SHA256 encrypted password. MySQL server has an in-memory cache of SHA256 key for successful authentication. When a cache hit occur, the connection is validated, if not, using some more steps to a process similar to sha256_password.

Caching SHA256 authentication possible exchanges:

• client send a SHA-2 encrypted password

• server result in either OK_Packet , ERR_Packet or "fast" authentication result

• if fast authentication result

  • if connection use SSL (SSLRequest Packet sent)

    • client send a clear password answer

  • else

    • if client doesn't know server RSA public key

      • client sends a public key request

      • server sends a public key response

    • client sends an RSA encrypted password

    • ends with server sending either OK_Packet , ERR_Packet

SHA-2 encrypted password

Encryption is XOR(SHA256(password), SHA256(seed, SHA256(SHA256(password))))

• byte<32> encrypted password

"fast" authentication result

result of fast authentication.

• byte authentication result

0x03 value means success authentication. 0x04 value means continue

Client clear password answer

• string password without encryption

Public key request

• byte<1> fixed 0x02 value

Public key response

• byte<1> fixed 0x01 value

• byte public key data

RSA encrypted password

• byte<256> RSA encrypted password

RSA encrypted value of XOR(password, seed) using server public key (RSA_PKCS1_OAEP_PADDING).

_{This page is licensed: CC BY-SA / Gnu FDL}

Connection is done by many exchanges:

• (Create socket)

• If first byte from server is 0xFF:

  • packet is an ERR_Packet, socket has to be closed

• else

  • Packet is an Initial handshake packet

  • If SSL/TLS connection

    • Client sends SSLRequest packet and switches to SSL mode for sending and receiving the following messages:

  • Client sends Handshake response packet

  • Server sends either:

    • An OK packet in case of success OK_Packet

    • An error packet in case of error ERR_Packet

    • Authentication switch

      • If the client or server doesn't have PLUGIN_AUTH capability:

        • Server sends 0xFE byte

        • Client sends old_password

      • else

        • Server sends Authentication switch request

        • Client may have many exchange with the server according to the Plugin.

      • Authentication switch ends with server sending either OK_Packet or ERR_Packet

Initial Handshake Packet

• int<1> protocol version

• string server version (MariaDB server version for 10.X versions is by default prefixed by "5.5.5-". MariaDB 11.0 and later versions do not have a "5.5.5-" default prefix)

• int<4> connection id

• string<8> authentication plugin data (1st part)

• string<1> reserved byte

• int<2> server capabilities (1st part)

• int<1> server default collation

• int<2> status flags

• int<2> server capabilities (2nd part)

• if (server_capabilities & PLUGIN_AUTH)

  • int<1> plugin data length

• else

  • int<1> 0x00

• string<6> filler

• if (server_capabilities & CLIENT_MYSQL)

  • string<4> filler

• else

  • int<4> server capabilities 3rd part . MariaDB specific flags /* MariaDB 10.2 or later */

• if (server_capabilities & CLIENT_SECURE_CONNECTION)

  • string authentication plugin data 2nd part . Length = max(12, plugin data length - 9)

  • string<1> reserved byte

• if (server_capabilities & PLUGIN_AUTH)

  • string authentication plugin name

Client Handshake Response

If the client requests a TLS/SSL connection, first response will be an SSL connection request packet, then a handshake response packet. If no TLS is required, client send directly a handshake response packet.

SSLRequest Packet

• int<4> client capabilities

• int<4> max packet size

• int<1> client's default character set and collation

• string<19> reserved

• if not (server_capabilities & CLIENT_MYSQL)

  • int<4> extended client capabilities

• else

  • string<4> reserved

ZERO-CONFIGURATION SSL ENCRYPTION

Automatic Encrypted Connections (MariaDB 11.4+):

Previously, failed SSL connections due to self-signed certificates prevented communication. MariaDB 11.4+ introduces a secondary validation method that works for all servers.

What Happens When SSL Validation Fails?

Even without a valid SSL certificate, the connector can still authenticate by remembering the server's fingerprint (unique identifier). However, it needs to confirm the connection is secure.

Verifying a Secure Connection:

The confirmation method depends on the connection type. When using secure MitM-proof methods, like Unix sockets, connector can automatically validate the connection. Otherwise, a shared secret is used.

Shared Secret for Secure Connection:

The shared secret is only used if the authentication plugin password is hashable (e.g., mysql_native_password , client_ed25519 or parsec) and not empty.

It's calculated by hashing the user's hash password with the authentication seed and the server fingerprint.

Password hash is generated depending on authentication plugin:

• ed25519 : identical to password encryption

• native password : identical to password encryption

• parsec: ext-salt + raw ed25519 public key

Server 11.4+ Confirmation Details:

For servers running MariaDB 11.4 or later, the final confirmation packet contains:

• int<1> encryption (actually only 0x01 = SHA256 encryption)

• byte shared secret.

Matching the Shared Secret:

If the calculated shared secret matches the received one, the SSL connection is considered valid (host validation is not needed). Otherwise, the connection must be closed for security reasons.

Handshake Response Packet

• int<4> client capabilities

• int<4> max packet size

• int<1> client's default character set and collation

• string<19> reserved

• if not (server_capabilities & CLIENT_MYSQL)

  • int<4> extended client capabilities

• else

  • string<4> reserved

• string username

• if (password)

  • if (server_capabilities & PLUGIN_AUTH_LENENC_CLIENT_DATA)

    • string authentication data

  • else if (server_capabilities & CLIENT_SECURE_CONNECTION)

    • int<1> length of authentication response

    • string authentication response (length is indicated by previous field)

  • else

    • string authentication response null ended

• else

  • string<1>\0 (empty password)

• if (server_capabilities & CLIENT_CONNECT_WITH_DB)

  • string default database name

• if (server_capabilities & CLIENT_PLUGIN_AUTH)

  • string authentication plugin name

• if (server_capabilities & CLIENT_CONNECT_ATTRS)

  • int size of connection attributes

  • while packet has remaining data

    • string key

    • string value

Server Response to Handshake Response Packet

The server responds with an OK_packet, an ERR_packet or an Authentication Switch Request packet.

Authentication Switch Request

(If client and server support CLIENT_AUTH capability)

• int<1> 0xFE : Authentication switch request header

• string authentication plugin name

• byte authentication plugin data

Plugin List

mysql_old_password Plugin

deprecated send a 8 byte encrypted password

authentication plugin data format :

• byte<8> 8-byte seed

Client response :

• string old format encrypted password

mysql_clear_password Plugin

send clear password to server

Client response :

• string password without encryption

mysql_native_password Plugin

SHA-1 encrypted password with server seed

authentication plugin data format :

• string seed

Client response :

• byte sha1 encrypted password

The password is encrypted with: SHA1( password ) ^ SHA1( seed + SHA1( SHA1( password ) ) )

dialog Plugin (PAM)

Interactive exchanges to permit fill passwords - for example for 2-Step authentication.

authentication plugin data format :

• byte<1> password type

• string prompt message

The server can send one or many requests. For each of them, the client must display this prompt message to the user, to permit the user to type requested information, then send it to the server in string format. Password type indicate answer format ( 2 means "read the input with the echo enabled", 4 means "password-like input, echo disabled")

First authentication format (from authentication switch packet) can be empty since 10.4.

This end when server send an EOF_Packet, OK_Packet or ERROR_packet.

auth_gssapi_client Plugin

gssapi implementation

authentication plugin data format :

• string serverPrincipalName (UTF-8 format)

• string mechanisms (UTF-8 format)

Client must exchange packet with server until having a mutual GSSAPI authentication. The only difference compared to standard client-server GSSAPI authentication is that exchanges contain standard protocol with packet headers.

client_ed25519 Plugin

The ed25519 plugin uses the Elliptic Curve Digital Signature Algorithm to securely store users' passwords and to authenticate users. It has been Implemented in the server since MariaDB 10.1.22.

See plugin description.

The server sends a random nonce that the client signs.

authentication plugin data format :

• byte seed

Client response :

• byte ed25519 encrypted password

parsec Plugin

authentication plugin data format :

• string<32> server nonce

Client has to send an empty packet to request "ext-salt".

Format of ext-salt is

• string<1> 'P' (denotes KDF algorithm = PBKDF2)

• byte<1> iteration factor. number of iterations correspond to 1024 << iteration factor (0x0 means 1024, 0x1 means 2048, etc.)

• byte salt

client must then :

• generate derived key = hash password with PBKDF2 ( sha512 digest) with iteration number and salt from ext-salt.

• generate a client 32 bytes nonce

• generate the signature with ed25519 of an array concatenation of server nonce + client nonce with the generated derived key as private key.

Client response :

• byte<32> client nonce

• byte<64> signature

Capabilities

Server and Client have different capabilities, here is the possibles values. client with capabilities CLIENT_MYSQL + CONNECT_WITH_DB will have a value of 9 (1 + 8).

Native Password Authentication

The 20 byte string 'seed' is calculated by concatenating scramble first part (8 bytes) and scramble second part from Initial handshake packet. After that, the client calculates a password hash using the password and seed by using ^ (bitwise xor), + (string concatenation) and SHA1 as follows:

SHA1( passwd) ^ SHA1( seed + SHA1( SHA1( passwd ) ) )

_{This page is licensed: CC BY-SA / Gnu FDL}

SHA256 authentication possible exchanges:

• if connection use SSL (SSLRequest Packet sent)

  • client send a clear password answer

• else

  • if client doesn't know server RSA public key

    • client sends a public key request

    • server sends a public key response

  • client sends an RSA encrypted password

  • ends with server sending either OK_Packet , ERR_Packet

Client clear password answer

• string password without encryption

Public key request

• byte<1> fixed 0x01 value

Public key response

• byte<1> fixed 0x01 value

• byte public key data

RSA encrypted password

• byte<256> RSA encrypted password

RSA encrypted value of XOR(password, seed) using server public key (RSA_PKCS1_OAEP_PADDING).

_{This page is licensed: CC BY-SA / Gnu FDL}