Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.

There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.

The issue, of course, lies in what the “certain” privileges are. If it is “who has the SUPER privilege?” then a simple

SELECT user,host FROM mysql.user WHERE Super_priv='Y';

is sufficient. If it is “who has write access to the foo database”, you might write:

SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';

but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:

SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y'
 UNION SELECT user,host FROM user WHERE Select_priv='Y';

What if you just want a list of the GRANT statements for every user? The maatkit toolkit has a great tool, mk-show-grants, that will do this for you. In fact, it even sorts the order of the permissions, so you can easily compare them. MySQL does not always print out the permissions in the same order (ie, GRANT SELECT, INSERT... might show up as GRANT INSERT, SELECT...)

But what if you want a partial list? What if you want to find the GRANT statements for all users not coming from localhost? mk-show-grants allows you to specify which users to look at, but it does not allow pattern matching.

This is where I get creative with SQL and scripting. First I come up with the query to find the users I want (a mysql user is user@host):

SELECT user,host FROM mysql.user WHERE host!='localhost';

Most folks would stop here, and manually run a SHOW GRANTS FOR user@host for each user that appears.

But I hate tedium. So, I make it so the output will print out a command so I can show the grants:

SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';

I’ll run the statement from a mysql -s command line, so the silly table format is not shown, and either copy and paste the statements directly into the buffer to get the results, or copy into a file (or have used the tee command in MySQL).

Then I can source the file in MySQL to get the results (tee’ing the output into a file).

The point of this is not necessarily to find user grants. The point is to think creatively, with SQL as a tool in your arsenal. Know the strengths and weaknesses of your tools. My word processor can do some search-and-replace functionality easier than vi or emacs, and vi does “replace the beginning/end of line” very well. SQL statements can combine text with query results. Do something in 2 or 3 parts, using a combination of tools (SQL, shell scripting) to make each part as easy as possible.

Be creative.