mysql8.0之后的随机密码_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字段)。