Procedure privileges

I came across a problem on site yesterday. In moving the development environment to a new server and creating more appropriate permissions for users (they were using ALL on *.*) I found that the Java application would crash with a NullPointerException. The permissions were standard, and calling the Stored Procedure worked via the mysql prompt.

CREATE USER [email protected];
CALL sp_test()

You can spend a lot of time looking into problems, luckily this development configuration had taken my advice to enabled the General Query Log. (Something everybody should do to know your SQL).

In closer inspection the following command was being sent to the MySQL Server. SHOW CREATE PROCEDURE sp_test; Attempting to run this command via the mysql prompt works.

| Procedure | sql_mode | Create Procedure |
| sp_test   |          | NULL             |

On closer inspection, the Create Procedure content contains NULL, seems suspicious. Running this command as the ‘root’ MySQL user I get the content of the procedure.

RFTM sheds light that this command requires privileges to mysql.proc. Running the following was necessary to get operation.

 GRANT SELECT ON mysql.proc to [email protected];

This requirement is rather stupid in my book. I’ve yet to determine why SHOW CREATE PROCEDURE is called, probably some requirement via Connector/J but this permission problem definitely needs fixing.


  1. says

    Hi Ronald,

    Connector/J needs to issue “SHOW CREATE PROCEDURE” and parse the procedure source (not kidding!) because there is no parameter-level metadata about stored procedures available until MySQL-6.0 (in I_S.parameters).

    The driver needs this information to know about OUT, INOUT and parameter names, all required by JDBC to function correctly with stored procedures.

    The driver can be told to punt with “noAccessToProcedureBodies=true”, but you won’t get reliable metadata (name, directionality, type or storage qualifieers) and you can’t refer to parameters by name.