Search code examples
mysqlsql-grant

Grant execute gives Illegal Grant/Revoke command Error when run on table

Why didn't this work? I have tried GRANT:

GRANT DELETE,INSERT,SELECT,UPDATE,TRIGGER,EXECUTE ON databasename.tablename
TO 'user123'@'%' IDENTIFIED BY 'pas$-word11';

it's throwing back error:

ERROR 1144 (42000): Illegal GRANT/REVOKE command; 
please consult the manual to see which privileges can be used`
Solution

  • I found out that the EXECUTE privilege could not be run with any table specified, since the "level" is not aiming at table

    (About grant Execute)

    Enable the user to execute stored routines. Levels: Global, database, routine.

    MySql ref manual

    I had to run EXECUTE separately with

    GRANT EXECUTE ON tablename.* TO 'user123'@'%' IDENTIFIED BY 'pas$-word11';