I need to give an user TRIGGER permission for an whole schema in mysql to import mysql workbench backup.
I tried with:
grant trigger ON `schemaname`.* TO `user`@`localhost`
But while importing there comes the error that the user haven't the permissions.
ERROR 1142 (42000) at line 53: TRIGGER command denied to user 'user'@'localhost' for table 'table'
I tried to give the user TRIGGER permission to the table - that works, but of course only for that table, for the others still came the error.
Is there any way to give an user trigger permission to an schema without giving him the permission for every table separately?
Answer
From MySQL Docs
In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege.
So you need to give SUPER privileges to the User. While importing, there will be command like "Create Trigger..." which is throwing an error.
Check your MySQL version and definer value as well for trigger in the importing file.
Edit: For version 5.1, follow MySQL docs, that says:
CREATE TRIGGER requires the TRIGGER privilege for the table associated with the
trigger. The statement might also require the SUPER privilege, depending on
the DEFINER value, as described later in this section. If binary logging is
enabled, CREATE TRIGGER might require the SUPER privilege, as described in
Section 19.7, “Binary Logging of Stored Programs”. (Before MySQL 5.1.6, there is
no TRIGGER privilege and this statement requires the SUPER privilege in all cases)
The DEFINER clause determines the security context to be used when checking access
privileges at trigger activation time.
So, you need to check Definer value for importing trigger. It might have something like : DEFINER = root. Try removing the definer then try importing. Hope it works...