MySQL: SHOW GRANTS for all users, from CLI, with a one-liner

If you ever have to double-check all your MySQL Users permissions database per database, and server by server, it’s a pain in the ass, and as far as SHOW GRANTS only takes one username, you cannot export everything simply. Let’s not discuss about how you could have used PhpMyAdmin, you just can’t, or it has become your full-time job as it will be very long if you have many servers with many databases.
Thanks to a combination of MySQL and bash magics, you still can achieve this with a bash one-liner. You’ll only have to input your eventual MySQL password twice.

mysql -u root -p -P 3306 -s <<< 'SELECT CONCAT("SHOW GRANTS FOR ",user,"@",host,";") FROM mysql.user WHERE host="host.example.com";' | sed -e "s/FOR /&'/" -e "s/@/'&'"/ -e "s/;/'&/" | mysql -u root -p -P 3306 -s

This sample one-liner limits the users connecting from a remote host, but if you remove the WHERE part, you can have all grants for all known users.

Note: you have to input your password twice. The second time, it might be displayed on-screen, but you can be sure it will never be in your bash history.

2 comments so far.

  1. Great and straight forward tutorial.

    Saved me a lot of trouble when migrating users and privileges to another DB.

    Thanks.

  2. Hey, thank you for this. I’m trying to track down a problem on a server with several hundred users. Very helpful.

Share your thoughts

*