我正在使用
MySQL存储函数并且遇到麻烦.
创建了一个函数并对其进行了测试后,我似乎无法允许其他用户执行它.从文档中,我似乎需要向其他用户授予EXECUTE访问权限,但这似乎不够.
我已经把几个脚本放在一起,我相信它会证明这个问题:
# This script creates two databases with a stored function in each.
#
# On one database, tester in granted all privileges.
# On the other, tester only gets a few.
#
# We want to find the minimum privileges required to allow tester to execute the
# stored function.
#
# This script must be run by an administrative user, i.e. root
CREATE DATABASE test1;
DELIMITER $$
CREATE FUNCTION test1.foo () RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
RETURN ('garp');
END$$
DELIMITER ;
GRANT ALL PRIVILEGES ON test1.* TO 'tester'@'localhost';
#
CREATE DATABASE test2;
DELIMITER $$
CREATE FUNCTION test2.foo () RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
RETURN ('garp');
END$$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE test2.foo TO 'tester'@'localhost';
和
# This script tests whether tester can access the stored functions
#
# It should be executed by tester
SELECT 'test1.foo(): ', test1.foo ();
SELECT 'test2.foo(): ', test2.foo ();
当我运行执行第二个脚本时,我收到一个错误:
$mysql --user=tester --password=tester --skip-column-names < testScript2.sql
test1.foo(): garp
ERROR 1370 (42000) at line 6: execute command denied to user 'tester'@'localhost' for routine 'test2.foo'
我毫不怀疑我错过了一些明显的东西,但我看不出那是什么.我想我在第一个脚本中的GRANT EXECUTE …语句中出了问题,并且对我使用单引号深感怀疑,但我记得尝试大多数放置和包含单引号的组合而没有成功.
我真的很感激那些可以指出我的错误的人.
作为参考,我正在运行服务器版本:5.1.67-0ubuntu0.10.04.1(Ubuntu)(在Ubuntu上!).
谢谢