How to fix ERROR 1144: Illegal GRANT/REVOKE command in MySQL

Copy your failing GRANT or REVOKE command and examine it carefully. Check the MySQL manual for the correct syntax of the privilege level you need.

Common mistake:

GRANT LOCK TABLES ON database.table TO user@localhost;  -- ERROR 1144

Correct syntax:

GRANT LOCK TABLES ON database.* TO user@localhost;  -- Database level, not table

If your command includes EXECUTE privilege, ensure you specify the PROCEDURE or FUNCTION keyword to indicate the object type.

Mistake:

GRANT EXECUTE ON mydb.stored_proc TO user@localhost;  -- ERROR 1144

Correct:

GRANT EXECUTE ON PROCEDURE mydb.stored_proc TO user@localhost;
GRANT EXECUTE ON FUNCTION mydb.my_function TO user@localhost;

Some MySQL privileges can only be granted at the global level. Move these from database/table scope to global scope.

Global-only privileges requiring *.*:
- PROCESS
- SUPER
- RELOAD
- SHOW DATABASES
- SHUTDOWN
- FILE
- REPLICATION MASTER ADMIN
- ROLE_ADMIN (MySQL 8.0+)

Example fix:

-- Wrong: Database level
GRANT PROCESS ON mydb.* TO user@localhost;  -- ERROR 1144

-- Correct: Global level
GRANT PROCESS ON *.* TO user@localhost;

If you need to grant mixed privilege types, break them into separate statements with appropriate scopes.

Instead of:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON mydb.users TO user@localhost;  -- ERROR 1144

Use:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO user@localhost;
GRANT LOCK TABLES ON mydb.* TO user@localhost;

Some privileges only exist in specific MySQL versions. If using a privilege not supported in your version, remove it or upgrade MySQL.

Run this to check your version:

SELECT VERSION();

Version-specific examples:
- ROLE_ADMIN (MySQL 8.0+)
- SYSTEM_VARIABLES_ADMIN (MySQL 8.0.14+)
- BACKUP_ADMIN (MySQL 8.0.3+)

If the privilege doesn't exist in your version, either upgrade or remove it from the GRANT statement.

Once you've fixed the syntax, execute the corrected command.

Example complete command with correct syntax:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO 'appuser'@'localhost';
GRANT CREATE ON mydb.* TO 'appuser'@'localhost';
GRANT EXECUTE ON PROCEDURE mydb.generate_report TO 'appuser'@'localhost';
GRANT LOCK TABLES ON mydb.* TO 'appuser'@'localhost';

Verify success with:

SHOW GRANTS FOR 'appuser'@'localhost';