Mysql TRIGGER command denied to user

15. March 2012 SysAdmin 0

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 [email protected]'%';

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 [email protected]'%' 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 [email protected]'%' identified by 'PASS';<br />
grant TRIGGER on dbname.abcd_table to [email protected]'localhost' identified by 'PASS';

Then verified GRANTS of the user.


Leave a Reply

Your email address will not be published. Required fields are marked *