mysql打印用户权限的小技巧

mysql5.5 5.6

SHOW GRANTS FOR

mysql> SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user;
+------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') |
+------------------------------------------------------+
| SHOW GRANTS FOR 'root'@'localhost';                  |
+------------------------------------------------------+
1 rows in set (0.01 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*79065082DC40BD289FD24E8D80A7DAEFC8D15F94' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql5.7

SHOW CREATE USER

mysql> SELECT CONCAT('SHOW CREATE USER   ''', user, '''@''', host, ''';' ) AS query FROM mysql.user;
+-------------------------------------------------+
| query                                           |
+-------------------------------------------------+
| SHOW CREATE USER   'root'@'172.17.0.1';         |
| SHOW CREATE USER   'mysql.session'@'localhost'; |
| SHOW CREATE USER   'mysql.sys'@'localhost';     |
| SHOW CREATE USER   'root'@'localhost';          |
+-------------------------------------------------+
4 rows in set (0.03 sec)

mysql>  SHOW CREATE USER   'root'@'172.17.0.1'; 
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@172.17.0.1                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'172.17.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*51AC5E121D319E23ABA95E9131ED8C06D2B9DBE8' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)