User Tools

Site Tools


mysql:export_users_with_grants

MySQL - Export users with grants

Make it easy to have all the queries needed to determine the grants for every user.

mysql> SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user;
 
+-------------------------------------------------+
| query                                           |
+-------------------------------------------------+
| SHOW GRANTS FOR 'root'@'127.0.0.1';             |
| SHOW GRANTS FOR 'root'@'::1';                   |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'email'@'localhost';            |
| SHOW GRANTS FOR 'greylist'@'localhost';         |
| SHOW GRANTS FOR 'mysql.sys'@'localhost';        |
| SHOW GRANTS FOR 'root'@'localhost';             |
| SHOW GRANTS FOR 'roundcube'@'localhost';        |
| SHOW GRANTS FOR 'roundcubeuser'@'localhost';    |
| SHOW GRANTS FOR 'snort'@'localhost';            |
| SHOW GRANTS FOR 'spamassassin'@'localhost';     |
| SHOW GRANTS FOR 'wiki'@'localhost';             |
| SHOW GRANTS FOR 'wiki'@'localhost.localdomain'; |
+-------------------------------------------------+
13 rows in set (0.00 sec)

Then get the actual grants for that user.

mysql> SHOW GRANTS FOR 'email'@'localhost'; 
 
+-------------------------------------------------------------------+
| Grants for email@localhost                                        |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'email'@'localhost'                         |
| GRANT ALL PRIVILEGES ON `email_accounts`.* TO 'email'@'localhost' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

To combine all the above, run this query:

### mysql -uroot -p{PASS} -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | mysql -uroot -p{PASS} | sed 's/\(GRANT .\)/\1;/;s/^\(Grants for .\)/## \1 ##/;/##/{x;p;x;}'
mysql/export_users_with_grants.txt · Last modified: 2020/07/15 10:30 by 127.0.0.1

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki