Mysql CREATE DEFINER

Question

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.

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.

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.

My servers have now 192.168.0.5 and 192.168.0.6.

Theoretically my new DEFINER should be like this:

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.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?

Also second question, there is the possibility to list more than one IP to be more tidy like:

CREATE DEFINER='localuser02'@'192.168.0.5, 192.168.0.6' PROCEDURE 'Createuser'

Thanks in advance

Answer 1

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.%