Mysql – Why/when does creating a thesql trigger require SUPER privileges

MySQLSecuritytrigger

I'm attempting to parse and understand what the MySQL doc says about when SUPER privileges are required for creating triggers. They say:

If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege[1]

This sort of implies that there are circumstances where it does not require the SUPER privilege. Are they referring to the log_bin_trust_function_creators=1 setting, or is there something else?

Secondly, in [2] they say:

… trusted not to create stored functions that will cause unsafe events to be written to the binary log.

Just what exactly is the danger here? What kinds of events might be "unsafe" in the binary log, and to whom?

[1] https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

[2] https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_log_bin_trust_function_creators

Best Answer

With regard to SUPER and TRIGGER privileges, you need the TRIGGER privilege to create triggers on tables you have rights for.

If you play binlogs, there is the possibility of seeing commands to create triggers on tables you do not have rights for. This is implied from the MySQL Documentation:

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”.

With regard to your second point

trusted not to create stored functions that will cause unsafe events to be written to the binary log.

There are occasions when certain SELECTs on a Master may yield different results on a Slave. For example, let's say you ran this query on a a MASTER

SELECT * FROM tbl WHERE name='...' LIMIT 10;

How could a SLAVE produce different results?

If you have a several hundred INSERTs commands occur on a Master, they get serialized in the Binary Logs. Even if a DB Connection 1 completes an INSERT before DB Connection 2, it is possible for DB Connection 2 to write its event in the Master's Binary Logs before DB Connection 1. This is particularly true in a write-intensive, heavy OLAP environment. This would make a Slave's copy of tbl physically out-of-sync with its Master. Running queries can hide this.

OK, you are probably thinking it's just a SELECT.

SELECTs are not in Binary Logs. INSERTs, DELETEs, and UPDATEs are.

Did you know you could do a query like this ?

DELETE FROM tbl LIMIT 10;

Replicating this can really make tbl out of sync.

Stored Functions can also have this headache if it the code within it is not DETERMINISTIC. This is why log_bin_trust_function_creators was invented. This alleviates mysqld of the responsibility of figuring out if data operations are synchronized in terms of replication. Yes, they fudged their way around downstream replication issues.

Related Question