MySQL Bugs: #20704: CREATE PROCEDURE without correct grant yields incorrect error

Bug #20704	CREATE PROCEDURE without correct grant yields incorrect error
Submitted:	26 Jun 2006 19:17	Modified:	6 Aug 2007 15:06
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-log	OS:	Linux (Fedora core 5)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
The mysql-test-run code below results in the error message:
mysqltest: At line 8: query 'CREATE PROCEDURE p1()
SELECT VERSION()' failed: 1044: Access denied for user 'sp_execute'@'localhost' to database 'procDB'

It would seem that this message would be more appropriate, though less than ideal for this circumstance:
Error: 1403 SQLSTATE: 42000 (ER_NONEXISTING_PROC_GRANT) 
Message: There is no such grant defined for user '%s' on host '%s' on routine '%s' 

How to repeat:
CREATE DATABASE procDB;

GRANT EXECUTE, INSERT, DELETE, UPDATE, SELECT ON procDB.*
             TO  'sp_execute'@'localhost' IDENTIFIED BY 'sp_execute';

connect (sp_execute, localhost, sp_execute, sp_execute,procDB);

CREATE PROCEDURE p1()
     SELECT VERSION();

ER_NONEXISTING_PROC_GRANT is returned in case of a semantic error in REVOKE, when one is trying to revoke grants on a stored routine that were never granted, in similarity with ER_NONEXISTING_GRANT.

"Access denied" error, issued in case of insufficient privileges is opaque in consistency with the spirit of the SQL standard, when an insufficient privilege error reveals very little information about data or issued grants. This is consistent with server behavior in case of other objects - tables or views.

See also:
http://dev.mysql.com/doc/refman/5.1/en/grant.html

And http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

Eric, I re-read the chapters that I recommended to you, and they are not very explicit. Perhaps documentation could be improved to reflect the above explanation.