MySQL Authentications

认证方法

1. mysql 320 password(3.20)             32-bit hash
2. mysql_old_password(3.21-4.0)         16-byte MD5 hash(aka. mysql323) 无盐
3. mysql_native_password(4.1)           41-byte double-SHA1 hash 无盐
4. sha256_password(5.6.6)               SHA-256 hash
5. caching_sha2_password(8.0.3)         caching SHA-256 hash
6. Multi-Factor Authentication(8.0.27)  支持多种认证方式
7. mysql_clear_password(5.5.10)         仅 client 端支持，用于 server 端需要 cleartext 的特殊情况，如 PAM and simple LDAP
8. ed25519(mariadb 10.1.22)             仅 MariaDB 支持
9. ...

mysql_320_password

MySQL 3.20 开始使用的认证方法

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_old_password

MySQL 3.21 开始使用的认证方法，5.7.5 起不再支持

Lacking any sort of salt, ignoring all whitespace, and having a simplistic algorithm that amounts to little more than a checksum, this is not secure, and should not be used for any purpose.

认证协议 同上 mysql 320

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.

密码格式

16字节密码散列

如
318b243e220ca492
673761a01d8a119f

密码算法

#!python3
# https://stackoverflow.com/questions/37596450/old-password-function-in-5-7-5

def mysql_old_password(password):
    nr = 1345345333
    add = 7
    nr2 = 0x12345671
    for c in (ord(x) for x in password if x not in (' ', '\t')):
        nr^= (((nr & 63)+add)*c)+ (nr << 8) & 0xFFFFFFFF
        nr2= (nr2 + ((nr2 << 8) ^ nr)) & 0xFFFFFFFF
        add= (add + c) & 0xFFFFFFFF
    return "%08x%08x" % (nr & 0x7FFFFFFF,nr2 & 0x7FFFFFFF)

if __name__ == '__main__':
    import sys
    if len(sys.argv) != 2:
        print >> sys.stderr , 'Python Implementation of MySQL\'s old password hash'
        print >> sys.stderr , 'Usage: %s password' % sys.argv[0]
        sys.exit(1)
    print(mysql_old_password(sys.argv[1]))

mysql_native_password

MySQL 4.1.1 开始使用

Lacking any sort of salt, and using only 2 rounds of the common SHA1 message digest, it’s not very secure, and should not be used for any purpose.

The advantage of mysql_native_password is that it support challenge-response