MySQL Bugs: #74297: create user with plugin and password

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:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	authentication, password, plugin, Security

Description:
With CREATE USER I can use IDENTIFIED WITH _or_ IDENTIFIED BY.

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.

Added tags

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';

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.

Verified as feature request.

This is fixed in MySQL 5.7.6 with new CREATE USER syntax.

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.