# What does pt-show-grants look like?

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 don’t 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, I’d 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, I’d 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.