Today i got a new mysql issue from one of my colleague. –

Issue

In MySQL TRIGGER command denied to user ‘username’@’192.168.0.112′ for table ‘abcd_table’

What you normally do ?
As usual, I checked the user’s GRANTS with the following command.

mysql> show grants for username@'%';

GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'D1..........1A9F2' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON dbname. TO 'username'@'%'

As you can see there is no TRIGGER permission, I have granted the same with grant TRIGGER on dbname.* to username@'%' identified by 'PASS'; But, the issue wasn’t solved. I again checked the GRANTS, but there was nothing related to TRIGGER. After some googling, I found that you can only grant TRIGGER permission to a table.

Solution

grant TRIGGER on dbname.abcd_table to username@'%' identified by 'PASS'; grant TRIGGER on dbname.abcd_table to username@'localhost' identified by 'PASS';

Then verified GRANTS of the user.