MySQL8.0 用户表功能增强

原创 KevinCui 2023-03-03

1240

对于一款数据库来说，数据库系统本身的访问控制是非常重要的。MySQL8.0.4开始，MySQL默认身份验证插件从mysql_native_password 改为 caching_sha2_password。相应地libmysqlclient 也使用 caching_sha2_password 作为默认的身份验证机制。当然，在8.0版本中两种密码机制都支持的。
除此之外，在用户安全控制方面，添加了一些更加实用的功能。

密码过期策略

密码过期是MySQL 5.7和8.0都支持的功能。支持手动过期帐户密码，也可以建立密码自动过期的策略。根据策略，从其最近密码更改的日期时间开始评估，期限一到密码自动过期。

#my.cnf配置文件
[mysqld]
default_password_lifetime=180

#语句设置过期时间
mysql> CREATE USER 'tester05'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
mysql> ALTER USER 'tester05'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#语句设置不过期
mysql> ALTER USER 'tester05'@'localhost' PASSWORD EXPIRE NEVER;

#当密码过期之后，提示更改密码，如不更改无法执行操作语句。
mysql> SELECT  1;
ERROR 1820 (HY000): You must reset your password using ALTER USER 
                    statement before executing this statement.

密码重用策略

MySQL允许对重用以前的密码进行限制。重用限制可以基于密码更改次数、时间两者建立。可以全局地建立重用策略，并且可以将单个帐户设置为遵循全局策略或使用特定的每个帐户行为覆盖全局策略。

帐户的密码历史记录由过去分配的密码组成。MySQL可以限制从这个历史记录中选择新密码
就是说帐户被限制在密码修改次数的基础上，新密码不能从最近的密码的指定次数中选择。
例如，如果密码修改次数设置为3次，则新密码不能与最近3次的密码相同。
根据时间限制帐户：
则不能从历史记录中更新于指定天数的密码中选择新密码。例如，如果密码重复使用间隔设置为60天，则新密码必须不是最近60天内所选择的密码。

指标	说明
password-history	用于根据所需的最少密码更改次数控制以前密码的重用。就是说密码多次变更后，可以重新使用之前密码。
password_reuse_interval	根据经过的时间控制以前密码的重用。

设置方式：

#my.cnf配置文件
[mysqld]
password_history=6
password_reuse_interval=365

#全局设置
SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;

#采取全局值
mysql> CREATE USER 'tester05'@'localhost' 
  PASSWORD HISTORY DEFAULT   PASSWORD REUSE INTERVAL DEFAULT;
mysql> ALTER USER 'tester05'@'localhost'
  PASSWORD HISTORY DEFAULT   PASSWORD REUSE INTERVAL DEFAULT;

#单个用户设置
mysql>CREATE USER 'tester05'@'localhost'
      PASSWORD HISTORY 5   PASSWORD REUSE INTERVAL 100 DAY;

mysql> ALTER USER 'tester05'@'localhost'  
       PASSWORD HISTORY 10   PASSWORD REUSE INTERVAL 365 DAY;

#同一个密码多次使用错误提示
mysql> ALTER USER 'tester05'@'localhost' IDENTIFIED BY '123456'; 
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'tester05'@'localhost' IDENTIFIED BY '123456'; 
ERROR 3638 (HY000): Cannot use these credentials for 'tester05@localhost' 
                    because they contradict the password history policy

备注：从这里分析出，MySQL8.0版本会隐秘的保留以前面密码数据字典当中。

验证旧密码策略

从MySQL 8.0.13开始，可以要求通过指定要替换的当前密码来验证更改帐户密码的尝试。这使得DBA能够防止用户在没有证明当前密码的情况下更改密码。

如果帐户设置为PASSWORD REQUIRED CURRENT，则密码更改必须指定当前密码。
如果帐户设置为PASSWORD REQUIRED CURRENT OPTIONAL，则密码更改不需要指定当前密码。

不同的策略下的影响：

Per-Account Setting	System Variable	Changes Require Current Password
PASSWORD REQUIRE CURRENT	OFF	Yes
PASSWORD REQUIRE CURRENT	ON	Yes
PASSWORD REQUIRE CURRENT OPTIONAL	OFF	No
PASSWORD REQUIRE CURRENT OPTIONAL	ON	No
PASSWORD REQUIRE CURRENT DEFAULT	OFF	No
PASSWORD REQUIRE CURRENT DEFAULT	ON	Yes

设置方式：

#my.cnf
[mysqld]
password_require_current=ON

#持久化配置文件
mysql> SET PERSIST password_require_current = ON;
mysql> SET PERSIST password_require_current = OFF;

#创建密码当前验证用户
mysql> DROP USER if EXISTS 'tester05'@'localhost';
mysql> CREATE USER 'tester05'@'localhost' PASSWORD REQUIRE CURRENT;
mysql> ALTER  USER 'tester05'@'localhost' PASSWORD REQUIRE CURRENT;

#更改密码
mysql> SELECT  USER() ;
+--------------------+
| USER()             |
+--------------------+
| tester05@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql>> ALTER USER USER() IDENTIFIED BY '1234567';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

mysql> ALTER USER USER() IDENTIFIED BY '1234567' REPLACE '123456';
Query OK, 0 rows affected (0.00 sec)

REPLACE方式在binlog二进制日志中省略原密码，以避免向其写入明文密码：

# at 4874
#230302 13:06:55 server id 129  end_log_pos 5109 CRC32 0x4430efad 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 154
SET TIMESTAMP=1677733615.932292/*!*/;
ALTER USER 'tester05'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

双重密码支持

从MySQL 8.0.14开始，允许用户帐户具有双重密码，为user设置主密码和辅助密码。好处是因为DBA人员变动无管理密码相关记录，不需要用skip-grant-tables重新启动服务。可以通过补助密码进行重置。
当然日常使用中只允许一个帐户使用主密码.

当分配新的主密码时，ALTER USER和SET PASSWORD语句的RETAIN CURRENT PASSWORD子句会将帐户当前密码保存为其辅助密码。
ALTER USER的DISCARD OLD PASSWORD子句丢弃帐户辅助密码，只保留主密码。
管理员操作辅助密码，需要APPLICATION_PASSWORD_ADMIN权限。

#添加补助密码
mysql>ALTER USER 'tester05'@'localhost'
      IDENTIFIED BY 'abc123' RETAIN CURRENT PASSWORD;

#丢弃帐户辅助密码
mysql> ALTER USER 'tester05'@'localhost' 
  DISCARD OLD PASSWORD;

补助密码会显示在mysql.user表的User_attributes属性上面：

mysql> SELECT * FROM mysql.user WHERE user='tester05'\G
*************************** 1. row ***************************
                    Host: localhost
                    User: tester05
。。。        
                  plugin: mysql_native_password
   authentication_string: *6691484EA6B50DDDE1926A220DA01FA9E575C18A
。。。
         User_attributes: {"additional_password": "*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9"}

随机密码生成

从MySQL 8.0.18开始，CREATE USER、ALTER USER和SET PASSWORD语句可以为用户帐户生成随机密码，而不需要管理员指定的明文密码。以后不用为设置强度过硬的密码而苦恼。

默认情况下，生成的随机密码长度为20个字符。此长度由generated_random_password_length系统变量控制，其范围为5到255。

对于每个由语句生成随机密码的帐户，该语句将密码存储在mysql.user系统表中，并为帐户身份验证插件进行适当的hash。之后执行语句还返回结果集的一行中的明文密码。

mysql> DROP USER if EXISTS 'tester05'@'localhost';
mysql> CREATE USER 'tester05'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
+----------+-----------+----------------------+-------------+
| user     | host      | generated password   | auth_factor |
+----------+-----------+----------------------+-------------+
| tester05 | localhost | dcG0UQbjr<,lbk:x:%.: |           1 |
+----------+-----------+----------------------+-------------+
1 row in set (0.00 sec)

登录失败跟踪

从MySQL 8.0.19开始，管理员可以配置用户帐户连续多次登录失败会导致帐户暂时锁定。
登录失败：是指客户端在尝试连接时未能提供正确的密码。它不包括由于未知用户或网络问题等原因导致的连接失败。对于具有双重密码的帐户，任何一个帐户密码都是正确的。

使用CREATE USER和ALTER USER语句中的FAILED_LOGIN_ATTEMPTS和PASSWORD_LOCK_TIME选项，每个帐户都可以配置所需的登录失败次数和锁定时间。

FAILED_LOGIN_ATTEMPTS尝试N
此选项指示是否跟踪指定错误密码的帐户登录尝试。数字N指定有多少连续错误密码导致临时帐户锁定。
PASSWORD_LOCK_TIME｛N|无界｝
此选项指示在连续多次登录尝试提供错误密码后锁定帐户的时间。该值是一个数字N，用于指定帐户保持锁定的天数，或者是UNBOUNDED，用于指定当帐户进入临时锁定状态时，该状态的持续时间是无限的，直到帐户解锁后才会结束。

备注：N值范围为0到32767

设置方式：

mysql> DROP USER if EXISTS 'tester05'@'localhost';
mysql> CREATE USER 'tuserer05'@'localhost' IDENTIFIED BY '123456'  
       FAILED_LOGIN_ATTEMPTS  3 PASSWORD_LOCK_TIME 7;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

登录验证：

#第一次 
shel# mysql -utuser-p -S /opt/data8.0/data/mysql.sock
Enter password: 
ERROR 1045 (28000): Access denied for user 'tuser'@'localhost' (using password: YES)

#第二次 
shel# mysql -utuser-p -S /opt/data8.0/data/mysql.sock
Enter password: 
ERROR 1045 (28000): Access denied for user 'tuser'@'localhost' (using password: YES)

#第三次
shel# mysql -utuser-p -S /opt/data8.0/data/mysql.sock
Enter password: 
ERROR 3955 (HY000): Access denied for user 'tuser'@'localhost'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 3 consecutive failed logins.

以下是设呢情况下进行重置
1.全局重置：

服务器重新启动
FLUSH PRIVILEGES （使用skip-grant-tables授权表启动服务器会导致不读取授权表，从而禁用失败的登录跟踪）

2.单独用户账号重置:

此用户登录成功。
锁定持续时间已过。在这种情况下，失败的登录计数在下次登录尝试时重置。
执行ALTER USER语句变更FAILED_LOGIN_ATTEMPTS或PASSWORD_LOCK_TIME（或两者）设置为任何值。会重置
执行ALTER USER …UNLOCK语句。

3.对于PROXY USER用户代理，则对代理用户跟踪而不是被代理用户。

总结

在账号安全方面MySQL8.0做了更多的实用的功能，让账号安全得到更多的保证，在实际环境中要合理的利用起来。期待后续可以完全把validate_password组件&Connection-Control插件集成到mysql.user表的User_attributes里。

组合操作：

mysql> DROP USER if EXISTS 'tuser'@'localhost';
mysql> CREATE USER 'tuser'@'localhost' IDENTIFIED BY RANDOM PASSWORD
     PASSWORD EXPIRE INTERVAL 90 DAY  
     PASSWORD HISTORY 5   PASSWORD REUSE INTERVAL 100 DAY 
     PASSWORD REQUIRE CURRENT  
     FAILED_LOGIN_ATTEMPTS  3 PASSWORD_LOCK_TIME 7;
+----------+-----------+----------------------+-------------+
| user     | host      | generated password   | auth_factor |
+----------+-----------+----------------------+-------------+
| tuser    | localhost | @OXsX}}Cidoy.kqWxF@6 |           1 |
+----------+-----------+----------------------+-------------+
1 row in set (0.00 sec)
mysql> ALTER USER 'tuser'@'localhost'
       IDENTIFIED BY 'tuser' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT  * FROM  mysql.user WHERE  user='tuser'\G
*************************** 1. row ***************************
                    Host: localhost
                    User: tuser
。。。
        password_expired: N
   password_last_changed: 2023-03-02 14:16:05
       password_lifetime: 90
          account_locked: N
        Create_role_priv: N
          Drop_role_priv: N
  Password_reuse_history: 5
     Password_reuse_time: 100
Password_require_current: Y
         User_attributes: {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 7}, "additional_password": "*8F31DF06BEBB76AF0105DFA4AAA0C359407C5E15"}

字段说明：

过期时间相关：password_lifetime，password_expired
密码重用 Password_reuse_history ，Password_reuse_time
密码验证 Password_require_current
双重密码,登录失败跟踪：User_attributes属性

墨力计划 mysql

最后修改时间：2023-03-07 09:14:21

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

关注作者

文章被以下合辑收录

专家专栏 | 崔虎龙（共158篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。