How to fix ERROR 1141: No such grant defined for user in MySQL

First, check what users and hosts actually exist in your MySQL instance:

SELECT User, Host FROM mysql.user;

Look for the exact user@host combination you're working with. MySQL treats "user@localhost" and "[email protected]" as completely different accounts.

Use SHOW GRANTS to see what privileges are actually assigned:

SHOW GRANTS FOR 'username'@'hostname';

Replace username and hostname with your actual values (keep the quotes). This command shows you exactly what privileges exist for this user@host, so you know what can be revoked.

Based on the output from SHOW GRANTS, craft your REVOKE statement to match what actually exists:

REVOKE SELECT, INSERT ON database.* FROM 'username'@'hostname';

Ensure the specific privileges you're revoking are listed in the SHOW GRANTS output. Do not attempt to revoke privileges that don't appear there.

If you're running MySQL 8.0.30 or later, use the IF EXISTS clause to make REVOKE operations more forgiving:

REVOKE IF EXISTS SELECT, INSERT ON database.* FROM 'username'@'hostname';

This will issue a warning instead of an error if the privilege doesn't exist, allowing your scripts to continue without failure.

If privileges exist for a wildcard host ('user'@'%') but you're trying to revoke for a specific host ('user'@'localhost'), you need to revoke from the wildcard:

-- This will fail if grant exists only for '%'
REVOKE SELECT ON database.* FROM 'user'@'localhost';

-- This is what you actually need
REVOKE SELECT ON database.* FROM 'user'@'%';

Always match the exact host in your REVOKE statement to where the privileges were originally granted.

If you have global privileges and want to revoke privileges only for specific databases, enable partial_revokes:

SET GLOBAL partial_revokes = ON;

Then you can revoke database-specific privileges:

REVOKE SELECT ON mydb.* FROM 'user'@'%';

This is only necessary if you previously granted global privileges and now want to restrict them to specific databases.

After making privilege changes, flush the privilege cache to ensure changes take effect immediately:

FLUSH PRIVILEGES;

While MySQL usually applies privilege changes immediately, explicitly flushing ensures all connections see the updated permissions.