MySQL Bugs: #40139: ExecuteNonQuery hangs

Bug #40139	ExecuteNonQuery hangs
Submitted:	19 Oct 2008 11:47	Modified:	21 Nov 2008 14:46
Reporter:	Sarah Green	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have 2 users with (as far as I can tell) exactly the same permissions granted on the database yet when I ExecuteNonQuery ***with a command type of stored procedure***, one works and the other hangs. Yet both behave as expected if I use CALL in the command text and ExecuteNonQuery with a command type of Text.

I have spent several hours examining the permissions in MySQL Administrator to try to ascertain if/what the differences are between the users but I can find none.

How to repeat:
1. Create a user abc with SELECT permission only to the database.
2. Create a stored procedure (that does nothing) called xxx 
3. Assign EXECUTE permission to user abc for this procedure only (GRANT EXECUTE ON PROCEDURE xxx TO abc;)
4. Create a command to call the stored procedure using a connection string for abc:
   dim dbCommand = New MySqlCommand("xxx", myConnection)
   dbCommand.CommandType = CommandType.StoredProcedure
   dbCommand.Connection.Open()
   dbCommand.ExecuteNonQuery()

   When run the ExecuteNonQuery command hangs without reporting any errors. 

5. Amend the command to use text command type:
   dim dbCommand = New MySqlCommand("CALL xxx();", myConnection)
   dbCommand.CommandType = CommandType.Text
   dbCommand.Connection.Open()
   dbCommand.ExecuteNonQuery()

   When run the ExecuteNonQuery command performs the stored procedure correctly
   and returns without any errors.

6. Create another stored procedure called yyy and do *not* give access to abc. 

7. Repeat as for 4 above using yyy:
   dim dbCommand = New MySqlCommand("yyy", myConnection)
   dbCommand.CommandType = CommandType.StoredProcedure
   dbCommand.Connection.Open()
   dbCommand.ExecuteNonQuery()

   When run the ExecuteNonQuery command returns an error saying the 
   procedure yyy cannot be found. (Harmless enough but incorrect)

5. Repeat as for 5 above using yyy:
   dim dbCommand = New MySqlCommand("CALL yyy();", myConnection)
   dbCommand.CommandType = CommandType.Text
   dbCommand.Connection.Open()
   dbCommand.ExecuteNonQuery()

   When run the ExecuteNonQuery command correctly returns an access-denied error.

Suggested fix:
Suggestions welcome please!

I have since discovered that the difference between the user that works and the user that doesn't is that the stored procedures were created by (and automatically tagged with DEFINER=user1) and hence the command set with type StoredProcedure is obeying the DEFINER security permissions (and only allowing user1 to execute thr procedure) whilst the command with type Text is ignoring these and using the GRANTed permissions (which allows user2 to have EXECUTE rights).

I have lessened the Severity on this bug as a result of discovering this. However, it seems strange and confusing that the two behave differently - tho' I'm not sure which is "right".

didn't really see the hang but saw that I could clean up how it operates.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59008

Fixed in 5.2.6+

An entry was added to the 5.2.6 changelog:

When ExecuteNonQuery was called with a command type of Stored Procedure it worked for one user but resulted in a hang for another user with the same database permissions.

However, if CALL was used in the command text and ExecuteNonQuery was used with a command type of Text, the call worked for both users.