Whilst a long time MS SQL Server user, MySQL is new to me. I've created an Internet site for a charity using PHP and MySQL. When I created the stored procedures in MySQL Workbench I didn't use the DEFINER parameter, so they are all set as the logged in account at the time, e.g. mysqluser@xx.xx.xx.xx The IP address was my home ip at the time, which has now changed. I now can't edit the stored procedures as I don't have the appropriate privileges apparently. Is this simply an issue with how Cpanel is used to create the MariaDb and the users, or can I alter how I write the stored procedures so that my IP is no longer important? I'm also worried that PHP login details might somehow stop working. Any help to relieve this confusion would be appreciated.
1 Answers
I'm not sure what solutions there are for your current circumstance, but to answer your question
You could use:
CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
or
CREATE DEFINER = 'remoteuser'@'%' PROCEDURE some_func()
As long as you have remote access to the host machine, you'd still be able to say for example ssh then login mysql as 'admin'@'localhost'. Or login as 'remoteuser' regardless of your IP address, mind that wildcard host would not work if a user with the same name had a hostname that's more specific. Also privileges need to be granted to these users beforehand.
For DHCP, you can also do something with wildcard such as:
CREATE DEFINER = 'user'@'192.168.%' PROCEDURE do_sth()
Related