Mysql 下的Function权限问题,执行了下面的语句后,赋权给squery后,怎么function不执行啊?用别的用户可以执行的。是否权限设置有问题?另外,function所操作的表给予了select update delete权限了。
grant execute on function chrm_db.func_training_update to 'squery'@'%';
附:Function如下:
CREATE FUNCTION `Func_Employee_Update`(P_EMPLOYEE_ID VARCHAR(20), P_ENGLISH_NAME VARCHAR(20), P_CELL_PHONE VARCHAR(30), P_MESSAGE_ADDRESS VARCHAR(80), P_EMERGENCY_PHONE VARCHAR(30), P_HOME_PHONE VARCHAR(30), P_SPECIALTY VARCHAR(200))
RETURNS int(11)
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Update chrm_employee
set
ENGLISH_NAME = P_ENGLISH_NAME,
CELL_PHONE=P_CELL_PHONE,
MESSAGE_ADDRESS=P_MESSAGE_ADDRESS,
EMERGENCY_PHONE=P_EMERGENCY_PHONE,
HOME_PHONE=P_HOME_PHONE,
SPECIALTY=P_SPECIALTY
Where employee_id=P_EMPLOYEE_ID ;
return 1;
END;
grant execute on function chrm_db.func_training_update to 'squery'@'%';
附:Function如下:
CREATE FUNCTION `Func_Employee_Update`(P_EMPLOYEE_ID VARCHAR(20), P_ENGLISH_NAME VARCHAR(20), P_CELL_PHONE VARCHAR(30), P_MESSAGE_ADDRESS VARCHAR(80), P_EMERGENCY_PHONE VARCHAR(30), P_HOME_PHONE VARCHAR(30), P_SPECIALTY VARCHAR(200))
RETURNS int(11)
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Update chrm_employee
set
ENGLISH_NAME = P_ENGLISH_NAME,
CELL_PHONE=P_CELL_PHONE,
MESSAGE_ADDRESS=P_MESSAGE_ADDRESS,
EMERGENCY_PHONE=P_EMERGENCY_PHONE,
HOME_PHONE=P_HOME_PHONE,
SPECIALTY=P_SPECIALTY
Where employee_id=P_EMPLOYEE_ID ;
return 1;
END;
直接在你的SELECT语句中执行就行了。 可以参考一下MySQL的帮助文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
SELECT Func_Employee_Update(....);
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)