A long long time ago (in a galaxy far away… cue the music!) MySQL added support for an authentication plugin which is now known as mysql_native_password. The mysql_native_password plugin uses SHA1 hash to

• Store the password(SHA1(SHA1(password)) in mysql.user table
• Authenticate user

One of the good traits of this plugin is that it allows authentication using challenge-response mechanism which made it possible to verify identity of the client on an unencrypted channel without having a need to send the actual password.

Over time, we identified several areas to improve on from an authentication scheme point of view.

1. Transformation of the password must use salt (an added factor) while storing the value in database. Without it, two accounts with same password would have identical hashes. While this does not reveal the actual password, it does provide a clue about passwords being used by users, and limits the effort needed to brute-force attack and obtain the password.
2. Make it harder to crack a stored password using brute-force attack. It’s better to use many (thousands) rounds of hash while storing the password.
3. Use a stronger hashing mechanism. As technology has evolved, SHA1 along with other hashing predecessors such as MD5 have be shown to be quite easy to crack. Note: NIST deprecated in 2011. Thus, if you can obtain the hash – from the mysql.user table or by sniffing an unencrypted channel you can reverse engineer and crack these passwords quickly, especially if they are short – 8 characters or less. Also see FIPS 180-4.
4. Use different hashing schemes for authentication phase and password storage. mysql_native_password plugin uses similar transformation (SHA1(SHA1(password)) in both cases.

To overcome these limitations, starting with MySQL-8.0.3, a new authentication plugin – caching_sha2_password was introduced. Starting with MySQL-8.0.4, this plugin became the new default authentication plugin for MySQL server. With caching_sha2_password authentication, we address above mentioned concerns while making sure that performance is not impacted. Many applications using MySQL connect and disconnect with very high frequency.

Design highlights of MySQL caching_sha2_password are:

• Use SHA-2 hashing mechanism to transform the password. Specifically, it uses SHA256.
• A 20 byte long salt is used with each password while generating hash. Since a salt is a random number, even if two users are using same password, the end result of transformation process will be drastically different.
• In order to make it significantly harder to try and guess password using brute force mechanism, 5000 rounds of SHA2 hashes are performed on password and salt before storing the final transformation in mysql.user table.
• Two mode of operations:
  • COMPLETE: Requires a client to send actual password securely (on a TLS connection or using RSA keypair). Server generates 5000 rounds of hash and compare against the value stored in mysql.user.
  • FAST: Permits a challenge-response based authentication that uses SHA2 hashes. Highly performant and secure at the same time.
• DBA can force database clients to periodically use COMPLETE mode to ascertain knowledge of the actual password. This is expensive.
• Decoupling password storage and authentication by using different number of rounds of hash. Even if someone has access to both of them, this information can not be used to infer password OR derive sha2 hash of the password in practically viable amount of time.

It would take a very long time to brute force an 8 char long password with 5000 rounds of salted hash. Way longer than any password expiration policy – even the most liberal ones. A longer password would simply make things more difficult.

Following table compares mysql_native_password with caching_sha2_password.

Please refer to this blog post  about caching_sha2_password plugin. You may also refer to documentation for more details. This doxygen page provides details of how authentication is performed using caching_sha2_password plugin.

In addition to the new plugin, several features were added to protect against attempts to identify user information and mitigate risks associated with weak passwords:

• Support for TLS connections without any additional efforts (Server side support and Client side support) to make sure connections are secure by default
• CREATE USER/ALTER USER provides several options to specify policy for password management
• Controlling what can and cannot be used as password – length, characters complexity, etc.
• Slow down brute force attempts to guess passwords adding delays as well as setting maximum attempts limitations
• Password reset with random one time passwords.
• Additional measures to prevent user enumeration

Together with caching_sha2_password, these features strengthen user accounts against attacks on passwords. Please use them as per your requirements and let us know your feedback.

Additionally the mysql schema’s data can be encrypted at rest (InnoDB encryption, Binary log encryption). This protects sensitive data such as password hashes against unauthorized access to via files. This hides many details from the OS/Filesystem. FYI – DBAs (users with required set of privileges – e.g. SELECT on mysql.user table) can see this hash data regardless of the use of data at rest encryption schemes. That being said, the cost to reverse engineer passwords is prohibitive even then

If security alone isn’t enough to motivate you to upgrade to caching_sha2_password, then another business motivator is regulatory compliance. Most regulations have disallowed the use of sha1, md5, other weak cryptography for passwords or other uses. (HIPAA, GDPR, …)

To summarize:

• If you are using mysql_native_password, plan sometime quite soon to migrate to caching_sha2_password or one of the enterprise authentication plugins that enables integration with external authentication servers. SHA1 is just not secure enough and switching is not difficult.
• Access to mysql.user table should be as restrictive as possible. Even though it does not store the actual password, information within that table is very sensitive – especially the password hashes. In fact, no matter where you store such hashes – Be it within MySQL database or on a external authentication server such as LDAP server, they must always be protected. OpenLDAP documentation clarifies this nicely:

• Use password policy features provided by MySQL to control password lifecycle.
• Use controls provided by MySQL to protect against brute force attack on passwords.
• Use InnoDB encryption on the mysql schema and ideally on all your tables, as well as binary log encryption to protect data at rest against unauthorized access.
• Always use encrypted connections: Be it server-client communication or server-server communication in an HA topology. Encrypting data at rest alone is not sufficient. Data must be protected while in transit.
• always protect your backups by encrypting it to avoid data leaks

As always, a big thank you for using MySQL!