Bug #74297 create user with plugin and password
Submitted: 9 Oct 2014 12:42 Modified: 8 Apr 2015 21:05
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: authentication, password, plugin, Security

[9 Oct 2014 12:42] Daniël van Eeden

When I want to create a user with a specific plugin:
CREATE USER 'foo'@'bar' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD FOR 'foo'@'bar' = PASSWORD('sakila');

This leaves a window in which the user exists, but without password. The user is already allowed to login. 

How to repeat:
Try to create a user with an explicit auth plugin and a password. Try to do this without any window in witch password-less logins on this account are allowed.

Suggested fix:
Creating the user as disabled (not expired!) and the enabling it when the password is set might work. Unfortunately mysql_no_login is an authentication plugin itself, so this doesn't work for now.

The best solution: allow the user to specify a plugin and a password in CREATE USER.
[9 Oct 2014 12:42] Daniël van Eeden
Added tags
[9 Oct 2014 13:48] Daniël van Eeden
It is possible to specify an authentication_string (saved in mysql.user.authentication_string), but that doesn't change anything.

create user 'test2'@'%' identified with mysql_native_password AS 'foobar';
[9 Oct 2014 14:36] Todd Farmer
Hi Daniël, thanks for a great feature request.  There is work underway already to support this exact use case in 5.7 (ability to define account, plugin and password in a single command).  I'll leave this bug report open until that's implemented.
[9 Oct 2014 14:37] Todd Farmer
Verified as feature request.
[8 Apr 2015 21:01] Todd Farmer
This is fixed in MySQL 5.7.6 with new CREATE USER syntax.
[8 Apr 2015 21:05] Todd Farmer
Posted by developer:
This is fixed in MySQL 5.7.6+ with the introduction of new CREATE USER syntax (WL#6409).  Documented in 5.7.6 change log and CREATE USER syntax documentation.