MySQL Bugs: #43830: CREATE PROCEDURE causes ERROR 1410 unless ALTER ROUTINE is granted

Bug #43830	CREATE PROCEDURE causes ERROR 1410 unless ALTER ROUTINE is granted
Submitted:	24 Mar 2009 12:06	Modified:	27 Mar 2009 7:29
Reporter:	Dmitry Samborskiy	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.32, 5.0, 5.1, 6.0 bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi All,

The problem occures when user with limited access rights 
(EXECUTE, CREATE ROUTINE) is trying to define procedure.

Though, the procedure is created successfully, 
ERROR 1410 arises which inevitably causes termination
of SQL script execution.

The idea was to allow several SQL developers to create
their own private SQL routine sets. (Access to their
own routines is granted by demand with a special procedure
that GRANTS needed privileges with PREPARE/EXECUTE 
dynamic SQL.)

Below is a sequence of commands that demonstrate the problem.

Best regards,
  Dmitry

How to repeat:
$ mysql -uroot -p mysql

mysql> CREATE USER `test` IDENTIFIED BY '123';
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.07 sec)

mysql> GRANT CREATE ROUTINE, EXECUTE ON `db1`.* TO 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

$ mysql -utest -p123 db1

mysql> delimiter |
mysql> CREATE PROCEDURE p1 (OUT i INT) BEGIN SET i = 1; END|
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> delimiter ;
mysql> CALL p1(@i); SELECT @i;
Query OK, 0 rows affected (0.00 sec)

+------+
| @i   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> Bye

$ mysql -uroot -p mysql

mysql> GRANT ALTER ROUTINE ON `db1`.* TO 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE db1.p1;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

# When ALTER ROUTINE is granted, proc is defined w/o error message:
$ mysql -utest -p123 db1

mysql> delimiter |
mysql> CREATE PROCEDURE p1 (OUT i INT) BEGIN SET i = 1; END|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL p1(@i); SELECT @i;
Query OK, 0 rows affected (0.01 sec)

+------+
| @i   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

Thank you for the report.

I can not repeat described behavior.

Please provide output of SHOW GRANTS when you connect as user test first time and your configuration file.

> Please provide output of SHOW GRANTS when you connect as user test
> first time and your configuration file.

See below. (Actually, I observed this behaviour on three hosts
with installed MySQL 5.1.30-community / 5.1.32-community under Fedora Core 6, 10 and CentOS 5.0.)

mysql> show grants for test;
+----------------------------------------------------------------------------+
| Grants for test@%                                                          |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '773359240eb9a1d9' | 
| GRANT EXECUTE, CREATE ROUTINE ON `db1`.* TO 'test'@'%'                     | 
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

$ cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

default_storage_engine=InnoDB

set-variable = max_allowed_packet=1024M
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server = utf8
collation-server = utf8_general_ci
[client]
default-character-set=utf8 
     
[mysql.server]
user=mysql
#basedir=/var/lib

set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Thank you for the feedback.

Verified as described with versions 5.0 and 5.1.30. Since version 5.1 server crashes for me with following backtrace:

Thread 1 (process 23820):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x085a46c5 in my_write_core (sig=6) at stacktrace.c:310
#3  0x0824c685 in handle_segfault (sig=6) at mysqld.cc:2513
#4  <signal handler called>
#5  0x002ce402 in __kernel_vsyscall ()
#6  0x00314f90 in raise () from /lib/libc.so.6
#7  0x00316678 in abort () from /lib/libc.so.6
#8  0x0030e269 in __assert_fail () from /lib/libc.so.6
#9  0x08232a9d in Diagnostics_area::set_ok_status (this=0x8aa1f00, thd=0x8aa1150, affected_rows_arg=0, last_insert_id_arg=0, message_arg=0x0) at sql_class.cc:436
#10 0x08174de5 in my_ok (thd=0x8aa1150, affected_rows=0, id=0, message=0x0) at sql_class.h:2255
#11 0x082684d5 in mysql_execute_command (thd=0x8aa1150) at sql_parse.cc:4172
#12 0x0826af74 in mysql_parse (thd=0x8aa1150, inBuf=0x8ab34b0 "CREATE PROCEDURE p1 (OUT i INT) BEGIN SET i = 1; END", length=52, found_semicolon=0xb74222fc) at sql_parse.cc:5831
#13 0x0826bbb0 in dispatch_command (command=COM_QUERY, thd=0x8aa1150, packet=0x8aab451 "CREATE PROCEDURE p1 (OUT i INT) BEGIN SET i = 1; END", packet_length=52) at sql_parse.cc:1216
#14 0x0826cdce in do_command (thd=0x8aa1150) at sql_parse.cc:857
#15 0x08259a05 in handle_one_connection (arg=0x8aa1150) at sql_connect.cc:1115
#16 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#17 0x003b74fe in clone () from /lib/libc.so.6

Test case:

create database if not exists db1;

CREATE USER `test`;
GRANT CREATE ROUTINE, EXECUTE ON `db1`.* TO 'test'@'%';

connect (addcontest, localhost, test,,);
connection addcontest;

show grants;

use db1;
delimiter |;
CREATE PROCEDURE p1 (OUT i INT) BEGIN SET i = 1; END|
CALL p1(@i)|
SELECT @i|
delimiter ;|

connection default;

GRANT ALTER ROUTINE ON `db1`.* TO 'test'@'%';
DROP PROCEDURE db1.p1;

connection addcontest;

delimiter |;
CREATE PROCEDURE p1 (OUT i INT) BEGIN SET i = 1; END|
CALL p1(@i)|
SELECT @i|