[note: A broken link from a defunct blog made me dig up this post from archive.org and re-post it here; Beginners may find this informative!]
The OurSQL Podcast did an episode on some of the lesser-known but very useful tools in the Percona Toolkit. pt-show-grants is one of those tools that I use pretty frequently. While the manual page has an explanation of all the features and a few examples, you dont really see the output, and often you decide whether or not to use a tool based on what it gives you as output.
So here is a small example of an actual command I did today using pt-show-grants. I wanted to find the grants for a particular user. To do that without pt-show-grants, Id have to login to MySQL, run
mysql> SELECT host FROM mysql.user WHERE user='aus4_dev';
And then use that host information in a SHOW GRANTS statement:
mysql> SHOW GRANTS FOR aus4_dev@HOST;
But I would have to do this for each HOST if there were 2 hosts, Id have to run the SHOW GRANTS command twice.
Happily, pt-show-grants has an option called only, which will show you all user@host combinations for the username you specify. I have login information stored in a .my.cnf on this particular dev machine, and except for the password and host, this is an exact copy/paste of what I typed and the output:
[scabral@dev1.db ~]$ bin/pt-show-grants --only aus4_dev
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.1.52-log at 2012-03-01 08:52:01
-- Grants for 'aus4_dev'@'10.0.0.1'
GRANT USAGE ON *.* TO 'aus4_dev'@'10.0.0.1' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'10.0.0.2';
-- Grants for 'aus4_dev'@'10.0.0.2'
GRANT USAGE ON *.* TO 'aus4_dev'@'10.0.0.2' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'10.0.0.2';
By default, if I did not put in the only, it would show me all the users that I was allowed to see. There is also an ignore option, so if you want to show all users except a particular username, you can do that as well.
Being able to find all user@host users and their grants given a particular username is very handy and eliminates the need to go into the database to find the hostnames.
[original post date: 3/1/2012]