MySQL Toolkit’s Show Grants tool can now separate grants into individual statements, convert them into REVOKE statements, and help you remove users from a server, even if your server doesn’t support DROP USER.
Changes
The comment on my last post, combined with my need to revoke some grants today on a server that accidentally got grants replicated to it, prompted me to add more functionality to this tool. It can now:
- Separate grants into individual statements, one grant per line. Instead of a line with
GRANT SELECT, INSERT...you can now see one line for each grant, if you wish. This makes it easier to pipe the output through other tools to select only the grants you want, and actually makes the output more diff-friendly too. - Convert
GRANTstatements into equivalentREVOKEstatements (also one at a time if you wish). This makes it easier to remove specific grants from a user. - Add
DROP USERand other statements to help you completely remove a user’s account. MySQL historically has three ways to do this. In recent versions you can just sayDROP USERand it gets rid of the user and all the user’s grants. In older versions this command didn’t remove the grants, so you still had to manually remove those — a tedious process. And in even older versions there’s noDROP USERcommand at all; you have toREVOKEeverything, thenDELETEfrom the user table andFLUSH PRIVILEGES. The tool now has options to create these statements for you.
The temptation I resisted
I had to resist the temptation to add more options, for example to entirely remove the GRANT statements so the tool will merely output the statements required to remove a user. But I think that’s feature creep; you can easily pipe the output through grep -v "^GRANT" and do it yourself.
Still, if you’d find this or other features useful, let me know.
About MySQL Toolkit
MySQL Toolkit is a set of essential tools for MySQL users, developers and administrators. The project’s goal is to make high-quality command-line tools that follow the UNIX philosophy of doing one thing and doing it well. They are designed for scriptability and ease of processing with standard command-line utilities such as awk and sed. Other tools in the toolkit include a table checksummer and a duplicate key checker.
The tool does not yet take into consideration a socket that differs from /tmp/mysql.sock for a local MySQL server. Even if it’s correctly specified in ~/.my.cnf, the it tries to connect to /tmp/mysql.sock.
Workaround: Connect with -host-Option giving the FQDN or IP address of localhost.
Quite true. This and all the other tools use my own code to read the .my.cnf file. I just recently learned this isn’t necessary: the MySQL DBD driver has this ability built-in! I am going to convert them to use this, and I feel sure this will solve the problem. (Plus, it’ll be lots less code for me to maintain).