MySQL存储过程权限 [英] MySQL Stored Procedure Permissions

问题描述

我试图授予用户权限在MySQL数据库上的存储过程级别运行存储过程，而不是允许用户执行数据库中的任何存储过程.我正在尝试执行以下代码:

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:

GRANT EXECUTE ON myDB.spName TO 'TestUser'@'localhost';

但是我一直收到以下错误:

Illegal GRANT/REVOKE command, please consult the manual to see which privileges can be used.

But i keep getting the following error:

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';

我得到一个不同的错误说明:

Cant find any matching rows in the user table.

And i get a different error stating:

Cant find any matching rows in the user table.

我对我要去哪里错感到困惑?

I am confused as to where I am going wrong?

在MySQL Workbench上，我似乎也看不到通过GUI在存储过程级别授予权限的任何方法.这是正确的还是我错过了什么?

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?

谢谢.

推荐答案

您的第二次尝试是正确的方法:

Your second attempt is the right approach:

GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost';

但是如果不起作用，请验证...

but if that is not working, verify ...

a)您(您正在从中运行所有这些命令的用户)具有授予权限 [i.e with GRANT OPTION].如果您是root用户，则您具有授予权限.

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.

b)您要授予执行权限的用户，例如

b) the user exists to which you are granting execute permission e.g.

 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'; 

希望这会有所帮助:)

这篇关于MySQL存储过程权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！