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:
#!/bin/bash tmp=/tmp/showgrant$$ mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql > $tmp cat $tmp | while read user host do echo "# $user @ $host" mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'" done rm $tmp
You can also use this faster oneliner:
#!/bin/sh
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’
Cheers Paul, nice one.
Brilliant little script… 😉
Sweet! Thanks!
Excellent post, Paul. Thanks for this simple (yet brilliant) little script!