Select Page

Security Validation and Password Expiration in MySQL 5.7

Author: Srinivasa Krishna | | August 18, 2016

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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.