MySQL 8.0系列——生成随机密码

背景

从前,有一个客户,数据库高权账号的密码,要求每天自动地修改为随机密码,以增强数据库的安全性。

我们开发了一个用程序模块来实现的,当然您也可以用脚本 + cron 来实现。

现在,实现这个需求更为简单了。

8.0.18版本新增了一个小功能,CREATE USER,ALTER USER和 SET PASSWORD语句可以生成随机密码。

生成随机密码

mysql> create user 'fanderchan'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------------+------+----------------------+
| user       | host | generated password   |
+------------+------+----------------------+
| fanderchan | %    | YMeIblT.jRciKLWkB0RL |
+------------+------+----------------------+
1 row in set (0.01 sec)

mysql> ALTER USER 'fanderchan'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------------+------+----------------------+
| user       | host | generated password   |
+------------+------+----------------------+
| fanderchan | %    | !bnMk/Lu4C87VMOdXQh. |
+------------+------+----------------------+
1 row in set (0.01 sec)

mysql> SET PASSWORD FOR 'fanderchan'@'%' TO RANDOM;
+------------+------+----------------------+
| user       | host | generated password   |
+------------+------+----------------------+
| fanderchan | %    | dCdJT5h9[Gm/dsC.aVHm |
+------------+------+----------------------+
1 row in set (0.01 sec)

可以看见默认情况下,随机密码长度为20个字符,并且具有很好的复杂度(大写字母、小写字母、特殊符号、数字)

相关参数

控制随机密码的长度

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

补充说明

  • validate_password是一个可选安装的MySQL密码增强插件,但随机密码不受validate_password插件的影响和控制
  • MySQL8.0 默认的密码认证插件是caching_sha2_password,MySQL5.7 默认的密码认证插件是mysql_native_password
mysql> show global variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

所以,我在这里补充两个MySQL 8.0的授权语法和现象:

mysql> create user 'fander1'@'%' IDENTIFIED WITH 'caching_sha2_password' BY RANDOM PASSWORD;
+---------+------+----------------------+
| user    | host | generated password   |
+---------+------+----------------------+
| fander1 | %    | Mk3UO%gi8HB6Qe>KFKxE |
+---------+------+----------------------+
1 row in set (0.01 sec)

mysql> create user 'fander2'@'%' IDENTIFIED WITH 'mysql_native_password' BY RANDOM PASSWORD;
+---------+------+----------------------+
| user    | host | generated password   |
+---------+------+----------------------+
| fander2 | %    | Kp+t0g-3ALKeisQ>yBU/ |
+---------+------+----------------------+
1 row in set (0.00 sec)

mysql> select user,host,plugin,authentication_string from mysql.user where user like 'fander%';
+------------+------+-----------------------+------------------------------------------------------------------------+
| user       | host | plugin                | authentication_string                                                  |
+------------+------+-----------------------+------------------------------------------------------------------------+
| fander1    | %    | caching_sha2_password | $A$005$WG[R/	c]0Z8wdaalCKRoKJFNkh1owsuzQ0lsP9JSGLDHlmdGhM8DvSM1 |
| fander2    | %    | mysql_native_password | *8DADAA12E42653774E3CB670F92E0A58171FE2E8                              |
+------------+------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

我想表达的是,在binlog里,授权语句的密码显示为加密后的的密文密码 (authentication_string字段)。

参考:
https://dev.mysql.com/doc/refman/8.0/en/password-management.html#random-password-generation

posted on 2019-11-01 16:21  fanderchan  阅读(3035)  评论(0编辑  收藏  举报

导航