And you hto such? The evolution of MySQL and MariaDB authentication protocols in faces

In ancient times, before Facebook and Google, when 32 megabytes of RAM was dofig as much, security was also ... a bit naive. The viruses pushed the CD-ROM tray and played Yankee Doodle. The article "Smashing the stack for fun and profit" has already been conceived, but not yet written. Everyone used telnet and ftp, and only the most advanced paranoids knew about ssh.

About this time, plus or minus a year, MySQL was born and there were users in it who should not be allowed to other people's data, but allowed to their own.

Michael Widenius (or simply Monty) was clearly familiar with paranoid bezopasnichny safe, which is worth one such point (from the source, global.h ):
')

 /* 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 

So it is no wonder that passwords in MySQL have never been transmitted in clear text. Random strings based on hashes were transmitted. Specifically, the first authentication protocol (cited from mysql-3.20, 1996) worked like this:

• Server stored password hash . However, the hash was quite simple, something like this:
  
  

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

  
  This, for a moment, is 32 bits in all.
  
  
• For authentication, the server sent a random string of eight letters to the client.
  
  
• The client considered the hash (the one above) of this line and the hash of the same password. Then the XOR of these two resulting 32-bit numbers initialized a random number generator, generated eight “random” bytes and sent them to the server.
  
  
• The server, in turn, took the password hash (which he knew) and XOR-il it with the hash of that random string (which he himself generated, that is, he also knew it). Well, I ran a random number generator on my side and compared it with what the client sent.

The advantages of this solution were obvious. The password has never been sent in clear text. And it was not stored in clear form, either. But, really, 32 bits per hash is not serious even in 1996. Therefore, in the next major release (mysql-3.21), the hash was 64-bit. And in this form, under the name of "old mysql authentication", this protocol lives now. MySQL 5.7 sawed it out, but at 5.6 it was still there, and MariaDB even has it at 10.2. I sincerely hope that no one uses it now.

* * *

The main problem of this scheme, as we realized somewhere in the millennial region, is that the password is stored, suddenly, in clear text. Yes Yes. That is, the password hash is stored, as if, but the client doesn’t need a password - the hash is used for authentication. That is, it is enough to drag away the mysql.user nameplate with password hashes and, after a slight modification of the client library, you can connect like anyone.

Well, this self-made hash function was very suspicious. As a result, by the way, it was broken ( sqlhack.com ), but by that time we already had a new protocol.

We thought of it then (and “we” were me, kostja , Peter Zaitsev, and several other comrades) with such goals:

• What is stored on the server is not enough for authentication
• What is sent over the wire is not enough to authenticate
• Bonus - use the normal crypto hash function, enough amateur

And we got the following "double-SHA1" protocol, which was included in MySQL-4.1 and is still used unchanged:

• The server stores SHA1(SHA1(password)).
  
  
• For authentication, the server still sends a random string (20 letters) to the client - which is historically called “scramble”.
  
  
• The client sends the following to the server:
  
  

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

  
  where ⊕ is XOR and || - string concatenation.
  
  
• Accordingly, the server does not know SHA1(password) , but it knows scramble and SHA1(SHA1(password)) , which means it can calculate the first operand in this client construct. Then he gets the XOR th second, that is, SHA1(password) . And, counting from it, SHA1 can finally compare it with what is stored in the table for this user. Uff.

The protocol was successful, all goals were achieved. It is useless to listen to authentication, it is useless to squeeze password hashes. But the fly in the ointment was, after all, if someone managed to pull down the mysql.user table with password hashes and listen to the authentication — then he would be able to repeat what the server does and restore SHA1(password) in order to pretend to be appropriate user This we have not closed, and I have a strong suspicion that without public key cryptography it is not locked in principle. However, this very small fly in the ointment, if you already have hashes, passwords are often easier to pick up using a dictionary.

* * *

Everything was good, but progress, alas, does not stand still. MySQL has come under the wing of Orakla, MariaDB has budded and healed with its life, and, regardless of these perturbations, the reliability of SHA1 fell every year. The first bustled in Orakle. The development of a new protocol was commissioned to comrade Christopher Peterson. By that time I was already in MariaDB, so I can only guess what he was thinking and with whom he consulted. However, the main thing is clear - the goal was to switch to SHA2 and remove this small remaining spoon of tar. He correctly understood that public key cryptography is needed. So the new protocol in MySQL-5.7 uses SHA256 (256-bit version of SHA2) and RSA. And it works like this:

• SHA256(password) is stored on the server SHA256(password)
• The server, as before, sends a 20-letter scramble to the client
• The client reads the server's public RSA key from the file stored in advance
• The client XOR-it password received scramble-ohm (if the password is longer, scramble repeats in the loop), encrypts the server key and sends
• The server, accordingly, decrypts with its secret key, XOR-it back, gets the password in its original open form, counts the SHA256 from it and compares

It's pretty simple. A minus, from my point of view, is one, but a big one - it’s damn inconvenient to distribute the server public key in advance to all clients. And there may still be a lot of servers, and one client may need to connect to all in turn. For this, probably, in MySQL they did that the client can request the public key from the server. But in our troubled time, this option cannot be seriously considered - well, in fact, why would the client believe that some set of bytes that someone sent to him is really the public key of the server? Man-in-the-middle has not been canceled yet. And yet, somehow it is not good that the server receives the password in the clear, is it not enough? Trifle, but unpleasant.

But in MariaDB there was not even this, only SHA1. In principle, it was enough, although the banks grumbled. But in the light of the latest news, we also began to look for a replacement for a veteran hash. In fact, nothing terrible has happened yet - well, they learned to look for collisions, well, can someone generate two passwords with the same hash, and then what? But every user can not explain it. Yes, and suddenly tomorrow something else will be found for SHA1?

* * *

I also built a new protocol based on public key cryptography. So that neither mysql.user , nor interception of traffic, or both of them together, or even a complete compromise of the server would be able to open the password. And, of course, from the user's point of view, everything should have worked as before - entered a password, got access. No files to distribute in advance. The protocol was based on ed25519 , this is a crypto signature using an elliptic curve, which was invented by the legendary Daniel J. Bernstein (or just djb). He also wrote several ready-to-use implementations, one of which is used in OpenSSH. By the way, the name comes from the type of curve used (Edwards curve) and the order of the 2 ²⁵⁵ –19 field. Usually (in openssh and everywhere else) ed25519 works like this (omitting the math):

• 32 random bytes are generated - this will be the secret key (almost).
• SHA512 is considered from them, then all mathematical magic occurs and a public key is obtained.
• The text is signed with a secret key. The signature can be checked with a public key.

Based on this, the new authentication protocol in MariaDB works:

• Instead of random 32 bytes, we simply take the user's password (that is, the password is actually the secret key), and then SHA512 and then calculate the public key, as usual.
• The public key is stored on the server as a password in mysql.user (43 bytes in base64)
• For authentication, the server sends a random 32-byte scramble
• Client signs it
• The server verifies the signature

Everything! Even easier than with SHA1. The drawbacks, in fact, are not yet visible - the password is not stored on the server, is not sent, the server does not see it at all and cannot recover it at any time. Man-in-the-middle rests. There are no files with keys. Naturally, passwords can be brutal, well, there's nothing you can do, just refuse passwords at all.

This new protocol first appeared in MariaDB-10.1.22 as a separate plug-in, and in 10.2 or 10.3 it will be more tightly integrated into the server.