Bug #21292 Cannot call stored procedure with JDBC without select_priv on mysql.proc
Submitted: 26 Jul 2006 8:52 Modified: 1 Aug 2006 0:31
Reporter: Kristian Koehntopp Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:Connector/J 3.1.12 OS:Linux (SLES 9)
Assigned to: CPU Architecture:Any
Tags: jdbc, privileges, stored procedure

[26 Jul 2006 8:52] Kristian Koehntopp
Description:
I have a stored procedure kris.demo() created by root that kris can call on the MySQL command line.

When calling the same procedure with JDBC, I get "java.sql.SQLException: Driver requires declaration of procedure to either contain a '\nbegin' or '\n' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types."

The actual production mysql.proc table contains procedures that contain secrets that cannot be exposed by granting select_priv on mysql.proc. The caller of a (any) stored procedure may not view the actual sourcecode being called of this (any) stored procedure.

How to repeat:
As root:

create database kris;
grant all on kris.* to "kris"@"127.0.0.1";
delimiter //
create procedure kris.demo()
deterministic
begin
  select "hello world";
end;
//
delimiter ;

As kris:

show create procedure kris.demo;

Make sure you do not get the procedure body as kris, because kris.demo() contains secret data.

kris@localhost [kris]> show create procedure demo;
+-----------+----------+------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+------------------+
| demo      |          |                  |
+-----------+----------+------------------+
1 row in set (0.00 sec)

Try to call kris.demo() using jdbc.

Suggested fix:
Make jdbc parse result set types instead of mysql.proc data, as all other connectors do as well.

Make sure procedure bodies can be kept secret.
[26 Jul 2006 8:58] Tonci Grgin
Seems non OS, non JDBC version dependent since it's repeatable on, for example, XP with 3.1.12, 3.1.13 and 5.0 2006-07-04 snapshot. Checking more.
[31 Jul 2006 23:00] MySQL Verification Team
Please see bug: http://bugs.mysql.com/bug.php?id=20235 too.
[1 Aug 2006 0:31] Mark Matthews
As already discussed w/ reporter of bug, this is not a bug, but a limitation of the server, as the JDBC specification _requires_ metadata from stored procedures about _parameters_ (i.e result sets don't help here!) that is currently only available in the body of the stored procedure, since we haven't yet implemented INFORMATION_SCHEMA.parameters in the server. Other drivers (ODBC, ADO.Net) that support actual types for parameters beyond string/number a'la PHP/Perl require the same information and use similar mechanisms to retrieve it.

See "noAccessToProcedureBodies" in /J 5.0.3 for a somewhat hackish, non-JDBC compliant workaround.