How we can check users permissions in mysql?

Solution Verified - Updated -

Environment

  • Red Hat Enterprise Linux 5
  • Red Hat Enterprise Linux 6

Issue

  • How to check which permissions were assigned to user?

Resolution

  • Login to mysql, run command as follows.
 mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*005902A09CD55C9C4A20591CDE9FB77EE301DD16' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • Or specific permissions can be grep as follows.
mysql> SELECT * FROM `information_schema`.`USER_PRIVILEGES`;

| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | SELECT                  | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | INSERT                  | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | UPDATE                  | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | DELETE                  | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | CREATE                  | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | DROP                    | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | RELOAD                  | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | SHUTDOWN                | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | PROCESS                 | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | FILE                    | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | REFERENCES              | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | INDEX                   | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | ALTER                   | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | SHOW DATABASES          | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | SUPER                   | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | CREATE TEMPORARY TABLES | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | LOCK TABLES             | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | EXECUTE                 | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | REPLICATION SLAVE       | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | REPLICATION CLIENT      | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | CREATE VIEW             | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | SHOW VIEW               | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | CREATE ROUTINE          | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | ALTER ROUTINE           | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | CREATE USER             | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | EVENT                   | NO           |
| 'root'@'192.168.122.0/255.255.255.0'      | NULL          | TRIGGER                 | NO           |

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

Comments