MySQL-5.7 Password Policy and user resource limits

1. Password Policy

The strength of the password was enhanced in MySQL 5.6, and the Validate_password plugin was introduced. Support for password strength requirements.

(1) Install plug-in

[[email protected] ~]# ll/usr/local/mysql/lib/plugin/validate_password.so-rwxr-xr-x 1 mysql mysql 204359 Sep 14 01 :27/usr/local/mysql/lib/plugin/validate_password.somysql> Install plugin Validate_password soname ' Validate_ Password.so '; Query OK, 0 rows affected (0.11 sec) mysql> Show plugins;+----------------------------+----------+------------------ --+----------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+----------------------+---------+| Binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | Mysql_native_password | ACTIVE | Authentication | NULL | GPL | | Sha256_password | ACTIVE | Authentication | NULL | GPL | ....... ......... Omit | Validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |+----------------------------+----------+--------------------+----------------------+---------+45 rows in Set (0.00 sec) 

(2) Add configuration

[[email protected] ~]# cat /etc/my.cnf[mysqld]datadir=/data1/mysql/dataplugin-load=validate_password.sovalidate_password_policy=2validate-password=FORCE_PLUS_PERMANENT

(3) Detection configuration

mysql> SHOW VARIABLES like ' validate_password% '; +--------------------------------------+--------+| variable_name | Value |+--------------------------------------+--------+| Validate_password_check_user_name | OFF | |        Validate_password_dictionary_file | || Validate_password_length | 8 | | Validate_password_mixed_case_count | 1 | | Validate_password_number_count | 1 | | Validate_password_policy | Strong | | Validate_password_special_char_count | 1 |+--------------------------------------+--------+7 rows in Set (0.02 sec) mysql> set Password=password (' abc '); ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> set Password=password (' Mysql20 17 '); ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> set Password=password (' [email& Nbsp;protected])!& '); ERROR 1819 (HY000): Your password does not satisfy the current policy reqUirementsmysql> set Password=password (' [email protected])!&sql2017 '); Query OK, 0 rows affected, 1 Warning (0.00 sec)

2. Strategy

mysql> SHOW VARIABLES LIKE ‘validate_password%‘;+--------------------------------------+--------+| Variable_name                        | Value  |+--------------------------------------+--------+| validate_password_check_user_name    | OFF    || validate_password_dictionary_file    |        || validate_password_length             | 8      || validate_password_mixed_case_count   | 1      || validate_password_number_count       | 1      || validate_password_policy             | STRONG || validate_password_special_char_count | 1      |+--------------------------------------+--------+

Description
Validate-password=on/off/force/force_plus_permanent: Decide whether to use the plug-in (and force/permanently force use).

Validate_password_dictionary_file: The dictionary file path that the plugin uses to verify password strength.

Validate_password_length: Minimum password length.

Validate_password_mixed_case_count: The password must contain at least the number of lowercase letters and uppercase letters.

Validate_password_number_count: The number of digits that the password must contain at least.

Validate_password_policy: Password strength check level, 0/low, 1/medium, 2/strong.

Validate_password_special_char_count: The minimum number of special characters the password should contain.

About validate_password_policy-Password strength Check level:

Policy          Tests Performed0 or LOW    Length1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

3. User Resource Limitations

(1) max_user_connections
The function of this parameter is to set the maximum connection limit for all users to connect to the MySQL instance at the same time. However, this parameter cannot be treated differently for each user.

mysql> show global variables like ‘%max_user_connect%‘;+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| max_user_connections | 0     |+----------------------+-------+1 row in set (0.00 sec)mysql> set global max_user_connections=2;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like ‘%max_user_connect%‘;+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| max_user_connections | 2     |+----------------------+-------+1 row in set (0.00 sec)

(2) Max_queries_per_hour
This parameter sets the number of times a user can execute a query within an hour (basically including all statements).

(3) Max_updates_per_hour
This parameter sets the number of times a user can perform a modification in an hour (only statements that modify a database or table).

(4) Max_connections_per_hour
This parameter sets the time that a user can connect to MySQL within an hour.

Starting with version 5.0.3, the resource limit on the user ' test ' @ '%.test.com ' refers to all connections to the test user through the test.com domain host, not to the host1.test.com and host2.test.com hosts respectively.

(5) Set User resource limits

mysql> create user ‘test1‘@‘localhost‘ identified by ‘MYsql20!&‘    -> with max_queries_per_hour 20    -> max_updates_per_hour 10    -> max_user_connections 2;Query OK, 0 rows affected (0.00 sec)mysql> alter user ‘test1‘@‘localhost‘ with max_queries_per_hour 100;Query OK, 0 rows affected (0.00 sec)取消某项资源限制既把原先的值改成0.当某个用户的max_user_connections非0时，则忽略全局系统参数对应的配置，反之则使用全局参数。

4. Password Expiration policy

  mysql> Show global variables like '%password% '; +---------------------------------------+--------+| variable_name | Value |+---------------------------------------+--------+| Default_password_lifetime | 0 | | Disconnect_on_expired_password | On | | Log_builtin_as_identified_by_password | OFF | | Mysql_native_password_proxy_users | OFF | | Old_passwords | 0 | |        Report_password | || Sha256_password_proxy_users | OFF | | Validate_password_check_user_name | OFF | |        Validate_password_dictionary_file | || Validate_password_length | 8 | | Validate_password_mixed_case_count | 1 | | Validate_password_number_count | 1 | | Validate_password_policy | Strong | | Validate_password_special_char_count | 1 |+---------------------------------------+--------+14 rows in Set (0.01 sec)  

Description
1) default_password_lifetime
Set all user password expiration time, 0 to never expire;
If a password expiration policy is set for individual users, the parameter will be overwritten;

alter user ‘test3‘@‘localhost‘ password expire interval 90 day;alter user ‘test3‘@‘localhost‘ password expire never; （永不过期）alter user ‘test3‘@‘localhost‘ password expire default; （默认过期策略）

2) Manual forced expiration

alter user ‘test3‘@‘localhost‘ password expire;

5. User lock mechanism

Set the user's lock state by executing the Create-user/alter user command with the account Lock/unlock clause;
The default creation user is unlock state;

mysql> create user [email protected] identified by ‘MY20sql!&‘ account lock;Query OK, 0 rows affected (0.00 sec)mysql> quitBye[[email protected] ~]# mysql -uabc2 -pEnter password: ERROR 3118 (HY000): Access denied for user ‘abc2‘@‘localhost‘. Account is locked.mysql> alter user ‘abc2‘@‘localhost‘ account unlock;Query OK, 0 rows affected (0.00 sec)mysql> quitBye[[email protected] ~]# mysql -uabc2 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7

6. Password Generation Tips

https://suijimimashengcheng.51240.com/

MySQL-5.7 Password Policy and user resource limits