Batchmodeandexpiredpasswords_MySQL

Aseriesofrelateddiscussionstriggered by difficulty in setting passwords via scripts using the mysql command-line client when an account has anexpired passwordcaused me to look into the interaction between expired passwords and batch mode, and this blog post resulted. I hope it’s a useful explanation of the behavior and the workaround to those troubled by it, and amplifies the excellent documentation in the user manual.

The ability to flag accounts as having expired passwords first appeared in MySQL 5.6, with furtherimprovements made in MySQL 5.7. When an account is flagged with an expired password, it enters what the manual refers to as a “sandbox mode.” Connections are allowed, but operations are restricted until the SET PASSWORD statement is issued. It’s important to realize that this security feature is entirely focused on password maintenance – it isnot an appropriate mechanism for temporarily locking out users. While the sandbox mode shares certain characteristics with a locked account, in that users are unable to accomplish real work, it can be immediately bypassed by the affected user by issuing SET PASSWORD. That there are no controls to prohibit users from simply issuing SET PASSWORD = PASSWORD(‘same password’) – or even setting a blank password – is a topic for another discussion, but serves to highlight that the expired password mechanism is wholly unsuitable for general account locking.

The sandbox mode is clearly targeted at interactive users – somebody who can process the error messages related to expired passwords on an established connection and know how to resolve them:

mysql> SELECT 1; ERROR 1820 (HY000): You must SET PASSWORD before executing this statement mysql> SET PASSWORD = PASSWORD('testpwd'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+1 row in set (0.00 sec) mysql>

This sandbox mode can create all sorts of problems for non-interactive clients. For example, a connection pool library may establish a connection and assume the absence of errors in creating the connection indicates a valid connection to hand out to application threads. A batch job may not have adequate error checking. Consequently, the sandbox mode only applies to clients which indicate they can deal with it. On the protocol level, this is done by setting the client capability flag, CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS. Different connectors/APIs will have different ways to set this capability flag:

• In the C API, this is done by setting the MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS option usingmysql_options()
• PHP, via mysqli,honors the same option
• Using Connector/JAVA, setting thedisconnectOnExpiredPasswords propertyto false
• Connector/ODBC has acan_handle_exp_pwd option

Most applications won’t be able to deal with expired password sandbox mode, and the default is universally to not set this flag. The resulting behavior is that the server authenticates the user, but sends an error message in response and terminates the connection. This is also the expected behavior when using older (pre-5.6.10) clients such as mysql – they are unaware of the new client capabilities flag, and do not indicate they can support password expiration sandbox mode.

The mysql client differentiates between batch and interactive mode when determining whether to set the capabilities flag. Notably for the discussion which prompted this blog post, if you use the -e option to specify a statement to execute, the client connects in batch mode (from client/mysql.cc):

case 'e':status.batch= 1;status.add_to_history= 0;

This is also set with the -B option.

When the connection is initialized, we find this code:

my_bool handle_expired= (opt_connect_expired_password || !status.batch) ?TRUE : FALSE;

The result is that trying to connect using the mysql client and -e with an account having an expired password will produce an error:

R:/ade/mysql-5.6.19-winx64>bin/mysql -uexptest -P3307 -e"SELECT 1;"ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

This can be circumvented by explicitly indicating expired passwords can be handled, using the–connect-expired-password option:

R:/ade/mysql-5.6.19-winx64>bin/mysql -uexptest -P3307 /-e"SET PASSWORD='';" --connect-expired-password

The documentation also notes how other standard clients determine whether to set the capability flag or not:

MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDSis enabled formysqltestunconditionally, formysqlin interactive mode, and formysqladminif the first command ispassword.

If you disagree with the decision to block connections as they are established when the capability flag is not set, you can control this on the server side using the –disconnect_on_expired_password option (set it to OFF; default is ON). This causes connections to be established in sandbox mode regardless of the capabilities indicated by the client:

R:/ade/mysql-5.6.19-winx64>bin/mysql -uexptest -P3307 -e"SELECT 1;"ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement

As seen in the example above, the sandbox mode still applies – so your application code will need to be prepared to deal with it.

The blog posts cited at the beginning of this post highlight where this may be most frequently observed – following an RPM installation of MySQL Server 5.6. Because RPMs limit interactivity, it’s not feasible to prompt users for a root password. Consequentially, the RPM installation assigns random passwords to the root accounts and flags them with expired passwords, prompting users to change the password on first use. This is described in themanual page on RPM installations:

As of MySQL 5.6.8, new RPM install operations (not upgrades) invokemysql_install_dbwith the--random-passwordsoption that provides for more secure MySQL installation. Invokingmysql_install_dbwith--random-passwordscauses it to assign a random password to the MySQLrootaccounts, set the“password expired”flag for those accounts, and not create anonymous-user MySQL accounts. It will be necessary after installation to start the server, connect asrootusing the password written to the$HOME/.mysql_secretfile, and assign a newrootpassword. Until this is done,rootcannot do anything else.

Users scripting RPM deployments of MySQL may want to script password updates as a post-install step. Those doing so should use mysqladmin, or may use mysql –connect-expired-password -e.