How to fix ERROR 1147: No such grant defined for table in MySQL

First, check what users actually exist and their host patterns in the mysql.user system table. This helps identify any typos:

SELECT User, Host FROM mysql.user WHERE User = 'your_username';

Note the exact spelling and host pattern. Common mistakes include forgetting to specify the hostname (defaults to '%' for all hosts) or mistyping the username.

Query the mysql.tables_priv table to see what table-level privileges actually exist for the user:

SELECT User, Host, Db, Table_name, Table_priv FROM mysql.tables_priv
WHERE User = 'your_username' AND Host = 'your_host';

If this query returns no rows, then no table-level privileges exist for that user. The REVOKE statement is attempting to remove something that was never granted.

If no table-level grants exist, the privilege may have been granted at the database level instead. Check mysql.db:

SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.db
WHERE User = 'your_username' AND Host = 'your_host';

If privileges exist here but not in mysql.tables_priv, then the problem is privilege scope mismatch: you cannot revoke table-level grants if the privilege was granted at the database level.

The simplest fix for modern MySQL versions is to use the IF EXISTS clause, which converts the error into a warning and allows the statement to succeed:

REVOKE IF EXISTS SELECT, INSERT ON database.table FROM 'your_username'@'your_host';

This is idempotent: if the grant does not exist, the statement succeeds with a warning instead of failing with an error. This is ideal for deployment automation.

If the privilege was granted at the database level, revoke it at that level instead:

-- If privilege was granted at database level:
REVOKE SELECT, INSERT ON database.* FROM 'your_username'@'your_host';

-- NOT at table level:
-- REVOKE SELECT, INSERT ON database.table FROM 'your_username'@'your_host'; -- This causes error 1147

Always match the REVOKE statement's scope (global, database, table, or column) to the scope where the privilege was originally granted.

If the privilege should exist but doesn't (e.g., after a restore), grant it first, then revoke if needed:

-- Grant the privilege at the table level:
GRANT SELECT, INSERT ON database.table TO 'your_username'@'your_host';

-- Now you can revoke it without error:
REVOKE SELECT, INSERT ON database.table FROM 'your_username'@'your_host';

This is useful when migrating databases between environments where privilege grants were lost or not copied correctly.