MySQL User password expiration function-MySQL

This article describes in detail the user password expiration function of MySQL. For more information, see Payment Card IndustryThat is, the payment card industry. the PCI industry represents debit cards, credit cards, pre-payment cards, e-wallets, ATM and POS cards and related businesses.
Pci dss,That is, the PCI Data Security Standard (Payment Card Industry Data Security Standard) is developed by the PCI Security Standards Board to enable international adoption of consistent Data Security measures.

Pci dss standards require users to change their passwords every 90 days. So how can the MySQL database adapt to this situation? Fortunately, the password_expired feature has been added since MySQL version 5.6.6. it allows you to set the Expiration Time.

This feature has been added to the mysql. user data table, but its default value is "N ". You can use the alter user statement to modify the value.

The following is a simple example of how to set the expiration date of a MySQL User account:

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

Once a user sets this option to "Y", the user can still log on to the MySQL server, but cannot run any query statement before the user sets a new password, the following error message is displayed:

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

After a user sets a new password, all operations performed by the user (based on the user's own permissions) are permitted:

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 password expiration time of a MySQL User.

Since MySQL 5.7.4, the user's password expiration time feature has been improved. you can use the global variable default_password_lifetime to set the password expiration policy, this global variable can be used to set a global automatic password expiration policy.

Usage example:
You can set a default value in the MySQL configuration file, so that all MySQL User Passwords expire for 90 days, and MySQL calculates the time from startup. The configuration of my. cnf is as follows:

[mysqld]default_password_lifetime=90

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

[mysqld]default_password_lifetime=0

You can use the super permission to modify this configuration when running MySQL:

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 specific values for each specific USER account, which will automatically overwrite the global policy for 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;

Allow the user to use the default password to expire the global policy:

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

From MySQL 5.7.6, you can also use the alter user statement to modify your password:

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

Postscript

New features of locking/unlocking user accounts are added to user management in MySQL 5.7.8. 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 account lock as follows:

mysql> CREATE USER 'furrywall'@'localhost' IDENTIFIED BY '71m32ch4n6317' ACCOUNT LOCK;Query OK, 0 rows affected (0.00 sec)

As shown in the following figure, the newly created user will receive an ERROR 3118 ERROR message when logging on to the system:

$ mysql -ufurrywall -pEnter password:ERROR 3118 (HY000): Access denied for user 'furrywall'@'localhost'. Account is locked.

In this case, 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 been unlocked and can log on:

$ mysql -ufurrywall -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 17Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql>

You can also lock the user account as follows:

mysql> ALTER USER 'furrywall'@'localhost' ACCOUNT LOCK;Query OK, 0 rows affected (0.00 sec)

The above section describes the user password expiration function of MySQL. For more information, see PHP Chinese website (www.php1.cn )!