Why are the passwords in MySQL (5.x) not salted?

Question

I'm working with a legacy application that is connected to a MySQL database (5.x). When I created two different users for the MySQL database with the same password, the hash of those passwords are the same.

Is there an option to enable salting of the passwords in mysql.user?

Answer 1

No, there isn't.

If this is a major issue for you, there is a PAM plugin available from Percona that works with MySQL if you want to authenticate against something external rather than using the internal mechanism. MariaDB has a different solution.

Oracle also has a proprietary (non-free) PAM plugin for people who just like to give them money.

Although, unless you have some kind of compliance issue to deal with, the practicalities of the MySQL authentication protocol are such that this seems like a non-issue. After all, your database server should generally not be accessible on the Internet, and if it is, it should be restricted to the hosts that can connect, and those hosts should be required to use SSL.

The server stores, essentially, the output of this expression:

CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password')))));

mysql> select PASSWORD('password');
+-------------------------------------------+
| PASSWORD('password')                      |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password')))));
+--------------------------------------------------+
| CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password'))))) |
+--------------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19        |
+--------------------------------------------------+
1 row in set (0.00 sec)

But that's apparently not just a misguided attempt along the lines of "if hashing once is good, then hashing twice is better." It has to do with the way the authentication protocol works.

When a client tries to authenticate, the server sends a 20-byte random challenge string.

The client takes that string, and calculates this:

SHA1('password') XOR SHA1( "20-bytes random data from server"<concat>SHA1(SHA1('password')))

So the client is calculating the value that should be stored in the password column (if the correct password has been entered), appending that value to the 20 random bytes, taking the SHA1 of the whole thing, XORing it with the SHA1 of the password itself, and sending that back to the server.

Given that the inverse function of XOR is also XOR, The server can then take the response, and, having also calculated the SHA1() of the string including the 20 bytes, can XOR it back into SHA1('password'), then SHA1() that value...

SHA1(client-response XOR SHA1(CONCAT("same random 20-bytes",mysql.user.password)))

...and the output would be the value stored in the password column of the mysql.user table.

So in this exchange, neither the plaintext password nor what's stored in the password field is ever sent over the wire, just a 20-byte random challenge and a 20-byte response, which is itself the XOR of two hashed values and would also not be usable in a replay attack against the same account or on another account with the same password unless the server sent the same challenge and the passwords are the same.

Since two clients with the same password would arguably "never" get the same challenge, they would just as infrequently "never" send the same response for authentication.

Unless the server sent the salt to the client, I don't readily see how such an exchange could take place. On the other hand, if the server were to send the salt to the client, that implies that the salt would have to exist on the server... and if the salt exists on the server, and I am in possession of one known password/salt combination, then identifying other accounts whose salt and my password matches their password hash, then identifying other accounts where my password could be used is no less trivial that it is under the current scheme.