| Bug #66250 | ALTER USER ... PASSWORD EXPIRE allows open access to account | ||
|---|---|---|---|
| Submitted: | 7 Aug 2012 20:42 | Modified: | 20 Aug 2012 17:58 |
| Reporter: | Kolbe Kegel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S2 (Serious) |
| Version: | 5.6.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Aug 2012 11:50]
Mark Leith
This has been fixed, patch that fixed this:
3968 Georgi Kodinov 2012-07-04
Bug #14226518: ALTER USER ... PASSWORD EXPIRE HAS CONFUSING
AFTEREFFECTS
Made sure ALTER USER ... PASSWORD EXPIRE doesn't clear the
password neither in the table nor into the in-memory cache of it.
Test added.
added:
internal/mysql-test/suite/i_main/r/connect.result
internal/mysql-test/suite/i_main/t/connect.test
modified:
sql/sql_acl.cc
=== added file 'internal/mysql-test/suite/i_main/r/connect.result'
--- a/internal/mysql-test/suite/i_main/r/connect.result 1970-01-01 00:00:00 +0000
+++ b/internal/mysql-test/suite/i_main/r/connect.result 2012-07-04 15:30:17 +0000
@@ -0,0 +1,37 @@
+#
+# Bug #14226518: ALTER USER ... PASSWORD EXPIRE HAS CONFUSING
+# AFTEREFFECTS
+#
+CREATE USER g1@localhost identified by 'g1pass';
+SELECT LENGTH(password) FROM mysql.user WHERE Host = 'localhost' AND User = 'g1';
+LENGTH(password)
+41
+ALTER USER g1@localhost PASSWORD EXPIRE;
+SELECT LENGTH(password) FROM mysql.user WHERE Host = 'localhost' AND User = 'g1';
+LENGTH(password)
+41
+SELECT USER();
+USER()
+g1@localhost
+SELECT USER();
+ERROR HY000: You must SET PASSWORD before executing this statement
+FLUSH PRIVILEGES;
+SELECT USER();
+USER()
+g1@localhost
+SELECT USER();
+ERROR HY000: You must SET PASSWORD before executing this statement
+SET PASSWORD = PASSWORD('g1newpass');
+SELECT USER();
+USER()
+g1@localhost
+SELECT USER();
+USER()
+g1@localhost
+SELECT LENGTH(password) FROM mysql.user WHERE Host = 'localhost' AND User = 'g1';
+LENGTH(password)
+41
+DROP USER g1@localhost;
+# ------------------------------------------------------------------
+# -- End of 5.6 tests
+# ------------------------------------------------------------------
=== added file 'internal/mysql-test/suite/i_main/t/connect.test'
--- a/internal/mysql-test/suite/i_main/t/connect.test 1970-01-01 00:00:00 +0000
+++ b/internal/mysql-test/suite/i_main/t/connect.test 2012-07-04 15:30:17 +0000
@@ -0,0 +1,61 @@
+# This test makes no sense with the embedded server
+--source include/not_embedded.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+
+--echo #
+--echo # Bug #14226518: ALTER USER ... PASSWORD EXPIRE HAS CONFUSING
+--echo # AFTEREFFECTS
+--echo #
+
+CREATE USER g1@localhost identified by 'g1pass';
+SELECT LENGTH(password) FROM mysql.user WHERE Host = 'localhost' AND User = 'g1';
+
+connect(g1_conn_before,localhost,g1,g1pass,test);
+
+connection default;
+ALTER USER g1@localhost PASSWORD EXPIRE;
+SELECT LENGTH(password) FROM mysql.user WHERE Host = 'localhost' AND User = 'g1';
+connect(g1_conn_after,localhost,g1,g1pass,test);
+
+connection g1_conn_before;
+SELECT USER();
+
+connection g1_conn_after;
+--error ER_MUST_CHANGE_PASSWORD
+SELECT USER();
+
+connection default;
+FLUSH PRIVILEGES;
+
+connection g1_conn_before;
+SELECT USER();
+
+connection g1_conn_after;
+--error ER_MUST_CHANGE_PASSWORD
+SELECT USER();
+SET PASSWORD = PASSWORD('g1newpass');
+
+connection g1_conn_before;
+SELECT USER();
+
+connection g1_conn_after;
+SELECT USER();
+
+connection default;
+SELECT LENGTH(password) FROM mysql.user WHERE Host = 'localhost' AND User = 'g1';
+
+disconnect g1_conn_before;
+disconnect g1_conn_after;
+DROP USER g1@localhost;
+
+
+--echo # ------------------------------------------------------------------
+--echo # -- End of 5.6 tests
+--echo # ------------------------------------------------------------------
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
+
=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc 2012-06-20 09:19:32 +0000
+++ b/sql/sql_acl.cc 2012-07-04 15:30:17 +0000
@@ -867,7 +867,7 @@ static bool update_user_table(THD *, TAB
const char *new_password,
uint new_password_len,
enum mysql_user_table_field password_field,
- const char must_expire);
+ bool password_expired);
static my_bool acl_load(THD *thd, TABLE_LIST *tables);
static my_bool grant_load(THD *thd, TABLE_LIST *tables);
static inline void get_grantor(THD *thd, char* grantor);
@@ -2443,7 +2443,7 @@ bool change_password(THD *thd, const cha
if (update_user_table(thd, table,
acl_user->host.get_host() ? acl_user->host.get_host() : "",
acl_user->user ? acl_user->user : "",
- new_password, new_password_len, password_field, 'N'))
+ new_password, new_password_len, password_field, false))
{
mysql_mutex_unlock(&acl_cache->lock); /* purecov: deadcode */
goto end;
@@ -2633,7 +2633,7 @@ update_user_table(THD *thd, TABLE *table
const char *host, const char *user,
const char *new_password, uint new_password_len,
enum mysql_user_table_field password_field,
- const char password_expired)
+ bool password_expired)
{
char user_key[MAX_KEY_LENGTH];
int error;
@@ -2658,17 +2658,25 @@ update_user_table(THD *thd, TABLE *table
DBUG_RETURN(1); /* purecov: deadcode */
}
store_record(table,record[1]);
-
- table->field[(int) password_field]->store(new_password, new_password_len,
- system_charset_info);
- if (new_password_len == SCRAMBLED_PASSWORD_CHAR_LENGTH_323 &&
- password_field == MYSQL_USER_FIELD_PASSWORD)
+
+ /*
+ When the flag is on we're inside ALTER TABLE ... PASSWORD EXPIRE and we
+ have no password to update.
+ */
+ if (!password_expired)
{
- WARN_DEPRECATED_41_PWD_HASH(thd);
+ table->field[(int) password_field]->store(new_password, new_password_len,
+ system_charset_info);
+ if (new_password_len == SCRAMBLED_PASSWORD_CHAR_LENGTH_323 &&
+ password_field == MYSQL_USER_FIELD_PASSWORD)
+ {
+ WARN_DEPRECATED_41_PWD_HASH(thd);
+ }
}
/* password_expired */
- table->field[MYSQL_USER_FIELD_PASSWORD_EXPIRED]->store(&password_expired, 1,
+ table->field[MYSQL_USER_FIELD_PASSWORD_EXPIRED]->store(password_expired ?
+ "Y" : "N", 1,
system_charset_info);
if ((error=table->file->ha_update_row(table->record[1],table->record[0])) &&
@@ -7730,7 +7738,7 @@ bool mysql_user_password_expire(THD *thd
acl_user->host.get_host() ?
acl_user->host.get_host() : "",
acl_user->user ? acl_user->user : "",
- NULL, 0, password_field,'Y'))
+ NULL, 0, password_field, true))
{
result= true;
append_user(thd, &wrong_users, user_from, wrong_users.length() > 0,
[20 Aug 2012 17:58]
Paul DuBois
Documentation is updated. http://dev.mysql.com/doc/refman/5.6/en/alter-user.html
Description: I noticed the new ALTER USER ... PASSWORD EXPIRE feature in the MySQL 5.6.6 changelog and thought the "also sets the Password column to the empty string" part of the behavior couldn't possibly be corrrect. Sure enough, though, executing ALTER USER ... PASSWORD EXPIRE sets a user's password to the empty string and allows open (i.e. no password) access to the account. Note that ALTER USER does not, apparently, cause privilege tables to be flushed, so no-password access to the user account is not possible until either a) FLUSH PRIVILEGES is executed or b) the MySQL instance is restarted. This is an enormous security problem and cannot possibly be the intended behavior of this feature. How to repeat: mysql 5.6.6-m9 (root) [test]> select user,host,password from mysql.user; +-------+-----------------------------------+-------------------------------------------+ | user | host | password | +-------+-----------------------------------+-------------------------------------------+ | root | localhost | | | root | myhostname | | | root | 127.0.0.1 | | | root | ::1 | | | mysql | localhost | *0D3CED9BEC10A777AEC23CCC353A8C08A633045E | +-------+-----------------------------------+-------------------------------------------+ 5 rows in set (0.00 sec) mysql 5.6.6-m9 (root) [test]> alter user 'mysql'@'localhost' password expire; Query OK, 0 rows affected (0.00 sec) mysql 5.6.6-m9 (root) [test]> select user,host,password from mysql.user; +-------+-----------------------------------+----------+ | user | host | password | +-------+-----------------------------------+----------+ | root | localhost | | | root | myhostname | | | root | 127.0.0.1 | | | root | ::1 | | | mysql | localhost | | +-------+-----------------------------------+----------+ 5 rows in set (0.00 sec) ... user@myhostname mysql-5.6.6-m9-osx10.7-x86_64 $ ./bin/mysql -u mysql ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: NO) ... mysql 5.6.6-m9 (root) [test]> flush privileges; Query OK, 0 rows affected (0.00 sec) ... user@myhostname mysql-5.6.6-m9-osx10.7-x86_64 $ ./bin/mysql -u mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Server version: 5.6.6-m9 Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6.6-m9 (mysql) [test]> select current_user(); ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql 5.6.6-m9 (mysql) [test]> set password = password('haha my new account'); Query OK, 0 rows affected (0.00 sec) Suggested fix: ALTER USER ... EXPIRE PASSWORD should *not* make any change to Password column in mysql.user. If Password is left alone, the user can log in using their old password but must execute SET PASSWORD before they are allowed to perform any other work. Surely that is the only sensible way for this feature to work.