MySQL Authentications

锺离森

2023-12-01

认证方法

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 mechanism which is very quick and does not require encrypted connection. However, mysql_native_password relies on SHA1 algorithm and NIST has suggested to stop using it.

认证协议

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.

密码格式

星号 + 40字节十六进制表示的密码散列

如：
*14BDCEBE19082CE2A1F959FD02F964D7AF4CFC29

密码算法

A mysql-41 password hash consists of an asterisk * followed by 40 hexadecimal digits, directly encoding the 160 bit checksum. An example hash (of password) is *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19. MySQL always uses upper-case letters, and so does Passlib (though Passlib will recognize lower-case letters as well).

The checksum is calculated simply, as the SHA1 hash of the SHA1 hash of the password, which is then encoded into hexadecimal.

#!python3
# https://foss.heptapod.net/python-libs/passlib/-/blob/branch/stable/passlib/handlers/mysql.py

def mysql_native_password(password):
    if isinstance(password, str):
        password = password.encode("utf-8")
    return sha1(sha1(password).digest()).hexdigest().upper()

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

sha256_password

MySQL 5.6.6 开始使用

Since MySQL 5.6, sha256_password authentication plugin is supported. It uses multiple rounds of SHA256 hash on a salted password to make sure that the hash transformation is more secure. However, it requires either encrypted connections or support for an RSA key pair. So, while password security is stronger, secure connections and multiple rounds of hash transformations require more time in the authentication process.

认证协议

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! 应该是 salt+password，再 5000 次 sha256
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.

密码格式

$5$ + 20字节盐 + $ + 43字节密码散列

如：
$5$-Q79?S ;"0GQ} $XAnNxGr1TcfE6NdTjeAu.vMV8EW1Nl5dv.XomcV3LHC

盐包含不可见的控制字符

密码算法

#!python
import hashlib
import os
import sys

small_letters = list(map(lambda x: bytes((x,)), range(ord('a'), ord('z')+1)))
big_letters = list(map(lambda x: bytes((x,)), range(ord('A'), ord('Z')+1)))
digits = list(map(lambda x: bytes((x,)), range(ord('0'), ord('9')+1)))
i64 = [ b'.', b'/' ]
i64 += digits
i64 += big_letters
i64 += small_letters

def to64(v, n):
    str = b''
    n -= 1
    while n >= 0:
        str += i64[ v & 0x3F ]
        v >>= 6
        n -= 1
    return str

def sha_crypts(bits, key, salt, loops):
    bytes = bits / 8
    
    # 计算哈希值 a
    # print(key)
    # print(salt)
    b = hashlib.sha256(key+salt+key).digest()
    tmp = key + salt
    i = len(key)
    while i > 0:
        if i > bytes:
            tmp += b
        else:
            tmp += b[0:i]
        i -= bytes
    i = len(key)
    while i > 0:
        if i & 1 != 0:
            tmp += b
        else:
            tmp += key
        i >>= 1
    hash = hashlib.sha256(tmp)
    a = hash.digest()
    # print(f'a({len(a)})={a}')
    # print(hash.hexdigest())

    # 计算字符串 p
    tmp = b''
    for i in range(len(key)):
        tmp += key
    dp = hashlib.sha256(tmp).digest()
    p = b''
    i = len(key)
    while i > 0:
        if i > bytes:
            p += dp
        else:
            p += dp[0:i]
        i -= bytes
    # print(f'p({len(p)})={p}')
    # print(p.hex())

    # 计算字符串 s
    tmp = b''
    til = 16 + a[0]
    for i in range(til):
        tmp += salt
    ds = hashlib.sha256(tmp).digest()
    s = b''
    i = len(salt)
    while i > 0:
        if i > bytes:
            s += ds
        else:
            s += ds[0:i]
        i -= bytes
    # print(f's({len(s)})={s}')
    # print(s.hex())

    # 计算哈希值 c
    c = a
    for i in range(loops):
        if i & 1 != 0:
            tmp = p
        else:
            tmp = c
        if i % 3 != 0:
            tmp += s
        if i % 7 != 0:
            tmp += p
        if i & 1 != 0:
            tmp += c
        else:
            tmp += p
        hash = hashlib.sha256(tmp)
        c = hash.digest()
    # print(f'c({len(c)})={c}')
    # print(hash.hexdigest())

    # 最终哈希值
    if bits == 256:
        inc1 = 10
        inc2 = 21
        mod = 30
        end = 0
    else:
        inc1 = 21
        inc2 = 22
        mod = 63
        end = 21
    i = 0
    tmp = b''
    while True:
        #print(f'i={i}')
        x = c[i]
        y = c[(i+inc1)%mod]
        z = c[(i+inc1*2)%mod]
        tmp += to64( ((x<<16)|(y<<8)|z), 4)
        i = (i+inc2)%mod
        if i == end:
            break
    if bits == 256:
        tmp += to64( ((c[31]<<8)|c[30]), 3 )
    else:
        tmp += to64( c[63], 2 )
    return tmp

def generate_user_salt():
    tmp = os.urandom(20)
    str = b''
    for i in range(len(tmp)):
        n = tmp[i] & 0x7f
        if n == 0 or n == ord('$'):
            n += 1
        str += bytes((n,))
    return str

def sha256_password(password, salt=None):
    if not salt:
        salt_bytes = generate_user_salt()
        salt = salt_bytes.hex()
    password = password.encode('utf8')
    salt_bytes = bytearray.fromhex(salt).decode().encode('utf8')
    loops = 5000
    hash = sha_crypts(256, password, salt_bytes, loops)
    result = b'$5$'+salt_bytes+b'$'+hash
    print(f'bytes: {result}')
    result2 = '$5$'+salt+'$'+hash.hex()
    print(f'hex: {result2}')

if __name__ == '__main__':
    if len(sys.argv) < 2:
        print >> sys.stderr , 'Python Implementation of MySQL\'s sha256 password hash'
        print >> sys.stderr , 'Usage: %s {password} {salt}' % sys.argv[0]
        sys.exit(1)
    if len(sys.argv) >= 3:
        sha256_password(sys.argv[1], sys.argv[2])
    else:
        sha256_password(sys.argv[1])

caching_sha2_password

MySQL 8.0 开始支持

For a majority of connection attempts, when there exists a cached copy of the password hash in memory, it uses a SHA256-based challenge-response mechanism while authenticating a client (compared to a SHA1-based challenge-response mechanism in mysql_native_password). This is faster and allows secure authentication over an unencrypted channel. The following figure summarizes challenge-response based authentication.

!fast.jpg!

It employs a technique similar to sha256_password before storing credential information in the mysql.user table. It uses 5000 rounds of SHA256 transformation on a salted password.

The following figure summarizes full authentication.

!full.jpg!

mysql_native_password vs. caching_sha2_password

                                                mysql_native_password    caching_sha2_password
散列                                            SHA1                     SHA256
用盐                                            没有                     YES – 20 Bytes
使用散列的轮数                                  2                        5000
Supports Challenge-Response Authentication      YES                      YES (FAST mode)
Re-ascertain Password Knowledge                 无需                     YES (COMPLETE mode)

密码格式

$A$005$ + 20字节盐 + 43字节密码散列

如
$A$005$tnsH54yUg1kNa        K(6NKkD7aVVSpEqy8NrQa0F94.Pgvv7XO8up991nv32WL8

密码算法同 sha256_password

ed25519

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.

兼容性

                             MySQL Server         MySQL Client                MySQL Connector/Python     MySQL Connector/J
mysql_old_password           <5.7.5(2014-09-25)   <5.7.5                      not supported              all versions
mysql_native_password        4.1.1+               4.1.1+                      all versions               all versions
sha256_password              5.6.6+(2012-08-07)   5.6.6+                      1.2.1+(2014-03-31)         5.1.31+(2014-06-07)
caching_sha2_password        8.0.3+(2017-09-21)   5.7.23+(2018-07-27),8.0.3+  8.0.5+(2017-09-28)         5.1.46+(2018-03-12),8.0.9+(2018-01-30)
Multi-Factor Authentication  8.0.27+(2021-10-19)  8.0.27+                     8.0.28+(2022-01-18)        8.0.28+(2022-01-18)
ed25519                      not supported        not supported               not supported              not supported


                             MariaDB Server & Client                   MariaDB Connector/C  MariaDB Connector/J
mysql_old_password           all versions                              all versions         all versions
mysql_native_password        all versions                              all versions         all versions
sha256_password              not supported                             3.0.2+(2017-07-20)   2.5.0+(2019-10-03)
caching_sha2_password        not supported                             3.0.8+(2018-12-21)   2.5.0+
Multi-Factor Authentication  not supported                             not supported        not supported
ed25519                      10.1.22+(2017-03-14),10.2.5+(2017-04-05)  3.1.0+(2019-04-08)   2.2.1+(2017-12-22)


                             Perl-DBD:MySQL
mysql_old_password           depends on lib{mysqlclient,mariadb}.so
mysql_native_password        depends on lib{mysqlclient,mariadb}.so
sha256_password              depends on lib{mysqlclient,mariadb}.so
caching_sha2_password        depends on lib{mysqlclient,mariadb}.so
Multi-Factor Authentication  depends on lib{mysqlclient,mariadb}.so

参考

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
https://dev.mysql.com/doc/refman/5.7/en/authentication-plugins.html
https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/authentication-plugins.html
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/
https://downloads.mysql.com/docs/mysql-5.5-relnotes-en.pdf
https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/
https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/
https://dev.mysql.com/doc/relnotes/connector-python/en/
https://dev.mysql.com/doc/relnotes/connector-cpp/en/
https://mariadb.com/kb/en/authentication-plugins/
https://mariadb.com/kb/en/about-mariadb-connector-j/#choosing-a-version
https://github.com/mysql/mysql-connector-j/tree/release/8.0/src/main/protocol-impl/java/com/mysql/cj/protocol/a/authentication
https://github.com/mysql/mysql-connector-python/blob/master/lib/mysql/connector/authentication.py
https://passlib.readthedocs.io/en/stable/lib/passlib.hash.mysql323.html
https://lefred.be/content/perl-mysql-8-0/
https://www.cnblogs.com/olinux/p/13201497.html
https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/
https://stackoverflow.com/questions/37596450/old-password-function-in-5-7-5
https://mariadb.org/history-of-mysql-mariadb-authentication-protocols/
https://dciabrin.net/posts/2020/09/connecting-to-mariadb-with-auth_ed25519-and-pymysql.html
http://mysqlblog.fivefarmers.com/2015/08/31/protecting-mysql-passwords-with-sha256_password-plugin/
https://crypto.stackexchange.com/questions/77427/whats-the-algorithm-behind-mysqls-sha256-password-hashing-scheme
https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/
https://github.com/hashcat/hashcat/issues/2305
https://www.percona.com/blog/2020/06/12/brute-force-mysql-password-from-a-hash/
https://dev.mysql.com/blog-archive/a-tale-of-two-password-authentication-plugins/

.eof.

MySQL Authentications

认证方法

mysql_320_password

mysql_old_password

mysql_native_password

sha256_password

caching_sha2_password

ed25519

兼容性

参考

相关阅读

相关文章

相关问答