WL#11544: Current password required for SET PASSWORD
Affects: Server-8.0
—
Status: Complete
At present an user can change his password without needing to know the current password. Consider the case when a malicious user gets access to the benign user's terminal for a very brief period and changes the password. This might have two implications - 1. Benign user will be unable to access the terminal unless his password is reset by administrator. 2. During this period, the malicious user can connect to the server through the benign user's changed credentials. Hence, there is a scope to enhance the security by authorizing the users to change their password only if they could provide the current password. However, there could be exceptions to this policy. We must have to design the feature such that it could be easy to enforce or exempt the current password requirement at the time of changing it. Keeping these requirements in mind, this workflow aims to : 1. Change the SQL syntax of CREATE USER and ALTER USER, SET PASSWORD commands to enable the user to specify the current password. 2. Introduce a global system variable on the server through which current password policy could be enforced to the users. current password will be required at the time of changing password in case the system variable is ON otherwise it will not be required.
Definitions
-----------
D-1: Change Current Password Policy - Defines if the user needs to supply the
current password or not at the time of changing it. It depends on
following two factors -
1.a) The value of 'password_require_current' column in the mysql.user table
for every user.
1.b) The value of global server variable 'password_require_current'.
If the system variable is turned ON then this policy is enforced
otherwise it is not.
Here is a matrix that indicates how the policy is enforced. The flag
value for users in the table gets the higher precedence.
-- Column value ------- Global variable value -----------Policy
-- N ------- OFF ----------- exempt
-- N ------- ON ----------- exempt
-- Y ------- OFF ----------- require
-- Y ------- ON ----------- require
-- NULL ------- OFF ----------- exempt
-- NULL ------- ON ----------- require
D-2: Privileged User - If an user either has 'CREATE USER' privilege, or
'UPDATE' privilege on the mysql database. This user can change the
password of other users.
D-3: Refer the Interface Specification section for details about new SQL
commands and variables introduced.
Functional requirements
-----------------------
F-1: This feature must work with all plugins that store the password in the
mysql.user.authentication_string
F-2: Based on the SQL command, enum flag must be set as following in the
'password_require_current' column of the mysql.user table for an user.
CREATE USER usr --> NULL
CREATE USER usr PASSWORD REQUIRE CURRENT --> Y
CREATE USER usr PASSWORD REQUIRE CURRENT DEFAULT --> NULL
CREATE USER usr PASSWORD REQUIRE CURRENT OPTIONAL --> N
ALTER USER usr --> No-OP
ALTER USER usr PASSWORD REQUIRE CURRENT --> Y
ALTER USER usr PASSWORD REQUIRE CURRENT DEFAULT --> NULL
ALTER USER usr PASSWORD REQUIRE CURRENT OPTIONAL --> N
F-3: Privileged users may not provide the current password while changing
it for self.
Examples:
mysql@root> CREATE USER u1 IDENTIFIED BY 'pwd',
> u2 IDENTIFIED BY 'pwd',
> u3 IDENTIFIED BY 'pwd';
mysql@root> GRANT CREATE USER ON *.* TO 'u1' WITH GRANT OPTION;
mysql@root> GRANT UPDATE on mysql.* TO 'u2';
mysql@u1> ALTER USER user() IDENTIFIED BY 'new_pwd';
mysql@u1> SET PASSWORD = 'new_pwd';
mysql@u2> ALTER USER USER() IDENTIFIED BY 'new_pwd';
mysql@u2> SET PASSWORD = 'new_pwd';
mysql@u1> ALTER USER u3 IDENTIFIED BY 'new_pwd';
mysql@u1> SET PASSWORD FOR u3 = 'new_pwd';
mysql@u2> ALTER USER u3 IDENTIFIED BY 'new_pwd';
mysql@u2> SET PASSWORD FOR u3 = 'new_pwd';
F-3.1: However, if privileged user specifies the current password while
changing its own password. It would be treated as if the user
explicitly wants to verify the current password while changing it.
mysql@u1> ALTER USER user() IDENTIFIED BY 'new_pwd' REPLACE 'current_pwd';
mysql@u1> SET PASSWORD = 'new_pwd' REPLACE 'current_pwd';
mysql@u2> ALTER USER u2 IDENTIFIED BY 'new_pwd' REPLACE 'current_pwd';
mysql@u2> SET PASSWORD FOR u2 = 'new_pwd' REPLACE 'current_pwd';
F-3.2: If the user specified his own current password wrong then error
must be thrown.
Following statements must throw error.
mysql@u1> ALTER USER user() IDENTIFIED BY 'new_pwd' REPLACE 'junk_pwd';
mysql@u1> SET PASSWORD = 'new_pwd' REPLACE 'junk_pwd';
mysql@u2> ALTER USER u2 IDENTIFIED BY 'new_pwd' REPLACE 'junk_pwd';
mysql@u2> SET PASSWORD for u2 = 'new_pwd' REPLACE 'junk_pwd';
Error - Incorrect current password. Specify the correct password.
F-4: Privileged users must not provide the current password while changing
it for other users.
Examples : (... From F-3)
mysql@u1> ALTER USER u3 IDENTIFIED BY 'new_pwd' REPLACE 'current_pwd';
mysql@u1> SET PASSWORD FOR u3 = 'new_pwd' REPLACE 'current_pwd';
mysql@u2> ALTER USER u3 IDENTIFIED BY 'new_pwd' REPLACE 'junk_pwd';
mysql@u2> SET PASSWORD FOR u3 = 'new_pwd' REPLACE 'junk_pwdd';
Error - No need to specify the current password while changing it for
other users.
F-5: If the non-privileged user has 'Y' flag set in the table then
the 'change current password' policy must be enforced irrespective of the
value of global variable 'password_require_current'
mysql@root> CREATE USER u1 IDENTIFIED BY '123' PASSWORD REQUIRE CURRENT;
mysql@root> SHOW VARIABLES LIKE 'password_require_current';
password_require_current : ON/OFF
mysql@u1> ALTER USER USER() IDENTIFIED BY '789' REPLACE '123';
mysql@u1> SET PASSWORD='789' REPLACE '123';
F-5.1: Throw error in following cases.
mysql@u1> ALTER USER USER() IDENTIFIED BY '789';
mysql@u1> SET PASSWORD='789';
Error - Current password needs to be specified to change the password
F-6: If the non-privileged user has no flag set in the table(i.e NULL value)
then the 'change current password' policy must be in effect as per the
value of global variable 'password_require_current'
mysql@root> CREATE USER u1 IDENTIFIED BY '123' PASSWORD REQUIRE CURRENT
> DEFAULT;
mysql@root> SHOW VARIABLES LIKE 'password_require_current';
password_require_current : ON
mysql@u1> ALTER USER USER() IDENTIFIED BY '789' REPLACE '123';
mysql@u1> SET PASSWORD='789' REPLACE '123';
mysql@root> SHOW VARIABLES LIKE 'password_require_current';
password_require_current : OFF
mysql@u1> ALTER USER USER() IDENTIFIED BY '789';
mysql@u1> SET PASSWORD='789';
F-7: The non-privileged user do not need to specify the current password,
if the user is exempted(Refer D-1) from the 'change current password'
policy.
mysql@root> CREATE USER u1 IDENTIFIED BY '123' PASSWORD REQUIRE CURRENT
> OPTIONAL;
mysql@u1> ALTER USER USER() IDENTIFIED BY 'new_pwd';
mysql@u1> SET PASSWORD='new_pwd';
If the user still specifies the current password then it means user wants
to the current password to be verified intentionally through it is
optional
F7.1) If the current password matches then allow to change the password.
mysql@u1> ALTER USER USER() IDENTIFIED BY 'new_pwd' REPLACE
> 'current_pwd';
mysql@u1> SET PASSWORD='new_pwd' REPLACE 'current_pwd';
F7.2) If the current password mismatch/not found then error out.
mysql@u1> ALTER USER USER() IDENTIFIED BY 'new_pwd' REPLACE
> 'wrong_current_pwd';
mysql@u1> SET PASSWORD='new_pwd' REPLACE 'wrong_current_pwd';
Error - Incorrect current password. Specify the correct password.
F-8: Current password require preference must be changeable as following in
any order.
CREATE USER u1; or CREATE USER u1 PASSWORD REQUIRE CURRENT DEFAULT;
# It will set the column 'password_require_current' as 'NULL' in the
mysql.user table. It means that 'change current password' policy will
vary according to the value of server variable 'password_require_current'.
ALTER USER u1 PASSWORD REQUIRE CURRENT
# It will set the column 'password_require_current' as 'Y' in the
mysql.user table. It means that 'change current password' policy will
enforced for that user irrespective the value of the server variable
'password_require_current'.
ALTER USER u1 PASSWORD REQUIRE CURRENT DEFAULT
# It will set the column 'password_require_current' as 'NULL' in the
mysql.user table. It means that 'change current password' policy will
vary according to the value of server variable 'password_require_current'.
ALTER USER u1 PASSWORD REQUIRE CURRENT OPTIONAL
# It will set the column 'password_require_current' as 'N' in the
mysql.user table. It means that 'change current password' policy will
be enforced only if the user specify the current password.
Refer 'F-7' for examples.
F-9: Changing the variable will become effective immediately globally.
mysql@root> CREATE USER u1;
mysql@root> SET VARIABLE @@global.password_require_current='ON'
mysql@u1> SET PASSWORD='1234' REPLACE '4567';
mysql@u1> ALTER USER USER() IDENTIFIED BY '1234' REPLACE '4567';
mysql@root> SET VARIABLE @@global.password_require_current='OFF'
mysql@u1> SET PASSWORD='1234';
mysql@u1> ALTER USER USER() IDENTIFIED BY '1234';
F-10: Impact on replication :
F-10.1: The new clauses to CREATE/ALTER USER will be replicated into the
ACL statement text if specified in the original statement.
mysql@root> CREATE USER foo;
mysql@root> ALTER USER foo;
mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT;
mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT;
mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT DEFAULT;
mysql@root> ALTER USER foo IDENTIFIED_BY '1234'
> PASSWORD REQUIRE CURRENT DEFAULT;
Must be as following in the binlog.
CREATE USER foo;
ALTER USER foo;
CREATE USER foo PASSWORD REQUIRE CURRENT;
ALTER USER foo PASSWORD REQUIRE CURRENT;
CREATE USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
CREATE USER foo PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER foo IDENTIFIED_BY '1234' PASSWORD REQUIRE
CURRENT DEFAULT;
F-10.2: We must omit writing the REPLACE clause of
SET PASSWORD/ALTER USER in the binlog. This is to avoid having
the clear text current password.
We assume that the slave applier will run with enough privileges
that it won't require it in all cases.
mysql@foo> SET PASSWORD='1234' REPLACE '143';
mysql@foo> ALTER USER user() IDENTIFIED BY '1234' REPLACE '143';
Must be as follwing in the binlog:
SET PASSWORD='1234';
ALTER USER user() IDENTIFIED BY '1234';
F-11: Upgrade/Downgrade scenarios :
F-11.1: The 'mysql_upgrade' utility will add the 'password_require_current'
column in the mysql.user table with default value 'NULL' for each
user.
That means server must look for the value of the global variable
'password_require_current' at the time of changing own password.
F-11.2: 'CREATE|ALTER USER' must adhere to the 'PASSWORD REQUIRE' clause
if the mysql.user has the column 'password_require_current'.
In other words server must run fine even without the new column
'password_require_current' as long as there is no non-default
"...PASSWORD REQUIRE CURRENT..."|"...REPLACE..." clause specified
in the CREATE|ALTER|SET PASSWORD statements.
This will also mean that an 8.0.x server can run fine on a 8.0.x-1
database. If the any of the clause are specified, server must throw
an error.
F-11.3: 8.0.x-1 server must run on a database(created through 8.0.x server)
that has newly created column.
F-12: SHOW CREATE USER will return the PASSWORD REQUIRE CURRENT clause as it
will be configured at that moment. The same output must be considered for
any other client i.e. mysqlpump
Examples :
mysql@root> CREATE USER u1;
mysql@root> CREATE USER u1 PASSWORD REQUIRE CURRENT DEFAULT;
Both of the above statements will set the default value of
'PASSWORD REQUIRE CURRENT' clause.
mysql@root> SHOW CREATE USER u1\G
*************************** 1. row ***************************
CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
PASSWORD CURRENT REQUIRE DEFAULT
mysql@root> ALTER USER u1 PASSWORD REQUIRE CURRENT;
mysql@root> SHOW CREATE USER u1\G
*************************** 1. row ***************************
CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
PASSWORD CURRENT REQUIRE
mysql@root> ALTER USER u1 PASSWORD REQUIRE CURRENT OPTIONAL;
mysql@root> SHOW CREATE USER u1\G
*************************** 1. row ***************************
CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
PASSWORD CURRENT REQUIRE OPTIONAL
mysql@root> ALTER USER u1 IDENTIFIED BY '1234' PASSWORD REQUIRE CURRENT
> OPTIONAL;
mysql@root> SHOW CREATE USER u1\G
*************************** 1. row ***************************
CREATE USER for u1@%: CREATE USER 'u1'@'%' IDENTIFIED WITH
'caching_sha2_password' AS '$A$005$M^h¶ ])1;%Z↓<▼mz \r■^Zc0IxjKzDaeJz32
edtf0Bc2CJm/EMZbZpZraU.bvod7' REQUIRE NONE PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT
PASSWORD CURRENT REQUIRE OPTIONAL
F-13: When printing the CREATE USER statement in
audit log/performance schema/error log/query log/ the current password
will be masked out using exactly the same rules as the new password
i.e. it will depend on the --log-raw and query rewrite plugin installed.
F-14: Providing the incomplete SQL clause must result in usual error.
>CREATE USER user PASSWORD REQUIRE;
>ALTER USER user PASSWORD REQUIRE;
>ALTER USER user() IDENTIFIED BY 'pwd' REPLACE;
>SET USER user() IDENTIFIED BY 'pwd' REPLACE;
ERROR 1064 (42000): You have an error in your SQL syntax...
Interface Specification
-----------------------
I-1: Alter user syntax will be modified as following to define whether
'change current password' policy to be enforced or not for an user.
ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]]
[password_option]
auth_option: {
IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string']
IDENTIFIED WITH 'auth_plugin' BY 'auth_string'
[REPLACE 'current_auth_string']
}
password_option : {
PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}
Examples :
mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT;
mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT DEFAULT;
mysql@root> ALTER USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
mysql@foo> ALTER USER user() IDENTIFIED BY 'new_pwd';
mysql@foo> ALTER USER user() IDENTIFIED BY 'new_pwd'
> REPLACE 'current_pwd';
mysql@foo> ALTER USER foo IDENTIFIED BY 'new_pwd';
mysql@foo> ALTER USER foo IDENTIFIED BY 'new_pwd'
> REPLACE 'current_pwd';
mysql@foo> ALTER USER foo IDENTIFIED BY 'new_pwd'
> REPLACE 'current_pwd';
mysql@foo> ALTER USER foo IDENTIFIED WITH 'mysql_native_password'
> BY 'new_pwd' REPLACE 'current_pwd';
mysql@foo> ALTER USER foo IDENTIFIED WITH 'mysql_native_password'
> BY 'new_pwd' REPLACE 'current_pwd' PASSWORD EXPIRE;
I-2: Create user syntax will be modified as following to define whether
'change current password' policy to be enforced or not for an user.
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
[password_option]
password_option: {
PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}
The default value of 'PASSWORD REQUIRE CURRENT' clause is 'DEFAULT'.
Examples:
mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT;
mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT OPTIONAL;
mysql@root> CREATE USER foo PASSWORD REQUIRE CURRENT DEFAULT;
mysql@root> CREATE USER foo; # Same effect as of previous statement.
mysql@root> CREATE USER foo IDENTIDIED WITH 'mysql_native_password' BY
> '1234' PASSWORD REQUIRE CURRENT DEFAULT PASSWORD
> EXPIRE DEFAULT ACCOUNT UNLOCK;
mysql@root> CREATE USER foo IDENTIDIED BY '1234'
> PASSWORD REQUIRE CURRENT DEFAULT PASSWORD
> EXPIRE DEFAULT PASSWORD REUSE INTERVAL 360 DAY;
mysql@root> create user u1 identified by '12',
u2 identified by '34'
password require current;
I-3: Set password syntax will be modified as following to change the password
for self.
SET PASSWORD [FOR user] = password_option
password_option : {
'auth_string' [REPLACE 'auth_string']
}
Examples :
SET PASSWORD = 'new_pwd' REPLACE 'current_password';
SET PASSWORD FOR foo='new_pwd';
I-4: Following server variable will be added. It's global only.
Either of the SUPER/SYSTEM_VARIABLES_ADMIN privilege is require in order
to change it.
Name - password_require_current
Scope - Global
Variable type - boolean
Dynamic - Yes
Default - OFF
I-5: A new config option --password-require-current must be added corresponding
to the global variable added in I-4.
I-6: Following new column will be added in the mysql.user table.
Field: password_require_current
Type: enum('N','Y')
Null: Yes
Key: No
Default: NULL
High-Level Specification
------------------------
Here is simplified algorithm for this feature.
bool is_required_current_password(ACL_USER *user) {
if(user->flag_required_current_password == NULL)
return global_variable_password_require_current_value;
else if (user->flag_required_current_password == Yes)
return true;
else
return false;
}
bool is_current_pwd_valid(current_pwd) {
if(current_pwd == user->password)
return true;
else
return false;
}
bool can_skip_current_pwd = !is_required_current_password(user);
if () {
if (can_skip_current_pwd ||
user->has_CREATE_USER_priv ||
user->has_UPDATE_priv_ON_mysql_*) {
goto change_pwd_with_optional_current_pass;
} else {
goto change_pwd_with_required_current_pass;
}
} else {
goto dont_require_the_current_pass;
}
change_pwd_with_optional_current_pass:
{
if (SQL_command_has_REPLACE_pwd &&
!is_current_pwd_valid(pwd) {
error : "Incorrect current password. Specify the valid password."
} else {
change_password();
}
}
change_pwd_with_required_current_pass:
if (not SQL_command_has_REPLACE_pwd) {
error : "Current password is missing which is required to change the
password"
} else if (not is_current_pwd_valid(pwd)) {
error : "Incorrect current password. Specify the valid password."
}
else {
change_password();
}
dont_require_the_current_pass:
if (SQL_command_has_REPLACE_pwd) {
error: "No need to specify the current password while changing it for
other users."
} else {
change_password();
}
Error messages
---------------
ER_CURRENT_PASSWORD_NOT_REQUIRED
eng "Do not specify the current password while changing it for other users."
ER_INCORRECT_CURRENT_PASSWORD
eng "Incorrect current password. Specify the correct password which has to be
replaced."
ER_MISSING_CURRENT_PASSWORD
eng "Current password needs to be specified in the REPLACE clause in order to
change it."
Possible implementation
-----------------------
1. A following method is added which is called from the
set_and_validate_user_attributes() method.
/**
Checks, if the REPLACE clause is required, optional or not required.
It throws error:
If REPLACE clause is required but not specified.
If REPLACE clause is not required but specified.
If current password specified in the REPLACE clause does not match with
authentication string of the user.
The plaintext current password is erased from LEX_USER, iff its length > 0
.
@param thd The execution context
@param Str LEX user
@param acl_user The associated user which carries the ACL
@param auth Auth plugin to use for verification
@param is_privileged_user Whether caller has CREATE_USER_ACL
or UPDATE_ACL over mysql.*
@param user_exists Whether user already exists
@retval true operation failed
@retval false success
*/
static bool validate_password_require_current(THD *thd, LEX_USER *Str,
ACL_USER *acl_user,
st_mysql_auth *auth,
bool is_privileged_user,
bool user_exists) {}
2. Following code will insert the column value in the mysql.user table in the
existing replace_user_table() method
if (combo->alter_status.update_current_password != Current_password::NONE)
{
/* ALTER USER .. PASSWORD REQUIRE CURRENT */
if (table->s->fields > MYSQL_USER_FIELD_PASSWORD_REQUIRE_CURRENT) {
Field *fld = table->field[MYSQL_USER_FIELD_PASSWORD_REQUIRE_CURRENT];
Current_password cp = combo->alter_status.update_current_password;
if (cp == Current_password::DEFAULT) {
fld->set_null();
} else if (cp == Current_password::NOT_REQUIRE) {
fld->store("N", 1, system_charset_info);
fld->set_notnull();
} else if (cp == Current_password::REQUIRE) {
fld->store("Y", 1, system_charset_info);
fld->set_notnull();
} else {
my_error(ER_BAD_FIELD_ERROR, MYF(0), "password_require_current",
"mysql.user");
DBUG_RETURN(-1);
}
} else {
my_error(ER_BAD_FIELD_ERROR, MYF(0), "password_require_current",
"mysql.user");
DBUG_RETURN(-1);
}
}
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.