Detail MySQL user password expiration function

Payment Card Industry is the payment card industry. The PCI industry represents debit cards, credit cards, prepaid cards, e-wallets, ATM and POS cards and related businesses.
PCI DSS, or PCI Data Security standard (Payment Card Industry Data Security Standard) was developed by the PCI Safety Standards Committee to enable the international adoption of 1 to 1 data security measures.

The PCI DSS standard requires users to change their passwords every 90 days. So how does the MySQL database fit into this situation? Fortunately, as of version 5.6.6 of MySQL, the password_expired feature has been added, which allows you to set the expiration time of the user.

This feature has been added to the ES28en.user data table, but its default value is "N". You can modify this value using the ALTER USER statement.

Here's a simple example of how to set an expiration date for an MySQL user account:

mysql> ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE;

1 Once this option is set to "Y", the user can still log in to the MySQL server, but cannot run any query until the user has set a new password, and will get the following error message:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
Keep in mind that this does not affect any current connections the account has open.

When the user sets a new password, all actions of the user (based on the user's own permissions) are allowed:

mysql> SET PASSWORD=PASSWORD('mechipoderranen');
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| data        |
| logs        |
| mysql       |
| performance_schema |
| test        |
+--------------------+
6 rows in set (0.00 sec)
mysql>

DBA can use the cron timer task to set the expiration time of MySQL users' passwords.

Starting with version 5.7.4 of MySQL, the user's password expiration time feature has been improved by setting the password expiration policy using a global variable, default_password_lifetime. This global variable can set a global automatic password expiration policy.

Usage examples:
You can set a default value in the MySQL configuration file that will cause all MySQL users to have their passwords expire in 90 days, and MySQL will calculate the time from startup time. my. cnf configuration is as follows:

[mysqld]
default_password_lifetime=90

If you want to set the global policy that passwords never expire, you can do this :(note that this is the default value and can be undeclared in the configuration file)

[mysqld]
default_password_lifetime=0

You can modify this configuration at MySQL runtime using super permissions:

mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

You can also use the ALTER USER command to set a specific value separately for each specific user account, which automatically overrides the global policy of password expiration. Note that the INTERVAL unit of the ALTER USER statement is "day".

ALTER USER  ' testuser'@ ' localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

Disable password expiration:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;

Let users use the default password expiration global policy:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;

Starting with version 5.7.6 of MySQL, you can also use the ALTER USER statement to change the user's password:

mysql> ALTER USER USER() IDENTIFIED BY '637h1m27h36r33K';
Query OK, 0 rows affected (0.00 sec)

Afterword.

New features to lock/unlock user accounts have been added to MySQL 5.7.8 start user management, related to user management is locking/unlocking user accounts when CREATE USER, or at a later time running the ALTER USER statement.

Create a user with an account lock:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
Keep in mind that this does not affect any current connections the account has open.

0

As shown below, the newly created user will get an ERROR 3118 error message when trying to log in:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
Keep in mind that this does not affect any current connections the account has open.

1

You need to use ALTER USER... ACCOUNT UNLOCK statement unlocks:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
Keep in mind that this does not affect any current connections the account has open.

2

Now the user is unlocked and ready to log in:

$ mysql -ufurrywall -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.7.8-rc MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>

You can also lock user accounts like this:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
Keep in mind that this does not affect any current connections the account has open.

4

Above is the introduction of MySQL user password expiration function related content, I hope to help you learn.