Triggers in MySQL: a cautionary tale

I had the pleasure of handling a recent customer support request asking that they be able to install some triggers into their MySQL database one of our shared web hosting servers. This should be no trouble, you'd think. You just GRANT them the CREATE TRIGGER privilege and be done with it. Or so I thought. </didactic narration>

Triggers are a relatively recent addition to MySQL, having appeared in version 5.0. Postgres has had them since 1997 and they're consequently more mature. Yet another great reason to choose Postgres.

As a disclaimer, this applies to Redhat Enterprise Linux 5.2 with packaged MySQL 5.0.45, which is what we run on our newest shared web hosting server.

Unlike in Postgres, defining triggers in MySQL isn't handled sanely.

  • Defining the triggers
    • Prior to 5.1.6, there's no ability to GRANT CREATE TRIGGER
    • Because of this, the definer has to have the SUPER privilege, ie. probably root

    • Pray that your users don't create/delete triggers on a regular basis
  • Running triggers
    • Prior to 5.0.17, the trigger runs as the user that fires it
    • This is almost acceptable, though potentially limiting if you want to use triggers in a sudo-ish fashion
    • As of 5.0.17, it runs as the defined DEFINER (specified when the trigger is created).
    • This almost sounds okay, except the DEFINER has to have the SUPER privilege, ie. probably root

    • I'm pretty sure this means every trigger must run as root

I'm not even sure that this is "fixed" in 5.1.6, as the documentation seems to indicate that even though you can now GRANT privileges to define triggers with the owner as the DEFINER (ie. non root), the DEFINER probably still needs to have SUPER.

References