In most cases, MySQL password instructions provide information on changing MySQL user passwords on the production system (e.g., reset root password without restart). It is even recommended to change passwords regularly for security reasons. But still, sometimes DBA duties on legacy systems offer surprises and you need to recover the original password for some old users.
There is no magic: as long as only hashes are stored and not the original passwords, the only way to recover the lost password is to brute force it from the known hash.
Note on Security and mysql-unsha1 Attack
Interestingly, if a hacker has access to password hash and can sniff mysql traffic, he doesn’t need to recover a plain text password from it. It doesn’t matter how strong the password and how strong the hashing algorithm inside the auth plugin, due to MySQL protocol design, sniffed hash is enough to connect to a database with a patched version of MySQL client. It means, if a hacker has access to a database backup and traffic, he automatically receives all needed information (SHAs) for connecting to a running database. See for the attack details.
Since MySQL 8.0, caching_sha2_password auth plugin is used by default, and this plugin brings a stronger sha256 function instead of sha1 used in mysql_native_password plugin. For authentication with caching_sha2_password plugin, it is also enough to have only a hash and be able to sniff traffic, see for the implementation details.
Still, if you want to have a password that works with an unmodified client, however, you need to do some hacking, see instructions below.
Dump Hash
Let’s return to the password recovery. First of all, we need to dump hashes.
MySQL 5.7 uses the mysql_native_password auth plugin by default and we can dump sha1 hashes with the following command.
1 | % mysql -Ns -uroot -e "SELECT SUBSTR(authentication_string,2) AS hash FROM mysql.user WHERE plugin = 'mysql_native_password' AND authentication_string NOT LIKE '%THISISNOTAVALIDPASSWORD%' AND authentication_string !='';" > sha1_hashes |
MySQL 8.0 uses the caching_sha2_password auth plugin by default and we can dump sha256 hashes as follows.
1 | % mysql -Ns -uroot -e "SELECT CONCAT('$mysql',LEFT(authentication_string,6),'*',INSERT(HEX(SUBSTR(authentication_string,8)),41,0,'*')) AS hash FROM mysql.user WHERE plugin = 'caching_sha2_password' AND authentication_string NOT LIKE '%INVALIDSALTANDPASSWORD%' AND authentication_string !='';" > sha256_hashes |
If you need to get the root password hash and don’t have a user who has read access to mysql.user table, you should start mysqld with the --skip-grant-tables option, see the official doc for details.
Run Linode GPU Instance
For password recovery, it is needed to run calculations on some powerful GPUs, and there are not so many cloud providers with GPU instances on the market. Linode is one of the remarkable cloud providers if you need a simple, reliable provider with a really helpful support department. Linode has a powerful CLI tool that simplifies “bash” automation a lot. Also, for more serious automation, the official Terraform provider exists.
128GB GPU Linode instance password recovery speed is 30000 MH/s (million hashes per second), which is very good. It needs only 2 hours to brute-force an 8-characters MySQL 5.7 passwords (upper case, lower case, numbers). Instance price is only 6 USD/Hour.
For example, the other biggest cloud provider (4 x NVIDIA Tesla V100 instance) with the same recovery speed cost two times more expensive – 12.24 USD/Hour.
Prepare Dictionary
The password brute-forcing is done based on dictionaries. We will use a small rockyou dictionary as an example, to show how it goes.
1 2 3 | % wget 'https://gitlab.com/kalilinux/packages/wordlists/-/raw/kali/master/rockyou.txt.gz' % gunzip rockyou.txt.gz |
You can find really good dictionaries on the weakpass dot com website.
But it is possible that even the largest dictionary will not be enough for the recovery. In such a case you should check if the validate_password plugin is enabled and prepare a dictionary based on it. Check it as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 | % mysql -uroot -e "SHOW VARIABLES LIKE 'validate_password%';" +--------------------------------------+-------------------------------+ | Variable_name | Value | +--------------------------------------+-------------------------------+ | validate_password_check_user_name | ON | | validate_password_dictionary_file | /var/lib/mysql/prohibited.txt | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | STRONG | | validate_password_special_char_count | 1 | +--------------------------------------+-------------------------------+ |
If the output of this command is empty, it means that the plugin is disabled. You can find some more details about the plugin in one of our previous blog posts about it, Improving MySQL Password Security with Validation Plugin.
The validate_password_policy field is the most important one here. It can have the following values:
| Policy | Tests Performed |
| 0 or LOW | Length |
| 1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
| 2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
If validate_password_policy=STRONG and validate_password_dictionary_file is set, we need to exclude passwords from validate_password_dictionary_file:
1 2 3 4 5 | cat huge-dictonary.txt | pw-inspector -m 8 -M 32 -l -u -n -p | sort -u | grep -F -v -x -f prohibited.txt > reduced-dictonary.txt |
In the example above:
-m 8 is the minimal length of the password, value from validate_password_length variable;
-M 32 is the maximal length of the password, for replication passwords the maximal length is 32 characters, see MySQL release nodes;
-n password should contain numbers, see validate_password_number_count variable;
-l -u password should contain lowercase/uppercase characters, see validate_password_mixed_case_count variable;
-p password should contain special characters, see validate_password_special_char_count variable;
prohibited.txt is a file from validate_password_dictionary_file variable;
huge-dictonary.txt is the initial dictionary;
reduced-dictonary.txt is the new dictionary without words from prohibited.txt.
If the dictionary attack failed, you have to create your own dictionary for the brute force. In this case, we recommend using one of the following tools: crunch, maskprocessor or via Hashcat options.
Compile Hashcat
In the case of MySQL 8.0, the latest version of hashcat from the master branch should be compiled due to the fact that code from https://github.com/hashcat/hashcat/issues/2305 wasn’t released in any version right now.
1 2 3 4 5 6 7 8 9 | % sudo apt -y install make gcc % git clone https://github.com/hashcat/hashcat.git % cd hashcat % make % sudo make install |
Enable OpenCL for NVIDIA
Update to the latest software, disable the nouveau driver and reboot:
1 2 3 4 5 6 7 | % sudo apt update && sudo apt full-upgrade -y % echo -e "blacklist nouveaunoptions nouveau modeset=0nalias nouveau off" | sudo tee /etc/modprobe.d/blacklist-nouveau.conf % sudo update-initramfs -u % reboot |
Install the proprietary driver and reboot
1 2 3 4 5 6 7 | % sudo apt install -y nvidia-cuda-toolkit ocl-icd-libopencl1 % sudo apt install -y nvidia-driver-440 nvidia-utils-440 % sudo apt remove mesa-opencl-icd % reboot |
Check the driver
1 2 3 | % sudo nvidia-smi % hashcat -I |
Run Password Recovery
For mysql_native_password (MySQL 5.7) use the 300 code:
1 | % hashcat -m 300 -a 0 -D 2 -O -w 3 ./sha1_hashes ./rockyou.txt |
For caching_sha2_password (MySQL 8.0) use the 7401 code:
1 | % hashcat -m 7401 -a 0 -D 2 -O -w 3 ./sha256_hashes ./rockyou.txt |
If your password was recovered correctly, you can run the same command with the --show option to display the password.
1 2 3 | % hashcat -m 300 -a 0 -D 2 ./sha1_hashes ./rockyou.txt --show 0913bf2e2ce20ce21bfb1961af124d4920458e5f:new_password |
Here new_password is the correct answer.
Conclusion
8-chars password with lower and upper case letters and digits for MySQL 5.7 can be recovered only in 2 hours on the Linode GPU instance. The same password for MySQL 8.0 can be recovered in 2.8 years. But in general, hackers don’t need to recover plain text passwords at all (see “mysql-unsha1 attack” section above). To reduce risks, it is needed to protect the content of mysql.user table, there are a few things that can be done:
- don’t store hashes in MySQL itself, for example, use LDAP plugin for Percona Server
- or use encryption at rest with HashiCorp Vault plugin
Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.
Great article!
According to https://github.com/cyrus-and/mysql-unsha1/blob/master/README.md , access to mysql.user table alone is not enough.
While this article claims that “if a hacker has access to a database backup, he automatically receives all needed information (SHAs)”.
Did I miss something?
Hi Rene!
if you have a backup:
1) restore the database from the backup (at least mysql.user table)
2) run mysqld with –skip-grant-tables option
3) dump hashes according to commands in the “Dump Hash” section.
Hi Mykola,
Ok, so you still need to brute force the password, so having the hash is not enough “for connecting to a running database”.
Correct?
No, you need to build patched mysql client https://github.com/cyrus-and/mysql-unsha1/blob/master/README.md#the-patched-mysql-client which able to connect with hash, without plain text password.
The patched mysql client is able to connect using sha1(password), while in mysql.user you have sha1(sha1(password))
Yes, you absolutely right, it is also needed to sniff traffic, as described in https://github.com/cyrus-and/mysql-unsha1/blob/master/README.md#the-sniffer
sorry for the confusion, I have fixed wording in the “mysql-unsha1 Attack” section.
How can get the access to database backup,and traffic???
What’s the equivalent situation for Mariadb?
Hi Ed,
MariaDB use just regular
mysql_native_passwordplugin, so situation is identical to MySQL 5.7That’s why MySQL 8.0 uses caching_sha2_password authentication by default. As you said, the password can then be recovered in 2.8 years. MySQL 8.0 can add password expiration and account locking 😉
So if you still use the mysql_native_password authentication plugin, it’s time to change it !
I also wanted to add that caching_sha2_password authentication plugin uses different hashes for storing password and during authentication.
This means that even if stored hashes are available, one can not use them directly along with packet sniffed from network.
This trick worked on mysql_native_password because same hashing scheme is used to store the password and to authenticate user.
And additionally, it’s always possible to encrypt the mysql system tables if your version supports it: https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html#innodb-mysql-tablespace-encryption-enabling-disabling
Cheers,
Me again 😉 For more information about the new default authentication plugin which is much more secure, please read https://mysqlserverteam.com/a-tale-of-two-password-authentication-plugins/
I think sql server is usually putted behind some security layers, then bruteforce is not easy