In most situations, MySQL password directions offer information on changing MySQL user passwords over the production system (e.g., reset root password with no restart). It’s even suggested to change passwords for security reasons. But still, sometimes DBA responsibilities on legacy programs provide surprises and you want to recoup the password for some old users.
There’s absolutely no magic long as just hashes are stored and not the initial passwords, the only way to recoup the lost password will be to brute force it from the hash that is known.
Notice on Security and mysql-unsha1 Attack
Interestingly, if a hacker gets access to hash, then he doesn’t even need to recover that an plain text password in it. It doesn’regardless of how powerful the password and how powerful the hashing algorithm in the plugin, since due to MySQL protocol design, hash is enough to connect to a database with a patched version of MySQL client. It means, if a hacker gets access to a database backup, he automatically receives all necessary advice (SHAs) for linking to a database that is running. See for the attack details.
Considering that MySQL 8.0, caching_sha2_password auth plugin is used by default, and this plugin brings a stronger sha256 function rather than sha1 used in mysql_native_password plugin. For authentication with caching_sha2_password plugin, it’s also enough to have just a ribbon, so watch for the implementation details.
Caching_sha2_password plugin doesn’t include any extra security in comparison to plugin t want plain text password in order be able to connect to the instance having a patched MySQL client.
If you want to have a password that works with an unmodified client, but you will need to do a little bit of hacking, see directions below.
Let’s return to the password recovery. First of all, we need to ditch hashes.
MySQL 5.7 uses the mysql_native_password auth plugin by default and we can dump sha1 hashes with the following command.
% 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 percent’ 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.
% 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 will need to find the root password and don’t have a user that gets read access to mysql.user table, then you must start mysqld with the –skip-grant-tables alternative, see the official doc for details.
Run Linode GPU Instance
For password recovery, it’s needed to conduct calculations on several GPUs, and there are not so cloud suppliers with GPU cases available on the industry. Linode is among the remarkable cloud suppliers if you will need a easy, dependable provider with a really valuable support department. Linode includes a effective CLI tool that simplifies “celebration ” automation a whole lot. Also, for much more acute automation, the official Terraform supplier exists.
128GB GPU Linode example password recovery speed is 30000 MH/s (million hashes per second), that is extremely great. It requires only two hours to brute-force an 8-characters MySQL 5.7 passwords (upper case, lower case, numbers). Instance cost is just 6 USD/Hour.
For example, the additional most important cloud provider (4 x NVIDIA Tesla V100 example ) with exactly the exact same recovery speed cost two times more expensive — 12.24 USD/Hour.
The password brute-forcing is completed according to dictionaries. We’ll utilize a small rockyou dictionary to show how it goes.
% wget ‘https://gitlab.com/kalilinux/packages/wordlists/-/raw/kali/master/rockyou.txt.gz’
% gunzip rockyou.txt.gz
You’ll find dictionaries around the dot com website.
Nevertheless, it’s likely that the most significant dictionary won’t be enough for your own retrieval. In such a situation you should check if the plugin that is validate_password is enabled and also prepare a dictionary in it. Check it as follows:
% mysql -uroot -e”SHOW VARIABLES LIKE’validate_password percent’;”
| 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 command’s output is empty, then it means the plugin will be disabled. You can find some additional information about the plugin from one of our previous blog posts about it, Improving MySQL Password Security with Validation Plugin.
The validate_password_policy field is the one here. It may have the following values:
0 or LOW
1 or more MEDIUM
Length; numeric, lowercase/uppercase, and characters two or STRONG
Length; numeric, lowercase/uppercase, and characters; dictionary file
In case validate_password_policy=STRONG and validate_password_dictionary_file is set, we need to exclude passwords out of validate_password_dictionary_file:
Kitty huge-dictonary.txt \
| pw-inspector -m 8 -M 32 -l -u -n -de \
| type -un \
| grep -F -v -x -f prohibited.txt \
In the example above:-m 8 is the minimum length of the password, worth from validate_password_length factor;-M 32 is the maximal length of the password, even for replication passwords the maximal length is 32 characters, watch MySQL launch nodes;-n password must contain amounts, see validate_password_number_count factor;-l -u password must contain lowercase/uppercase characters, watch validate_password_mixed_case_count factor;-p password must contain special characters, watch validate_password_special_char_count factor;prohibited.txt is an file from validate_password_dictionary_file factor;huge-dictonary.txt is the first dictionary;reduced-dictonary.txt is the new dictionary with no words out of prohibited.txt.
If the dictionary attack failed, you have to create your own dictionary. We recommend using one of the following programs: maskprocessor, crunch or through Hashcat choices.
In the case of MySQL 8.0, the latest version of hashcat in the master branch should be compiled because of the simple fact that code out of https://github.com/hashcat/hashcat/issues/2305 wasn’t released in virtually any version at the moment.
% sudo apt -y install make gcc
percent git clone https://github.com/hashcat/hashcat.git
percent cd hashcat% make% sudo make install
Empower OpenCL for NVIDIA
Update to the Most Recent applications, disable the driver and then reboot:
% sudo apt upgrade && sudo apt full-upgrade -y
echo -e”blacklist nouveau\noptions nouveau modeset=0\nalias nouveau off” | sudo tee /etc/modprobe. d/blacklist-nouveau. Conf
percent sudo update-initramfs -un% reboot
Install the proprietary driver and reboot
% sudo install -y nvidia-cuda-toolkit ocl-icd-libopencl1
percent sudo apt install -y remove mesa-opencl-icd% reboot
Examine the motorist
% sudo hashcat -I
Run Password Recovery
To get mysql_native_password (MySQL 5.7) utilize the 300 code:
% hashcat -m 300 -a 0 -D two -O -w 3. /sha1_hashes . /rockyou.txt
To get caching_sha2_password (MySQL 8.0) utilize the 7401 code:
% hashcat -m 7401 -a 0 -D two -O -w 3. /sha256_hashes . /rockyou.txt
If your password was retrieved properly, you can run the same command with the –display option to display the password.
% hashcat -m 300 -a 0 -D two. /sha1_hashes . /rockyou.txt –reveal 0913bf2e2ce20ce21bfb1961af124d4920458e5f:new_password
Here new_password will be the correct answer.
8-chars password lower and upper case letters and digits for MySQL 5.7 may be retrieved only in two hours to the Linode GPU instance. Exactly the same password for MySQL 8.0 could be retrieved in 2.8 years. But in general, hackers don’t even need to recoup plain text passwords whatsoever (see “mysql-unsha1 assault ” section above). It’s Required to protect the content of mysql.user table to reduce dangers, there are a Couple of things that can be achieved:
Don ’t even save hashes in MySQL itself, as an example, utilize LDAP plugin for Percona Server
Article Source and Credit percona.com https://www.percona.com/blog/2020/06/12/brute-force-mysql-password-from-a-hash/ Buy Tickets for every event – Sports, Concerts, Festivals and more buytickets.com