| Bug #25716 | Privileges on trigger broke replication | ||
|---|---|---|---|
| Submitted: | 19 Jan 2007 9:18 | Modified: | 4 Aug 2008 16:55 |
| Reporter: | Okulov Vitaliy | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.30 Enterprise | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | privileges, replication, super, trigger | ||
[22 Jan 2007 13:15]
Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php. In your case just read http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html: "Triggers can update tables (as of MySQL 5.0.10), so error messages similar to those for stored functions occur with CREATE TRIGGER if you do not have the SUPER privilege and log_bin_trust_function_creators is 0."
[22 Jan 2007 14:13]
Okulov Vitaliy
But log_bin_trust_function_creators=1 & i cant understand why replication fall when i revoke super privileges. Если можно то по-русски. По идее репликация не должна падать если sql поток на slave сервере не смог отработать запрос из-за того, что не хватает прав.
[31 Jan 2007 14:27]
Okulov Vitaliy
Any news?
[6 Mar 2007 14:37]
Valeriy Kravchuk
If you'll revoke SUPER privilage on master, statement that gives you this error will not be in the binary log on master (verified with latest 5.0.38-BK). So, replication will not be broken. If you revoke SUPER privilege on slave, then it will be your administration problem, as it is just required by design (and replication is statement-based). I think, it is not a bug. If you can show the case when executing UPDATE on a table with trigger by the user without SUPER privilege leads to UPDATE being written into master's binary log, please, send it.
[6 Apr 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[4 Aug 2008 16:55]
Valeriy Kravchuk
Read http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html: "From MySQL 5.0.17 on, MySQL checks trigger privileges like this: - At CREATE TRIGGER time, the user that issues the statement must have the SUPER privilege. - At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges: - The SUPER privilege. - The SELECT privilege for the subject table if references to table columns occur via OLD.col_name or NEW.col_name in the trigger definition. - The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger definition. - Whatever other privileges normally are required for the statements executed by the trigger."
Description: Replication falls with error. Last_Errno: 1227 Last_Error: Error 'Access denied; you need the SUPER privilege for this operation' on query. Default database: 'oneclick'. Query: 'update oneclick.banner set area_mode='all' where id=3325' Skip_Counter: 0 How to repeat: Grant user SUPER privileges, create trigger, revoke super privileges and update table. After that replication falls.