Search code examples
mysqlstored-proceduresshared-hosting

How to allow any user to run stored procedure in shared hosting mysql

When creating stored procedure in MySQL database hosted on shared hosting server, a definer for the procedure automatically added:

CREATE DEFINER=teq_user1@My IP Address PROCEDURE dpr_tax(IN...

this limits calling this procedure from this IP only. We need to allow certain user to call this procedure regardless the IP.

I have tried:
GRANT EXECUTE ON PROCEDURE teq_acc.dpr_tax TO 'teq_user1'@'%';
and
GRANT EXECUTE ON PROCEDURE teq_acc.dpr_tax TO 'teq_user1'@'localhost';

but following error received:
grant command denied to user 'teq_user1'@'My IP address'

Solution

  • I found a fix:
    "DEFINER=teq_user1@My IP Address"
    is added when you create stored procedure from a client app connected via "Remote SQL" such as MySQL Workbench. But if you re-created the procedure from PHPmyAdmin, Definer will be:
    "DEFINER=teq_user1@localhost" which can be run from any IP.