MySQL 8密码策略

原创杨明翰 2021-08-24

2009

MySQL8提供了较多的密码管理功能和策略，包括密码过期时间设置，密码重用限制，密码验证，双密码，密码强度评估和密码失败跟踪等。DBA使用提供的这些功能和策略对MySQL用户的密码进行管理和配置，进一步完善数据库的安全保障。

1、密码过期策略

MySQL提供参数配置设置全局密码过期时间，也可以创建用户是指定该用户的密码过期时间，也可以手动设置某一用户密码过期；系统从用户最近一次密码更新时间计时，当超过其允许的生命周期时必须重置密码后才能正常登陆。

#设置全局密码过期时间default_password_lifetime，单位为天
1、修改配置文件重启后生效
[mysqld]
default_password_lifetime=180

2、在线修改并持久化
SET PERSIST default_password_lifetime = 180;

#default_password_lifetime=0 时默认密码用不过期
复制

除全局配置外，也可以创建用户时指定密码过期时间

#创建用户test_passwd并设置密码过期时间为90天

mysql> CREATE USER 'test_passwd'@'localhost' identified by 'test_passwd' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd';
+-------------+-----------+-----------------------+-------------------+
| user        | host      | password_last_changed | password_lifetime |
+-------------+-----------+-----------------------+-------------------+
| test_passwd | localhost | 2021-08-24 14:29:07   |                90 |
+-------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)


#创建用户test_passwd_never并设置密码用不过期

mysql> CREATE USER 'test_passwd_never'@'localhost' identified by 'test_passwd' PASSWORD EXPIRE NEVER;       
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd_never';
+-------------------+-----------+-----------------------+-------------------+
| user              | host      | password_last_changed | password_lifetime |
+-------------------+-----------+-----------------------+-------------------+
| test_passwd_never | localhost | 2021-08-24 14:32:27   |                 0 |
+-------------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)

#创建用户test_passwd_default并设置密码过期时间遵循系统默认值

mysql> CREATE USER 'test_passwd_default'@'localhost' identified by 'test_passwd' PASSWORD EXPIRE DEFAULT;     
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd_default';
+---------------------+-----------+-----------------------+-------------------+
| user                | host      | password_last_changed | password_lifetime |
+---------------------+-----------+-----------------------+-------------------+
| test_passwd_default | localhost | 2021-08-24 14:33:56   |              NULL |
+---------------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)


复制

也可以手动设置某一用户密码过期

mysql> ALTER USER 'test_passwd'@'localhost' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

    当用户密码过期后，登录动作受只读参数disconnect_on_expired_password 和 客户端共同影响。对于新版客户端（可以处理过期密码）可以正常登录但进入沙箱模式。否则当disconnect_on_expired_password=on时拒绝登录；
    进入沙箱模式后只能重置密码后才能正常使用；

mysql> select 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user current_user() identified by 'new_passwd';
Query OK, 0 rows affected (0.01 sec)

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
复制

2、密码重用策略

MySQL可以限制已使用的密码重复使用，可以根据密码修改的次数和密码使用的时间间隔两个维度进行限制。同样的既可以设置一个全局策略，也可以对每个用户设置单独的策略。

当开启密码重用策略后，mysql.password_history表中将会记录用户使用的历史密码和该密码被修改的时间。可以配置限制密码更改的次数，如password_history设置为3，则不能使用最近三次使用过的密码；也可以配置限制密码使用时间，如配置password_reuse_interval=60，则不能使用60天内使用过的密码；

空密码不计入密码历史表，可以随时重复使用。

mysql> alter user 'test'@'%' identified by '12345';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'test'@'%' identified by '123455';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from password_history;
+------+------+----------------------------+-------------------------------------------+
| Host | User | Password_timestamp         | Password                                  |
+------+------+----------------------------+-------------------------------------------+
| %    | test | 2021-08-24 15:00:06.189674 | *E2401F534305A172461D896178F24FD3E8408B57 |
| %    | test | 2021-08-24 15:00:00.974273 | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+------+------+----------------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> alter user 'test'@'%' identified by '12345566';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from password_history;
+------+------+----------------------------+-------------------------------------------+
| Host | User | Password_timestamp         | Password                                  |
+------+------+----------------------------+-------------------------------------------+
| %    | test | 2021-08-24 15:07:10.518840 | *00A14ACBB5EA91ACF5D5D51A3B4B8424B7244CB7 |
| %    | test | 2021-08-24 15:00:06.189674 | *E2401F534305A172461D896178F24FD3E8408B57 |
| %    | test | 2021-08-24 15:00:00.974273 | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+------+------+----------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

复制

设置全局密码重用策略，如：不能使用最近六次使用过的密码，并且一年内没有使用过

#修改配置文件重启生效
[mysqld]
password_history=6
password_reuse_interval=365

#在线修改并持久化
mysql> SET PERSIST password_history = 6;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST password_reuse_interval = 365;
Query OK, 0 rows affected (0.00 sec)
复制

也可创建用户是指定或修改某一用户的密码重用策略

#创建用户时指定
mysql> create user 'test_reuse'@'localhost' PASSWORD HISTORY 6 PASSWORD REUSE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,Password_reuse_history,Password_reuse_time from mysql.user where user='test_reuse';
+------------+-----------+------------------------+---------------------+
| user       | host      | Password_reuse_history | Password_reuse_time |
+------------+-----------+------------------------+---------------------+
| test_reuse | localhost |                      6 |                 365 |
+------------+-----------+------------------------+---------------------+
1 row in set (0.00 sec)

#修改现有用户配置
mysql> alter user  'test_reuse'@'localhost' PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 60 DAY;    
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,Password_reuse_history,Password_reuse_time from mysql.user where user='test_reuse';
+------------+-----------+------------------------+---------------------+
| user       | host      | Password_reuse_history | Password_reuse_time |
+------------+-----------+------------------------+---------------------+
| test_reuse | localhost |                      3 |                  60 |
+------------+-----------+------------------------+---------------------+
1 row in set (0.01 sec)

#修改成默认值
mysql>  alter user  'test_reuse'@'localhost' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT; 
Query OK, 0 rows affected (0.00 sec)

mysql>  select user,host,Password_reuse_history,Password_reuse_time from mysql.user where user='test_reuse';
+------------+-----------+------------------------+---------------------+
| user       | host      | Password_reuse_history | Password_reuse_time |
+------------+-----------+------------------------+---------------------+
| test_reuse | localhost |                   NULL |                NULL |
+------------+-----------+------------------------+---------------------+
1 row in set (0.00 sec)

复制

设置重用规则，当修改的密码不满足该规则时会报错

mysql>  alter user 'test'@'%' PASSWORD HISTORY 2;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'test'@'%' identified by '12345';
ERROR 3638 (HY000): Cannot use these credentials for 'test@%' because they contradict the password history policy

mysql> alter user 'test'@'%' identified by '123458888';
Query OK, 0 rows affected (0.01 sec)
复制

3、密码验证策略

从MySQL8.0.13开始支持密码验证策略，通过全局和指定用户的配置可以实现修改密码时需要提供当前密码。避免用户密码被恶意更改的情况。

如果用户设置了PASSWORD REQUIRE CURRENT则必须密码验证，如用户设置了PASSWORD REQUIRE OPTIONAL则不需要密码验证，如用户设置了PASSWORD REQUIRE CURRENT DEFAULT（默认值）则受参数password_require_current控制；

#开启关闭全局密码验证
SET PERSIST password_require_current = ON/OFF;
修改用户
alter user 'test'@'%' PASSWORD REQUIRE CURRENT;
alter user 'test'@'%' PASSWORD REQUIRE CURRENT OPTIONAL;
alter user 'test'@'%' PASSWORD REQUIRE CURRENT DEFAULT;
复制

4、双密码

从MySQL8.0.14开始允许给用户配置双密码。如果您为主密码为空的帐户指定 RETAIN CURRENT PASSWORD，则语句失败。如果帐户有二级密码，并且您在未指定 RETAIN CURRENT PASSWORD 的情况下更改其主密码，则二级密码保持不变。

#test用户当前密码为old_passwd
[root@node1 ~]# mysql -utest -pold_passwd -h127.0.0.1
mysql> 

#修改test用户密码为new_passwd，保留原密码
mysql> alter user current_user() identified by 'new_passwd' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)

#这时新旧密码都可以登录
[root@node1 ~]# mysql -utest -pold_passwd -h127.0.0.1
mysql> 

[root@node1 ~]# mysql -utest -pnew_passwd -h127.0.0.1
mysql> 

mysql> select user,host,authentication_string,User_attributes from mysql.user where user='test' and host='%';
+------+------+-------------------------------------------+----------------------------------------------------------------------+
| user | host | authentication_string                     | User_attributes                                                      |
+------+------+-------------------------------------------+----------------------------------------------------------------------+
| test | %    | *9AAC034BCF47864DA01A23844FA65F8B6D23E10E | {"additional_password": "*EBED5A04B3B2D65B204464B8C4CEA9C06181C1F6"} |
+------+------+-------------------------------------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

#删除旧密码后 old_passwd停用
mysql>  alter user current_user() DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string,User_attributes from mysql.user where user='test' and host='%';
+------+------+-------------------------------------------+-----------------+
| user | host | authentication_string                     | User_attributes |
+------+------+-------------------------------------------+-----------------+
| test | %    | *9AAC034BCF47864DA01A23844FA65F8B6D23E10E | NULL            |
+------+------+-------------------------------------------+-----------------+
1 row in set (0.00 sec)
复制

5、随机密码生产

从MySQL8.0.18开始，在创建用户、修改密码时可以生成随机密码。随机密码长度受generated_random_password_length控制，范围从5到255，默认为20。

mysql> show  global variables like 'generated_random_password_length';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| generated_random_password_length | 20    |
+----------------------------------+-------+
1 row in set (0.01 sec)

#创建用户随机密码
mysql> create user 'test_random'@'%' IDENTIFIED BY RANDOM PASSWORD;
+-------------+------+----------------------+
| user        | host | generated password   |
+-------------+------+----------------------+
| test_random | %    | [AZ):HMMQY}qD)gU}3Z[ |
+-------------+------+----------------------+
1 row in set (0.00 sec)

#使用生产的密码登录
[root@node1 ~]# mysql -utest_random -p'[AZ):HMMQY}qD)gU}3Z[' -h127.0.0.1          
mysql> 

#修改用户密码为随机密码
mysql>  alter user 'test_random'@'%' IDENTIFIED BY RANDOM PASSWORD;
+-------------+------+----------------------+
| user        | host | generated password   |
+-------------+------+----------------------+
| test_random | %    | G>3Rg2MFR%N(R[DL6gkA |
+-------------+------+----------------------+
1 row in set (0.01 sec)

mysql> SET PASSWORD FOR 'test_random'@'%' to RANDOM;
+-------------+------+----------------------+
| user        | host | generated password   |
+-------------+------+----------------------+
| test_random | %    | [>C&F{p4[m>FuzPm6;j_ |
+-------------+------+----------------------+
1 row in set (0.01 sec)
复制

6、登录失败跟踪和临时账户锁定

从MySQL8.0.19开始，管理员可以配置用户当连续登录失败超过阈值时的锁定时间；

#创建用户test_lock连续失败3次后锁定两天
create user 'test_lock'@'%' IDENTIFIED BY RANDOM PASSWORD FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

#修改用户test_lock连续失败2次后锁定3天
alter user 'test_lock'@'%' FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME 3;

#连续失败后该账户被锁定
[root@node1 ~]# mysql -utest_lock -p'[' -h127.0.0.1                   
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test_lock'@'127.0.0.1'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 2 consecutive failed logins.


mysql> select user,host,User_attributes from mysql.user where user='test_lock'\G  
*************************** 1. row ***************************
           user: test_lock
           host: %
User_attributes: {"Password_locking": {"failed_login_attempts": 2, "password_lock_time_days": 3}}
1 row in set (0.00 sec)
复制

解除锁定的方式

1、服务器重启
2、执行 flush privileges;
3、alter user 'test_lock'@'%' account unlock;
复制

7、密码强度评估

MySQL8.0开始，使用validate_password组件进行密码强度的评估。

validate_password 组件实现了以下功能：

对于分配作为明文值提供的密码的 SQL 语句，validate_password 根据当前密码策略检查密码，如果密码较弱则拒绝该密码（该语句返回 ER_NOT_VALID_PASSWORD 错误）。这适用于 ALTER USER、CREATE USER 和 SET PASSWORD 语句。

对于 CREATE USER 语句，validate_password 要求提供密码，并且满足密码策略。即使帐户最初被锁定也是如此，否则稍后解锁帐户将导致它在没有满足策略的密码的情况下变得可访问。

validate_password 实现了一个 VALIDATE_PASSWORD_STRENGTH() SQL 函数来评估潜在密码的强度。该函数接受一个密码参数并返回一个从 0（弱）到 100（强）的整数。

安装/卸载 validate_password组件

#安装
mysql> INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|            2 |                  2 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)

#卸载
mysql> uninstall  COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.00 sec)

复制

安装validate_password组件组件后创建简单密码的用户会报错

mysql> create user 'test_com'@'abc' identified by 'abc';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'test_com'@'abc' identified by 'abc@ABC';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'test_com'@'abc' identified by 'abc@ABC@123';
Query OK, 0 rows affected (0.01 sec)
复制

也可以使用VALIDATE_PASSWORD_STRENGTH() 函数评估密码强度

mysql> select VALIDATE_PASSWORD_STRENGTH('abc');
+-----------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select VALIDATE_PASSWORD_STRENGTH('abc@BC');
+--------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc@BC') |
+--------------------------------------+
|                                   25 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select VALIDATE_PASSWORD_STRENGTH('abc@BC@123');
+------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc@BC@123') |
+------------------------------------------+
|                                      100 |
+------------------------------------------+
1 row in set (0.00 sec)
复制

validate_password相关参数如下

mysql> SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+--------+
| 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.01 sec)
复制

其中validate_password.policy设置密码检查规则，分为LOW，MEDIUM和STRONG；

LOW只检查密码长度，要求密码长度至少为validate_password.length 默认是8；
MEDIUM 策略添加了密码必须至少包含validate_password.number_count（默认1）个数字字符、validate_password.mixed_case_count（默认1）个小写字符、validate_password.mixed_case_count （默认1）个大写字符和 validate_password.special_char_count （默认1）个特殊（非字母数字）字符的条件。
STRONG 策略添加了长度为 4 或更长的密码子字符串不能与字典文件中的单词匹配的条件（如果已指定）。要指定字典文件，请修改validate_password.dictionary_file。
validate_password.check_user_name 默认为ON，会拒绝密码为当前用户名的一部分，无论是正向或反向

ERROR 1396 (HY000): Operation CREATE USER failed for 'test_com'@'abc'
mysql> create user 'test_com'@'abc' identified by 'moc@ABC@123';    
ERROR 1396 (HY000): Operation CREATE USER failed for 'test_com'@'abc'
复制

mysql 密码 password密码策略 validate_password

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

筱悦星辰

1年前

若将岁月开成花，人间何处不芳华！

1年前