Xaprb

Download MySQL Show Grants

MySQL Toolkit’s Show Grants tool makes it easy to extract grants from a MySQL server in canonical form. You can use it to replicate grants between servers, diff grants, and avoid spurious changesets in version control systems. It’s part of the Maatkit project on Google Code.

It’s a fairly simple tool that connects to a MySQL server, issues SHOW GRANTS, and prints the results. By default it prints grants for every user, but you can specify users to show and users to ignore.

Why a tool for such a simple task?

It’s not as simple as it sounds. By default the output you get from running SHOW GRANTS is not canonicalized. The default output for the same grants is different on different servers, and in fact even on the same server if you wait and try again later! This means you can’t diff or visually inspect grants easily. It also means you’ll get spurious changesets if you’re automatically saving grants into version control.

This tool canonicalizes the grants in three ways:

• If there are multiple rows of output, they’re sorted.
• If there’s a row that contains the IDENTIFIED BY clause, which defines the user’s password, it comes first.
• Grants are sorted within the rows, so GRANT SELECT, INSERT... becomes GRANT INSERT, SELECT...

Besides canonicalizing grants, it’s a more convenient way to extract grants from one server and insert them on another. The output is semicolon-terminated so you can pipe it right into mysql and execute the grant statements.

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.