修改存储过程定义者

Question

我在MySQL数据库中有大约一百个存储例程，其中大多数都将“root”作为定义者。

我有另一个名为“abc”的mysql帐户，如何将所有例程的定义者更改为“abc”。

如果我只以“abc”用户身份访问MySQL服务器而不是“root”，是否可能执行相同操作？

- Mithun Sreedharan

Answer 1

查看定义者：

show procedure status;

您可以像这样更改它们：

UPDATE `mysql`.`proc` p SET definer = 'YournewDefiner' WHERE definer='OldDefinerShownBefore'

例如：

 UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%'

要小心，因为这将更改所有数据库的定义者。

- gibzaki

---

“show procedure status” 对我不起作用，但更新是有效的。谢谢。 - James McMahon

因为服务器不能注意到对表的直接操作，所以被投下反对票。(http://dev.mysql.com/doc/refman/5.5/en//stored-routines-privileges.html) - KDrewiske

UPDATE 更改了表格，但 mysqldump 没有看到它：mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES。即使重启了 mysql，问题仍然存在。我正在运行 mysql-5.5.31。 - x-yuri

我的错。 我的视图使用了错误的定义者。 update mysql.proc 方法确实可行。 - x-yuri

谢谢@bradmace，但实际上你可以通过添加 and db = 'my_database' 来优化你的SQL语句， 因此完整的SQL语句将是 UPDATE mysql.proc p SET definer = 'user@%' WHERE definer='root@%' and db = 'my_database'; - user3856437

Answer 2

您可以在mysql.proc表中更新定义程序的数据库/表...

update mysql.proc set definer='root@localhost' where name='t_p';

- Darshan Patil

---

在这种情况下，“t_p”是什么？ - posfan12

@posfan12，“t_p”是存储过程的名称。 - Sudar

Answer 3

据我所知： 您需要获取所有脚本文本并将其删除/重新创建为abc。除非abc有删除/创建存储过程的权限，否则您将无法以abc的身份执行此操作。

- Fosco

Answer 4

导出数据库<DB>的所有存储过程：

mysqldump --single-transaction --no-data --no-create-info --routines \
    --skip-triggers <DB> >routines.sql

编辑 routines.sql 并重新创建它们：

cat routines.sql | mysql <DB>

如有必要，请指定-u和-p开关。

- x-yuri

Answer 5

我通过导出数据库，用文本编辑器（不是文字处理软件）打开导出的文件，并进行查找和替换来完成大规模的Definer更改。将新的Definer替换为旧的Definer。然后，我重命名原始数据库并创建一个具有原始数据库名称的新数据库。只需使用新的Definer信息导入修改后的导出文件即可完成操作。我喜欢这个解决方案，因为它给了我一个备选方案，如果出现问题，可以删除创建的数据库并重命名副本。

- Brian Henkel

Answer 6

不要使用复杂的方法，创建一个名为abc的用户，授予该用户所有权限，并在连接字符串中连接到abc。这样会更好，mysql版本8不支持mysql.proc

- Armature

Answer 7

我之前遇到了这个问题，最后用以下方法解决了它。 当我往表格中插入数据时，出现了错误代码1449。 解决方法：进入触发器选项卡，看看有没有一些由用户定义的触发器，如果有就从列表中删除它们。 这样就可以在phpmyadmin中对表格进行操作了。

- Ritikesh

Answer 8

你也可以这样做：

update mysql.proc set definer = "root@localhost" where db = "mydatabase";

- user8629109

Answer 9

alter procedure old_proc_name
delimiter //
create PROCEDURE new_proc_name (parameter)
begin
 Your Sql Statement;
end//
delimiter ;

- Sam

---

什么不寻常的方式 - Shao Khan

不是 MySQL 语法。 - Abhijit Buchake