MySQL 存储过程权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10089308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Stored Procedure Permissions
提问by Olly
I am trying to give a user permission to run a stored procedure at the stored procedure level on a MySQL Database rather than allowing a user to execute any stored procedure in the database. I was trying to execute the following code:
我试图授予用户在 MySQL 数据库上的存储过程级别运行存储过程的权限，而不是允许用户执行数据库中的任何存储过程。我试图执行以下代码：
GRANT EXECUTE ON myDB.spName TO 'TestUser'@'localhost';
But i keep getting the following error: Illegal GRANT/REVOKE command, please consult the manual to see which privileges can be used.
但我不断收到以下错误： Illegal GRANT/REVOKE command, please consult the manual to see which privileges can be used.
I tried changing it to the following:
我尝试将其更改为以下内容：
GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost';
And i get a different error stating:Cant find any matching rows in the user table.
我得到一个不同的错误说明：Cant find any matching rows in the user table.
I am confused as to where I am going wrong?
我很困惑我哪里出错了？
Also on the MySQL Workbench I can not seem to see any way to grant permissions at the stored procedure level via the GUI. Is this correct or am I missing something?
同样在 MySQL Workbench 上，我似乎看不到任何通过 GUI 在存储过程级别授予权限的方法。这是正确的还是我错过了什么？
Thanks in advance.
提前致谢。
回答by sakhunzai
Your second attempt is the right approach:
您的第二次尝试是正确的方法：
GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost';
but if that is not working, verify ...
但如果这不起作用，请验证...
a) you (the user from which you are running all these command) have grant rights [i.e WITH GRANT OPTION]. If you are root, then you have grant rights.
a) 您（您从中运行所有这些命令的用户）拥有授予权限 [即 WITH GRANT OPTION]。如果您是 root，那么您就拥有授予权限。
b) the user exists to which you are granting execute permission e.g.
b) 存在您授予执行权限的用户，例如
select user from mysql.user where user like 'test%';
If not, then create the user e.g.
如果没有，则创建用户，例如
CREATE USER 'TestUser'@'localhost' IDENTIFIED BY 'passwordxxxx';
#depending on your needs
GRANT SELECT,DELETE,UPDATE PRIVILEGES ON myDb.* TO 'TestUser'@'localhost';
Hope this helps :)
希望这可以帮助 ：）
回答by Shane E. Bryan
To answer the other part of your question regarding MySQL Workbench, I was having the same issue. But after experimenting I discovered that if you create a role and open the privileges tab at the bottom you can then drag the routine from the Model Overview into the objects box. From there just click on the newly added object and add the permissions you want for that role.
要回答有关 MySQL Workbench 的问题的另一部分，我遇到了同样的问题。但经过试验后我发现，如果您创建一个角色并打开底部的权限选项卡，您就可以将例程从模型概览拖到对象框中。从那里只需单击新添加的对象并为该角色添加所需的权限。
Hope that helps :)
希望有帮助:)
