Last active
July 3, 2024 23:12
-
-
Save colinmollenhour/98842910522a65d17de10424e2c87f10 to your computer and use it in GitHub Desktop.
A Bash script to help you re-hash all MySQL passwords that are in mysql_native_password plugin format to caching_sha2_password format.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| set -e | |
| if [[ "$#" -ne 1 ]] || [[ $1 = '--help' ]]; then | |
| echo "This script can be used to update the password type for users with the mysql_native_password plugin." | |
| echo "Usage: $0 <mysql_container_name> [username]" | |
| exit 1 | |
| fi | |
| container_name=$1 | |
| # Function to hash a password using MySQL's native password hashing algorithm | |
| hash_password() { | |
| local password=$1 | |
| echo "*$(echo -n "$password" | openssl sha1 -binary | openssl sha1 -hex | cut -d' ' -f2 | tr '[:lower:]' '[:upper:]')" | |
| } | |
| # Function to prompt for a password and verify it | |
| verify_password() { | |
| local user=$1 | |
| local host=$2 | |
| local stored_hash=$3 | |
| while true; do | |
| read -sp "Enter the password for '$user'@'$host': " password | |
| echo | |
| # Hash the entered password | |
| entered_hash=$(hash_password "$password") | |
| # Compare the entered hash with the stored hash | |
| if [ "$entered_hash" == "$stored_hash" ]; then | |
| echo "Password is correct." | |
| echo "$password" | |
| return 0 | |
| else | |
| echo "Password is incorrect. Try again." | |
| fi | |
| done | |
| } | |
| where="1" | |
| if [[ -n "$2" ]]; then | |
| where="user = '$2'" | |
| fi | |
| mapfile -t users < <(docker exec -i "$container_name" mysql -e "SELECT user, host, authentication_string FROM mysql.user WHERE plugin='mysql_native_password' AND $where;" -s -N) | |
| if [ "${#users[@]}" -eq 0 ]; then | |
| printf '\u2705 ' | |
| echo "No users found with the mysql_native_password plugin." | |
| exit 0 | |
| fi | |
| # Process each user from the array | |
| for user_info in "${users[@]}"; do | |
| IFS=$'\t' read -r user host stored_hash <<< "$user_info" | |
| while true; do | |
| read -sp "Enter the password for '$user'@'$host': " password | |
| echo | |
| # Hash the entered password | |
| entered_hash=$(hash_password "$password") | |
| # Compare the entered hash with the stored hash | |
| if [ "$entered_hash" == "$stored_hash" ]; then | |
| docker exec -i "$container_name" mysql -e "ALTER USER '$user'@'$host' IDENTIFIED WITH caching_sha2_password BY '$password';" | |
| printf '\u2705 ' | |
| echo "User '$user'@'$host' password type changed to caching_sha2_password." | |
| break | |
| else | |
| printf '\u274c ' | |
| read -p "Password is incorrect. Do you want to (d)rop the user '$user'@'$host', (g)enerate a new password or (s)kip? (d/g/s): " choice | |
| case $choice in | |
| d|drop) | |
| docker exec -i "$container_name" mysql -e "DROP USER '$user'@'$host';" | |
| echo "User '$user'@'$host' dropped." | |
| break | |
| ;; | |
| g|generate) | |
| new_password=$(openssl rand -base64 12) | |
| docker exec "$container_name" mysql -e "ALTER USER '$user'@'$host' IDENTIFIED WITH caching_sha2_password BY '$new_password';" | |
| echo "New password for '$user'@'$host': $new_password" | |
| break | |
| ;; | |
| s|skip) | |
| echo "Skipping user '$user'@'$host'." | |
| break | |
| ;; | |
| *) | |
| echo "Invalid choice." | |
| ;; | |
| esac | |
| fi | |
| done | |
| done | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment