MySQL-5.7密碼原則及使用者資源限制

標籤：ash   epo   err   圖片   glob   資源   markdown   life   exp   

1.密碼原則

在mysql 5.6對密碼的強度進行了加強，推出了validate_password 外掛程式。支援密碼的強度要求。

（1）安裝外掛程式

[[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     |.....................省略| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |+----------------------------+----------+--------------------+----------------------+---------+45 rows in set (0.00 sec)

（2）添加配置

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

（3）檢測配置

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(‘mysql2017‘);ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> set password=password(‘[email 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.策略詳解

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      |+--------------------------------------+--------+

說明：
validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 決定是否使用該外掛程式(及強制/永久強制使用)。

validate_password_dictionary_file：外掛程式用於驗證密碼強度的字典檔案路徑。

validate_password_length：密碼最小長度。

validate_password_mixed_case_count：密碼至少要包含的小寫字母個數和大寫字母個數。

validate_password_number_count：密碼至少要包含的數字個數。

validate_password_policy：密碼強度檢查等級，0/LOW、1/MEDIUM、2/STRONG。

validate_password_special_char_count：密碼至少要包含的特殊字元數。

關於validate_password_policy-密碼強度檢查等級：

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.使用者資源限制

（1）max_user_connections
該參數作用是設定所有使用者在同一時間串連MySQL執行個體的最大串連數限制。但這個參數無法對每個使用者區別對待。

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
該參數設定一個使用者在一小時內可以執行查詢的次數（基本包含所有語句）。

（3）max_updates_per_hour
該參數設定一個使用者在一小時內可以執行修改的次數（僅包含修改資料庫或表的語句）。

（4）max_connections_per_hour
該參數設定一個使用者在一小時內可以串連MySQL的時間。

從5.0.3版本開始，對使用者‘test’@‘%.test.com‘的資源限制是指所有通過test.com網域名稱主機串連test使用者的串連，而不是分別指host1.test.com和host2.test.com主機過來的串連。

（5）設定使用者資源限制

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.密碼到期策略

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)

說明：
1）default_password_lifetime
設定所有使用者密碼到期時間，0為永不到期；
若為單獨使用者佈建了密碼到期策略，則會覆蓋該參數；

alter user ‘test3‘@‘localhost‘ password expire interval 90 day;alter user ‘test3‘@‘localhost‘ password expire never; （永不到期）alter user ‘test3‘@‘localhost‘ password expire default; （預設到期策略）

2）手動強制到期

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

5.使用者lock機制

通過執行create user/alter user命令中帶account lock/unlock子句設定使用者的lock狀態；
預設建立使用者是unlock狀態；

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.密碼產生技巧

https://suijimimashengcheng.51240.com/

MySQL-5.7密碼原則及使用者資源限制