可能有多个用户作为MySQL存储过程的DEFINER? [英] Possible to have multiple users as DEFINER for MySQL Stored Procedure?

问题描述

我在MySQL存储过程上有点挣扎，感到有些沮丧.我有鲍勃创建的一组SP.由于他是DEFINER，只有他才能看到它们的CREATE语句，对其进行修改等.

I'm struggling a little with MySQL stored procedures and getting a bit frustrated. I have a set of SPs created by Bob. As he is the DEFINER, only he can see the CREATE statement for them, amend them etc.

Mary在MySQL Workbench的架构中可以看到Bob的存储过程，但是看不到它们的作用-当她单击SP并选择发送到SQL编辑器-> CREATE语句"时，没有任何反应，因为她不是定义者.

Mary can see Bob's stored procedures in the schema in MySQL Workbench, but can't see what they do - when she clicks on the SP and selects "Send to SQL Editor -> CREATE statement" nothing happens, because she's not the definer.

从MS SQL的背景来看，这对我来说有点奇怪.有什么方法可以设置一个组(例如"DB_DEVS")并将该组作为存储过程的定义者，以便Bob和Mary可以看到彼此的代码?

Coming from a MS SQL background this is a little bizarre to me. Is there any way I can set up a group (e.g. "DB_DEVS") and make this group the definer of the Stored Procs so Bob and Mary can see each other's code?

推荐答案

DEFINER始终引用创建存储过程的用户.该用户只能是1个用户.

DEFINER always refers to the user who created the stored procedure. This can only be 1 user.

如果玛丽想看鲍勃的程序，她可以打电话:

If Mary wants to see Bobs procedure, she could call:

SHOW CREATE PROCEDURE proc_name

查看过程代码.她还可以调用以下代码查看代码:

To see the code of the procedure. She could also call the following to see the code:

SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='proc_name'

此处介绍如何使Mary能够通过MySQL-Workbench访问过程视图:

默认情况下，Mary无法将create语句发送到SQL-Editor.但这只是特权.玛丽只需要mysql.proc表中的基本SELECT特权.为此，请运行以下SQL语句(通过命令行或直接在工作台中):

By default, Mary is not able to send the create statement to the SQL-Editor. But this is just a privilege thing. Mary just needs a basic SELECT privilege in the mysql.proc table. To do this, run the following SQL-Statement (via Command line or directly in the Workbench):

GRANT SELECT ON mysql.proc TO 'mary'@'%'

此命令使Mary可以从所有主机访问Create-Statement.如果要将其限制为特定的主机，则可以执行以下操作:

This command enables Mary to access the Create-Statement from all hosts. If you want to limit it to a specific host you would do something like:

GRANT SELECT ON mysql.proc TO 'mary'@'192.168.2.1'

如果Mary拥有SELECT特权，则在执行Send to SQL Editor -> CREATE statement

If Mary has the SELECT privilege she should be able to see the procedure after doing Send to SQL Editor -> CREATE statement

注意::要运行GRANT -Command，您需要以具有授予特权的特权的用户身份登录(例如root-user)

NOTE: In order to run the GRANT-Command you need to be logged in as user who has the privilege to grant privileges (e.g. root-user)

+++++++++++++++++++++++++ EDIT +++++++++++++++++++++++

有一种快速而肮脏的"方式可以为大量用户实现这一目标，而无需为每个用户编写新的命令来授予特权: (确保对有权通过工作台插入行的用户执行此操作)

There is a "quick and dirty" way to achieve this for a large number of users without writing for each user a new command to grant the privilege: (make sure to do this with a user who has the privilege to insert rows via the Workbench)

1. 在工作台中打开一个新的SQL-Tab
2. 输入SELECT * FROM mysql.tables_priv;并运行
3. 结果网格上方应该有一个小按钮，可用于从csv文件导入数据.

4. 创建一个如下所示的CSV文件:

1. Open a new SQL-Tab in your Workbench
2. Type SELECT * FROM mysql.tables_priv; and run it
3. Above the result-grid there should be the a small button which allows you to import data from a csv-File.

4. Create a CSV-File which looks like this:

％，mysql，jane，proc，root @ localhost，"2016-02-19 22:51:47"，选择， ％，mysql，max，proc，root @ localhost，"2016-02-19 22:51:47"，选择， ％，mysql，steve，proc，root @ localhost，"2016-02-19 22:51:47"，选择， ％，mysql，greg，proc，root @ localhost，"2016-02-19 22:51:47"，选择， ％，mysql，jamie，proc，root @ localhost，"2016-02-19 22:51:47"，选择，

%,mysql,jane,proc,root@localhost,"2016-02-19 22:51:47",Select, %,mysql,max,proc,root@localhost,"2016-02-19 22:51:47",Select, %,mysql,steve,proc,root@localhost,"2016-02-19 22:51:47",Select, %,mysql,greg,proc,root@localhost,"2016-02-19 22:51:47",Select, %,mysql,jamie,proc,root@localhost,"2016-02-19 22:51:47",Select,

...更多用户

jane，max，steve，...将是您的用户.保持其他列不变.

jane, max, steve,... would be your users. Leave the other columns the way they are.

1. 导入您的csv文件
2. 在SQL窗口中运行FLUSH PRIVILEGES(从priv-tables重新加载特权)
3. 完成！您所有的用户现在都可以访问存储过程

1. Import your csv-File
2. Run FLUSH PRIVILEGES in an SQL-Window (reloades privileges from priv-tables)
3. Finished! All your users can now access Stored Procedures

这篇关于可能有多个用户作为MySQL存储过程的DEFINER?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！