Creating MySQL caching_sha2_password hash in Ruby

Documented hash format

The MySQL source documents the hash format as:

DELIMITER[digest_type]DELIMITER[iterations]DELIMITER[salt][digest]

Where:

• DELIMITER is always $
• digest_type is A (SHA256), the only option as of writing
• iterations is usually 005 and means 5000 SHA256 iterations

That means, for now, every password hash has the same prefix:

$A$005$

After that we concatenate the 20 byte salt/nonce and the 43 byte digest.

SHA256 digest

The digest is a bit of an ugly duck, it's not actually SHA2 as the name suggests. A URL in the source code points to crypt-sha2, which is one of the formats that unix crypt uses for passwd files. However, MySQL takes only the last part and appends it to their custom format described above. The format used by unix crypt contains the same metadata, so they could have used it as-is.

Crypt returns hashes in the format:

DELIMITER[digest_type]DELIMITER[salt]DELIMITER[digest]

To make things even more weird: the MySQL source contains their own implementation of crypt that doesn't follow the standard completely. They use 20 byte salt/nonce instead of the maximum 16 bytes allowed by crypt. That means you can't use any default crypt implementation to generate the MySQL password hashes.

Aside: possible bug

When using a custom amount of rounds in crypt, an extra rounds= parameter is added to the output. MySQL's own crypt implementation also adds this metadata even though they will throw it away later. The code that extracts the digest can't handle crypt output with extra parameters. Thus, using anything other than 5000 rounds will fail.

The caching_sha2_password is a bit of a mess. I don't understand why they made this the new default in recent MySQL versions. While researching the format I also noticed they generate an additional salt that is never used. Things like that make it hard to understand a badly documented format, especially if you don't speak fluent C.

Tests in MySQL

Password test:

alter user 'test'@'localhost' identified with caching_sha2_password by 'test';

Password as visible in mysql.user:

$A$005$d;nd5&?o[XK)d,M0thhy6jHzokdXUFgrl9ma06H8whUFhbQSTtY3gKp7mlCD

Password as shown by show create user when print_identified_with_as_hex=1:

0x24412430303524643B6E6435263F6F0F165B584B1A29642C4D3074686879366A487A6F6B6458554667726C396D6130364838776855466862515354745933674B70376D6C4344

When I hex decode that myself I get a different string:

$A$005$d;nd5&?o[XK)d,M0thhy6jHzokdXUFgrl9ma06H8whUFhbQSTtY3gKp7mlCD

The hash shown with mysql.user is not long enough, so the non printable characters are important.