Beware: MySQL 5.6.6’s ALTER TABLE … PASSWORD EXPIRE today for a free data buffet!

The MySQL 5.6.6 binaries were released today so I decided to take a look at the changelog. I was looking through some of the interesting-sounding new password and security features when I saw this sentence: The mysql.user table now has a password_expired column. Its default value is ‘N’, but can be set to ‘Y’ with the new ALTER USER statement (which also sets the Password column to the empty string). That didn’t sound right. Why would causing a user’s password to expire set their existing password to an empty string? There are two ways I can see this feature working. One is that the user logs in using their old/expired password and is forced to set a new password before they can do anything. The second is that they not be allowed to log in at all, using any password. Unfortunately, a third path is taken in MySQL 5.6.6 where a user can log in with no password at all when their password is expired. Huh?! This is super easy to reproduce. Here’s my totally normal user called «mysql»:

mysql 5.6.6-m9 (root) [test]> select user,host,password,password_expired from mysql.user where user='mysql';
+-------+-----------+-------------------------------------------+------------------+
| user  | host      | password                                  | password_expired |
+-------+-----------+-------------------------------------------+------------------+
| mysql | localhost | *0D3CED9BEC10A777AEC23CCC353A8C08A633045E | N                |
+-------+-----------+-------------------------------------------+------------------+
1 row in set (0.00 sec)

He can log in totally normally:

$ ./bin/mysql -u mysql -pabc -e 'select current_user()'
Warning: Using a password on the command line interface can be insecure.
+-----------------+
| current_user()  |
+-----------------+
| mysql@localhost |
+-----------------+

And then I can expire his password:

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,password_expired from mysql.user where user='mysql';
+-------+-----------+----------+------------------+
| user  | host      | password | password_expired |
+-------+-----------+----------+------------------+
| mysql | localhost |          | Y                |
+-------+-----------+----------+------------------+
1 row in set (0.00 sec)

He can still log in using his old password, for the time being, as apparently ALTER USERdoesn’t cause the privilege tables to be flushed…

$ ./bin/mysql -u mysql -pabc -e 'select current_user()'
Warning: Using a password on the command line interface can be insecure.
ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement

So far things look pretty reasonable. Expiring the user’s password causes his next login attempt to result in him being forced to change his password. Great, looks like reasonable behavior. Except, as I said, it looked like ALTER USER didn’t cause the privilege tables to be flushed, as the old password still worked…

mysql 5.6.6-m9 (root) [test]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

kolbe@74-61-10-68 mysql-5.6.6-m9-osx10.7-x86_64 $ ./bin/mysql -u mysql -e 'select current_user()'
ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement
kolbe@74-61-10-68 mysql-5.6.6-m9-osx10.7-x86_64 $ ./bin/mysql -u mysql -e 'set password=password("uh oh...")'
kolbe@74-61-10-68 mysql-5.6.6-m9-osx10.7-x86_64 $ ./bin/mysql -u mysql -p"uh oh..." -e 'select * from production.all_your_data'
Warning: Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'production.all_your_data' doesn't exist

mysql 5.6.6-m9 (root) [test]> select user,host,password,password_expired from mysql.user where user='mysql';
+-------+-----------+-------------------------------------------+------------------+
| user  | host      | password                                  | password_expired |
+-------+-----------+-------------------------------------------+------------------+
| mysql | localhost | *68877531D6DF73C1A23D45AE3968638FFE15EB91 | N                |
+-------+-----------+-------------------------------------------+------------------+
1 row in set (0.00 sec)

So… if a user’s password expires and privileges are flushed or the server is restarted, the user is allowed to connect without any password at all, then change the password to whatever they want, and obviously perform any work that the privileges for that user allow. The behavior of setting the user’s password to an empty string is completely documented at http://dev.mysql.com/doc/refman/5.6/en/alter-user.html so this is not even strictly a bug. Nevertheless, I filed a bug report (http://bugs.mysql.com/bug.php?id=66250); I marked it as a security bug so it is entirely hidden from public view, however. I fully expect that Oracle will change the behavior of this feature very, very soon. The reasonable thing to do would be to leave the Password column untouched, but set the password_expired column so that the user is forced to change his or her password on the next connection. Even when they do change the behavior of ALTER USER ... PASSWORD EXPIRE, though, I’m not sure what the point of this feature really is. There is no column in mysql.user to indicate when a user last changed his or her password, much less to specify an expiration date, so it’s not really possible to create an event or cronjob that would handle automatic password expiration. In its current form, aside from being a hideous and monumental security problem, this feature doesn’t make any sense and isn’t fully-baked enough to be useful, in my opinion. Nobody should be using MySQL 5.6 in production at this stage, but even fewer people should be using its ALTER USER ... PASSWORD EXPIRE feature.