How to realize the function of user password expiration in MySQL
This article mainly introduces MySQL how to achieve user password expiration function, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.
Starting with MySQL version 5.6.6, the password_expired feature has been added, which allows you to set the expiration time for users.
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 this value.
For example:
Mysql > ALTER USER mdba@'localhost' PASSWORD EXPIRE
Query OK, 0 rows affected (0.04 sec)
You cannot run any query statements until the user has set a new password, and you will get the following error message:
Mysql > show databases
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
After performing the following actions, all actions of this user will be allowed to be performed again:
Mysql > alter user mdba@localhost identified by 'Aisino 123'
Query OK, 0 rows affected (0.03 sec)
Mysql > flush privileges
Query OK, 0 rows affected (0.04 sec)
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | mysql |
| | performance_schema |
| | sys |
+-+
4 rows in set (0.00 sec)
A new feature of locking / unlocking user accounts has been added to user management since MySQL version 5.7.8.
For example:
Mysql > alter user mdba@localhost account lock
Query OK, 0 rows affected (0.04 sec)
Re-login found rejected:
[root@localhost] # mysql-u mdba-p
Enter password:
ERROR 3118 (HY000): Access denied for user 'mdba'@'localhost'. Account is locked.
Return to normal after unlocking:
Mysql > alter user mdba@localhost account unlock
Query OK, 0 rows affected (0.03 sec)
[root@localhost] # mysql-u mdba-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 942539
Server version: 5.7.17-debug-log Source distribution
Copyright (c) 2000, 2016, 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 >
Since MySQL version 5.7.4, the password expiration time of users has been improved, and the password expiration policy can be set through a global variable default_password_lifetime.
This global variable sets a global automatic password expiration policy.
Set a default value in the MySQL5.7 configuration file, which will cause the password expiration time for all MySQL users to be 90 days, and MySQL will calculate the time from startup.
For example, add: to my.cnf:
[mysqld]
Default_password_lifetime=90
This results in a password expiration time of 90 days for all MySQL users, and MySQL calculates the time from startup.
If you want to set a global policy that passwords never expire, you can set default_password_lifetime=0, or on the command line:
Mysql > SET GLOBAL default_password_lifetime = 0
Query OK, 0 rows affected (0.00 sec)
Thank you for reading this article carefully. I hope the article "how to achieve the expiration of user passwords in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!
