MySQL 创建存储过程时是否需要`definer`？

Is `definer` required when creating a stored procedure?

提问by Xorty

I've written all of MySQL procedures as root@localhost:

我已将所有 MySQL 程序编写为root@localhost：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_add_user`(...)

Trouble is, when deploying to another server, I have to replace rootwith current user and replace localhostwith current IP, which is annoying.

麻烦的是，在部署到另一台服务器时，我必须替换root为当前用户并替换localhost为当前IP，这很烦人。

Is there any way to write procedures so that someone who wants to use my database and procedures would not have to modify the definer of each procedure?

有什么方法可以编写程序，以便想要使用我的数据库和程序的人不必修改每个程序的定义者？

采纳答案by maid450

As stated in MySQL documentation here

在MySQL文档中所述这里

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

So, the DEFINER part is not mandatory, just CREATE PROCEDURE should work.

所以，DEFINER 部分不是强制性的，只是 CREATE PROCEDURE 应该可以工作。

回答by MonkeyFeeder

You can specify execution privileges by adding the following statement in the procedure body (after declaration):

您可以通过在过程体中（声明后）添加以下语句来指定执行权限：

SQL SECURITY INVOKER

Example:

例子：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_add_user`()
SQL SECURITY INVOKER
(...)

Doing so, the actual invoker privileges are applied and the DEFINERpart is omitted (even when it is auto-added during schema import). Full reference here: https://dev.mysql.com/doc/refman/5.7/en/stored-programs-security.html

这样做，将应用实际的调用者权限，并DEFINER省略该部分（即使在模式导入期间自动添加）。完整参考：https: //dev.mysql.com/doc/refman/5.7/en/stored-programs-security.html

回答by Ghulam Dastagir - Yasir

CREATE DEFINER=[your_web_user]@% PROCEDURE p_add_user(...)

Check it.. probably this will help you, if you want to define the user in your procedure...

检查它.. 可能这会帮助你，如果你想在你的程序中定义用户......