Detailed Mysql user password expiration function _mysql

Payment card Industry, the payment cards industry, the PCI industry represents debit cards, credit cards, prepaid cards, electronic wallets, ATM and POS cards and related businesses.
The PCI DSS, the PCI Data Security standard (Payment card Industry, Standard), is developed by the PCI Security Standards Board, designed to allow consistent data security in the world.

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

This feature has been added to the Mysql.user datasheet, but its default value is "N". You can use the ALTER USER statement to modify this value.

Here is a simple example of how to set the expiration date for a MySQL user account:

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

Once a user has this option set to "Y", the user can still log on to the MySQL server, but cannot run any queries before 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 the This statement Keep in mind that this
does not affect any C Urrent connections The account has open.

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

mysql> SET Password=password (' Mechipoderranen ');
Query OK, 0 rows Affected (0.00 sec)
mysql> show DATABASES;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| Data        |
| logs        | |
mysql       |
| performance_schema |
|
+--------------------+
6 rows in Set (0.00 sec)
mysql>

The DBA can set the MySQL user's password expiration time through the Cron timer task.

Starting with the MySQL 5.7.4 version, the user's password expiration time is improved, and a global variable default_password_lifetime can be used to set the password expiration policy, which sets a global automatic password expiration policy.

Usage examples:
You can set a default value in the MySQL configuration file, which will allow all MySQL users to expire their passwords by 90 days, and MySQL will start counting time from startup. The MY.CNF configuration is as follows:

[Mysqld]
Default_password_lifetime=90

If you want to set a global policy that passwords never expire, you can do this: (Note that this is the default value, which can be declared in a configuration file)

[Mysqld]
Default_password_lifetime=0

You can modify this configuration with super permissions while MySQL is running:

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

You can also use the ALTER USER command to set specific values individually for each specific user account, which automatically overwrites the global policy for password expiration. Note that the interval unit of the ALTER USER statement is "days."

ALTER USER ' testuser ' @ ' localhost ' PASSWORD EXPIRE INTERVAL Day;

Disable password expiration:

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

Let the user use the default password expiration global policy:

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

Starting with the MySQL 5.7.6 version, you can also modify the user's password by using the ALTER USER statement:

mysql> ALTER User User () identified by ' 637h1m27h36r33k ';
Query OK, 0 rows Affected (0.00 sec)

Postscript

New features for locking/unlocking user accounts are added to the MySQL 5.7.8 version to start user management, related to user management are locking/unlocking user accounts when CREATE user, or At a later time running the ALTER USER statement.

The following creates a user with an account lock:

mysql> CREATE USER ' furrywall ' @ ' localhost ' identified by ' 71m32ch4n6317 ' account LOCK;
Query OK, 0 rows Affected (0.00 sec)

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

$ mysql-ufurrywall-p
Enter Password:
ERROR 3118 (HY000): Access denied for user ' furrywall ' @ ' localhost '. The account is locked.

Now you need to use ALTER user ... The account unlock statement is unlocked:

Mysql>alter USER ' furrywall ' @ ' localhost ' account UNLOCK;
Query OK, 0 rows Affected (0.00 sec)

Now the user has unlocked it and can log in:

$ mysql-ufurrywall-p
Enter Password:
Welcome to the MySQL monitor. Commands End With; or G.
Your MySQL Connection ID
is server version:5.7.8-rc mysql Community server (GPL)
Copyright (c), 2015, Ora CLE and/or its affiliates. All rights reserved.
Oracle is a registered trademark to Oracle Corporation and/or its
affiliates. The other names may is 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> ALTER USER ' furrywall ' @ ' localhost ' account LOCK;
Query OK, 0 rows Affected (0.00 sec)

The above is for you to introduce the MySQL user password expiration features related content, I hope to help you learn.