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.
