Bug #21422 GRANT/REVOKE possible inside stored function, probably in a trigger
Submitted: 2 Aug 2006 19:35 Modified: 9 Oct 2007 17:58
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1/5.0 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[2 Aug 2006 19:35] Andrey Hristov 
Description:
See how to repeat

How to repeat:
create procedure proc_1() grant all on *.* to abc@host;
drop procedure proc_1;
delimiter |;
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
create function func_1() returns int begin grant all on *.* to abc@host; return 1; end|
delimiter ;|
prepare abc from "grant all on *.* to abc@host";

create procedure proc_1() revoke all on *.* from abc@host;
drop procedure proc_1;
delimiter |;
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
create function func_1() returns int begin revoke all on *.* from abc@host; return 1; end|
delimiter ;|
prepare abc from "revoke all on *.* from abc@host";
[6 Jan 2007 0:35] MySQL Verification Team 
Thank you for the bug report.
[29 Aug 2007 20:50] Konstantin Osipov 
Fixed by the patch for Bug#21975 Grant and revoke statements are non-transactional
[7 Sep 2007 8:08] Bugs System 
Pushed into 5.1.23-beta
[9 Oct 2007 17:58] Paul DuBois 
Noted in 5.1.23 changelog.

Incompatible change: GRANT and REVOKE statements now cause an
implicit commit, and thus are prohibited within stored functions and
triggers.
[25 Jun 2009 13:10] 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/77196

2810 Alexander Nozdrin	2009-06-25
      Fix for Bug#45716: enable a test case for Bug#21422.
[3 Jul 2009 6:13] Bugs System 
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:holyfoot@mysql.com-20090626091141-s0jhc72bbgyqudc3) (merge vers: 5.4.4-alpha) (pib:11)
[9 Jul 2009 7:34] Bugs System 
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:holyfoot@mysql.com-20090626091141-s0jhc72bbgyqudc3) (merge vers: 5.4.4-alpha) (pib:11)