So I've been corrected on my misunderstanding of this section of the MySQL docs:
At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:
- The TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)
- ...
So in MySQL 5, prior to 5.1.6, the you do not have to be SUPER to execute SQL which executes triggers, the DEFINER must have the SUPER privilege. While this is certainly better than I thought, we have several production systems which run on the same server and is hosted by another company. I cannot imagine why this other company would allow us to run code (the triggers) as SUPER and I'm sure that other teams wouldn't be too keen on me testing this:
CREATE TRIGGER tr_brand_insert AFTER INSERT ON brand
FOR REACH ROW BEGIN
DROP DATABASE IF EXIST other_teams_database;
END;
No idea if that would work, but given that we triggers are run with SUPER privileges, wouldn't it be a glorious way to say "I quit"? (And flee back to the US while I'm at it)
I'm still unsure why MySQL thought this was a good idea, but as has been pointed out to me, it can still be safe as long as the DBA or person responsible for adding your triggers reviews them first. Needless to say, I don't find that terribly reassuring.
And to my BBC colleagues reading this: it was great crack I was smoking. Talk to Phil. He'll hook you up :)