Mysql TRIGGER command denied to user

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 &#42;.&#42; TO 'username'@'%' IDENTIFIED BY PASSWORD '<em>D1..........1A9F2' |<br />
| 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.</em> 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';<br />
grant TRIGGER on dbname.abcd_table to username@'localhost' identified by 'PASS';

Then verified GRANTS of the user.

Jobin Joseph

nixhive.com/