MySQL Bugs: #79277: ALTER USER/SET PASSWORD do not work for --init-file execution

Bug #79277	ALTER USER/SET PASSWORD do not work for --init-file execution
Submitted:	13 Nov 2015 18:41	Modified:	14 Dec 2015 14:31
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.7.3+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is a problem I noticed while checking the instructions at:
http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

The advice given in the first two subsections is to reset the root password by starting the server with an --init-file file that contains either an ALTER USER or SET PASSWORD statement to assign the root account a password.

The instructions work in MySQL 5.7 up through 5.7.2. In 5.7.3, SET PASSWORD begins to fail. The ALTER USER syntax (introduced in 5.7.6) never does work when executed from --init-file.

From speaking with Joro, it seems that these statements should be working (the server is not operating under any special restrictions such as are in place with --skip-grant-tables).

How to repeat:
With the server running, create an account. For example: CREATE USER x;

Put either of these statements in a file (doesn't matter which one, neither one will work):

ALTER USER y IDENTIFIED BY 'pass';
SET PASSWORD FOR x = PASSWORD('pass');

Suppose the file is named /tmp/mypass.

Stop the server and restart it, passing to it the file:

bin/mysql_safe --init-file=/tmp/mypass &

The statement in /tmp/mypass will fail and the error log will contain this message:

[ERROR] 1131  You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

Suggested fix:
Permit SET PASSWORD and ALTER USER to work for --init-file execution.

Since the failures begin with 5.7.3, I looked in the release notes:
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

Perhaps this entry is relevant:

It is no longer possible to expire the password for anonymous-user accounts because an anonymous user cannot execute SET PASSWORD to reset the account password.

Thank you for the bug report.

2015-11-13T22:23:04.316060Z 0 [Note] Execution of init_file 'c:\dbs\mypass.txt' started.
2015-11-13T22:23:04.319568Z 1 [ERROR] 1131  You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords
2015-11-13T22:23:04.320557Z 0 [Note] Execution of init_file 'c:\dbs\mypass.txt' ended.

Noted in 5.7.11, 5.8.0 changelogs.

ALTER USER and SET PASSWORD did not work at server startup when
invoked in the file named using the --init-file option.