database

How do I show all grants for all users on my MySQL database?

This handy one-liner will display all grants for all users on your MySQL database:

mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql | sed ‘s,\t,”@”,g;s,^,show grants for “,g;s,$,”;,g;’ | mysql –batch –skip-column-names | sed ‘s,$,;,g’

Ex. mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql | sed ‘s,\t,”@”,g;s,^,show grants for “,g;s,$,”;,g;’ | mysql –batch –skip-column-names | sed ‘s,$,;,g’

GRANT USAGE ON *.* TO ‘user1’@’%’ IDENTIFIED BY PASSWORD ‘*B8B7B8C47EF8381F4C2BBD441DAF0E52AEE035DF’;
GRANT ALL PRIVILEGES ON `mydb`.* TO ‘user1’@’%’;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1′ WITH GRANT OPTION;
GRANT USAGE ON *.* TO ”@’dbhost.domain’;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’dbhost.domain’ WITH GRANT OPTION;
GRANT USAGE ON *.* TO ”@’localhost’;

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top