MySQL Bugs: #55251: GRANT EXECUTE has no effect

Bug #55251	GRANT EXECUTE has no effect
Submitted:	14 Jul 2010 9:46	Modified:	17 Aug 2010 13:37
Reporter:	jerome acheriteguy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.41	OS:	Linux (5.1.41-3ubuntu12.3)
Assigned to:		CPU Architecture:	Any
Tags:	execute, grant, Lucid, LYNX, ubuntu

Description:
GRANT EXECUTE has no effect, even if you flush privileges.

How to repeat:
One way to see this is:

mysql> GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'myuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE EXECUTE ON PROCEDURE mydb.myproc FROM 'myuser'@'localhost';
ERROR 1403 (42000): There is no such grant defined for user 'myuser' on host 'localhost' on routine 'myproc'

Please, check with a newer version, 5.1.48, step by step. For me it works:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create procedure proc1() select 111;
Query OK, 0 rows affected (0.09 sec)

mysql> create user myuser@localhost identified by 'mypass';
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -umyuser -pmypass -P3310 test
ERROR 1044 (42000): Access denied for user 'myuser'@'localhost' to database 'tes
t'

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant execute on procedure test.proc1 to myuser@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -umyuser -pmypass -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+-------------------------------------------------------------------------------
--------------------------------+
| Grants for myuser@localhost
                                |
+-------------------------------------------------------------------------------
--------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366E
AF75BB670AD8EA7A7FC1176A95CEF4' |
| GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO 'myuser'@'localhost'
                                |
+-------------------------------------------------------------------------------
--------------------------------+
2 rows in set (0.00 sec)

mysql> call proc1();
+-----+
| 111 |
+-----+
| 111 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> revoke execute on procedure test.proc1 from myuser@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -umyuser -pmypass -P3310 test
ERROR 1044 (42000): Access denied for user 'myuser'@'localhost' to database 'tes
t'

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".

I confirm the bug description.
Unless you upgrade database there is no workarround.
It is critical for this version.

Thank you for the feedback.

> I confirm the bug description.

Does this mean problem is repeatable with version 5.1.48 or 5.1.49?

> Unless you upgrade database there is no workarround.

What do you mean by "upgrade database"?

By upgrade, I mean change version which in my case is not possible for internal and political reasons.

Thank you for the feedback.

In this case I have to close bug as "Can't repeat" as we could not repeat it on our side. We don't backport bug fixes anyway. If you are able to upgrade and provide more information how to repeat the problem feel free to reopen the report.