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:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:5.6.6 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[7 Aug 2012 20:42] Kolbe Kegel
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.
[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