mysql 存储过程权限问题
协助开发导入库时，他提醒我，库里有存储过程的定义。我使用了--routines 的选项，可是导入时，出了问题：1.提示无法创建该存储过程，原来是忘了打开 log_bin_trust_function_creators 变量，
set global log_bin_trust_function_creators=on; 后，成功创建，从服务器也需要此操作
才行，如果二进制日志是 ROW 的话，即使从上有此存储过程，也是不会调用的。
2.等到导入成功后，却又出现了无法调用的问题，搜索了 http://www.iyunv.com/database/201403/288436.html 这篇文章，记录下此问题。
[*] 创建一个 definer 为 user_admin 的存储过程
mysql> DELIMITER //
mysql> CREATE DEFINER='user_admin' PROCEDURE num1()
-> BEGIN
-> SELECT COUNT(id) FROM itopserver;
-> END //
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER ;
2. 创建一个用户，并赋于其 相关权限
mysql> create user user_itop identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,insert,delete,update on itop.* to 'user_test'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user_test@'%';
+---------------------------------------------------------------------+
| Grants for user_test@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_test'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `itop`.* TO 'user_test'@'%' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
3. 使用 user_test 连接 mysql 并执行刚才创建的 procedure
mysql> call num1;
ERROR 1370 (42000): execute command denied to user 'user_test'@'%' for routine 'itop.num1'
4. 这样看是权限的问题，因为只给了他操作表的方法。 增加其 itop 的 execute 权限
mysql> grant execute on itop.* to user_test@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user_test@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for user_test@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_test'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `itop`.* TO 'user_test'@'%' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5. 重新使用此用户连接 Mysql 执行刚才定义的存储过程
mysql> use itop;
mysql> call num1();
ERROR 1449 (HY000): The user specified as a definer ('user_admin'@'%') does not exist
现在可以调用该存储过程了，但是提示存储过程定义中的definer不存在，
原来仅仅是连接到MySQL服务器的用户具有执行存储过程的权限是远远不够的，
最终要通过存储过程定义中指定的definer来执行存储过程。
6. 说 user_admin 不存在，那就创建 user_admin 这个用户
mysql> grant select,insert,delete,update on itop.* to 'user_admin'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user_admin@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for user_admin@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `itop`.* TO 'user_admin'@'%' |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
7. 重新使用 "user_test" 用户连接 Mysql 执行刚才定义的存储过程
mysql> use itop;
mysql> call num1();
ERROR 1370 (42000): execute command denied to user 'user_admin'@'%' for routine 'itop.num1'
8. 看来不仅仅是连接到MySQL服务器的用户需要具有存储过程上的执行权限，存储过程定义者同样需要该权限。
mysql> grant execute on itop.* to 'user_admin'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user_admin;
+-----------------------------------------------------------------------------------------------------------+
| Grants for user_admin@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `itop`.* TO 'user_admin'@'%' |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
9. 重新使用 "user_test" 用户连接 Mysql 执行刚才定义的存储过程
mysql> use itop;
mysql> call num1();
+-----------+
| COUNT(id) |
+-----------+
| 372 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
可以执行了
页:
[1]