MySQL Bugs: #51799: MySqlCommand required 'selected' grants on mysql.proc table for call stored proc

Bug #51799	MySqlCommand required 'selected' grants on mysql.proc table for call stored proc
Submitted:	6 Mar 2010 18:02	Modified:	30 Apr 2010 14:10
Reporter:	Sergey Vinogradov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S3 (Non-critical)
Version:	6.2.2	OS:	Windows
Assigned to:	Tony Bedford	CPU Architecture:	Any
Tags:	grant, mysql.proc, privileges, stored procedure, stored routines

Description:
Hello

In call stored procedure, MySqlCommand return error "SELECT command denied to user 'tmpUser4'@'localhost' for table 'proc'" . If grant select on mysql.proc to tmpUser4 called is successful, but stored procedure not require 'select' privilege. In MySql Client command line the call is successful without 'select' privilege on mysql.proc.

Call with user = storeAdmin is successful (definer of procedure)
Call other procedures with CommandType.StoredProcedure required mysql.proc select privilege, but text type command simple "call otherProc('val1', 'val2', 'val3')" is successful.

I can't to find a description of such behavior in help or a way to disable this feature.

The procedure code with helper procs:

create definer = storeAdmin
 function getUserNameFromFull(fullUserString VARCHAR(255))
	returns VARCHAR(100)
	SQL SECURITY DEFINER
	return  SUBSTRING_INDEX(fullUserString,'@',1)//

create definer = storeAdmin
 function getUserName()
	returns VARCHAR(100)
	SQL SECURITY DEFINER
	return  getUserNameFromFull(USER())//

create definer = storeAdmin
 function quoteIdentify(userName VARCHAR(100))
returns VARCHAR(100)
SQL SECURITY DEFINER
return  concat("`", replace(userName, "`", "'"), "`")//

create definer = storeAdmin
 procedure setPasswordToUser(IN userName VARCHAR(100), IN passwordString VARCHAR(100), IN curUserPwd VARCHAR(100), out success BOOL)
 SQL SECURITY DEFINER
begin
	set @adminUserName = getUserName();
	set @pwdLen        = ceil(CHAR_LENGTH(passwordString) / 3);
	select password into @pwdPassword from mysql.user where user = @adminUserName;

	IF @pwdPassword = password(curUserPwd) THEN

	set @a = concat('set password for ', quoteIdentify(userName), ' = password(', quote(passwordString), ')');
	
	prepare setPassword from @a;
	execute setPassword;

	deallocate prepare setPassword;

	set success = true;
	
	ELSE
	
	set success = false;
	
	END IF;
end//

The C# connection code:
isSuccess = false;
				try
				{
					MySqlConnection c = new MySqlConnection(query.connectionString);
					var cmd = new MySqlCommand("setPasswordToUser", c);

					cmd.Parameters.Add(new MySqlParameter("userName", userName));
					cmd.Parameters.Add(new MySqlParameter("passwordString", password));
					cmd.Parameters.Add(new MySqlParameter("curUserPwd", adminPwd));

					var p = new MySqlParameter("success", System.Data.DbType.Boolean);
					p.Direction = System.Data.ParameterDirection.Output;
					cmd.Parameters.Add(p);
					cmd.CommandType = System.Data.CommandType.StoredProcedure;

					try
					{
						c.Open();
						result = cmd.ExecuteNonQuery();
						isSuccess = Convert.ToBoolean(p.Value);
					}
					finally
					{
						c.Close();
					}
				}
				catch (MySqlException e)
				{
					result       = -1;
					errCode      = e.Number;
					errorMessage = e.Message;
				}

How to repeat:
Call stored routine without 'select' privilege on mysql.proc and CommandType set to StoredProcedure.

Suggested fix:
Add filed to disable this feature

Hi Sergey and thanks for your report.

I have two problems here:
  o cmd.Parameters.Add is *deprecated* and I believe docs have been updated with this fact. Please use AddWithValue instead
  o I do not see your connection string so I do not know if you added Use Procedure Bodies=false (http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html):
Use Procedure Bodies 	true 	Setting this option to false indicates that the user connecting to the database does not have the SELECT privileges for the mysql.proc (stored procedures) table. When to set to false, Connector/NET will not rely on this information being available when the procedure is called. Because Connector/NET will be unable to determine this information, you should explicitly set the types of the all the parameters before the call and the parameters should be added to the command in the exact same order as they appear in the procedure definition. This option was added in Connector/NET 5.0.4 and Connector/NET 1.0.10.

ok, not a bag in code thereat. I see more lack of documentation: in stored routines call description no information about this, as well passing and obtain parameter, as mysql.proc 'select' privilege requirement.

"Use procedure body" description must been in "Calling a sroted procedure from Connector/NET" partition of description, seems.

P.S. I do not understand, why i need to use AddWithValue on out parameter, when it have no value. Thank, my problem is solved, documentation is no well only.

Sergey let me try to explain.

The two options are AddWithValue and Add. The Add takes a parameter object so you might do something like .Add(new MySqlParameter("@id", MySqlDbType.Int32)).Value = 2; There is also an Add that takes a name and type. The only .Add that is deprecated is .Add(name, value).

Setting to "Documenting" so Tony can add a line or two in manual.

Try adding this in your connection string.
"use procedure bodies=false;"

Sergey

We just committed a fix to all branches 6.0 and higher that should fix this and change how were are doing some things.  There was misunderstanding and misuse of 'use procedure bodies' option.  The only time you should have to use 'use procedure bodies' is when you are executing a procedure that you only have execute privs on.  If the routine body is visible to you when you select on the information_schema.routines table, then that option is not necessary.

Have clarified use of 'Use Procedure Bodies' in connection string option reference (changes made to XML sources).