MySQL Bugs: #16279: Wildcards do not work in database names for ROUTINE-related privileges

Bug #16279	Wildcards do not work in database names for ROUTINE-related privileges
Submitted:	7 Jan 2006 18:28	Modified:	8 Jan 2006 2:48
Reporter:	Jack Levy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
We use wildcards in the database name when creating new users as in:

   GRANT ALL on `fred%`.* TO 'fred' IDENTIFIED BY 'secret';

This lets users create their own databases with their own name as initial substring, and gives them full privileges on all their own databases.  This works for all the old privileges, but not for CREATE ROUTINE and EXECUTE.  The server denies the user the ability to create and use stored procedures unless the database is named exactly in the GRANT. 

How to repeat:
Create a new user as described above and then attempt to create a stored procedure when logged in as the user.

I was unable to repeat creatin a very simple procedure, could you please
provide a complete script which shows the behavior reported:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL on `fred%`.* TO 'fred' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

miguel@hegel:~/dbs/5.0> bin/mysql -ufred -psecret -h192.168.0.119
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database freddb1;
Query OK, 1 row affected (0.02 sec)

mysql> use freddb1
Database changed
mysql> create table tb1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tb1 add column name char(35);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create view vtb1 as select * from tb1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_freddb1 |
+-------------------+
| tb1               |
| vtb1              |
+-------------------+
2 rows in set (0.00 sec)

mysql> delimiter //

mysql> create procedure sp1() begin select 1+1; end//
Query OK, 0 rows affected (0.01 sec)

mysql> call sp1();
    -> //
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure sp1\G
*************************** 1. row ***************************
       Procedure: sp1
        sql_mode: 
Create Procedure: CREATE PROCEDURE `sp1`()
begin select 1+1; end
1 row in set (0.00 sec)

Thanks in advance.

Many apologies.  I can't recreate this problem now.  I've only just upgraded to 5.0.18, and I now think I saw it under 5.0.17.  Sorry to have wasted your time.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Thank you for the feedback and bug report.