MySQL Bugs: #25033: Exception raised/ HANG if no SELECT privileges granted for stored procedure call

Bug #25033	Exception raised/ HANG if no SELECT privileges granted for stored procedure call
Submitted:	13 Dec 2006 10:51	Modified:	21 Jan 2014 23:03
Reporter:	Markus Wolters	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.8RC	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	exception, privileges, SELECT, stored procedure

Description:
An attempt to call a stored procedure causes an application hang, if the current user has no SELECT privileges for the mysql schema.

After investigation the problem, the reason is a raise of MySqlExecption during MySqlDataClient.StoredProcedure.GetProcedureBody() and in there during reader.Read(). But that exception never get cought and hangs the application (I don't know if the hang is my fault).

I set serverity to S2 because of the hang.

This is against the Change log, which states that no more SELECT privilieges would be required.

WORKAROUND: Against the documentaion grant user rights to SELECT 'mysql' schema

How to repeat:
Create a user with NO privileges for the 'mysql' schema. Under this user call a stored procedure like this:

  MySqlConnection conn = new MySqlConnection(_connectionString);
  MySqlCommand cmd     = new MySqlCommand();
  MySqlDataReader reader;

  conn.Open();
  cmd.Connection = conn;
  cmd.CommandText = "GetUser";
  cmd.CommandType = System.Data.CommandType.StoredProcedure;

  reader = cmd.ExecuteReader();
  reader.Close();

Suggested fix:
Point of failure:

namespace MySql.Data.MySqlClient
{
  internal class StoredProcedure
  {
    private string GetProcedureBody([...])
   {
	[...]
	MySqlDataReader reader = null;
	try
	{
	  MySqlCommand cmd = new MySqlCommand(String.Format("SHOW CREATE " +
		 "{0} {1}", type, spName), connection);
	  isFunc = type.ToLower(CultureInfo.InvariantCulture) == "function";
	  cmd.CommandText = String.Format("SHOW CREATE {0} {1}", type, spName);
	  reader = cmd.ExecuteReader();
------>   reader.Read();
	  sql_mode = reader.GetString(1);
	  return reader.GetString(2);
	}
	catch (Exception)
	{
------->  throw;
	}
	[...]
  }
}

I issued severity S1 because after further investigation, there's something wrong after that Exception. Somebody has to restart IIS completly to work with new connections to MySQL. There seams to be a problem with 

lock (idlePool.SyncRoot)

on some places because it goes into a deadlock. I can't find out exactly what the problem is.

There's also a weired thing here (Maybe that's the real problem):

MySqlDataReader.Read()
{
  [...]
  try
  {
    try
    {
    }
    catch (MySqlException ex)
    {
	if (ex.IsFatal)
	  connection.Terminate();     <--------- TERMINATION
	throw;
    }
  catch (Exception ex)
  {
  }
  finally
  {
     connection.SetState(ConnectionState.Open);  <- OVERRIDE ON CLOSE CONNECTION
  }
}

Hi Markus and thanks for your problem report. Loking into latest 1.0.x sources I see we're still caling "SHOW CREATE ". Procedure body for non-privileged user is not returned.

Hi Tonci,

the thing is, I'm wondering especially about the hang. With 1.0.7 I get shown an exception about missed SELECT privileges as expected. But with this release the application is just hanging, like in a deadlock or something. Do you get that hung too?

Markus

This can't be fixed  until the server exposes proc parameters via information schema.  The changelog entry was wrong and a new entry will be added indicating that select privs on the mysql database is required to run sprocs.