MySQL Bugs: #51401: GRANT treats nonexistent functions/privileges differently

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:		CPU Architecture:	Any

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.

Thank you for the bug report.

triage: setting tag to CHECKED (w4 give the grant as a function and then create as a procedure), I3 embarrassment

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.

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