// you’re reading...

Linux, Unix, and Solaris

Use BASH to extract MySQL ACL list

Need to quickly extract all the GRANT info for your database users?

Here’s a one-liner:

for i in $( mysql -u readuser -pReadPassword -h your.host -e "SELECT CONCAT(user,'@\'',host,'\'') AS ACL FROM mysql.user ORDER BY user" -s ); do mysql -u readuser -pReadPassword -h your.host -e "SHOW GRANTS FOR $i" -s ; done ;

Here’s a more elegant solution:

#!/bin/bash
################################################################################
echo -n ":: Enter MySQL username: "
read USER
echo -n ":: Enter MySQL password: "
read -s PASS
echo ""
echo -n ":: Enter MySQL hostname: "
read HOST
echo -n ":: Enter output filename: "
read FILENAME
echo "User: $USER Host: $HOST File: $FILENAME"
echo -n "Do you want to proceed? [Y/n]: "
read PROCEED
if [ "Y" == "$PROCEED" ]; then
NOW=`date`
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
echo " -- ACL extracted $NOW from $HOST"  >> $FILENAME
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
for i in $( mysql -u $USER -p$PASS -h $HOST -e "SELECT CONCAT(user,'@\'',host,'\'') AS ACL FROM mysql.user ORDER BY user" -s );   
do 
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "
echo "-- $i "
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "
echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- " >> $FILENAME
echo "-- $i " >> $FILENAME
ACL=`mysql -u $USER -p$PASS -h $HOST -e "SHOW GRANTS FOR $i" -s`
echo "$ACL ;" >> $FILENAME; 
echo ""
done ;
echo ""
echo "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #"
echo "#   Output stored to: $FILENAME"
echo "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #"
else
echo "Bailing out"
fi
################################################################################

Discussion

No comments for “Use BASH to extract MySQL ACL list”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: