Bug #21854 Problems with CREATE TRIGGER without DEFINER clause in --skip-grant-tables mode
Submitted: 27 Aug 2006 22:14 Modified: 26 Mar 2008 17:28
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.25-bk, 5.1.23 OS:Any (any)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[27 Aug 2006 22:14] Dmitry Lenev 
Description:
Trigger which is created when server was run with --skip-grant-tables option (assuming that DEFINER clause was not specified explicitly), prevents coresponding actions on the table when server is run in normal mode.
Error message which is emitted is not too helpful.

The same problem should exist for other SUID objects (e.g. views).

How to repeat:
# Start server with --skip-grant-tables option

drop table if exists t1;
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row set @a:= new.i;

# Restart server in normal mode

# This causes error with message which tells to little about problem
insert into t1 values (1);
# ERROR 1449 (HY000): There is no ''@'' registered

Suggested fix:
1) May be improve error message ?
2) And/or prohibit creation of suid objects without explicitly specified definer if server runs in --skip-grant-tables mode (altough see bug #16777).
[28 Aug 2006 11:12] Sveta Smirnova 
Thank you for the report.

Verified as described on Linux using last 5.0-BK sources.
[8 Mar 2008 18:29] Valeriy Kravchuk 
Bug #35162 was marked as a duplicate of this one.
[21 Mar 2008 15:04] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44333

ChangeSet@1.2568, 2008-03-21 18:05:14+03:00, anozdrin@quad.opbmk +5 -0
  A patch for Bug#21854: Problems with CREATE TRIGGER without
  DEFINER clause in --skip-grant-tables mode.
  
  Update error message.
[21 Mar 2008 15:33] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44335

ChangeSet@1.2568, 2008-03-21 18:34:12+03:00, anozdrin@quad.opbmk +5 -0
  A patch for Bug#21854: Problems with CREATE TRIGGER without
  DEFINER clause in --skip-grant-tables mode.
  
  Update error message.
[22 Mar 2008 8:00] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44343

ChangeSet@1.2569, 2008-03-22 11:01:31+03:00, anozdrin@quad.opbmk +4 -0
  Fix result files (Bug#21854).
[22 Mar 2008 8:08] Alexander Nozdrin 
Pushed into 5.1-runtime.
[26 Mar 2008 14:13] Bugs System 
Pushed into 5.1.24-rc
[26 Mar 2008 17:28] Paul DuBois 
Noted in 5.1.24 changelog.

For views or stored programs created with an invalid DEFINER value,
the error message was confusing (did not tie the problem to the
DEFINER clause) and has been improved.
[27 Mar 2008 17:50] Bugs System 
Pushed into 6.0.5-alpha
[29 Mar 2008 23:56] Jon Stephens 
Also documented for 5.1.23-ndb-6.3.11 and 6.0.5.