How to Grant Privileges in MySQL: Syntax and Examples

Use the GRANT statement in MySQL to give specific privileges to a user on databases or tables. The syntax is GRANT privileges ON database.table TO 'user'@'host'; followed by FLUSH PRIVILEGES; to apply changes.

Syntax

The GRANT statement assigns privileges to a MySQL user. It has three main parts:

• privileges: The actions the user can perform, like SELECT, INSERT, or ALL PRIVILEGES.
• database.table: Specifies where the privileges apply. Use *.* for all databases and tables.
• 'user'@'host': The MySQL user and the host they connect from.

After granting, run FLUSH PRIVILEGES; to reload the privilege tables.

sql

GRANT privileges ON database.table TO 'user'@'host';
FLUSH PRIVILEGES;

Example

This example grants all privileges on the shop database to user alice connecting from any host.

sql

GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%';
FLUSH PRIVILEGES;

Output

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

Common Pitfalls

Common mistakes include:

• Not specifying the correct 'user'@'host', which causes the grant to fail.
• Forgetting to run FLUSH PRIVILEGES; after granting.
• Using GRANT without specifying privileges or using incorrect privilege names.
• Granting privileges on the wrong database or table.

sql

/* Wrong: missing host part */
GRANT SELECT ON shop.products TO 'bob';

/* Correct: specify host */
GRANT SELECT ON shop.products TO 'bob'@'localhost';
FLUSH PRIVILEGES;

Quick Reference

Part	Description	Example
privileges	Actions allowed for the user	SELECT, INSERT, ALL PRIVILEGES
database.table	Scope of privileges	shop.*, mydb.orders
'user'@'host'	User and host allowed to connect	'alice'@'%', 'bob'@'localhost'
FLUSH PRIVILEGES	Reloads privilege tables	FLUSH PRIVILEGES;

Key Takeaways

Use GRANT to assign specific privileges to MySQL users on databases or tables.

Always specify the user and host in the format 'user'@'host' to avoid errors.

Run FLUSH PRIVILEGES after granting to apply changes immediately.

Use ALL PRIVILEGES for full access or list specific privileges for limited access.

Check your privilege scope carefully to avoid granting too many permissions.