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

[2 Aug 2006 21: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 1:35] Miguel Solorzano 
Thank you for the bug report.
[29 Aug 2007 22:50] Konstantin Osipov 
Fixed by the patch for Bug#21975 Grant and revoke statements are non-transactional
[7 Sep 2007 10:08] Bugs System 
Pushed into 5.1.23-beta
[9 Oct 2007 19: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.