SHOW GRANTS for all users on MySQL

Although there isn’t yet a command (that I am aware of) to show all user GRANTS in MySQL, you can write a quick bash script to do the job if you have shell access:

mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql > $tmp
cat $tmp | while read user host
echo "# $user @ $host"
mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"
rm $tmp

6 thoughts on “SHOW GRANTS for all users on MySQL”

  1. You can also use this faster oneliner:

    mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql | sed ‘s,\t,”@”,g;s,^,show grants for “,g;s,$,”;,g;’ | mysql –batch –skip-column-names | sed ‘s,$,;,g’

Leave a Reply

Your email address will not be published. Required fields are marked *