MySQL Querying User Permissions

In MySQL, you can check the corresponding user permissions by examining the data records in the mysql.user table, or you can use the SHOW GRANTS statement to query user permissions.

The mysql.user table under the MySQL database stores users' basic permissions, and you can use the SELECT statement to view it. The code for the SELECT statement is as follows:

SELECT * FROM mysql.user;

To execute this statement, you must have the SELECT privilege on the user table.

Note: Newly created users only have permission to log in to the MySQL server and do not have any other permissions, so they cannot query the user table.

In addition to using the SELECT statement, you can also use the SHOW GRANTS FOR statement to view permissions. Its syntax format is as follows:

SHOW GRANTS FOR 'username'@'hostname';

Here, username represents the username, and hostname represents the hostname or host IP.

Example 1

Let's create a user named testuser1 and query their permissions. The SQL statements and execution process are as follows:

mysql> CREATE USER 'testuser1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'testuser1'@'localhost';
+-----------------------------------------------+
| Grants for testuser1@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser1'@'localhost' |
+-----------------------------------------------+
1 row in set (0.00 sec)

Here, USAGE ON *.* indicates that this user does not have any privileges on any database or table.

Example 2

Let's query the permissions of the root user. The code is as follows:

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

In this case, GRANT ALL PRIVILEGES ON *.* indicates that the root user has all privileges on all databases and tables, and GRANT PROXY ON ''@'' indicates some additional privileges related to proxy.