mysql: Show GRANTs for all users

select * from information_schema.user_privileges;

EDIT:

As mentioned by Shlomi Noach:

It does not list database-specific, table-specific, column-specific, routine-specific privileges. Therefore, the grant GRANT SELECT ON mydb.* TO myuser@localhost does not show in information_schema.user_privileges. The common_schema solution presented above aggregates the data from user_privileges and other tables to give you the full picture.

Nothing built-in. You have two options though:

• Use common_schema's sql_show_grants view. For example, you can query:

  SELECT sql_grants FROM common_schema.sql_show_grants;
  

  Or you can query for particular users, for example:

  SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app';
  

  To install common_schema, follow the instructions here.

  Disclaimer: I am author of this tool.

• Use Percona Toolkit's pt-show-grants, for example:

  pt-show-grants --host localhost --user root --ask-pass
  

In both cases you can ask for the GRANT command or the REVOKE (opposite) command.

The first case requires that you install a schema, the latter requires that you install PERL scripts + dependencies.

This Linux shell fragment loops over all MySQL users and does a SHOW GRANTS for each:

mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort | \
while read u
 do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'
done

Works best if you can connect to MySQL without a password.

Output is formatted so it can be run in a MySQL shell. Caution: Output also contains the MySQL root user permissions and password! Remove those lines if you don't want the MySQL root user changed.