Bug #34679 Grant Privileges in Stored Procedures
Submitted: 19 Feb 2008 22:42 Modified: 20 Mar 2008 13:09
Reporter: Camilo Torregroza Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.45-community-nt OS:Any
Assigned to: CPU Architecture:Any
Tags: privileges, stored procedures

[19 Feb 2008 22:42] Camilo Torregroza
Description:
Hi 
I wirte this stored procedure, the goal is to grant privileges in some stored procedures the script of the procedure is at the end;

But when I try pass the login to a stored procedure and execute the procedure with a Login although the user is in the database i got the error:
Can't find any matching row in the user table Error 1133
there isn't any way to pass the user as a variable??
I was reading the doc and I query SELECT * FROM mysql.user p; and I find the user 
another issue is if the login has 2 or more host associated the script throw the  error Result consisted of more than one row Error 1172 so it's strange that in the first case doesn't find anything so could you please tell me if this is a bug 
DELIMITER $$

DROP PROCEDURE IF EXISTS `tesgestiontigo_dbo`.`OtorgarPermisosAdministrador2`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `tesgestiontigo_dbo`.`OtorgarPermisosAdministrador2`
(
  LogOn varchar(30) ,
  agregar tinyint(1)
)
BEGIN
DECLARE Usuario char(16);
SELECT `User` INTO Usuario from MySQL.user Where User = LogOn;

IF (Agregar = 1)
THEN
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllDependencias` TO Usuario;
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllEnlacesMicroondas` TO Usuario;
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllEstaciones` TO Usuario;
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`GetUsuarioByLogin` TO Usuario;

ELSE

REVOKE EXECUTE ON PROCEDURE formas_GetAllDependencias FROM Usuario;
REVOKE EXECUTE ON PROCEDURE formas_GetAllEnlacesMicroondas FROM Usuario;
REVOKE EXECUTE ON PROCEDURE formas_GetAllEstaciones FROM Usuario;
REVOKE EXECUTE ON PROCEDURE GetUsuarioByLogin FROM Usuario;*/

END IF;

END $$

DELIMITER ;

How to repeat:
Create this Script

DELIMITER $$

DROP PROCEDURE IF EXISTS `tesgestiontigo_dbo`.`OtorgarPermisosAdministrador2`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `tesgestiontigo_dbo`.`OtorgarPermisosAdministrador2`
(
  LogOn varchar(30) ,
  agregar tinyint(1)
)
BEGIN
DECLARE Usuario char(16);
SELECT `User` INTO Usuario from MySQL.user Where User = LogOn;

IF (Agregar = 1)
THEN
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllDependencias` TO Usuario;
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllEnlacesMicroondas` TO Usuario;
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllEstaciones` TO Usuario;
GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`GetUsuarioByLogin` TO Usuario;

ELSE

REVOKE EXECUTE ON PROCEDURE formas_GetAllDependencias FROM Usuario;
REVOKE EXECUTE ON PROCEDURE formas_GetAllEnlacesMicroondas FROM Usuario;
REVOKE EXECUTE ON PROCEDURE formas_GetAllEstaciones FROM Usuario;
REVOKE EXECUTE ON PROCEDURE GetUsuarioByLogin FROM Usuario;*/

END IF;

END $$

DELIMITER ;
now create one user with host => any host it means % 
execute the script with the login as the parameter 

Suggested fix:
N/A
[20 Feb 2008 9:21] todiff wu
Hello Camilo,

Please clarify your login and execution steps a little more and would better upload result of your SELECT * FROM mysql.user before running the procedure as well.
Normally Statements not allowed in SQL prepared statements are generally also not permitted in stored routines, GRANT is one of them, can’t pass parameters to such statement. 
http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

So your goal can be matched by replace the kindred statements 
” GRANT EXECUTE ON PROCEDURE `tesgestiontigo_dbo`.`formas_GetAllDependencias` TO Usuario;”  
with such a statement 
“insert into mysql.procs_priv values ('localhost',' tesgestiontigo_dbo ',Usuario,' formas_GetAllDependencias ','PROCEDURE','root@localhost','Execute',now());”
Then the value of parameter Usuario can be passed to the statement and grants proper privilege to users you want.
[20 Feb 2008 13:09] MySQL Verification Team
Setting to feedback according last request.
[21 Mar 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".