How to Show Grants for User in MySQL

Use the SHOW GRANTS FOR 'username'@'host'; command in MySQL to display the privileges assigned to a specific user. Replace username and host with the actual MySQL username and host name.

Syntax

The SHOW GRANTS statement displays the privileges granted to a MySQL user. The syntax is:

• SHOW GRANTS FOR 'username'@'host'; - Shows grants for the specified user and host.
• username is the MySQL user name.
• host is the host from which the user connects (often localhost or % for any host).

sql

SHOW GRANTS FOR 'username'@'host';

Example

This example shows how to display grants for a user named alice connecting from localhost. It lists all privileges assigned to that user.

sql

SHOW GRANTS FOR 'alice'@'localhost';

Output

Grants for alice@localhost
GRANT SELECT, INSERT ON `mydb`.* TO 'alice'@'localhost'
GRANT USAGE ON *.* TO 'alice'@'localhost'

Common Pitfalls

Common mistakes when using SHOW GRANTS include:

• Not specifying the correct host part, which causes an error or no results.
• Trying to run SHOW GRANTS without proper privileges, which may fail.
• Confusing the MySQL username with the system username.

Always check the exact user and host combination in the mysql.user table if unsure.

sql

/* Wrong: missing host part */
SHOW GRANTS FOR 'alice';

/* Right: specify host */
SHOW GRANTS FOR 'alice'@'localhost';

Quick Reference

Command	Description
SHOW GRANTS FOR 'user'@'host';	Display privileges for specified user and host
SHOW GRANTS;	Show privileges for current user
SELECT User, Host FROM mysql.user;	List all users and hosts in MySQL

Key Takeaways

Use SHOW GRANTS FOR 'username'@'host'; to see a user's privileges in MySQL.

Always specify both username and host to avoid errors.

You need proper privileges to view other users' grants.

Check mysql.user table to confirm user and host details.

SHOW GRANTS without FOR shows grants for the current user.