mysql.db 表上 Execute_priv 的含义

发布于 2024-08-02 09:00:56 字数 821 浏览 4 评论 0原文

我在“锁定”的 mysql 服务器上创建了用户“restricteduser”。 mysql.user 表中的 N 代表该帐户的所有权限。 mysql.db表有Y仅代表Select、Insert、Update、Delete、Create、Drop；该帐户的所有其他权限均为 N。我尝试创建一个存储过程，然后授予他仅运行该过程的权限，而不运行其他过程，但它不起作用。

用户收到：错误：对于例程“mydb.functionname”，执行命令被拒绝给用户“restricteduser”@“%”

存储过程：

CREATE DEFINER = 'restriceduser'@'%' FUNCTION `functionname`(sIn MEDIUMTEXT, sformat MEDIUMTEXT)
RETURNS int(11)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
....
END;

我尝试过的授予语句：

GRANT EXECUTE ON PROCEDURE mydb.functionname TO 'restricteduser'@'%';

我能够通过修改他的 mysql.db 条目来解决此

update mysql.db set execute_priv='Y' where user='restricteduser'

问题似乎比我想要的更多，因为它为他开放了运行该数据库中任何存储过程的权限，而我只希望他有运行指定函数的权限。

有人看到我的问题可能出在哪里吗？

原文

I created user 'restriceduser' on my mysql server that is 'locked down'. The mysql.user table has a N for all priveledges for that account. The mysql.db table has Y for only Select, Insert, Update, Delete, Create, Drop; all other privileges are N for that account. I tried to create a stored procedure and then grant him access to run only that procedure, no others, but it does not work.

The user receives: Error: execute command denied to user 'restricteduser'@'%' for routine 'mydb.functionname'

The stored procedure:

CREATE DEFINER = 'restriceduser'@'%' FUNCTION `functionname`(sIn MEDIUMTEXT, sformat MEDIUMTEXT)
RETURNS int(11)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
....
END;

The grant statement I tried:

GRANT EXECUTE ON PROCEDURE mydb.functionname TO 'restricteduser'@'%';

I was able to work around by modifying his mysql.db entry with

update mysql.db set execute_priv='Y' where user='restricteduser'

This seems to be more then I want, because it opens up permissions for him to run any stored procedure in that database, while I only wanted him to have permissions to run the designated function.

Does anyone see where my issue may lie?

分享到QQ

分享到微博