MySQL Bugs: #70465: Spurious definer warning

Bug #70465	Spurious definer warning
Submitted:	29 Sep 2013 21:40	Modified:	14 Oct 2013 18:14
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6.15, 5.7.4, 5.5.35, 5.1.73, 5.0.94	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Logged in as 'user'@'%' and importing or creating a routine eg ...

create definer='user'@'localhost' procedure p() ...

elicits the misleading warning ...

The user specified as a definer ('user'@'localhost') does not exist.

How to repeat:
As above

Suggested fix:
When the routine runs, MySQL can figure out that '%' includes 'localhost'. To be consistent, the Create Routine parser needs to apply the same wildcard logic.

Thank you for the report.

Verified as described.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

MySQL does login to user name matching in only one place: at authentication time.
All the other references to user accounts are never "expanded".
Take for example the GRANT statement (as another place where an user account is specified outside of the authentication context).
http://dev.mysql.com/doc/refman/5.6/en/grant.html says :
"If the account named in a GRANT statement does not exist in the mysql.user table, GRANT creates it if the NO_AUTO_CREATE_USER SQL mode is not enabled".

If the *exact* user account name doesn't exist in mysql.user the user account is considered different (and created or not etc).

No let's go back to the CREATE PROCEDURE documentation (http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html) :
"The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER()."

I read this to mean that the DEFINER clause is subject to the same rules as user account in the GRANT clause. I.e. no "expansion".

What you're proposing is to actually have the server alter the user account name you've explicitly specified (name@localhost) to a more general one (name@%). But since name@localhost doesn't exist what does this mean for e.g. name@bigbadhost ? Will it still be able to execute the procedure ? This is the ambiguity that we're trying to avoid by not doing name "expansion" in contexts other than authentication.

> this is not a bug. 

If it misleads the user, it is a bug.

If it suggests stupidity in the software, it is a mistake.

It does both, so it is a bug and a mistake.

> "The DEFINER clause specifies the MySQL account to be used when checking access privileges 
> at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

Indeed. Create this import text file ...

drop procedure if exists p;
create definer='USR'@'localhost' procedure p()
show grants for 'USR'@'%';

substituting for USR a username whose mysql.user.host='%', log in as that user, and import it with the source cmd. You'll get the idiotic warning. Now call  p(). Of course MySQL runs p() because it knows '%' includes 'localhost'.

When a Create Procedure Definer clause specifies an existing mysql.user.user value in a row with host='%', the failed match warning should be suppressed.