It's the end of the year, is your MySQL password safe?

Preface:

It’s the end of the year, isn’t it time to inspect your database? Generally, inspections will be concerned about password security issues, such as password complexity settings and whether they are modified regularly. Especially when conducting security assessments, the assessment agency will require a cryptographic security strategy. In fact, the MySQL system itself can set password complexity and automatic expiration policies, but it may be rarely used, and most students do not understand it in detail. In this article, let's learn how to set the database account password complexity and automatic expiration policy.

1. Password complexity policy settings

The MySQL system comes with a validate_password plug-in, which can verify the password strength. Passwords that do not meet the specified strength are not allowed to be set. MySQL 5.7 and 8.0 do not seem to enable this plug-in by default, which allows us to set passwords at will, such as 123, 123456, etc. If we want to regulate the password strength from the root, we can enable this plug-in. Let's take a look at how to set the password complexity policy through this plug-in.

1) Check if this plugin is installed

Enter the MySQL command line and use show plugins or check the validate_password related parameters to determine whether this plug-in has been installed. If there is no relevant parameter, it means that this plug-in is not installed

# Check before installation. If it is empty, it means that this plug-in is not installed.mysql> show variables like 'validate%';
Empty set (0.00 sec)

2) Install the validate_password plugin

# This plugin can be installed with the INSTALL PLUGIN command # The file name suffix is ​​different for each platform. For Unix and Unix-like systems, it is .so, and for Windows it is .dll
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.28 sec)

# View validate_password related parametersmysql> 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)

3) Explanation of password strength related parameters

After installing the validate_password plugin, some additional parameters related to password strength are added. These parameters are easy to understand from the literal meaning. The following is a brief explanation of several key parameters.

1. validate_password_policy

Represents the password policy. The default is MEDIUM. The configurable values ​​are as follows:

• 0 or LOW only needs to meet the password length (specified by the parameter validate_password_length)
• 1 or MEDIUM satisfies the LOW strategy and must contain at least 1 number, lowercase letter, uppercase letter, and special character.
• 2 or STRONG satisfies the MEDIUM policy, and the password cannot exist in the dictionary file.

2. validate_password_dictionary_file

A dictionary file used to configure passwords. When validate_password_policy is set to STRONG, a password dictionary file can be configured. Passwords in the dictionary file must not be used.

3. validate_password_length

Used to set the minimum length of the password, the default value is 8

4. validate_password_mixed_case_count

When validate_password_policy is set to MEDIUM or STRONG, the number of lowercase and uppercase letters in the password. The default is 1 and the minimum is 0; the default is to have at least one lowercase and one uppercase letter.

5. validate_password_number_count

When validate_password_policy is set to MEDIUM or STRONG, the minimum number of digits in the password, the default is 1 and the minimum 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 the password, the default is 1 and the minimum is 0

4) Specific settings for password complexity policy

After learning the above parameters, we can set the password complexity policy according to our own situation. For example, if I want the password to be at least 10 characters and contain uppercase and lowercase letters, numbers, and special characters, I can set it like this.

5) Test password complexity

The password complexity policy is only valid for operations after it takes effect. For example, if you previously had an account with a password of 123, you can continue to use the account, but if you change the password again, it must meet the complexity policy. Next, let's test the specific effect of the password complexity policy.

# Create a new user and set the password mysql> create user 'testuser'@'%' identified by '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@'%' identified by 'ab123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@'%' identified by 'Ab@123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@'%' identified by 'Bsdf@5467672';
Query OK, 0 rows affected (0.01 sec)

# Change passwordmysql> alter user 'testuser'@'%' identified by 'dfgf3435';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user 'testuser'@'%' identified by 'dBsdf@5467672';
Query OK, 0 rows affected (0.01 sec)

2. Set password to expire automatically

In addition to setting password complexity policies, we can also set automatic password expiration. For example, the password will expire every 90 days and must be changed before it can be used again. This way, our database account will be more secure. Let's take a look at how to set up automatic password expiration.

Set the expiration time of a certain account password separately

Use the ALTER USER statement to expire a single account password or to change the account expiration time.

# Check the database account status through the mysql.user system tablemysql> 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.01 sec)

# Make the expuser account password expire immediatelymysql> ALTER USER 'expuser'@'%' 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.00 sec)

# Change the account password to never expire mysql> ALTER USER 'expuser'@'%' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)

# Set the password of this account to expire in 90 daysmysql> ALTER USER 'expuser'@'%' 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.00 sec)

# Let this account use the default password expiration global policy mysql> ALTER USER 'expuser'@'%' PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.01 sec)

The mysql.user system table records the relevant information of each account. When the password_expired field value is Y, it means that the password has expired. You can still log in with an expired password, but you cannot perform any operations. If you perform operations, you will be prompted: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. You must change the password before you can perform normal operations.

For an account with a given expiration time, such as 90 days, the database system will compare the difference between the current time and the time when the password was last changed. If more than 90 days have passed since the last password change, the account password will be marked as expired and the password must be changed before any operation can be performed.

Set the global expiration policy

To build a global password auto-expiration policy, use the default_password_lifetime system variable. Prior to version 5.7.11, the default value of default_password_lifetime was 360 (the password must be changed approximately once a year), and in later versions the default value is 0, meaning that the password will not expire. The unit of this parameter is day. For example, we can set this parameter to 90, which means the global password automatic expiration policy is 90 days.

# Set the global expiration policy. Change it manually first and then add it to the configuration file. mysql> 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.00 sec)

# Write the configuration file to make the restart effective [mysqld]
default_password_lifetime = 90

Although you can "reset" an expired password by setting it to its current value, it is better to choose a different password for good policy reasons.

Summarize:

This article mainly introduces two security strategies for database passwords: password complexity and password expiration strategy. The more strategies you have, the more peace of mind you have. Remember: safety is no small matter.

The above is the detailed content of "Is your MySQL password safe?" at the end of the year. For more information about MySQL password security, please pay attention to other related articles on 123WORDPRESS.COM!