MySQL Bugs: #14834: Server denies to execute Stored Procedure

Bug #14834	Server denies to execute Stored Procedure
Submitted:	10 Nov 2005 15:31	Modified:	26 Jan 2006 3:11
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15/5.0BK	OS:	Windows (Windows, Linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
The server refuses to execute a stored procedure that's defined as SQL SECURITY DEFINER, although the user who has defined the procedure has sufficiant privileges.

How to repeat:
The user 'user@localhost' and 'mpopp@localhost' have both all privileges for the database mpopp_test, including the execute privilege. If the user 'user' executes the procedure getUniqueness, which has been defined by mpopp@localhost, it throws this error:

ERROR 1370 (42000): execute command denied to user 'mpopp'@'localhost' for routi
ne 'mpopp_test.getUniqueness'

Here's another example that illustrates the bug quite well:

mysql> create procedure getUser() select user(), current_user();
Query OK, 0 rows affected (0.00 sec)

mysql> call getUser();
+-----------------+-----------------+
| user()          | current_user()  |
+-----------------+-----------------+
| mpopp@localhost | mpopp@localhost |
+-----------------+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

E:\>mysql -u user
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 5.0.15-nt-max

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

mysql> use mpopp_test
Database changed
mysql> call getUser();
ERROR 1370 (42000): execute command denied to user 'mpopp'@'localhost' for routi
ne 'mpopp_test.getUser'
mysql> show grants;
+-------------------------------------------------------------------------------
------------------------------+
| Grants for user@localhost
                              |
+-------------------------------------------------------------------------------
------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*196BDEDE2AE4
F84CA44C47D54D78478C7E2BD7B7' |
| GRANT ALL PRIVILEGES ON `mpopp_test`.* TO 'user'@'localhost'
                              |
| GRANT SELECT ON `mysql`.`proc` TO 'user'@'localhost'
                              |
+-------------------------------------------------------------------------------
------------------------------+
3 rows in set (0.00 sec)

mysql>

Could you please correct me the steps I did wrong for to try to
repeat the behavior reported. Thanks in advance:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt

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

mysql> create database mpopp_test;
Query OK, 1 row affected (0.08 sec)

mysql> grant all on mpopp_test.* to
    -> "mpopp"@"localhost" identified
    -> by "mpopp";
Query OK, 0 rows affected (0.03 sec)

mysql> grant all on mpopp_test.* to
    -> "user"@"localhost" identified
    -> by "user";
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -umpopp -pmpopp mpopp_test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.15-nt

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

mysql> create procedure getUser() select user(), current_user();
Query OK, 0 rows affected (0.06 sec)

mysql> call getUser();
+-----------------+-----------------+
| user()          | current_user()  |
+-----------------+-----------------+
| mpopp@localhost | mpopp@localhost |
+-----------------+-----------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -uuser -puser mpopp_test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.15-nt

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

mysql> call getUser();
+----------------+-----------------+
| user()         | current_user()  |
+----------------+-----------------+
| user@localhost | mpopp@localhost |
+----------------+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

I think, I've found the problem - probably it's more a phpMyAdmin problem than a MySQL problem.

I've created the mpopp and user users in phpMyAdmin. phpMyAdmin escapes the underscore with a backslash, so in the mysql.db table there's actually

mysql> select host, user, db from db;
+-----------+--------+-------------+
| host      | user   | db          |
+-----------+--------+-------------+
| localhost | mpopp  | mpopp\_test |
| localhost | mpopp2 | mpopp\_test |
+-----------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select db, name from proc;
+------------+---------+
| db         | name    |
+------------+---------+
| mpopp_test | getUser |
+------------+---------+
1 row in set (0.00 sec)

mysql>

When MySQL compares mpopp\_test to mpopp_test, the comparison might fail and so it refuses to execute the Stored Procedure.

Should I report it as a bug to phpMyAdmin or could it be implemented in MySQL, that escaped underscores match unescaped underscores (actually, for accessing tables, it works)?

According what you said this isn't a MySQL issue, however when playing
with phpMyAdmin, I read some comments about to use it against the
5.0.XX server and I compiled the PHP stuff against the 5.0.XX client
library and some recommended extensions. So I recommend you before
to open a bug, to verify that before.

Thanks for the feedback.

at least to me it seems to be a MySQL bug.

pls read:
https://sourceforge.net/tracker/?func=detail&atid=377408&aid=1353603&group_id=23067

thank you

At least, database and table permissions take wildcards into account, so it would make sense to me that also the privileges permissions work with wildcards.

In this case, it would actually be a MySQL bug.

I Installed PhpMyAdmin:
Welcome to phpMyAdmin 2.6.4-pl2 
MySQL 5.0.15-nt-log running on localhost as root@localhost

and PHP which I compiled from source:

c:\miguel>php --version
PHP 5.0.5 (cli) (built: Nov 15 2005 22:48:43)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v2.0.5, Copyright (c) 1998-2004 Zend Technologies

With a fresh install I connected with mysql client and with phpMyAdmin,
and with phpMyAdmin created the database and issued the below 2
queries:

grant all on mpopp_test.* to
"mpopp"@"localhost" identified
by "mpopp";

grant all on mpopp_test.* to
"user"@"localhost" identified
by "user";

and from the mysql client:

c:\mysql\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt-log

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

mysql> select host, user, db from db;
+-----------+-------+------------+
| host      | user  | db         |
+-----------+-------+------------+
| %         |       | test       |
| %         |       | test\_%    |
| localhost | mpopp | mpopp_test |
| localhost | user  | mpopp_test |
+-----------+-------+------------+
4 rows in set (0.00 sec)

Below the log with the SQL statements sent by phpMyAdmin:

051115 23:32:20	     11 Connect     root@localhost on 
		     11 Query       SELECT VERSION() AS version
		     11 Query       SET NAMES utf8
		     11 Query       SET collation_connection = 'utf8_general_ci'
		     11 Query       SET NAMES utf8
		     11 Query       SET collation_connection = 'utf8_general_ci'
		     11 Query       SHOW SESSION VARIABLES LIKE 'collation_connection'
		     11 Query       SHOW SESSION VARIABLES LIKE 'character_set_connection'
		     11 Query       SHOW CHARACTER SET
		     11 Query       SHOW COLLATION
		     11 Init DB     mpopp_test
		     11 Query       grant all on mpopp_test.* to
"mpopp"@"localhost" identified
by "mpopp"
		     11 Query       SELECT DATABASE() AS 'db'
		     11 Init DB     mpopp_test
		     11 Query       SHOW TABLE STATUS FROM `mpopp_test`
		     11 Init DB     mysql
		     11 Init DB     mpopp_test
		     11 Query       SHOW CREATE DATABASE `mpopp_test`
		     11 Query       SHOW TABLES FROM `mpopp_test`
		     11 Quit       
051115 23:32:27	     12 Connect     root@localhost on 
		     12 Query       SELECT VERSION() AS version
		     12 Query       SET NAMES utf8
		     12 Query       SET collation_connection = 'utf8_general_ci'
		     12 Query       SET NAMES utf8
		     12 Query       SET collation_connection = 'utf8_general_ci'
		     12 Query       SHOW SESSION VARIABLES LIKE 'collation_connection'
		     12 Query       SHOW SESSION VARIABLES LIKE 'character_set_connection'
		     12 Query       SHOW CHARACTER SET
		     12 Query       SHOW COLLATION
		     12 Init DB     mpopp_test
		     12 Query       SHOW TABLE STATUS FROM `mpopp_test`
		     12 Init DB     mysql
		     12 Init DB     mpopp_test
		     12 Query       SHOW TABLES FROM `mpopp_test`
		     12 Quit       
051115 23:32:41	     13 Connect     root@localhost on 
		     13 Query       SELECT VERSION() AS version
		     13 Query       SET NAMES utf8
		     13 Query       SET collation_connection = 'utf8_general_ci'
		     13 Query       SET NAMES utf8
		     13 Query       SET collation_connection = 'utf8_general_ci'
		     13 Query       SHOW SESSION VARIABLES LIKE 'collation_connection'
		     13 Query       SHOW SESSION VARIABLES LIKE 'character_set_connection'
		     13 Query       SHOW CHARACTER SET
		     13 Query       SHOW COLLATION
		     13 Init DB     mpopp_test
		     13 Query       grant all on mpopp_test.* to
"user"@"localhost" identified
by "user"

Then what were the exactly steps you did with phpMyAdmin.

Thanks in advance.

Here's what 'cybot_tm' (phpMyAdmin developer) wrote about it:

\_ and \% is valid escaping for _ and %, otherwise _ would
mean ANY character and % any sequence of characters, thats
why 'mpopp_test' matches `mpopp_test` but also
`mpopp1test`,
`mpopp3test`, `mpoppxtest`, `mpoppttest`, `mpoppNtest`, ...

and 'mpopp\_test' matches ONLY `mpopp_test`

http://dev.mysql.com/doc/refman/5.0/en/request-access.html

so i think this is a MySQL issue. and not a bug in
phpMyAdmin as phpMyAdmin acts exactly according to the MySQL
documentation.

But you know the workaround! ;-)

--- end of comment ---

The mentioned page of the documentation at http://dev.mysql.com/doc/refman/5.0/en/request-access.html says:

The wildcard characters ‘%’ and ‘_’ can be used in the Host and Db columns of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (‘_’) as part of a database name, specify it as ‘\_’ in the GRANT statement.

---

So underscores have to be escaped, if being used literally.

However, in my example, the mpopp\_test in the db table did not match the information that's stored in the proc table (where the database name is specified without the backslash).

So it's obvious that the source of the bug is that the unescaped database name in the proc table does not match the escaped database name in the db table and so refuses to execute the stored procedure.

To repeat the example, it's necessary to come up with a db table like this:

mysql> select Host, User, Db from db;
+-----------+--------+-------------+
| Host      | User   | Db          |
+-----------+--------+-------------+
| localhost | mpopp2 | mpopp\_test |
| localhost | mpopp1 | mpopp_test  |
+-----------+--------+-------------+
2 rows in set (0.00 sec)

and a proc table such as this:

mysql> select Db, Name, Definer from proc;
+------------+---------+------------------+
| Db         | Name    | Definer          |
+------------+---------+------------------+
| mpopp_test | getUser | mpopp2@localhost |
+------------+---------+------------------+
1 row in set (0.01 sec)

Here's the complete way to reproduce the result. However, it's not the point that the user 'mpopp2' has been created by phpMyAdmin (please replace it with the create user and grant statement at the bottom), the point is that the database name is specified with underscores.

Lets assume, we have a fresh MySQL server with only one 
fully privileged user called root.

C:\>mysql -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 113 to server version: 5.0.15-nt-
max

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

mysql> create database mpopp_test;
Query OK, 1 row affected (0.00 sec)

mysql> create user mpopp1@localhost identified by
'abc';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on mpopp_test.* to mpopp1@localhost;
Query OK, 0 rows affected (0.00 sec)

/* forget this part and replace it with the 2 statements at the bottom
Now I use phpMyAdmin 2.7.0-beta1 to create a user called 
mpopp2@localhost and give him all privileges (including the 
EXECUTE privilege) on the mpopp_test database. Then I log in 
as mpopp2 to create the stored procedure:
*/

C:\>mysql -u mpopp2
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 136 to server version: 5.0.15-nt-
max

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

mysql> use mpopp_test
Database changed
mysql> create procedure getUser() select user(), 
current_user();
Query OK, 0 rows affected (0.00 sec)

mysql> call getUser();
+------------------+------------------+
| user()           | current_user()   |
+------------------+------------------+
| mpopp2@localhost | mpopp2@localhost |
+------------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Finally, I log in as mpopp1 to execute the Stored Procedure:

C:\>mysql -u mpopp1
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 137 to server version: 5.0.15-nt-
max

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

mysql> use mpopp_test
Database changed
mysql> call getUser();
ERROR 1370 (42000): execute command denied to user 
'mpopp2'@'localhost' for routine
'mpopp_test.getUser'

If we change back to the user root and look into the grant 
tables, we see the following:

C:\>mysql -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 138 to server version: 5.0.15-nt-
max

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

mysql> use mysql
Database changed
mysql> select Host, User, Password from user;
+-----------+--------+-------------------------------------------+
| Host      | User   | Password                                 
|
+-----------+--------+-------------------------------------------+
| %         | root   |                                          
|
| localhost | mpopp1 | 
*0D3CED9BEC10A777AEC23CCC353A8C08A633045E |
| localhost | mpopp2 | 
*0D3CED9BEC10A777AEC23CCC353A8C08A633045E |
+-----------+--------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> select Host, User, Db from db;
+-----------+--------+-------------+
| Host      | User   | Db          |
+-----------+--------+-------------+
| localhost | mpopp2 | mpopp\_test |
| localhost | mpopp1 | mpopp_test  |
+-----------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select Db, Name, Definer from proc;
+------------+---------+------------------+
| Db         | Name    | Definer          |
+------------+---------+------------------+
| mpopp_test | getUser | mpopp2@localhost |
+------------+---------+------------------+
1 row in set (0.01 sec)

-----

Replace the phpMyAdmin part with:

create user mpopp2@localhost identified by 'abc';
grant all on `mpopp\_test`.* to mpopp2@localhost;

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.17-debug

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

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

mysql> create user mpopp2@localhost identified by 'abc';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on `mpopp\_test`.* to mpopp2@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host, User, Db from db;
+-----------+--------+-------------+
| Host      | User   | Db          |
+-----------+--------+-------------+
| %         |        | test        |
| %         |        | test\_%     |
| localhost | user3  | db8         |
| localhost | mpopp2 | mpopp\_test |
+-----------+--------+-------------+
4 rows in set (0.00 sec)

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

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

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

mysql> grant all on `mpopp\_test`.* to mpopp2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Database changed
mysql> select Host, User, Db from db;
+-----------+--------+-------------+
| Host      | User   | Db          |
+-----------+--------+-------------+
| %         |        | test        |
| %         |        | test\_%     |
| localhost | mpopp2 | mpopp\_test |
+-----------+--------+-------------+
3 rows in set (0.00 sec)

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

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

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

mysql> create user mpopp1@localhost identified by
    -> 'abc';
Query OK, 0 rows affected (0.04 sec)

mysql> grant all on mpopp_test.* to mpopp1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> create user mpopp2@localhost identified by 'abc';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on `mpopp\_test`.* to mpopp2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysql -u mpopp2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-debug

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

mysql> use mpopp_test
Database changed
mysql> create procedure getUser() select user(),
    -> current_user();
Query OK, 0 rows affected (0.04 sec)

mysql> call getUser();
+------------------+------------------+
| user()           | current_user()   |
+------------------+------------------+
| mpopp2@localhost | mpopp2@localhost |
+------------------+------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysql -u mpopp1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.17-debug

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

mysql> use mpopp_test
Database changed
mysql> call getUser();
ERROR 1370 (42000): execute command denied to user 'mpopp2'@'localhost' for routine 'mpopp_test.getUser'
mysql>

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

Pushed to bk 5.0.19.

Documented in 5.0.19 changelog:

     <listitem>
        <para>
          <literal>GRANT</literal> statements specifying schema names
          that included underscore characters (i.e.
          <literal>my_schema</literal>) did not match if the underscore
          was escaped in the <literal>GRANT</literal> statement (i.e.
          <literal>GRANT ALL ON `my\_schema` ...</literal>). (Bug
          #14834)
        </para>
      </listitem>

This is a problem with the current version.