Notes on ALTER USER … PASSWORD EXPIRE
I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6. There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned. Here’s a quick summary:
You can use ALTER USER … PASSWORD EXPIRE in prepared statements as of 5.6.8-rc
This is important because there’s no other way to dynamically bind ALTER USER statements to a user name and host, which is necessary if you are trying to automate anything related to password policies. This wasn’t the case with earlier 5.6 releases, but was fixed in 5.6.8-rc:
mysql> SELECT password_expired -> FROM mysql.user -> WHERE user = 'root' AND host = 'localhost'; +------------------+ | password_expired | +------------------+ | N | +------------------+ 1 row in set (0.00 sec) mysql> SET @sql = 'ALTER USER root@localhost PASSWORD EXPIRE'; Query OK, 0 rows affected (0.00 sec) mysql> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.02 sec) Statement prepared mysql> EXECUTE stmt; Query OK, 0 rows affected (0.02 sec) mysql> SELECT password_expired -> FROM mysql.user -> WHERE user = 'root' AND host = 'localhost'; +------------------+ | password_expired | +------------------+ | Y | +------------------+ 1 row in set (0.00 sec)
Don’t assume that ALTER USER … PASSWORD EXPIRE will result in a changed password
The documentation is pretty clear about the fact that password expiration causes restriction of access until the SET PASSWORD is executed against the affected user account. That’s not the same as saying the password must be changed, though. This means that you can unlock the account by issuing SET PASSWORD but using the same password:
D:\mysql> bin\mysql -uexp_user -P3307 -pTest12# Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.9 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> select 1; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql> SET PASSWORD = PASSWORD('Test12#'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql>
In fact, it’s possible to issue SET PASSWORD = ” (even with the password verification plugin enabled). If you need to ensure passwords get changed, you’ll need to look at implementing your own password policy tools that checks for password reuse – perhaps similar to the one I document here.
Which leads me to…
There’s no corresponding ALTER USER … PASSWORD VALID command
Which is kind of annoying if you want to restore a user. There are a couple ways to do this, though. The best way leverages the behavior above – a user can simply issue SET PASSWORD with the current password to restore the user access. If you are a DBA trying to do this for an application user account, you don’t even need to know the password – you can use the password hash:
mysql> select user, host, password, password_expired from mysql.user -> WHERE user = 'app'\G *************************** 1. row *************************** user: app host: localhost password: *13299BEDAE0290A310EF8DC73EC9CD4D5B916309 password_expired: Y 1 row in set (0.00 sec) mysql> SET PASSWORD FOR app@localhost = '*13299BEDAE0290A310EF8DC73EC9CD4D5B916309'; Query OK, 0 rows affected (0.00 sec) mysql> select user, host, password, password_expired from mysql.user -> WHERE user = 'app'\G *************************** 1. row *************************** user: app host: localhost password: *13299BEDAE0290A310EF8DC73EC9CD4D5B916309 password_expired: N 1 row in set (0.00 sec)
You can also do a direct UPDATE against the mysql.user system table, followed by FLUSH PRIVILEGES. Of course, direct manipulation of the privilege tables is discouraged, but there are situations where this may be necessary (for example, SHA-256 passwords are not stored as hex, making it virtually impossible to use the hash as an argument to SET PASSWORD for accounts configured for SHA-256).
Expired passwords can cause serious problems for application accounts
The current implementation allows connections to be established, but restricts execution of statements other than those necessary to change passwords. This makes sense when you’re dealing with interactive clients; the goal is to force the user to maintain their password. It will cause problems for any accounts used by an application, though. Because there’s generally no indication of the problematic state (some connectors will notice because boilerplate connection set-up statements will fail), the driver may be unaware that the connection is effectively “unusable”. When the application goes to use the connection, it will consistently get errors (unless it’s actually maintaining it’s own password).
More problematic are cases related to connection pools. Because the only indications of unusable connection state are the errors from statement execution, the connection pool isn’t likely to see the connection as “dead” (unless it executes a validation query which fails) – so it may well not remove these unusable connections from a pool. At the same time, the documentation is very clear that clearing the password expiration flag will only affect new connections – so connection pools with persistent connections may live with connections the application cannot use – but which the pool library sees as “alive” – until the pool is flushed (possibly with an application restart). That’s a pretty significant impact – you’ll want to make absolutely sure that you don’t issue ALTER USER … PASSWORD EXPIRE on an account that may be used by an application – particularly one that leverages persistent connections or connection pools.
In an effort to make the impact of connecting using an account with an expired password less problematic for non-interactive clients, we’re looking at refusing the connection unless a new client capabilities flag is set, indicating that the client can handle this use case. That means that legacy clients (and driver) will see connections refused up front, instead of establishing connections that restrict behavior (which may only be discovered by the application at a later stage). Clients and drivers – such as the mysql command-line interface – will add support for the new capabilities flag as appropriate, and users who find themselves unable to connect due to an expired password will have two options to re-enable access:
- Connect using a new client that supports the new capabilities flag and change the password
- Connect as another user with appropriate privileges and issue SET PASSWORD for the affected to clear the expired password flag.
These were things that came up as I worked through creation of a password policy for MySQL, and I’ll be sharing an updated version of that code that incorporates some of this soon. In the meantime, I hope this information proves useful in clarifying how ALTER USER … PASSWORD EXPIRE can be used effectively, as well as problem areas you will want to avoid.