Security Validation and Password Expiration in MySQL 5.7

Databases can be a treasure trove of valuable and sensitive information as they contain confidential information, personal data and intellectual property. Stolen or lost customer data can result in competitive disadvantage, brand damage, serious fines, and even lawsuits.

That’s why security validation and password expiration features, newly improved for MySQL 5.7, have become so important in today’s data landscape. For more information on MySQL 5.7, download Datavail’s new whitepaper, MySQL 5.7 Features, Enhancements and Upgrade Path. The whitepaper discusses enhancements, security plugins and benefits of upgrading to MySQL 5.7. This blog post focuses on MySQL 5.7’s improved features of security validation and password expiration.

Security validation

Security validation in MySQL 5.7 has been improved with the validate_password plugin that can be used to test password strength policies and specify the password-length and password-complexity security policies. The mysql_no_login prevents all clients’ connections to an account that uses it.

The clear text value of a given password is checked against the current policy for the password and is rejected if found weak. The use of VALIDATE_PASSWORD_STRENGTH() SQL function statement can be used to assess the strength of potential passwords by taking a password argument and returning an integer from 0 (weak) to 100 (strong).

Password expiration

Database administrators are now able to expire account passwords manually in MySQL 5.7, as well as establish a policy for automatic password expiration. The manual expiration of password can be achieved by the database administrator through the use of the ALTER USER statement as shown in the example: ALTER USER ‘peter’@’localhost’ PASSWORD EXPIRE;As a result, the expired password gets marked in the corresponding mysql.user table row.

MySQL 5.7.4 and later versions have seen the introduction of the automatic password expiration. This can be set by: default_password_lifetime in my.cnf. The details of each account are indicated in the mysql.user table, including when the password was last changed. If its lifetime is past the permitted time frame, the server automatically treats the password as expired.

You can also configure the password to be changed in a certain number of days by using: ALTER USER ‘foo’@’localhost’ PASSWORD EXPIRE INTERVAL n Day;

The global automatic password expiration is defined by the default_password_lifetime system-variable policy and is applicable to accounts that use MySQL built-in authentication methods. One can establish a global policy for passwords to have a lifetime of a given time frame. The global policy can be overridden for individual accounts with ALTER USER:

When a connection is established successfully by a client, the server determines if the account password is expired. The password gets checked to determine if it has been manually expired by the server and, if so, the session gets restricted.

Based on the automatic password expiration policy, the server checks if the password is past its lifetime. In this case, the server considers the password expired and restricts the session.