Hi Stephane,

Why should it not have failed?
It sounds like a good outcome, because, see MySQL documentation:
http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html

Although it is possible to create a trigger with a nonexistent DEFINER account, it is not a good idea for such triggers to be activated until the account actually does exist. Otherwise, the behavior with respect to privilege checking is undefined.

It's much better that the statement fails when privileges cannot be checked properly, rather than they go random.

Me was thinking triggers are part of the table definition and grants should be checked according to the session user not the definer, execution of trigger should not follow DEFINER but CALLER. Or did i miss something important in that view?

I think MySQL outcome is confusing the ability to remove a trigger that should indeed be checking for the DEFINER user or any super user .

A trigger is a type of a stored procedure, so a lot of SP logic applies to them.

http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time.

MySQL takes the DEFINER user into account when checking trigger privileges as follows:

At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege.

At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:

The TRIGGER privilege for the subject table.

The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger body.

The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger body.

Whatever other privileges normally are required for the statements executed by the trigger.

There is also http://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html

i'm not the only one pointing that :
http://bugs.mysql.com/bug.php?id=34787

In life DBA live the company user are being dropped and the database stop inserting .

There's nothing we can do here. We can imitate "DROP USER ... CASCADE" behavior and auto-drop a trigger that has non-existent definer. Two problems with that

• doesn't work in read_only mode, any select can change table's metadata (affects locking)
• we don't do "DROP USER ... CASCADE" for any other objects, e.g. not for stored routines.