Who are you? The history of MySQL and MariaDB authentication protocols from 1997 to 2017

MySQL 3.20 to 4.0

In the good old days, when 32MB of RAM justified the name my-huge.cnf, when nobody knew Google and Facebook didn’t even exist, security was… how do I put it… kind of cute. Computer viruses didn’t steal millions and didn’t disrupt elections — they played Yankee Doodle or told you not to play with the PC. People used telnet and ftp, although some security conscious admins already knew ssh.

Somewhere around this time, give or take a few years, MySQL was born. And it had users, who had to be kept away from seeing others’ data, but allowed to use their own. This called for authentication.

Michael Widenius (or Monty) apparently was aware that some users are paranoid about security. The proof can be found even in early MySQL versions:

/* Paranoid settings. Define I_AM_PARANOID if you are paranoid */
#ifdef I_AM_PARANOID
#define DONT_ALLOW_USER_CHANGE 1
#define DONT_USE_MYSQL_PWD 1
#endif

This is from the global.h of MySQL–3.21. It should come as no surprise that MySQL never sent passwords from the client to the server in clear text. It sent random bytes that were generated from hashes of passwords. Technically, the first MySQL authentication protocol (as in MySQL–3.20, 1996) worked as follows:

• Server has stored the password hash in the mysql.user table. The hash function was rather simple, though:

  for (; *password ; password++)
  {
    tmp1 = *password;
    hash ^= (((hash & 63) + tmp2) * tmp1) + (hash << 8);
    tmp2 += tmp1;
  }
  

  Note, that the hash value was only 32 bits!

• During the authentication, the server started the handshake with a string of 8 random letters (called scramble).
• The client calculated the hash (as above) of this scramble, and the hash of the password. XOR of these two numbers produced one 32-bit seed that was used to initialize a pseudo-random number generator. That generator generated “random” 8 bytes and they were sent to the server.
• The server, basically, repeated the same — it knew the scramble, it had the hash of a password from the mysql.user table. So it also initialized a random number generator, generated 8 bytes, and compared them with what the client had sent.

This wasn’t a bad protocol. It had obvious strengths, for example, the password was never sent in clear. And was never stored in clear either. But, seriously, 32-bit? That wasn’t enough even in 1996. Which is why the next major MySQL release — 3.21 — used 64-bit hashes. Otherwise the protocol stayed the same. And it is still present (although not the default) in MySQL–5.6 and MariaDB–10.2. Luckily, it was removed from MySQL–5.7. I really hope nobody uses it nowadays.

MySQL 4.1 to 5.7

The main flaw of this protocol, as we suddenly realized in the early 2000, was that it stored the password in plain text. No kidding. It kind of stored the hash, but the client only needed the hash for authentication, not the password. That is, if someone could read password hashes from the mysql.user table, it would only take a small modification of the client library to use these hashes directly and to login as just anyone.

Also this hash function was rather suspicious. Not that we knew how to reverse it, but we still didn’t fully trust it. And indeed, it was cracked later, but by that time we already had a replacement.

For the new protocol we (that is, me, Konstantin Osipov, Peter Zaitsev, and a few others) set the following goals:

• Sniffing the authentication should not allow the attacker to authenticate as anyone
• Stealing the whole mysql.user table should not allow the attacker to authenticate as anyone
• Bonus point: use a proven well-known cryptographic hash function

And this is how the double-SHA1 (or new or mysql_native_password) protocol was created. It was first introduced in MySQL–4.1 and is still the most widely used MySQL authentication protocol. Every MySQL and MariaDB version supports it. It works as follows:

• The server stores SHA1(SHA1(password)) in the mysql.user table.
• For authentication the server sends a random 20-letter scramble.
• The client computes the following:

  SHA1( scramble || SHA1( SHA1( password ) ) ) ⊕ SHA1( password )

  where ⊕ is XOR and || is string concatenation. And sends it to the server.

• The server doesn’t know SHA1(password), but it knows the scramble and SHA1(SHA1(password)), so it can calculate the first part of the expression, and with a XOR it can get SHA1(password).
• Now all it needs to do is to calculate the SHA1 of SHA1(password) from the previous step, and it can compare the result with what is stored in the mysql.user table. Mission accomplished.

This protocol achieved all goals — sniffing the authentication handshake or stealing the mysql.user table would not help the attacker to impersonate users. Still, it wasn’t perfect. The server received SHA1(password) (so it could show up in core dumps, be extracted from the server memory, etc) and that was sufficient to impersonate a user. Furthermore, if someone was able to sniff the authentication handshake and steal the mysql.user table, he would be able to repeat all steps that server did, extract SHA1(password) and impersonate a legitimate user too. While it was a flaw, it was not a major issue — when one has password hashes it’s usually easier just to brute-force them. And I had a feeling that this flaw was impossible to fix anyway, unless we resort to public key cryptography.

MySQL 5.7 and up

Our users relied on double-SHA1 authentication. Life went on. MySQL was acquired by Sun, Sun was acquired by Oracle. MariaDB took off and grew in popularity. And, unrelated to all that, SHA1 was gradually considered less and less secure. Something had to be done. MySQL was the first to offer a replacement. A task of implementing a new authentication plugin was delegated to Kristofer Pettersson. I wasn’t in MySQL at that time, so I cannot know who he was discussing it with and what his goals were. But the basics are clear — move away from SHA1 and remove this last flaw. He correctly decided to use public key cryptography. So, the new MySQL 5.7 authentication protocol (sha256_password plugin) uses SHA256 (the 256-bit version of SHA2) and RSA. Together it works like this:

• The server stores SHA256(password), yay!
• During authentication it sends to the client a 20-letter scramble, just as before.
• Client reads the server’s public RSA key from a file that was distributed to the client in advance.
• Client calculates XOR of the password and the scramble (repeated as necessary to cover the whole password), encrypts it using the server’s public key, and sends to the server.
• Server decrypts the data using its secret RSA key, XORs the result with a scramble to extract the client’s plain-text password, calculates SHA2 of it, and compares that with the stored value from the mysql.user table.

Quite straightforward. There is just one nuisance. One has to distribute the server’s public key to all clients. And every client needs to have public keys for all servers it wants to connect to and juggle them as needed. I could see where it could become rather annoying, and probably that’s why if the client does not have the server’s public key, the server helpfully provides it, during authentication, at the cost of one round-trip. Of course, no security-conscious person should ever rely on that, how would the client know that the public key is authentic? A man-in-the-middle can replace it with his own public key and he’ll be able to obtain the client password in plain-text! And again, not that it’s particularly bad, but the server still gets the password in plain-text. Just like it did in all previous authentication protocols.

MariaDB 10.1 and up

But MariaDB didn’t have even that. We were still relying on the old proven double-SHA1 protocol. Banks were grumbling, but it was still secure enough. Nevertheless, in the light of recent news we’ve also started looking for alternatives. Double-SHA1 is still safe, mind you, even after taking recent research into account. The research shows than one can generate SHA1 collisions now, meaning one can generate two passwords that will have the same SHA1 hash. That’s bad for SHA1, but doesn’t really weaken our authentication. Still… It shows that SHA1 doesn’t have that much time left, eventually someone might find some way to break it.

I’ve also built the new authentication protocol using public key cryptography. With the goal that not the traffic sniffing, not the mysql.user table, not both together, not even a fully compromised server would be able to recover the password. And the behavior should stay as before — no files to distribute, no new round-trips in the protocol. The result is a protocol that uses ed25519 — elliptic curve digital signature algorithm that uses Edwards curve over the finite field of order 2²⁵⁵–19 (that’s why the algorithm is called Ed-255-19). Both the curve and the algorithm itself were invented by the well-known Daniel J. Bernstein (or, simply, djb). He and his team also created a few reference public domain implementations of ed25519, one of which is used in OpenSSH. The new MariaDB authentication protocol also uses one of these reference ed25519 implementations, and works like this:

• The user’s password is the secret key. We calculate SHA512(password) and applying some math magic convert it into a public key. This public key is stored by the server in the mysql.user table.
• For authentication the server sends a random 32-byte scramble to the client.
• The client signs it with its secret key.
• The server verifies the signature with the user’s public key.

That’s all! Much simpler than with double-SHA1. And it’s as secure as it can be, the password is not stored on the server, not sent anywhere, the server doesn’t even see it at any point in time and cannot restore it from the information it has. Nothing for the man-in-the-middle here either. No files. Same number of round-trips. One can still brute-force passwords, given the mysql.user table, but there’s nothing we can do about that.

This new protocol was first introduced in MariaDB–10.1.22 as a plugin, and it will be more conveniently integrated into the server in future versions.