MySQL GRANT ALL PRIVILEGES gives a user full access to a database. The right syntax is GRANT ALL PRIVILEGES ON database.* TO 'user'@'host' WITH GRANT OPTION. The right answer is to use a specific host (not % for wildcard), to revoke the GRANT OPTION for application users, and to FLUSH PRIVILEGES if the user already exists.
Table of contents
- The right GRANT syntax
- The WITH GRANT OPTION
- Creating the user first
- The host wildcard
- Revoking privileges
- The FLUSH PRIVILEGES gotcha
- FAQ
The right GRANT syntax
The right syntax to give a user full access to a database:
GRANT ALL PRIVILEGES ON mydb.* TO 'alice'@'localhost';This gives the user alice full access to all tables in mydb, but only when connecting from localhost. The right answer is to specify the host explicitly. The wrong answer is to use alice@% (any host), which is a security risk.
The WITH GRANT OPTION
The WITH GRANT OPTION clause lets the user grant their own privileges to other users. The right answer for a database admin is to include it:
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost' WITH GRANT OPTION;The wrong answer is to include it for an application user. The right answer for an app user is:
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'10.0.0.%';The right answer is to give the app user only the privileges it needs, not ALL.
Creating the user first
The right answer is to create the user with CREATE USER before GRANT:
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO 'alice'@'localhost';
FLUSH PRIVILEGES;The right answer is to use a strong password. The wrong answer is to create a user with no password (IDENTIFIED BY '') or with a simple password like ‘password’.
The host wildcard
The right answer is to specify a specific host. The wrong answer is alice@% (any host), which is a security risk. The right answer for an app that connects from a specific subnet:
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app'@'10.0.0.%';The % is a wildcard for any IP in the 10.0.0.0/24 subnet. The right answer is to combine this with a firewall that blocks MySQL from outside the subnet.
Revoking privileges
The right way to revoke is the REVOKE statement:
REVOKE ALL PRIVILEGES ON mydb.* FROM 'alice'@'localhost';The right answer is to also DROP USER if the user is no longer needed:
DROP USER 'alice'@'localhost';The FLUSH PRIVILEGES gotcha
The right answer is that GRANT and REVOKE automatically reload the privilege tables. The wrong answer is to think you need FLUSH PRIVILEGES after every change — you don’t, in modern MySQL.
The right answer is to use FLUSH PRIVILEGES only when you edit the grant tables directly (e.g., with INSERT INTO mysql.user), which is rare.
FAQ
What is the difference between ALL PRIVILEGES and ALL?
In MySQL 8.0, ALL and ALL PRIVILEGES are synonyms. The right answer is to use ALL PRIVILEGES for clarity.
Can I grant privileges on a specific table?
Yes: GRANT SELECT ON mydb.users TO 'alice'@'localhost';. The right answer is to be as specific as possible.
What privileges should an app user have?
The right answer is the minimum: SELECT, INSERT, UPDATE, DELETE for most apps. The wrong answer is ALL PRIVILEGES for an app user — the right answer is to give only what the app needs.
How do I see the privileges for a user?
SHOW GRANTS FOR 'alice'@'localhost';. The right answer is to audit this regularly to find over-privileged users.
Can I create a user that can only connect from one IP?
Yes: CREATE USER 'app'@'192.168.1.100' IDENTIFIED BY 'password';. The right answer is to use the specific IP, not a wildcard.
What is the difference between IDENTIFIED BY and IDENTIFIED WITH?
IDENTIFIED BY uses the default authentication plugin (caching_sha2_password in MySQL 8.0). IDENTIFIED WITH specifies a plugin. The right answer is to use IDENTIFIED BY for new users.
How do I grant privileges on all databases?
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;. The wrong answer is to do this for an app user — *.* is the right answer only for database admins.
What if the user already exists?
The right answer is to use GRANT directly — it will update the existing user’s privileges. The wrong answer is to DROP USER first and recreate.
How do I see all users?
SELECT user, host FROM mysql.user;. The right answer is to audit this regularly and remove users that no longer need access.
What about roles?
MySQL 8.0 supports roles, which are the right answer for a team with many users. The right answer is to create a role with the right privileges, then grant the role to users. The wrong answer is to manage privileges per-user.
If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.
Useful related references: