Bug #51401 GRANT treats nonexistent functions/privileges differently
Submitted: 22 Feb 2010 19:35 Modified: 22 Apr 2011 13:28
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to:
Triage: Triaged: D3 (Medium)

[22 Feb 2010 19:35] Paul Dubois
Description:
If I attempt to grant a privilege to a nonexistent stored function or procedure, the server treats them differently: It grants the privilege in one case and rejects it in the other.

How to repeat:
Test script:

create database db1;
create user u1;
grant execute on function db1.f1 to u1;
grant execute on procedure db1.p1 to u1;
show grants for u1;
drop database db1;
drop user u1;

Result of mysql --force:

mysql> create database db1;
Query OK, 1 row affected (0.07 sec)

mysql> create user u1;
Query OK, 0 rows affected (0.02 sec)

mysql> grant execute on function db1.f1 to u1;
ERROR 1305 (42000): FUNCTION or PROCEDURE f1 does not exist
mysql> grant execute on procedure db1.p1 to u1;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for u1;
+---------------------------------------------------+
| Grants for u1@%                                   |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'%'                    |
| GRANT EXECUTE ON PROCEDURE `db1`.`p1` TO 'u1'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user u1;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
The server should treat the two GRANT statements the same, I believe.
[23 Feb 2010 13:45] Miguel Solorzano
Thank you for the bug report.
[25 Feb 2010 17:51] Omer Barnir
triage: setting tag to CHECKED (w4 give the grant as a function and then create as a procedure), I3 embarrassment
[25 Feb 2010 18:13] Paul Dubois
I'm not sure what the correct behavior should be here. The manual says this about granting privileges to nonexistent objects. http://dev.mysql.com/doc/refman/5.1/en/grant.html:

"MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for databases or tables that are to be created at a later time."

So that says nothing about stored routines. If the principle for tables should apply to routines, I suppose GRANT should be rejected unless the privileges contain CREATE. But CREATE cannot be granted to individual routines. Maybe the grant should fail unless the privileges include CREATE ROUTINE.
[22 Apr 2011 13:28] Paul Dubois
Noted in 5.6.3 changelog.

Attempts to grant the EXECUTE or ALTER ROUTINE privilege for a
nonexistent stored procedure returned success instead of an error.

CHANGESET - http://lists.mysql.com/commits/135954