MySQL Bugs: #37023: Editing SP with no mysql\SELECT rights fails to throw error

Bug #37023	Editing SP with no mysql\SELECT rights fails to throw error
Submitted:	27 May 2008 23:45	Modified:	14 Feb 2009 16:50
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	no contents, qc, SP

Description:
Editing stored procedure accross network looses its guts in latest 1.2.12 version of Query Browser.

How to repeat:
Setup remote server and then edit existing SP

Thank you for the report.

I can not repeat described behavior. Please provide step-by-step instruction.

1. Setup following environment below
 ..Vista\MySQL 5.1.26 >> Vista\GUI-TOOLS-Latest
2. Enter new basic SP on server machine
3. Edit new SP reomtley with latest server components

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`MyProc` $$
CREATE PROCEDURE `test`.`MyProc` ()
BEGIN
select 1;
END $$

DELIMITER ;

Picture of the bug

Attachment: bug37023.jpg (image/pjpeg, text), 24.46 KiB.

I am also seeing this bug.  My database lives on a Solaris 10 box. The MySQL version is 5.0.51a-log. Query Browser lives on a Windows XP box. QB version=1.2.12.

The issue occurs with both functions and procedures. When opening a function or procedure to edit from QB, an empty skeleton is displayed as would be expected if one clicked "create function/procedure". However, the function/procedure will run successfully.

Interestingly, functions and procedures created in QB or Administrator open properly in edit mode. I thought this might have to do with the user@localhost vs the user@remotehost grants, but both have GRANT ALL PRIVILEGES ON mydb TO user@whateverhost

Many thanks for the description. I also think it is related to remote servers. I have to test with database on another machine.

http://dev.mysql.com/doc/refman/5.0/en/show-create-procedure.html

"The statements require that you be the owner of the routine or have SELECT access to the
mysql.proc table."

http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

"The default value is DEFINER. This feature is new in SQL:2003. The creator or invoker must have permission to access the database with which the routine is associated."

mysql> SELECT  routine_schema, routine_name, routine_type,security_type, definer
 FROM information_schema.ROUTINES;
+----------------+--------------+--------------+---------------+----------------
+
| routine_schema | routine_name | routine_type | security_type | definer
|
+----------------+--------------+--------------+---------------+----------------
+
| live           | MyProc2      | PROCEDURE    | DEFINER       | live@%
|
| live           | MYProc3      | PROCEDURE    | DEFINER       | live@%
|
| live           | Proc1        | PROCEDURE    | DEFINER       | root@localhost
|
| live           | proc4        | PROCEDURE    | DEFINER       | root@localhost
|
+----------------+--------------+--------------+---------------+----------------
+
4 rows in set (0.00 sec)

mysql> SELECT db, name, type, security_type, definer FROM mysql.proc;
+------+---------+-----------+---------------+----------------+
| db   | name    | type      | security_type | definer        |
+------+---------+-----------+---------------+----------------+
| live | Proc1   | PROCEDURE | DEFINER       | root@localhost |
| live | MyProc2 | PROCEDURE | DEFINER       | live@%         |
| live | MYProc3 | PROCEDURE | DEFINER       | live@%         |
| live | proc4   | PROCEDURE | DEFINER       | root@localhost |
+------+---------+-----------+---------------+----------------+
4 rows in set (0.00 sec)

Workaround : Edit proc using local machine and change DEFINER=`live`@`%`

ISSUE 1.  Editing SP with no mysql\SELECT rights fails to throw error
ISSUE 2.  MySQL allows 'root@%' to edit proc defined as 'root@localhost'

Could you consider the 1st as'Verified' and consider the latter security hole.

Many thanks for writing a bug report. Please try newer version and let us know if you still have this problem. The newest version is MySQL Query Browser 1.2.15

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Changing to the newest version was not sufficient.  Adding the GRANT: GRANT SELECT ON `mysql`.`proc` TO 'thisuser'@'that.computer' fixed the problem. But it did not make my sysadmin happy.