| Bug #40139 | ExecuteNonQuery hangs | ||
|---|---|---|---|
| Submitted: | 19 Oct 2008 13:47 | Modified: | 21 Nov 2008 15:46 |
| Reporter: | Sarah Green | ||
| Status: | Closed | ||
| Category: | Connector/Net | Severity: | S3 (Non-critical) |
| Version: | 5.2 | OS: | Any |
| Assigned to: | Target Version: | ||
[19 Oct 2008 14:05]
Sarah Green
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".
[17 Nov 2008 22:18]
Reggie Burnett
didn't really see the hang but saw that I could clean up how it operates.
[17 Nov 2008 22:19]
Bugs System
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
[17 Nov 2008 22:20]
Reggie Burnett
Fixed in 5.2.6+
[21 Nov 2008 15:46]
Tony Bedford
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.
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!