MysqlHow-ToBeginner · 3 min read

How to Grant All Privileges in MySQL: Syntax and Examples

To grant all privileges in MySQL, use the GRANT ALL PRIVILEGES ON database.* TO 'username'@'host'; command. This gives the user full access to the specified database. Remember to run FLUSH PRIVILEGES; to apply changes immediately.

📐

Syntax

The GRANT ALL PRIVILEGES statement gives a user full rights on a database or all databases. Here’s what each part means:

ALL PRIVILEGES: Grants every permission available.
ON database.*: Specifies the database and all its tables. Use *.* for all databases.
TO 'username'@'host': Defines the user and where they connect from.
IDENTIFIED BY 'password': (Optional) Sets or changes the user’s password.

After granting, use FLUSH PRIVILEGES; to reload permissions.

sql

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

💻

Example

This example grants all privileges on the shop database to user alice connecting from any host, with password secret123. It then reloads the privileges so changes take effect immediately.

sql

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

Output

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

⚠️

Common Pitfalls

Common mistakes when granting all privileges include:

Forgetting to run FLUSH PRIVILEGES;, so changes don’t apply immediately.
Using 'localhost' instead of '%' if the user connects from other hosts.
Granting privileges on the wrong database or missing the .* wildcard to include all tables.
Not setting a password, which can cause security risks.

sql

/* Wrong: Missing FLUSH PRIVILEGES */
GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%';

/* Right: Include FLUSH PRIVILEGES */
GRANT ALL PRIVILEGES ON shop.* TO 'alice'@'%' IDENTIFIED BY 'secret123';
FLUSH PRIVILEGES;

📊

Quick Reference

Command Part	Description
GRANT ALL PRIVILEGES	Gives full access rights
ON database.*	Specifies database and all tables
TO 'user'@'host'	Defines user and connection source
IDENTIFIED BY 'password'	Sets or changes user password
FLUSH PRIVILEGES	Reloads permission changes

✅

Key Takeaways

Use GRANT ALL PRIVILEGES ON database.* TO 'user'@'host' to give full access.

Always run FLUSH PRIVILEGES after granting rights to apply changes.

Use '%' as host to allow connections from any IP address.

Include IDENTIFIED BY to set or update the user password securely.

Check the database and user details carefully to avoid permission errors.