Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to set a secure database password". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Password complexity policy setting

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

1) check to see if this plug-in is installed

Go to the MySQL command line and determine whether the plug-in is installed by show plugins or by looking at the relevant parameters of validate_password. If there are no relevant parameters, the plug-in is not installed.

# if the pre-installation check is blank, the plug-in mysql > show variables like 'validate%'; Empty set (0.00 sec) is not installed.

2) install the validate_password plug-in

# this plug-in can be installed through the INSTALL PLUGIN command # the file name suffix for each platform is different for Unix and Unix-like systems, .so, for Windows 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 parameters mysql > 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) interpretation of parameters related to password strength

After installing the validate_password plug-in, some password strength-related parameters are added, which are also easy to understand literally. Here are a few key parameters that are briefly explained.

1 、 validate_password_policy

Represents the password policy. By default, MEDIUM can be configured with the following values:

0 or LOW only needs to meet the password length (specified by parameter validate_password_length)

1 or MEDIUM satisfies the LOW policy, but also requires at least 1 number, lowercase letter, uppercase letter and special character

2 or STRONG meets the MEDIUM policy, and the password cannot be stored in the dictionary file (dictionary file)

2 、 validate_password_dictionary_file

A dictionary file used to configure passwords. Password dictionary files can be configured when validate_password_policy is set to STRONG. Passwords that exist in dictionary files 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, at least the number of lowercase and uppercase letters in the password, the default is 1, the minimum is 0, and the default is 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 is 0 by default.

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 is 0 by default.

4) specific settings of password complexity policy

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

# set password length at least 10 digits 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.00 sec) # if you want to take effect permanently It is recommended that the following parameters be written into the configuration file [mysqld] plugin-load = validate_password.so validate_password_length = 10 validate_password_policy = 1 validate-password = FORCE_PLUS_PERMANENT

5) Test password complexity

The password complexity policy is only valid for operations that take effect. For example, if you have an account before, and the password is 123, the account can still be used, but if you change the password again, you need to meet the complexity policy. Let's test the specific effect of the password complexity policy.

# New user settings password mysql > create user' testuser'@'%' identified by '123password; 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 password mysql > 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 the password complexity policy, we can also set the password to expire automatically. For example, the password will expire every 90 days and must be changed before you can continue to use it, so that our database account will be more secure. Let's take a look at how to set the password to expire automatically.

Set the expiration time of an account password separately

Use the ALTER USER statement to make the password of a single account expire, or you can change the expiration time of the account.

# View the database account status mysql > select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user through the mysql.user system table +-- +-+ | 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) # make the expuser account Password expires immediately mysql > 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) # modify the account Passwords never expire mysql > ALTER USER 'expuser'@'%' PASSWORD EXPIRE NEVER Query OK, 0 rows affected (0.01 sec) # set the account password to expire in 90 days mysql > 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 value of the password_expired field is Y, it means that the password has expired. You can still log in with the expired password, but you can't do anything. The operation will prompt: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. You must change your password before you can operate normally.

For an account with a given expiration time, such as setting it to expire within 90 days, the database system will compare the difference between the current time and the last time the password was modified. If the time since the last password modification is more than 90 days, the account password will be marked as expired. The password must be changed before the operation can be carried out.

Set global expiration policy

To build a global password automatic expiration policy, use the default_password_lifetime system variable. Prior to version 5.7.11, the default default_password_lifetime value was 360 (the password must be changed about once a year), and the later version defaults to 0, indicating that the password will not expire. The unit of this parameter is days. For example, if we can set this parameter to 90, it means that the global password automatic expiration policy is 90 days.

# set the global expiration policy by manually changing 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 configuration file to make the restart effective [mysqld] default_password_lifetime = 90

Although you can "reset" an expired password by setting it to the current value, it is best to choose a different password for good Policy reasons.

This is the end of "how to set a secure database password". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.