I have a hosted MySql server with many databases each with many views. I access the server remotely from my office using HeidiSql.
Once in a while the IP address of my office changes. When this happens, I have to add the new office IP address to the server using cPanel and the "Remote MySql" tool so that I can remotely connect again.
However, all of the views have definer: USER@OLD_IP_ADDRESS. If I need to change a view, I get Access Denied. Up to now, I have been deleting the view (yes, i can delete the view) and recreating it, which makes the view's definer USER@NEW_IP_ADDRESS, and then I can edit the view -- until the Ip address changes again.
So, my question is: What is the best practice is an environment like this. Is there a way to define the views once and, without causing a security risk, be able to edit the views after an IP Address change.
Thanks for any guidance.
You could loosen the host in the MySQL user account a bit, according to the IP range of your internet provider. That way, you don't need to change it every time you get a slightly different IP:
[email protected].%
[email protected].%
This of course loosens the security in this account, but if your password is be a good one, it should not be too risky.
A better way is to make the MySQL server only accessible via SSH. In that case, your MySQL server can be set up using the --skip-networking option, as you always connecting from localhost. Your user account can be user@localhost or [email protected], which then would solve your above mentioned problem for all times.
HeidiSQL also supports SSH tunneled MySQL connections, see here for some tutorial.