WL#8540: Support IF [NOT] EXISTS clause in CREATE/DROP USER

Affects: Server-5.7   —   Status: Complete

MySQL should support IF [NOT] EXISTS clauses for CREATE, DROP and ALTER USER
commands.  This will allow for distribution of accounts using replication
without triggering replication failures in the event of non-synchronized
accounts on master and slave.  It also simplifies user scripting of account
management operations.
FR1:  CREATE USER IF NOT EXISTS will be valid syntax
FR1.1:  If any users in CREATE USER IF NOT EXISTS do exist, an appropriate
warning will be generated.
FR1.2:  When IF NOT EXISTS clause exists, CREATE USER statements will produce a
warning instead of an error due to preexisting matching users.
FR1.3:  CREATE USER without IF NOT EXISTS clause will continue to fail when a
preexisting user is specified.
FR1.4:  CREATE USER IF NOT EXISTS statements will be written to the binary log
regardless of whether user accounts were affected.
FR1.4.1:  CREATE USER IF NOT EXISTS statements will not be rewritten to include
attributes of existing account (e.g., password, auth plugin, locked status,
etc.) if the account exists.
FR1.4.2: CREATE USER IF NOT EXISTS statements with plain-text passwords will
trigger an error if the user does exist and --log_raw=OFF.
FR2:  DROP USER IF EXISTS will be valid syntax.
FR2.1:  If any users in DROP USER IF EXISTS do not exist, an appropriate warning
will be generated.
FR2.2:  When IF EXISTS clause exists, DROP USER statements will produce a
warning instead of an error due to non-existing users.
FR2.3:  DROP USER without IF EXISTS clause will continue to fail when a
non-existing user is specified.
FR2.4:  DROP USER IF EXISTS statements will be written to the binary log
regardless of whether user accounts were affected.
FR3:  ALTER USER IF EXISTS will be valid syntax.
FR3.1:  If any users in ALTER USER IF EXISTS do not exist, an appropriate
warning will be generated.
FR3.2:  ALTER USER IF EXISTS statements will not fail due to non-existing user.
FR3.2.1: ALTER USER IF EXISTS including plain-text password clauses will be
prohibited when the user does not exist.
FR3.3:  ALTER USER without IF EXISTS clause will continue to fail when a
non-existing user is specified.
FR3.4:  ALTER USER IF EXISTS statements will be written to the binary log
regardless of whether user accounts were affected.
FR3.4.1:  ALTER USER IF EXISTS statements will not be rewritten to include
attributes of existing account (e.g., password, auth plugin, locked status,
etc.) if the account exists.
FR3.4.2:  ALTER USER IF EXISTS statements with plain-text passwords will trigger
an error if the user does not exist and --log_raw=OFF.
FR4:  Warnings will be generated per-user, not per-statement.  E.g., a single
statement doing DROP USER IF EXISTS x@localhost, y@localhost will result in 2
warnings - one per user not dropped.
FR5:  Use of IF [NOT] EXISTS clauses will have no impact on statement rewriting
for general, slow and audit logs (password masking).


MySQL supports IF [NOT] EXISTS clauses in various DDL commands, such as:

* CREATE DATABASE
* DROP DATABASE
* CREATE TABLE
* DROP TABLE

This clause simplifies scripting operations against MySQL Server when in an
unknown state - it is used, for example, in mysqldump output.

Support for IF [NOT] EXISTS clauses is a long-requested feature:

http://bugs.mysql.com/bug.php?id=15287

This bug report has many duplicates.

We will add support for IF [NOT] EXISTS clause in CREATE USER, DROP USER and
ALTER USER syntax.

Both CREATE USER and ALTER USER are currently rewritten as the statements are
written to the binary log, most notably transforming plain-text passwords into
hashed passwords.

CREATE USER IF NOT EXISTS will not pick up/merge existing account attributes
when writing the command to the binary log.  If the CREATE USER IF NOT EXISTS
statement does not create a user on the master because it already exists, no
attributes of the existing user will be included in the rewritten command in the
binary log.

Likewise, ALTER USER IF EXISTS will not include any account attributes not
specified in the original statement.

The following files will be modified to implement this WL:

* sql/sql_yacc.yy - Adding grammar support for IF [NOT] EXISTS clauses to
CREATE, DROP and ALTER USER commands.
* sql/auth/auth_common.h - Revising definition of mysql_create_user(),
mysql_drop_user() and mysql_alter_user() commands to include appropriate
if_exists/if_not_exists boolean flag.
* sql/sql_parse.cc - Modifying calls to above functions to pass correct boolean
values based on lex state.
* sql/sql_user.cc - Modifying mysql_create_user(), mysql_drop_user() and
mysql_alter_user() functions to accept boolean flags indicating presence of IF
[NOT] EXISTS clause and generating warnings - instead of errors - where appropriate.