ALTER USER … PASSWORD EXPIRE – bitten by a bug

MySQL 5.6.6 was released yesterday – the list of changes is impressive.

One of the new things added within 5.6.6 was the ALTER USER … PASSWORD EXPIRE statement, which allows an administrator to expire the passwords for a user, so that they must update their password on next login.

Unfortunately, this was released within 5.6.6 with a serious problem – already noted by Kolbe Kegel – as it updated the password column to an empty string, as well as setting the password_expired flag. This obviously has serious implications, that really means you should not use this feature, if you are planning on using 5.6.6 in production (for whatever reason, given that it is not a GA release yet).

The good news is that this was caught and fixed around a month ago, but didn’t quite make the 5.6.6 cut off date (where we build, and put the release through QA testing) – here’s the same kind of test on my self-built 5.6.7 release:

$ mysql -uroot -pmysql -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 152259
Server version: 5.6.7 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> grant all on *.* to mark@localhost identified by 'mark';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password, password_expired from mysql.user;
+-----------------+-----------+-------------------------------------------+------------------+
| user            | host      | password                                  | password_expired |
+-----------------+-----------+-------------------------------------------+------------------+
| root            | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | N                |
| mark            | localhost | *E6ACCEDB2495496B191ED488F598F04239C85E73 | N                |
+-----------------+-----------+-------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> alter user mark@localhost password expire;
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password, password_expired from mysql.user;
+-----------------+-----------+-------------------------------------------+------------------+
| user            | host      | password                                  | password_expired |
+-----------------+-----------+-------------------------------------------+------------------+
| root            | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | N                |
| mark            | localhost | *E6ACCEDB2495496B191ED488F598F04239C85E73 | Y                |
+-----------------+-----------+-------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password, password_expired from mysql.user;
+-----------------+-----------+-------------------------------------------+------------------+
| user            | host      | password                                  | password_expired |
+-----------------+-----------+-------------------------------------------+------------------+
| root            | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | N                |
| mark            | localhost | *E6ACCEDB2495496B191ED488F598F04239C85E73 | Y                |
+-----------------+-----------+-------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
$ mysql -umark -P3307
ERROR 1045 (28000): Access denied for user 'mark'@'localhost' (using password: NO)
$ mysql -umark -pmark -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 163413
Server version: 5.6.7

Copyright (c) 2000, 2011, 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> select * from mysql.user;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password = password('newmark');
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------+
| Grants for mark@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mark'@'localhost' IDENTIFIED BY PASSWORD '*00B6543E480F70E68EB0FE311882F1B32E7EEF43' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select password('newmark');
+-------------------------------------------+
| password('newmark')                       |
+-------------------------------------------+
| *00B6543E480F70E68EB0FE311882F1B32E7EEF43 |
+-------------------------------------------+
1 row in set (0.00 sec)

So, please don’t use this feature until 5.6.7 is released! We’re sorry that this problem slipped in to the milestone release, but thankfully it was caught before our GA release, both with our own internal testing, and external community interest (thanks Kolbe!).

The documentation will be updated to reflect this in the near future as well.

8 comments

  1. I am really looking forward to 5.6 GA. If community interest remains as strong as it has been, then hopefully many minor bugs like this will be caught, and that will help 5.6 repeat 5.5’s story of a high-quality initial GA release.

    And, a lot of kudos to the internal QA team, whom I assume catch an order of magnitude more bugs than the community.

  2. Mark – is it only on next login? or before they can do anything? I’m thinking of a case where there’s a script that uses the same credentials, and it logs in before an interactive session does.

    • The script will fail, with the error message shown above. You’ll need the interactive session to update the password before the script will be able to run properly again.

      • Hi, Ok. And that’s usefull how? I know MySQL best for it’s use in hosting, so when a password expires, the site is shut down and probably unwanted?

        • Actually, MySQL is used in all sorts of environments, not just shared hosting. I would not imagine this feature would be used by a hosting provider at all (purely because it would shut down sites).

          That doesn’t stop it being useful to other companies that use MySQL for their own internal applications, however.

Got something to say?