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.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.