Unlocking MySQL User Access: The SHOW GRANTS Statement Explained
Viewing Granted Privileges in MySQL
- Privileges: These are specific actions a user can perform on a database object (e.g., table, view). Examples include
SELECT
,INSERT
,UPDATE
, andDELETE
. - User: An account used to connect and interact with the MySQL server.
Viewing Grants:
There are three ways to use the SHOW GRANTS
statement:
-
View Grants for the Current User:
SHOW GRANTS;
This displays the privileges granted to the user currently connected to the server.
-
View Grants for a Specific User:
SHOW GRANTS FOR 'username'@'host';
Replace
'username'
with the actual username and'host'
with the hostname or IP address where the user is allowed to connect. -
View Grants for a User with a Role:
SHOW GRANTS FOR 'username'@'host' USING 'rolename';
This displays the privileges granted to the user combined with the privileges of the specified role.
Example:
Consider a user named john
with the following grants:
SELECT
privilege on thecustomers
table
Running SHOW GRANTS;
for this user would display something like:
GRANT SELECT ON `database_name`.`customers` TO 'john'@'localhost';
GRANT INSERT ON `database_name`.`orders` TO 'john'@'localhost';
Related Issues and Solutions:
- Insufficient Privileges: If you attempt to use
SHOW GRANTS
on another user and lack theSELECT
privilege for themysql.user
table, you'll receive an error. To resolve this, either connect with an account with the necessary privileges or be granted theSELECT
privilege on themysql.user
table by the administrator.
Additional Notes:
- The
SHOW GRANTS
statement only displays granted privileges. It doesn't show revoked or default privileges.
mysql