MySQL 5.6 Show ALL Grants for User

Question

I have a MySQL pair that communicates over a VIP. I want to verify permissions for all users (including root) from ANY IP. When I attempt to view all grants for root I get this:

mysql> show grants for root;
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

But there are other grants for root:

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `*.*`.* TO 'root'@'localhost'                                                                                  |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

How can I view ALL grants for a user (all hosts and databases)?

I think you would get help from this [question](http://stackoverflow.com/questions/497317/how-can-i-view-all-grants-for-an-sql-database) — DecoderReloaded, Jan 27 '17 at 17:43
See http://dba.stackexchange.com/questions/23265/mysql-show-grants-for-all-users — pringi, Jan 27 '17 at 18:01

score 12 · Accepted Answer · answered Jan 27 '17 at 17:44

12

You can use this:

select * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;

answered Jan 27 '17 at 17:44

DecoderReloaded

514
4
12

MySQL 5.6 Show ALL Grants for User

1 Answers1