Bug #10640 CommandType does not document required privilege for stored procedures.
Submitted: 14 May 2005 19:36 Modified: 17 Jul 2006 12:47
Reporter: Mark Johnson (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:Connector / NET Documentation Severity:S3 (Non-critical)
Version:1.0.7, 1.0.4 OS:Microsoft Windows (windows)
Assigned to: MC Brown CPU Architecture:Any

[14 May 2005 19:36] Mark Johnson
When you call a stored procedure, the command object makes an additional SELECT call to MySql to determine the parameters of the stored procedure.  That SELECT call is:
SELECT param_list FROM  mysql.proc WHERE db=_latin1 'Recipe' AND
name=_latin1 'mj_spListAuthors

This requires that the user connecting to the database have an additional privilege above and beyond that required to simply invoke the stored procedure.  The user must have SELECT privilege on the mysql.proc table.

This means that using Connector/NET to call a stored procedure requires more privilege than simply calling the stored procedure.

How to repeat:
1. Create a database as root.
2. Create a stored procedure (default DEFINER security) as root.
3. Add a user with only Execute_priv for that database.
4. Call the stored procedure through mysql.  This works.
5. Call the stored procedure through Connector/NET.  This fails.
6. Add Select_priv to mysql.db for the user.
7. Repeat step 5.  This succeeds.

Suggested fix:
This additional privilege requirement should be documented on the Command.CommandType page along with the other remarks relating to the StoredProcedure type.
[21 Feb 2006 8:08] Valeriy Kravchuk
Bug #16703 marked as a duplicate of this one.
[26 May 2006 4:25] Mark Johnson
Bug 14835
duplicates this bug as well.
[17 Jul 2006 12:47] MC Brown
A note has been added in the C/NET documentation for this issue.
[17 Jul 2006 16:34] Paul van Rossem
1) This is not only a C/NET issue, but affects all connectors. Such as Connector/J as well.
2) I wonder if this is really the solution, just adding a comment to the docs. I think this additional privilige should not be required as this is not a logical concept, but only follows if one knows the implementation.