MySQL Bugs: #76622: "create procedure" implicitly does "create user" if DNS is incomplete

Bug #76622	"create procedure" implicitly does "create user" if DNS is incomplete
Submitted:	8 Apr 2015 16:21	Modified:	8 Jun 2015 11:12
Reporter:	Jörg Brühe (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.41, 5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Tested with 5.5.41 (Debian build, DEB) and 5.6.22 (Oracle build, tar.gz):

Under certain conditions, a "create procedure" implicitly grants privileges to a new combination of user@IP and so creates a new user account.
These privileges are "execute" + "alter" for the procedure, no others.

If the user then connects again on that machine, the new account is the most specific one and will be used, so the user suddenly does not have the original privileges (which he had when creating the procedure) but only those new, insufficient ones.

Conditions (under which I could reproduce it):
- Server and client are running on the same machine.
- The client uses the TCP protocol to connect to the server.
- The machine's IP address is not correctly resolved to its hostname.
- The user has all DML and DDL privileges on "*.*", but does not need "ALL PRIVILEGES".

These conditions may be stronger than needed, I did not try to soften them.

How to repeat:
This is the test script:

=====
[joerg@somewhere ~]$ cat bug-creta-proc.sql 
select user, host, password from mysql.user order by 1, 2;
select user(), current_user();

drop database if exists foobar ;
create database foobar ;

use foobar
delimiter //
create procedure show_foo ()
  BEGIN select user(), current_user(); END //
delimiter ;

select user, host, password from mysql.user order by 1, 2;
=====

My account has these privileges:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'joerg'@'%' IDENTIFIED BY PASSWORD '...'

Call the "mysql" client, forcing TCP within the machine:
mysql -u joerg -p... --port=3307 --protocol=TCP -h 192.168.0.79

Then use "source bug-creta-proc.sql" to run the test script.

You will notice that the second "select user, host ..." reports one more line than the first.
The new account is clearly connected to the "create procedure", see the privileges:

GRANT USAGE ON *.* TO 'joerg'@'192.168.0.79'
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `foobar`.`show_foo` TO 'joerg'@'192.168.0.79'

Suggested fix:
Do not implicitly grant privileges when a procedure is created.

Re the DNS:
It is sufficient to use a VM for the reproduction, for which there is no DNS server at all.
The machine will have a hostname from its setup, and an IP adrdress (probably via DHCP), that will be enough to reproduce the bug.

Hello Jörg Brühe,

Thank you for the report.
Imho this is known and documented behavior, quoting from manual "By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable" - https://dev.mysql.com/doc/refman/5.6/en/create-procedure.html
https://dev.mysql.com/doc/refman/5.6/en/stored-routines-privileges.html

Also - https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_automatic_sp_p...

Thanks,
Umesh

I must admit that note had escaped me - I am sorry.

However, this behavior is not really helpful, because the privileges are given to the wrong account: They are given to the result of "user()", while the check whether "execute" and "alter" are granted refers to "current_user()".
This is inconsistent.
In effect this means a new user account is created, which IMNSHO should not happen as a side effect of "create procedure".

So the subject line is still correct: "create procedure" implicitly does "create user" ...
What is wrong in my submission is the suggested fix - it should read
"Consider the value of "current_user()" to be the creator of the routine."

When you consider the "proxy user" whose importance will rise with MySQL 5.7, it is obvious that this difference is very relevant.

What I forgot:
The code already uses the "current_user()" account, because it takes the password from that account and sets it for the new "user()" account.
Even more inconsistent ...

Thank you for the feedback.
For now marking this as duplicate of Bug #77186, please follow up Bug #77186 for any updates.