USER_PRIVILEGES

The USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user system table:

  1. USE INFORMATION_SCHEMA;
  2. DESC USER_PRIVILEGES;

The output is as follows:

  1. +----------------+--------------+------+------+---------+-------+
  2. | Field          | Type         | Null | Key  | Default | Extra |
  3. +----------------+--------------+------+------+---------+-------+
  4. | GRANTEE        | varchar(81)  | YES  |      | NULL    |       |
  5. | TABLE_CATALOG  | varchar(512) | YES  |      | NULL    |       |
  6. | PRIVILEGE_TYPE | varchar(64)  | YES  |      | NULL    |       |
  7. | IS_GRANTABLE   | varchar(3)   | YES  |      | NULL    |       |
  8. +----------------+--------------+------+------+---------+-------+
  9. 4 rows in set (0.00 sec)

View the information in the USER_PRIVILEGES table:

  1. SELECT * FROM USER_PRIVILEGES;

The output is as follows:

  1. +------------+---------------+-------------------------+--------------+
  2. | GRANTEE    | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
  3. +------------+---------------+-------------------------+--------------+
  4. | 'root'@'%' | def           | SELECT                  | YES          |
  5. | 'root'@'%' | def           | INSERT                  | YES          |
  6. | 'root'@'%' | def           | UPDATE                  | YES          |
  7. | 'root'@'%' | def           | DELETE                  | YES          |
  8. | 'root'@'%' | def           | CREATE                  | YES          |
  9. | 'root'@'%' | def           | DROP                    | YES          |
  10. | 'root'@'%' | def           | PROCESS                 | YES          |
  11. | 'root'@'%' | def           | REFERENCES              | YES          |
  12. | 'root'@'%' | def           | ALTER                   | YES          |
  13. | 'root'@'%' | def           | SHOW DATABASES          | YES          |
  14. | 'root'@'%' | def           | SUPER                   | YES          |
  15. | 'root'@'%' | def           | EXECUTE                 | YES          |
  16. | 'root'@'%' | def           | INDEX                   | YES          |
  17. | 'root'@'%' | def           | CREATE USER             | YES          |
  18. | 'root'@'%' | def           | CREATE TABLESPACE       | YES          |
  19. | 'root'@'%' | def           | TRIGGER                 | YES          |
  20. | 'root'@'%' | def           | CREATE VIEW             | YES          |
  21. | 'root'@'%' | def           | SHOW VIEW               | YES          |
  22. | 'root'@'%' | def           | CREATE ROLE             | YES          |
  23. | 'root'@'%' | def           | DROP ROLE               | YES          |
  24. | 'root'@'%' | def           | CREATE TEMPORARY TABLES | YES          |
  25. | 'root'@'%' | def           | LOCK TABLES             | YES          |
  26. | 'root'@'%' | def           | CREATE ROUTINE          | YES          |
  27. | 'root'@'%' | def           | ALTER ROUTINE           | YES          |
  28. | 'root'@'%' | def           | EVENT                   | YES          |
  29. | 'root'@'%' | def           | SHUTDOWN                | YES          |
  30. | 'root'@'%' | def           | RELOAD                  | YES          |
  31. | 'root'@'%' | def           | FILE                    | YES          |
  32. | 'root'@'%' | def           | CONFIG                  | YES          |
  33. | 'root'@'%' | def           | REPLICATION CLIENT      | YES          |
  34. | 'root'@'%' | def           | REPLICATION SLAVE       | YES          |
  35. +------------+---------------+-------------------------+--------------+
  36. 31 rows in set (0.00 sec)
  1. +------------+---------------+-------------------------+--------------+
  2. | GRANTEE    | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
  3. +------------+---------------+-------------------------+--------------+
  4. | 'root'@'%' | def           | SELECT                  | YES          |
  5. | 'root'@'%' | def           | INSERT                  | YES          |
  6. | 'root'@'%' | def           | UPDATE                  | YES          |
  7. | 'root'@'%' | def           | DELETE                  | YES          |
  8. | 'root'@'%' | def           | CREATE                  | YES          |
  9. | 'root'@'%' | def           | DROP                    | YES          |
  10. | 'root'@'%' | def           | PROCESS                 | YES          |
  11. | 'root'@'%' | def           | REFERENCES              | YES          |
  12. | 'root'@'%' | def           | ALTER                   | YES          |
  13. | 'root'@'%' | def           | SHOW DATABASES          | YES          |
  14. | 'root'@'%' | def           | SUPER                   | YES          |
  15. | 'root'@'%' | def           | EXECUTE                 | YES          |
  16. | 'root'@'%' | def           | INDEX                   | YES          |
  17. | 'root'@'%' | def           | CREATE USER             | YES          |
  18. | 'root'@'%' | def           | CREATE TABLESPACE       | YES          |
  19. | 'root'@'%' | def           | TRIGGER                 | YES          |
  20. | 'root'@'%' | def           | CREATE VIEW             | YES          |
  21. | 'root'@'%' | def           | SHOW VIEW               | YES          |
  22. | 'root'@'%' | def           | CREATE ROLE             | YES          |
  23. | 'root'@'%' | def           | DROP ROLE               | YES          |
  24. | 'root'@'%' | def           | CREATE TEMPORARY TABLES | YES          |
  25. | 'root'@'%' | def           | LOCK TABLES             | YES          |
  26. | 'root'@'%' | def           | CREATE ROUTINE          | YES          |
  27. | 'root'@'%' | def           | ALTER ROUTINE           | YES          |
  28. | 'root'@'%' | def           | EVENT                   | YES          |
  29. | 'root'@'%' | def           | RELOAD                  | YES          |
  30. | 'root'@'%' | def           | FILE                    | YES          |
  31. | 'root'@'%' | def           | REPLICATION CLIENT      | YES          |
  32. | 'root'@'%' | def           | REPLICATION SLAVE       | YES          |
  33. +------------+---------------+-------------------------+--------------+
  34. 29 rows in set (0.00 sec)

Fields in the USER_PRIVILEGES table are described as follows:

  • GRANTEE: The name of the granted user, which is in the format of 'user_name'@'host_name'.
  • TABLE_CATALOG: The name of the catalog to which the table belongs. This value is always def.
  • PRIVILEGE_TYPE: The privilege type to be granted. Only one privilege type is shown in each row.
  • IS_GRANTABLE: If you have the GRANT OPTION privilege, the value is YES; otherwise, the value is NO.

See also