----------------------------------------------------------------------+
| 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'@'%' |
+----------------------------------------------------------------------------------------------------------------------------- |