MySQL Bugs: #58476: A trigger with a non-existent definer can be created but not executed

Bug #58476	A trigger with a non-existent definer can be created but not executed
Submitted:	24 Nov 2010 23:13	Modified:	25 Nov 2010 0:47
Reporter:	Sasha Pachev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.50	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	triggers

Description:
A trigger with a non-existent definer can be created with no error. A query that triggers its execution results in an error thoroughly confusing a DBA.

How to repeat:
USE test;
DROP TRIGGER IF EXISTS test_trigger;
DROP TABLE IF EXISTS test1,test2;
CREATE TABLE test1(n int);
CREATE TABLE test2(n int);

delimiter |

CREATE   DEFINER=foo@bar TRIGGER test_trigger AFTER INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 VALUES(NEW.n) ;
  END;
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='test_trigger';
INSERT INTO test1 VALUES(1);

Suggested fix:
For the DBA:

SELECT a.* FROM (SELECT definer,trigger_name,trigger_schema,event_object_table FROM INFORMATION_SCHEMA.TRIGGERS) a LEFT JOIN mysql.user ON definer = CONCAT(user,'@',host) WHERE user IS NULL;

this will list all triggers that will execute with an error. Fix them either by adding their users, or by dropping and re-creating them.

From the shell using the replace utility that comes with MySQL distribution:

replace bad_user good_user -- /path/to/datadir/*/*.TRG

followed by MySQL FLUSH PRIVILEGES

should do the trick as well. Any danger in that aside from this not being applicable when the internal trigger format changes?

For the developers of MySQL - check if the user exists before proceeding with trigger creation.

Thank you for the bug report.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.54-Win X64-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >USE test;
Database changed
mysql 5.1 >DROP TRIGGER IF EXISTS test_trigger;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >DROP TABLE IF EXISTS test1,test2;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >CREATE TABLE test1(n int);
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 >CREATE TABLE test2(n int);
Query OK, 0 rows affected (0.05 sec)

mysql 5.1 >
mysql 5.1 >delimiter |
mysql 5.1 >
mysql 5.1 >CREATE   DEFINER=foo@bar TRIGGER test_trigger AFTER INSERT ON test1
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO test2 VALUES(NEW.n) ;
    ->   END;
    -> |
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql 5.1 >SHOW WARNINGS|
+-------+------+--------------------------------------------------------------+
| Level | Code | Message                                                      |
+-------+------+--------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('foo'@'bar') does not exist |
+-------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.1 >DELIMITER ;
mysql 5.1 >SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='test_trigger'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: NULL
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: test_trigger
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: NULL
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: test1
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
    INSERT INTO test2 VALUES(NEW.n) ;
  END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE:
                   DEFINER: foo@bar
      CHARACTER_SET_CLIENT: latin1
      COLLATION_CONNECTION: latin1_swedish_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.13 sec)

mysql 5.1 >INSERT INTO test1 VALUES(1);
ERROR 1449 (HY000): The user specified as a definer ('foo'@'bar') does not exist
mysql 5.1 >

This is not a bug, it work as documented in the manual: "If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated."

Furthermore, even if a user exists at the time when the trigger is created, it does not imply it will exist at the time of its execution. Hence, if the user is dropped later, the "DBA" will see the same error.