MySQL用户执行存储过程的权限

MySQL中以用户执行存储过程的权限为EXECUTE 比如我们在名为configdb的数据库下创建了如下存储过程,存储过程的定义者为user_admin use configdb; drop procedure if exists sp_dev_test_user_add; delimiter $$ CREATE DEFINER=`user_admin`@`%` PROCEDURE `s

 　　MySQL中以用户执行存储过程的权限为EXECUTE

　　比如我们在名为configdb的数据库下创建了如下存储过程,存储过程的定义者为user_admin

　　use configdb;

　　drop procedure if exists sp_dev_test_user_add;

　　delimiter $$

　　CREATE DEFINER=`user_admin`@`%` PROCEDURE `sp_dev_test_user_add`(

　　in var_user varchar(30),

　　in var_ip varchar(15),

　　in var_username varchar(30),

　　in var_email varchar(30),

　　in var_orginfo varchar(30)

　　)

　　BEGIN

　　create temporary table errors (error varchar(500));

　　if exists ( select user from mysql.user where user=var_user) then

　　insert into errors values (concat('用户名 "',var_user,'" 已存在!'));

　　end if;

　　if exists (select * from errors) then

　　select error from errors;

　　else

　　set @user=concat(var_user,'@'',var_ip,''');

　　set @s=concat('create user ',@user,' identified by ''12345'';');

　　prepare cmd from @s;

　　execute cmd;

　　set @s=concat('GRANT SELECT ON `mysql`.`func` TO ',@user,';');

　　prepare cmd from @s;

　　execute cmd;

　　set @s=concat('GRANT SELECT ON `mysql`.`proc` TO ',@user,';');

　　prepare cmd from @s;

　　execute cmd;

　　replace into dev_test_userinfo values (var_user,var_username,var_email,var_orginfo);

　　end if;

　　drop temporary table errors;

　　END

　　$$

　　delimiter ;

　　试着创建一个普通用户user_test1

　　mysql>create user user_test1 identified by '12345';

　　查看其权限

　　mysql>show grants for user_test1;

　　+-----------------------------------------------------------------------------------------------------------+

　　| Grants for user_test1@% |

　　+-----------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |

　　+-----------------------------------------------------------------------------------------------------------+

　　赋予其configdb上的selectinsertdeleteupdate权限

　　mysql>grant select,insert,delete,update on configdb.* to 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9'

　　mysql> show grants for user_test1;

　　+-----------------------------------------------------------------------------------------------------------+

　　| Grants for user_test1@% |

　　+-----------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE ON `configdb`.* TO 'user_test1'@'%' |

　　+-----------------------------------------------------------------------------------------------------------+

　　使用此用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql>call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　ERROR 1370 (42000): execute command denied to user 'user_test1'@'%' for routine 'configdb.sp_dev_test_user_add'

　　看来是权限不足，继续赋予其configdb上的execute权限

　　mysql> grant execute on configdb.* to 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

　　mysql> show grants for user_test1;

　　+-----------------------------------------------------------------------------------------------------------+

　　| Grants for user_test1@% |

　　+-----------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `configdb`.* TO 'user_test1'@'%' |

　　+-----------------------------------------------------------------------------------------------------------+

　　重新使用此用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql>call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　ERROR 1449 (HY000): The user specified as a definer ('user_admin'@'%') does not exist

　　这次可以调用该存储过程了，但是提示存储过程定义中的definer不存在，原来仅仅是连接到MySQL服务器的用户具有执行存储过程的权限是远远不够的，最终要通过存储过程定义中指定的definer来执行存储过程。

　　创建user_admin'@'%'这个用户，并赋予configdb上相应的权限

　　mysql>create user user_admin identified by '12345';

　　mysql> grant select,insert,delete,update on configdb.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

　　mysql> show grants for user_admin;

　　+-----------------------------------------------------------------------------------------------------------+

　　| Grants for user_admin@% |

　　+-----------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE ON `configdb`.* TO 'user_admin'@'%' |

　　+-----------------------------------------------------------------------------------------------------------+

　　重新使用'user_test1'@'%'用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　ERROR 1370 (42000): execute command denied to user 'user_admin'@'%' for routine 'configdb.sp_dev_test_user_add'

　　看来不仅仅是连接到MySQL服务器的用户需要具有存储过程上的执行权限，存储过程定义者同样需要该权限。

　　mysql> grant execute on configdb.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

　　mysql> show grants for user_admin;

　　+-----------------------------------------------------------------------------------------------------------+

　　| Grants for user_admin@% |

　　+-----------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `configdb`.* TO 'user_admin'@'%' |

　　+-----------------------------------------------------------------------------------------------------------+

　　重新使用'user_test1'@'%'用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　ERROR 1044 (42000): Access denied for user 'user_admin'@'%' to database 'configdb'

　　可以执行存储过程了，但是提示权限不足，仔细查看存储过程的定义可以看到，存储过程中包含创建用户和赋予权限的语句，而我们赋给'user_test1'@'%'用户和'user_admin'@'%'都不具有这样的权限。

　　赋予'user_test1'@'%'创建用户的权限和赋权的权限，以及创建临时表的权限

　　mysql> grant create user on *.* to 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' with grant option;

　　mysql> grant create temporary tables on configdb.* to 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

　　mysql> show grants for 'user_test1'@'%';

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　| Grants for user_test1@% |

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　| GRANT CREATE USER ON *.* TO 'user_test1'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON `configdb`.* TO 'user_test1'@'%' |

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　重新使用'user_test1'@'%'用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　ERROR 1044 (42000): Access denied for user 'user_admin'@'%' to database 'configdb'

　　对了，不管你是以什么账户登录的MySQL，最后是使用存储过程的definer执行存储过程的，所以应当把创建用户和赋权的权限付给definer，这里为user_admin'@'%'这个账户。

　　赋予'user_admin'@'%'创建用户的权限和赋权的权限

　　mysql> grant create user on *.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' with grant option;

　　mysql> grant create temporary tables on configdb.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

　　mysql> show grants for 'user_admin'@'%';

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　| Grants for user_admin@% |

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　| GRANT CREATE USER ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON `configdb`.* TO 'user_admin'@'%' |

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　重新使用'user_test1'@'%'用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　ERROR 1142 (42000): SELECT command denied to user 'user_admin'@'%' for table 'user'

　　哦，除了configdb库外还得有mysql库上user表的权限，给加上，看来权限问题还真是棘手，呵呵~

　　mysql> grant select,insert,delete,update on mysql.* to 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

　　mysql> show grants for 'user_admin'@'%';

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　| Grants for user_admin@% |

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　| GRANT CREATE USER ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, EXECUTE ON `configdb`.* TO 'user_admin'@'%' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'user_admin'@'%' |

　　+-----------------------------------------------------------------------------------------------------------------------------------+

　　重新使用'user_test1'@'%'用户登录MySQL执行刚才定义的存储过程

　　mysql>use configdb;

　　mysql> call sp_dev_test_user_add('uapp_yzz','172.16.%','yzz','yzz@email','MySQL DBA');

　　Query OK, 0 rows affected (0.05 sec)