Over the years, MySQL has used three different mechanisms for securing passwords both for storage and for transmission across networks. This blog post aims to provide a brief history of the various mechanisms and highlight reasons to migrate accounts to use the sha256_password mechanism introduced in MySQL Server 5.6.
Original (old) passwords
The original password security mechanism, now known as the mysql_old_password, is insecure in a number of ways, and should not be used (the mysql_old_password authentication plugin is removed from MySQL Server distributions as of version 5.7.5). It uses a broken hashing algorithm, and attackers who gain access to the password hash stored in the mysql.user table can authenticate with that data. In short, it’s relatively easy for attackers to find the plain-text password from the hash, but they don’t need to even do that to gain access to MySQL servers configured with such accounts.
Accounts which are configured to use this old password mechanism can be identified by one of the following characteristics:
- The value of the plugin column in mysql.user is “mysql_old_password”. This column was introduced with pluggable authentication in MySQL 5.5, so this is only applicable to MySQL Server versions 5.5 and 5.6.
- The length of the hash stored in the password column of mysql.user is 16 characters. This is true in all versions of MySQL Server through 5.7, when the password column was removed.
For backward compatibility reasons, support for this old mechanism was left available across many releases, but is finally being fully phased out. Users with accounts still leveraging this vulnerable password mechanism should change immediately.
New (native) passwords
MySQL 4.1 introduced a new password mechanism. In all versions of MySQL Server since, this has been the default password mechanism, and as of MySQL Server 5.5, is implemented in the mysql_native_password authentication plugin (which is enabled by default). This mechanism leverages SHA1 hashing – an algorithm considered secure back in early MySQL 4.1 days, but which now has known weaknesses that some project may be exploitable within several years. Additionally, the mechanism was improved such that stored password hashes could not be used to authenticate. The network exchange includes a random seed, but even when an attacker knows both the seed and the client-supplied password hash it generates, it is not possible to extract the password without resorting to brute-force attacks.
This sounds great, and is surely an improvement over the original password mechanism, but there is a notable weakness in password storage: Hashes are now relatively inexpensive to compute, and produce consistent output for consistent input. Predictability and speed are the enemies of security.
The example below demonstrates how the same password produces the same hash:
mysql> CREATE USER u1@localhost
-> IDENTIFIED WITH mysql_native_password
-> BY 'pwd';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE USER u2@localhost
-> IDENTIFIED WITH mysql_native_password
-> BY 'pwd';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- Compare just the last 10 hex characters of
mysql> -- the hash for ease of reading:
mysql> SELECT
-> user,
-> plugin,
-> SUBSTR(authentication_string, -10) hash
-> FROM mysql.user
-> WHERE user IN ('u1', 'u2')\G
*************************** 1. row ***************************
user: u1
plugin: mysql_native_password
hash: 326D2021DD
*************************** 2. row ***************************
user: u2
plugin: mysql_native_password
hash: 326D2021DD
2 rows in set (0.00 sec)
Because the hash of a given password string is exactly the same, every time, it’s possible to leverage precomputed rainbow tables where an attacker only has to look up the stolen hashed password and see what plain-text value corresponds to the known hash value.
The effort required to build rainbow tables is a function of the complexity and length of passwords. The longer the passwords, and the more possibilities for each character (just lower-case characters, or mixed-case characters plus numbers and symbols?), the larger the resulting rainbow table, and the longer it takes to produce. But that doesn’t change one key factor – the rainbow table only has to be computed once, and can be used against hashes from any MySQL Server installation. Provided the plain-text password is included in the set used to build the rainbow table, cracking the password can be nearly instantaneous once the hashed value is known.
Because of the above, passwords stored using the mysql_native_password mechanism can be considered only as secure as the hashed values are unknown. That means that somebody with access to the mysql.user table, or who can view (binary, audit, general or slow query) logs (which contain hashed passwords for CREATE USER or SET PASSWORD/ALTER USER commands), or otherwise has access to the hashed password, may be able to obtain the plain-text password. Because users often use identical or similar passwords across various systems, this has implications beyond MySQL itself.
Why sha256_password is better
The sha256_password plugin was introduced in MySQL Server 5.6, and provides additional security focused on password storage. It does so by addressing the two key elements which make mysql_native_password vulnerable – hash computation becomes more expensive/time-consuming, and the output is randomized. Additionally, using the stronger SHA-256 algorithm provides eliminates dependencies on the vulnerable SHA1 algorigthm.
The implementation of sha256_password is intentionally expensive – it cycles through several thousand iterations in the creation of the resulting hash. Because it’s more time-consuming to produce hashes, brute-force attacks become more expensive to attempt.
To randomize output, sha256_password incorporates a random salt in generating the hash. As a result, hashing the exact same plain-text password twice will (virtually always) result in very different hash values. This can be seen in the example below:
mysql> ALTER USER u@localhost
-> IDENTIFIED BY 'pwd';
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> -- Compare just the last 10 hex characters of
mysql> -- the hash for ease of reading:
mysql> SELECT
-> plugin,
-> SUBSTR(HEX(authentication_string), -10) hash
-> FROM mysql.user
-> WHERE user = 'u'\G
*************************** 1. row ***************************
plugin: sha256_password
hash: 48474F6534
1 row in set (0.00 sec)
mysql>
mysql> ALTER USER u@localhost
-> IDENTIFIED BY 'pwd';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT
-> plugin,
-> SUBSTR(HEX(authentication_string), -10) hash
-> FROM mysql.user
-> WHERE user = 'u'\G
*************************** 1. row ***************************
plugin: sha256_password
hash: 5056353132
1 row in set (0.00 sec)
The generated hash is unique to the salt used to produce it, which makes rainbow tables ineffective. Every entry in the rainbow table would have to be generated by the same salt used in the targeted hash. That eliminates the possibility of using precomputed rainbow tables – you can’t start computing the hash equivalents of the candidate passwords until you know the salt that was used.
The salt itself is embedded into the stored hash, and is easily extracted in order to re-compute a hash with the same seed and a user-supplied plain-text password, to see if they match. If an attacker can guess the password, or only has to check a handful of commonly-used passwords to find a match, this improvement in password storage will have minimal impact. But if users apply best practices for selection of passwords (long passwords composed of mixed case characters, numbers and symbols which don’t use words from common dictionaries), it makes testing password equality excessively time-consuming. By employing an appropriate password rotation policy, the attack window is bounded on both when the attack work can start (when the attacker gets the salt) and when it must be complete (when the user changes their password, and a new hash – and salt – is generated).
Transmission across the network
By using a few tricks, the mysql_native_password mechanism can safely transmit a password across the network without requiring transport-level security. The password hash stored by MySQL Server is generated by hashing the plain-text password twice using SHA1. When the client transmits the password to the server, it uses three pieces of information:
- The SHA1 hash of the plain text password
- The SHA1 hash of the the SHA1 hash of the plain text password
- The random seed sent by the MySQL Server in the handshake.
As documented in the internals manual, what is actually sent to the server is the following:
#1 xor SHA1( #3 concat #2)
The random seed (#3) from the server prevents replay attacks – nobody can record the client authentication response and replay it to establish a new connection. The payload for each authentication process will differ because the random seed will differ.
Because the server knows the random seed it sent to the client, and knows the values of SHA1(SHA1(plaintext)) – because that’s what’s stored in the mysql.user table – it can reverse the XOR operation to get SHA1(plaintext) by itself. Hashing that one more time with SHA1 should produce SHA1(SHA1(plaintext)), matching what’s stored in mysql.user. This proves that the client knows more than the double-hashed password stored in the mysql.user table.
All of this is predicated on the server knowing – and the client being able to generate – the stored hashed password. In other words, this mechanism requires consistency. That’s why sha256_password can’t use these same tricks to securely transmit the password – generating the stored hash requires the account salt. Sending that seed to the client is technically possible, but that value could be monitored to determine whether a guessed user name exists (if it does, it would send the same salt value consistently until the password is changed). To avoid leaking such information, the server does all the hashing itself – which means it needs to obtain the password in plain-text from the client.
Getting the password from the client to the server in plain-text is problematic – it requires transport-level security. This is implemented in two different ways for sha256_password: SSL/TLS or using RSA key pairs. If neither of these options are enabled, the sha256_password plugin will detect it cannot obtain the password from the client securely, and will fail authentication.
RSA public/private key encryption is useful in that only the password exchange is encrypted, and other, normal operations do not incur performance overhead of encryption. However, this functionality is supported by the OpenSSL library, but not the yaSSL library used in Community builds (because of license compatibility issues). This means that using RSA for secure password transmission is limited to MySQL Enterprise builds, or packages custom built to use OpenSSL instead of yaSSL.
Leveraging the RSA mechanism also has usability concerns – in order to encrypt the password using the server’s public RSA key, the client needs to first obtain this key. Distributing keys securely can be an operational headache. MySQL Server will supply its own RSA public key upon request from the client, so that the key doesn’t have to be explicitly distributed and configured for each client. But this introduces another security concern – a proxy in the middle may substitute an RSA public key for which it has the private key, decrypt and harvest the plain-text password, then re-encrypting the password with the actual server RSA public key for the connection attempt to continue. For this reason, it’s strongly recommended that clients define a local RSA public key to use instead of request the server RSA key during the handshake.
The SSL/TLS option has no special security concerns, and is available to all users, though it has to be configured. Making SSL/TLS easy to deploy is a key security emphasis for MySQL Server 5.7, and this is a key reason – sha256_password may offer superior password storage, but that’s of limited usefulness if nobody can connect because SSL/TLS is not configured (and RSA not available).
It’s worth saying here, if you’re serious about password security, you need to be using secure connections – period. The above discussion about password exchange on the network deals only with securing the authentication communication. Passwords are exposed in a number of other operations, such as user account maintenance, configuring replication slaves or FEDERATED tables, etc. And that’s just MySQL Server account passwords – if MySQL Server is being used to store web account information, many other operations may expose plain-text passwords or other confidential information. Passive eavesdroppers on the network can extract such information with ease if connections are not leveraging secure transport.
Conclusion
The sha256_password authentication plugin was designed to address a fundamental weakness of the mysql_native_password authentication system. If exposure of password hashes – whether in logs, storage, SQL injection attacks, etc. – is a concern for your organization, you need to consider using sha256_password to better secure these hashes. While mysql_native_password leverages unique tricks to securely exchange passwords without relying on transport-level security, this effectively trades storage security for transmission security and makes passwords susceptible to cracking using rainbow tables. The mysql_native_password mechanism is also dependent upon the SHA1 algorithm, which is known to be vulnerable, and may become suddenly exploitable in the near future. Furthermore, you should consider leveraging SSL/TLS for all connections, as the secure password exchange mechanism for mysql_native_password only secures the authentication process, and not subsequent commands which may need security (e.g., account and replication maintenance operations).
