SHOW GRANTS

Description

Use the SHOW GRANTS statement to display all grant information for a user. This would display privileges that were assigned to the user using the GRANT command.

SHOW GRANTS requires the SELECT privilege privileges to query all tables in the mo_catalog database, except to display privileges and roles for the current user.

To name the account or role for SHOW GRANTS, use the same format as for the GRANT statement, for example:

  1. show grants for 'root'@'localhost';

Syntax

  1. > SHOW GRANTS FOR {username[@hostname] | rolename};

Examples

  1. > create role role1;
  2. > grant all on table *.* to role1;
  3. > grant create table, drop table on database *.* to role1;
  4. > create user user1 identified by 'pass1';
  5. > grant role1 to user1;
  6. > show grants for 'user1'@'localhost';
  7. +--------------------------------------------------------+
  8. | Grants for user1@localhost                             |
  9. +--------------------------------------------------------+
  10. | GRANT connect ON account  `user1`@`localhost`          |
  11. | GRANT table all ON table *.* `user1`@`localhost`       |
  12. | GRANT create table ON database *.* `user1`@`localhost` |
  13. | GRANT drop table ON database *.* `user1`@`localhost`   |
  14. +--------------------------------------------------------+
  15. 4 rows in set (0.00 sec)
  17. mysql> show grants for ROLE role1;
  18. +--------------------------------------------+
  19. | Grants for role1                           |
  20. +--------------------------------------------+
  21. | GRANT table all ON table *.* `role1`       |
  22. | GRANT create table ON database *.* `role1` |
  23. | GRANT drop table ON database *.* `role1`   |
  24. +--------------------------------------------+
  25. 3 rows in set (0.00 sec)