Search code examples
mysqlmysqljs

MySQL throwing "execute command denied to user" when running stored procedure remotely

I have a nodejs service which is trying to run a stored procedure on a mysql database but the call fails with execute command denied to user.

The same service can successfully execute simple SELECTs and return data with no problems.

If I connect to the mysql database using the mysql command line utility, and log in as the same user that the nodejs service is configured to use, then the stored procedure works fine.

Why does running the stored proc remotely fail, but running it locally work?

EDIT

Following the advice below, this is the result of running show grants whilst accessing mysql from the command line: enter image description here

And this is the result when I run it through my node app:

{"Grants for myuser@%":"GRANT USAGE ON *.* TO `myuser`@`%` IDENTIFIED BY PASSWORD '*blahblahblahblahblahblah'"}

I don't understand why the GRANT SELECT, INSERT etc has gone since i thought using the % wildcard meant that it shouldnt care about which domain it thinks myuser is coming from.

Solution

  • Keep in mind that 'user'@'localhost' is a different user spec than 'user'@'%' or any other remote user. They are effectively two different users, and they can have different sets of privileges.

    I suggest you connect as the user locally and run SHOW GRANTS;. Then connect remotely and do the same.

    You need the EXECUTE privilege to execute procedures. I think that privilege will be missing when you connect as the user remotely.

    Re your updated question:

    The privileges shown, which only include USAGE, means that the user can login (i.e. authenticate) but does not have any privileges on anything. But the user identity 'myuser'@'%' is the same as the other case where several privileges are present, including EXECUTE.

    I would try to verify that you are in fact connecting to the same instance of MySQL.

    Do the following queries and check whether they all give the same result in your app's session and the interactive session:

    SELECT @@hostname, @@port, @@socket;
SELECT @@server_uuid, @@server_id;
SELECT USER();
SELECT VERSION();
SHOW GLOBAL STATUS LIKE 'Uptime';

    If they don't, then your node app is connecting to a different instance of MySQL Server. The uptime will be different, since it changes every second, but it should be consistent with the times between when you execute the query from each client.