Differences

This shows you the differences between two versions of the page.

--- mysql:export_users_with_grants [2016/07/04 00:10] – created peter
+++ mysql:export_users_with_grants [2020/07/15 09:30] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== MySQL - Export users with grants ======
-Run this query:
+Make it easy to have all the queries needed to determine the grants for every user.
+<code mysql>
+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'; |
++-------------------------------------------------+
+rows in set (0.00 sec)
+</code>
+Then get the actual grants for that user.
+<code mysql>
+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' |
++-------------------------------------------------------------------+
+rows in set (0.00 sec)
+</code>
+To combine all the above, run this query:
 <code mysql>
 ### 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;}'
 </code>