Mysql 创建定义器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16594860/
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 CREATE DEFINER
提问by Marcos Lamba
it's my first question here and I honestly made it after long investigation without answers.
这是我在这里的第一个问题，经过长时间的调查，我诚实地提出了没有答案的问题。
I have created an intranet web application that run on a CentOS web server, the application use another local server (always CentOS) for the mysql database.
我创建了一个在 CentOS Web 服务器上运行的 Intranet Web 应用程序，该应用程序使用另一个本地服务器（始终是 CentOS）作为 mysql 数据库。
Inside the database I have created routines wich evey start like this:
在数据库中，我创建了每一个这样的例程：
CREATE DEFINER='localuser02'@'192.168.0.5' PROCEDURE 'Createuser'
The IP 192.168.0.5 is the IP of the server where application is installed.
IP 192.168.0.5 是安装应用程序的服务器的 IP。
Until here all is ok the application is working. Now some days ago as the server that run the application is an old one, to add another server (I mean commodity server), what I was thinking is to do a traffic balance between them.
直到这里一切正常，应用程序正在运行。现在几天前，由于运行应用程序的服务器是旧的，要添加另一台服务器（我的意思是商品服务器），我的想法是在它们之间进行流量平衡。
Now I have setup correctly both servers to run the application, the problem is with the SQL DEFINER which have just one IP.
现在我已经正确设置了两台服务器来运行应用程序，问题在于只有一个 IP 的 SQL DEFINER。
My servers have now 192.168.0.5 and 192.168.0.6.
我的服务器现在有 192.168.0.5 和 192.168.0.6。
Theoretically my new DEFINER should be like this:
理论上我的新 DEFINER 应该是这样的：
CREATE DEFINER='localuser02'@'192.168.0.5, 192.168.0.6' PROCEDURE 'Createuser'
But I don't know how to proceed.
但我不知道如何继续。
thanks for the answer.
谢谢你的回答。
For wildcard I have understand like using with percent like you said 192.168.% or like suggested by eggyal 192.168.0.% my question and concerns in doing this if it will work? Since my network is distributed like this:
对于通配符，我明白像使用百分比一样，就像你说的 192.168.% 或像eggyal 192.168.0.% 建议的那样，我的问题和担忧是否可行？由于我的网络是这样分布的：
192.168.0.1 - router
192.168.0.2 - voip phone
192.168.0.3 - voip phone
192.168.0.4 - firewall
192.168.0.5 - Server 01
192.168.0.6 - Server 02
192.168.0.7 - SQL Server
192.168.0.8 - LAN Printer
192.168.0.9 - First pc
192.168.0.10 - Second pc
My question is having IP from 192.168.0.1 to xxx.10 reserved, if I add wildcard on SQL could be a collision with address I mean could the DEFINER try to authenticate for example on voip phone or a printer or the firewall instead of the two servers exclusively?
我的问题是保留从 192.168.0.1 到 xxx.10 的 IP，如果我在 SQL 上添加通配符可能与地址冲突我的意思是 DEFINER 可以尝试在例如 voip 电话或打印机或防火墙上进行身份验证而不是两者服务器专用？
Also second question, there is the possibility to list more than one IP to be more tidy like:
还有第二个问题，有可能列出多个 IP 以便更整洁，例如：
CREATE DEFINER='localuser02'@'192.168.0.5, 192.168.0.6' PROCEDURE 'Createuser'
CREATE DEFINER='localuser02'@'192.168.0.5, 192.168.0.6' PROCEDURE 'Createuser'
Thanks in advance
提前致谢
回答by udog
The DEFINER clause controls the account used at execution time, unless you also specify SQL SECURITY INVOKER. As your code is currently written, regardless of which user executes the code at runtime, it will be executed under the security context specified in DEFINER. If you need to specify a broader range of addresses for the definer, use % by itself in the host portion, or use something like 192.168.%
DEFINER 子句控制在执行时使用的帐户，除非您还指定了 SQL SECURITY INVOKER。由于您的代码是当前编写的，无论哪个用户在运行时执行代码，它都会在 DEFINER 中指定的安全上下文下执行。如果您需要为定义器指定更广泛的地址范围，请在主机部分单独使用 %，或使用类似 192.168.% 的内容
