Preface:
The end of the year, your database should be inspected? During common inspection, you need to pay attention to password security issues, such as password complexity Settings and whether passwords are periodically changed. In particular, for equal-security profiling, the profiling organization will require a password security policy. In fact, the MySQL system itself can set password complexity and automatic expiration policy, which may be rarely used, most students do not understand in detail. In this article, we will learn how to set password complexity and automatic expiration policies for database accounts.
1. Set the password complexity policy
The MySQL system provides the validate_password plug-in to verify the password strength. A password that does not reach the specified strength cannot be set. MySQL 5.7 and 8.0 do not seem to have this plugin enabled by default, which allows us to set passwords as we wish, such as 123, 123456, etc. If we want to regulate password strength at its root, we can enable this plug-in. Let’s take a look at how to set password complexity policies using this plug-in.
1) Check whether the plug-in is installed
Go to the MySQL cli and run show plugins or view parameters related to validate_password to check whether the plug-in is installed. If no parameter is specified, the plug-in is not installed
Mysql is not installed if the check before installation is empty> show variables like 'validate%';
Empty set (0.00 sec)
Copy the code2) Install validate_password
The filename suffix varies from platform to platform. So for Unix and Unix-like systems,.dll for Windows mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.28SEC) # check mysql for validate_password> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
Copy the code3) Explanation of password strength related parameters
With the validate_password plugin, there are some password strength parameters that are easy to read literally. Here are some of the key parameters.
1. Validate_password_policy indicates the password policy. The default value is MEDIUM.
0 or LOW Specifies the password length (specified by parameter VALIDate_password_length). 1 or MEDIUM Meets the LOW policy and contains at least one digit, lowercase letters, uppercase letters, and special characters
2 or STRONG The password must meet the MEDIUM policy and cannot be stored in a dictionary file
2. Validate_password_dictionary_file specifies the dictionary file used to configure passwords. If validate_password_policy is set to STRONG, the password dictionary file can be configured.
3. Validate_password_length sets the minimum password length. The default value is 8
When validATE_PASSword_mixed_case_count is set to MEDIUM or STRONG, the password contains at least two lowercase and uppercase letters. The default value is 1. The minimum value is 0. The default is to have at least one lowercase and one uppercase letter.
5. Validate_password_number_count Specifies the minimum number of digits in a password when validATE_password_policy is set to MEDIUM or STRONG. The default value is 1 and the minimum value is 0
6. Validate_password_special_char_count When validATE_PASSword_policy is set to MEDIUM or STRONG, the minimum number of special characters in a password is 0
4) Set the password complexity policy
After learning the preceding parameters, you can set the password complexity policy based on your requirements. For example, if you want a password to contain at least 10 characters, uppercase and lowercase letters, digits, and special characters, you can set the password complexity policy based on your requirements.
Set password length to minimum10A mysql> set global validate_password_length = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 10 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00SEC) # To make it permanent, it is recommended to write the following parameters to the [mysqld] plugin-load = validate_password.so
validate_password_length = 10
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT
Copy the code5) Test password complexity
The password complexity policy takes effect only after the operation takes effect. For example, if you have an account and the password is 123, the account can still be used. However, if you change the password again, the password must meet the complexity policy. Let’s test the effect of the password complexity policy.
# create user set password mysql> create user 'testuser'@The '%' identified by '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@The '%' identified by 'ab123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@The '%' identified by 'Ab@123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@The '%' identified by 'Bsdf@5467672';
Query OK, 0 rows affected (0.01SEC) # change password mysql> alter user 'testuser'@The '%' identified by 'dfgf3435';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user 'testuser'@The '%' identified by 'dBsdf@5467672';
Query OK, 0 rows affected (0.01 sec)
Copy the code2. Set the password to expire automatically
In addition to setting the password complexity policy, we can also set automatic password expiration. For example, the password will expire every 90 days and must be changed before continuing to use the password. In this way, our database account will be more secure. Let’s take a look at setting automatic password expiration.
Set the password expiration time of an account
You can use the ALTER USER statement to expire the password of a single account or to change the account expiration time.
Mysql > select * from mysql.user> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| user | host | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| expuser | % | N | NULL | 2021- 01- 05 14:30:30 | N |
| root | % | N | NULL | 2020- 10- 30 14:45:43 | N |
| testuser | % | N | NULL | 2021- 01- 04 17:22:37 | N |
| mysql.infoschema | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| mysql.session | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| mysql.sys | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| root | localhost | N | NULL | 2020- 10- 30 14:38:55 | N |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
7 rows in set (0.01SEC) # expuser password immediately expired mysql> ALTER USER 'expuser'@The '%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| user | host | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| expuser | % | Y | NULL | 2021- 01- 05 14:30:30 | N |
| root | % | N | NULL | 2020- 10- 30 14:45:43 | N |
| testuser | % | N | NULL | 2021- 01- 04 17:22:37 | N |
| mysql.infoschema | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| mysql.session | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| mysql.sys | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| root | localhost | N | NULL | 2020- 10- 30 14:38:55 | N |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
7 rows in set (0.00SEC) # change account password never expire mysql> ALTER USER 'expuser'@The '%' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01SEC) # Set this account password separately90Days overdue mysql> ALTER USER 'expuser'@The '%' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| user | host | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| expuser | % | N | 90 | 2021- 01- 05 14:41:28 | N |
| root | % | N | NULL | 2020- 10- 30 14:45:43 | N |
| testuser | % | N | NULL | 2021- 01- 04 17:22:37 | N |
| mysql.infoschema | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| mysql.session | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| mysql.sys | localhost | N | NULL | 2020- 10- 30 14:37:09 | Y |
| root | localhost | N | NULL | 2020- 10- 30 14:38:55 | N |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
7 rows in set (0.00SEC) # let this account expire with the default password global policy mysql> ALTER USER 'expuser'@The '%' PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.01 sec)
Copy the codeThe mysql.user system table records information about each account. If the value of the password_expired field is Y, the password has expired. You can still log in with the expired password but cannot perform any operation. You must reset your password using ALTER USER statement before executing this statement. You must change the password to perform normal operations.
For an account with a specified expiration time, for example, 90 days, the database compares the difference between the current time and the time when the password was changed last time. If the time since the last password was changed exceeds 90 days, the account password is marked as expired. You can perform operations only after changing the password.
Set a global expiration policy
To build a global automatic password expiration policy, use the default_password_lifetime system variable. Prior to version 5.7.11, the default default_password_lifetime value was 360(passwords must be changed approximately once a year). In later versions, the default value is 0, indicating that passwords will not expire. The unit of this parameter is day. For example, if this parameter is set to 90, the global automatic password expiration policy is 90 days.
Mysql > set global expiration policy> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 90 |
+---------------------------+-------+
1 row in set (0.00SEC) # Write configuration file to make restart effect [mysqld] default_password_lifetime= 90
Copy the codeAlthough it is possible to “reset” an expired password by setting it to the current value, for good Policy, it is best to choose another password.
Conclusion:
This article mainly introduces two security policies about database password, password complexity plus password expiration policy, one more policy, one more peace of mind. Remember: safety is no small thing.