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: | |
Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
Version: | 5.1.50 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | triggers |
[24 Nov 2010 23:13]
Sasha Pachev
[24 Nov 2010 23:38]
MySQL Verification Team
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 >
[25 Nov 2010 0:47]
Davi Arnaut
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.