前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >用 MySQL 批量生成强密码

用 MySQL 批量生成强密码

作者头像
用户1148526
发布于 2024-07-26 12:27:14
190
发布于 2024-07-26 12:27:14
举报
文章被收录于专栏：Hadoop数据仓库

一、初始实现

1. 创建生成密码的函数

代码语言：javascript
复制
use test;
drop function if exists fn_GenerateStrongPassword;
delimiter //
 
create function fn_GenerateStrongPassword(length int) 
returns varchar(255)
no sql
begin
    declare allowedchars varchar(128) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()-_=+[]{}\|;:,./?';
    declare returnstr varchar(255) default '';
    declare i int default 0;
 
    while i < length do
        set returnstr = concat(returnstr, substring(allowedchars, floor(1 + rand() * 87), 1));
        set i = i + 1;
    end while;

    return returnstr;
end //
 
delimiter ;

说明：

  • 密码长度为入参。
  • 从构成密码的 88 个预定义字符中随机取 16 个字符。

2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度

validate_password 是 MySQL 默认的密码管理策略插件，可通过配置对用户密码长度、强度进行管理。

（1）安装密码验证插件
代码语言：javascript
复制
mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name                            | Status   | Type               | Library              | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |

...

| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL                 | GPL     |
| validate_password               | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+---------------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)

mysql>
（2）设置密码验证策略参数
代码语言：javascript
复制
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.00 sec)

mysql> set global validate_password_length=16;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password_policy=2;
Query OK, 0 rows affected (0.00 sec)

mysql>

密码验证策略参数说明如下表：

参数

默认值

描述

validate_password_check_user_name

OFF

设置为 ON 时表示能将密码设置为当前用户名

validate_password_dictionary_file

用于检查密码的字典文件的文件名，默认为空。

validate_password_length

8

密码的最小长度

validate_password_mixed_case_count

1

如果密码策略是中等或更强，validate_password要求密码具有的小写和大写字符的最小数量。

validate_password_number_count

1

密码必须包含的数字个数。

validate_password_policy

MEDIUM

密码强度检验等级，可以使用数值0、1、2或LOW、MEDIUM、STRONG来指定。

validate_password_special_char_count

1

密码必须包含的特殊字符个数。

MySQL 密码强度等级定义如下表：

密码规则

强度得分

Length < 4

0

Length >= 4 and < validate_password_length

25

Satisfies policy 1（LOW：只验证长度）

50

Satisfies policy 2（MEDIUM：验证长度、数字、大小写字母、特殊字符）

75

Satisfies policy 3（STRONG：验证长度、数字、大小写字母、特殊字符、字典文件）

100

（3）批量生成密码并获取强度
代码语言：javascript
复制
mysql> select password, strength, if(r=1,n,'') n
    ->   from (select password, strength, 
    ->                row_number() over (partition by strength order by password desc) n,
    ->                rank() over (partition by strength order by password) r
    ->           from (select password, validate_password_strength(password) strength
    ->                   from (select test.fn_GenerateStrongPassword(16) password from mysql.help_topic limit 100) t
    ->                  order by strength, password) t) t;
+------------------+----------+----+
| password         | strength | n  |
+------------------+----------+----+
| ,/bn@p0/&?3m(5(h |       50 | 13 |
| C.=IK]tLPhb-jd=n |       50 |    |
| cfveQz/gH@Qu#?%) |       50 |    |
| E(?W&x.eCQO=},hU |       50 |    |
| H:YeTO%I@K}B=DiO |       50 |    |
| Jc{NUCco!lRg}B=A |       50 |    |
| Mkxar?F=nv$a*;C( |       50 |    |
| OFLhjyj7clYQ0gwf |       50 |    |
| rCeyxOVDet.paOAl |       50 |    |
| sLUEn#t[#glN+.wF |       50 |    |
| TGCRVsKNd*}m?V#h |       50 |    |
| tooBiSy[Vj{qsT:r |       50 |    |
| Ulhd|U+@eizri]pt |       50 |    |
| _JQw&pTf%1%?6C5x |      100 | 87 |
| -#u|}*w+1Iq#oYIx |      100 |    |
| -7{NXS)1U=7(d$5. |      100 |    |
| ,{4PO}pnv#?##Oij |      100 |    |
| !^@BJixcD1auoia! |      100 |    |
| ?[ICQL7jQmt!]B|7 |      100 |    |
| .Y.k6/]VbQFF0Mim |      100 |    |
| (8/_x)y.cr|bARWx |      100 |    |
| (rYx4F}O11tz^c(/ |      100 |    |
| [S}cJ.ZgY88E0Os7 |      100 |    |
| ]:fRz:[Wk:E:8Y+W |      100 |    |
| @fkK@I^0s:h2^fa( |      100 |    |
| #6hPifc,07sg!F6t |      100 |    |
| #AB4wFcgAAX.k8fw |      100 |    |
| %9s:l*ZYk+/C7G_i |      100 |    |
| ^*6}1wQ6_sXooBgJ |      100 |    |
| ^EMnK4:)x[Yz9Z+Z |      100 |    |
| =d2:|$/7Ja)h:PUw |      100 |    |
| =moJVP$CG]EhH7ra |      100 |    |
| |^js$jyh2&hglJ7l |      100 |    |
| |99H[rGvk]f4cs?B |      100 |    |
| 1.dyz3xMJ@L.V(L@ |      100 |    |
| 15KmO}oh|Fcfu.n_ |      100 |    |
| 1A@!K}D|3E{LOc%! |      100 |    |
| 2l*14G;Zk--35H.8 |      100 |    |
| 4^?2k#ETWtLSw^im |      100 |    |
| 6PJ3]Zy2vFiI0CT3 |      100 |    |
| 8r.wHmUy%b$$QrS/ |      100 |    |
| 90K.3tvXh!DW$jyg |      100 |    |
| 9I+nqP-#p6^|xYg5 |      100 |    |
| 9Z=Uh)+=0;XdSIPp |      100 |    |
| AF}P4$-|m}l:E{MT |      100 |    |
| AMyi3=IJ=f#TJOkp |      100 |    |
| bm6+HyxU)Rf-;rqC |      100 |    |
| c#3[Vi_|h6fAB0X0 |      100 |    |
| c+w0#X5V$lJ3:)w_ |      100 |    |
| C=z}9#Z^f?*b8G(: |      100 |    |
| C$&0n5*c$6gH8vsA |      100 |    |
| CKlFJ.3rkb0QDtg@ |      100 |    |
| CMw,btdW62k%L]om |      100 |    |
| D!W2LBzT!b|Yj)$C |      100 |    |
| d/&?2fRBfG9CY}*x |      100 |    |
| e),C%)-9/_x)x[2M |      100 |    |
| e%9n9ceo8{O5(lu# |      100 |    |
| f{Ea/}4Kq7*?W*Ao |      100 |    |
| fEV60TQ++$pZNPb5 |      100 |    |
| GTTbUZHmXHvg2-Ew |      100 |    |
| i^Z4JmRf**ZXeW3P |      100 |    |
| Ip9;1rrHCPK4;%a# |      100 |    |
| J_bU1MI5gJ_.A5C6 |      100 |    |
| Jeehyl-^FQFE7BW_ |      100 |    |
| k(8/=ChK[i]mf]vX |      100 |    |
| L%VN4*igjBvA^.U% |      100 |    |
| LVLRu80QAgOg.5C0 |      100 |    |
| m{g9tf2[XpsS|fY9 |      100 |    |
| M&16OHSKUM197xC- |      100 |    |
| M%Pls68Jc[DeueSK |      100 |    |
| md%@CMu=YA(kr2Q1 |      100 |    |
| Mfa)g;NPa3gUKTDk |      100 |    |
| N!t.rg&0p#s(L9s, |      100 |    |
| nnBl9iI0DZ{$fc/- |      100 |    |
| o,yV[^s#hq9}OYR& |      100 |    |
| O*25Ida;4JlK@E1: |      100 |    |
| q7)f=kb@1_INe;R9 |      100 |    |
| qLY3Igs)P{j-_0b; |      100 |    |
| Rh;Kxg1^f?&.QRf* |      100 |    |
| s%q45D$!COHSKXZs |      100 |    |
| s2Js-Uov!=lhd:2s |      100 |    |
| STh+f9zHfmSnu!]z |      100 |    |
| t#ea]N13C^[h&7bg |      100 |    |
| tFkTv6V%q7^[cV4T |      100 |    |
| TGyyW.qbUZHmVB|7 |      100 |    |
| TM4=Es/zR48TVnoF |      100 |    |
| Ty+P^N:IlP.NDC1e |      100 |    |
| tZC)lr2OP/OK8o*Q |      100 |    |
| U_46H,2suU;j@DRO |      100 |    |
| u_Ule_j/1fSGDZ}& |      100 |    |
| u$fdcb?|4JjDE#6j |      100 |    |
| Ulf|LI9yB#^4+JM. |      100 |    |
| V(L9p-1U[&Ao{aD9 |      100 |    |
| V=31i9q[*EG(|sx9 |      100 |    |
| wQ9.(jjs@a-mu$d. |      100 |    |
| WT|i9p&J9vnb3hXZ |      100 |    |
| x*nHOpQ|nbZ]0=ox |      100 |    |
| X8#5cr{;,/eywLJ$ |      100 |    |
| X9%#J*(3@#SDo(Vz |      100 |    |
| Z|]5Y.iZ4Kq0;WaF |      100 |    |
+------------------+----------+----+
100 rows in set (0.00 sec)

mysql>

从查询结果可以看到，有 13 个密码的强度得分为 50，原因是使用随机函数循环取得 16 个字符，有一定的概率不能完全覆盖到数字、大小写字母和特殊字符。如 Mkxar?F=nv$a*;C( 中就没有数字。经过多次测试，约有 10% - 20% 会出现这种情况。

二、改进实现

1. 创建生成密码的函数

代码语言：javascript
复制
use test;
drop function if exists fn_GenerateStrongPassword;
delimiter //
 
create function fn_GenerateStrongPassword(length int) 
returns varchar(255)
no sql
begin
    declare allowedchars varchar(128) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()-_=+[]{}\|;:,./?';
    declare regexp_str varchar(128) default '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[^a-zA-Z0-9])([a-zA-Z0-9]|[^a-zA-Z0-9]){16}$';
    declare returnstr varchar(255) default '';
    declare i int default 0;
 
    add_loop:loop
        set returnstr = '';
        set i = 0;
 
        -- 密码长度为入参
        while i < length do
            set returnstr = concat(returnstr, substring(allowedchars, floor(1 + rand() * 87), 1));
            set i = i + 1;
        end while;
        
        -- 用正则函数判断强度，区分大小写
        if not regexp_like(returnstr,regexp_str,'c') then 
            iterate add_loop;    -- 不满足需求则重新生成密码
        else 
            leave add_loop;      -- 满足需求则退出循环
        end if;
    end loop add_loop;
 
    -- 返回符合需求的密码
    return returnstr;
end //
 
delimiter ;

说明：

  • 增加一层外循环，用于迭代生成一个完整的密码。
  • 用正则函数 regexp_like 判断强度，不满足需求则重新生成密码，满足需求则退出循环，然后返回结果。注意要使用区分大小写的匹配类型（regexp_like 的第三个参数设置为 'c'）。
  • 正则表达式说明：
    • ^ 代表开始。
    • (?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[^a-zA-Z0-9]) 用四个肯定顺序环视零宽断言对字符进行判定。
    • (?=.*[a-z]) 判断小写字母是否存在。
    • (?=.*[A-Z]) 判断大写字母是否存在。
    • (?=.*[0-9]) 判断数字是否存在。
    • (?=.*[^a-zA-Z0-9]) 判断特殊字符是否存在。
    • ([a-zA-Z0-9]|[^a-zA-Z0-9]){16} 代表从两个 [] 内的字符中任取一个，共 16 位。
    • $ 代表结尾。

2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度

代码语言：javascript
复制
mysql> select password, strength, if(r=1,n,'') n
    ->   from (select password, strength, 
    ->                row_number() over (partition by strength order by password desc) n,
    ->                rank() over (partition by strength order by password) r
    ->           from (select password, validate_password_strength(password) strength
    ->                   from (select test.fn_GenerateStrongPassword(16) password from mysql.help_topic limit 100) t
    ->                  order by strength, password) t) t;
+------------------+----------+-----+
| password         | strength | n   |
+------------------+----------+-----+
| _i:MJ7m9ciGX#?$& |      100 | 100 |
| _mpO*XO206rbQGI+ |      100 |     |
| ,MFNr3T@gs)N)$yn |      100 |     |
| ;OSoAal5=CfEU4X| |      100 |     |
| ;rsN3^?4rh-]?xF? |      100 |     |
| !a}U:m[:i7cn7[JJ |      100 |     |
| !DSXB[KQnBjZ2D)g |      100 |     |
| .;$.1k^UDhJ&!t,k |      100 |     |
| .{1B$)+_59TTe9zK |      100 |     |
| (8,%|C=z|$.1k&XS |      100 |     |
| (J2=KTHF3fMc&[eZ |      100 |     |
| (Vx#+mjhmM*5-u89 |      100 |     |
| [MYXg9ug0yxV{=XA |      100 |     |
| {1D=w#}D/{UaH{Hr |      100 |     |
| }cJ;PZS%yl)@o5@0 |      100 |     |
| @xoc5p|j$M.QXEdo |      100 |     |
| *d*]bLkzj6,)u9*} |      100 |     |
| &r3V_YHo9,@%3+LW |      100 |     |
| #u||[ZC+GxrsMY1v |      100 |     |
| %1!({h!CRQ.IfiDG |      100 |     |
| ^(9eo9?=Cewm,E+A |      100 |     |
| ^*6|3D_qJQqS,zZg |      100 |     |
| ^v})FD1cDY]9)b4j |      100 |     |
| +@fiCE%^Z1vMN,Jp |      100 |     |
| =Q[,k#DPFG%0wqmm |      100 |     |
| |_N%K-{aD7C2i7cm |      100 |     |
| |R@mTsO8chDNt&x. |      100 |     |
| $,Y/re4fI*&WKM;H |      100 |     |
| 0{JzyR4!6o_#mQd@ |      100 |     |
| 26NyeJ[qA;*qTe8x |      100 |     |
| 2l=_8+z+O!r_32ob |      100 |     |
| 2MGUYy8W@?$))6_v |      100 |     |
| 3hTIH$1^c=qGAJlK |      100 |     |
| 4_Dm0kO/T6#!G0K. |      100 |     |
| 4+LYXk{mc!Ps7!Z& |      100 |     |
| 48U3R6^{sB.[P#zw |      100 |     |
| 5C0W79Ot!;T)Tpy{ |      100 |     |
| 5V%s#iu[9=pBeCNy |      100 |     |
| 7(d%8m5*hc{IAB7L |      100 |     |
| 72eJ}B_w*qVnlr3T |      100 |     |
| 8,$]ooDo=%xeOpP- |      100 |     |
| 89LhlHTO^O/V&yfM |      100 |     |
| 8gztx7V!:1pf&#CK |      100 |     |
| 9/_u80QCp[=43rmk |      100 |     |
| asbPBm^K(&Qg}C|4 |      100 |     |
| bzMBwE,$]oqM2&f. |      100 |     |
| c-g]uO6]R&P?X=YB |      100 |     |
| C3n]aF^&181:,;(w |      100 |     |
| CiSv0^%Sv80Py,,| |      100 |     |
| dAJkFNoP]cOv-M9n |      100 |     |
| DZ,/ezA7Ox,?iRu4 |      100 |     |
| eBKmP|tC/:$:Q2Xa |      100 |     |
| F{MPh.2oaQN(7:@^ |      100 |     |
| fnYMRlpP=_47KkEF |      100 |     |
| GCQO+,qg*^Ob7AS6 |      100 |     |
| gRBdyz1n,D-mqTe7 |      100 |     |
| gZ!-aV9-h:QXIvh8 |      100 |     |
| hAvB)pL2^e{GmZT+ |      100 |     |
| IhwbzLyi7ds};:{9 |      100 |     |
| ijyhZ87yJs(M#K=c |      100 |     |
| Ir_1OTrO@t|:am0l |      100 |     |
| JOhe/$-]/tpu1D]M |      100 |     |
| Jxn;tA_Dm8?;7X-O |      100 |     |
| k0n9.*f|LFV52k^R |      100 |     |
| k296q.zW|;]4U%r0 |      100 |     |
| kFKc[Cbfxm;wP22v |      100 |     |
| l3#-}j%Sw^imN(!n |      100 |     |
| lGQzan^H1=Q]:i2& |      100 |     |
| liis#hq0/^|z!8yC |      100 |     |
| LK(&Rmu!}JAxMH4b |      100 |     |
| MlDxC_u5U#hr%r@e |      100 |     |
| ng]w3F]JDOAn-!gt |      100 |     |
| nJY73i4]Ve2;(w-K |      100 |     |
| nnAgM?3qd5iWQ&N{ |      100 |     |
| o%EOx.cvnd@VUd1{ |      100 |     |
| oQ}i%UFtdTP(3!9B |      100 |     |
| pnv^ju_SbW#d,02? |      100 |     |
| qL198ARXFjM+e4dz |      100 |     |
| r%t)K2_FxwQ8|Ynb |      100 |     |
| RZNQje}NTu3Lzm+[ |      100 |     |
| S+&EG_ja8Ke/^;D} |      100 |     |
| s0]C.+KTChL}y8V9 |      100 |     |
| Smt51gX1A#%3(oJT |      100 |     |
| Su5W*FI{A-w^kzgS |      100 |     |
| T6$$TFuh@G8zKu}+ |      100 |     |
| TJM?4trCds,n].sk |      100 |     |
| TN6.=IG0L?!Y$b_q |      100 |     |
| Tv6Y[9-f-:xQ39X& |      100 |     |
| U4Y?wxYk__9|V/xG |      100 |     |
| Uj,PSljlFI{C{Wf5 |      100 |     |
| V!|Q6-qO%J#Rv#.4 |      100 |     |
| VJJ-|j%Tz}0$7hI# |      100 |     |
| vpnt9^)^K^184l$F |      100 |     |
| W@:V+#ht=ZHn1Yj^ |      100 |     |
| W9*|vKG3an$zux7V |      100 |     |
| X5T9]EgFV54tsJI% |      100 |     |
| Y{(Bp;l[?D9MnK5/ |      100 |     |
| yQZQ8,@*&XMVGug4 |      100 |     |
| Z87ARWC,(nDuolly |      100 |     |
| zcwsx!*-%CB4spu6 |      100 |     |
+------------------+----------+-----+
100 rows in set (0.01 sec)

mysql>

从查询结果可以看到，所有 100 个密码强度得分都是 100，完全满足需求。因为不满足需求的情况不到 20%，所以不用担心外层循环陷入无休止的迭代中。当然必须指出，这版实现使用的是“判错重来”方式，会有不到 20% 的内层 while 循环做了无用功。

3. 卸载密码验证插件

代码语言：javascript
复制
mysql> uninstall plugin validate_password;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show plugins;
+---------------------------------+----------+--------------------+---------+---------+
| Name                            | Status   | Type               | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |

...

| mysqlx                          | ACTIVE   | DAEMON             | NULL    | GPL     |
| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL    | GPL     |
+---------------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)

mysql> show variables like 'validate_password%';
Empty set (0.00 sec)

mysql>
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2024-07-25，如有侵权请联系 cloudcommunity@tencent.com 删除
mysql
rows
set
插件
函数

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

mysql
rows
set
插件
函数
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 一、初始实现
    • 1. 创建生成密码的函数
      • 2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度
        • （1）安装密码验证插件
        • （2）设置密码验证策略参数
        • （3）批量生成密码并获取强度
    • 二、改进实现
      • 1. 创建生成密码的函数
        • 2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度
          • 3. 卸载密码验证插件
          相关产品与服务
          云数据库 MySQL
          腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
          产品介绍产品文档
          云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
          领券

          腾讯云开发者

          扫码关注腾讯云开发者

          扫码关注腾讯云开发者

          领取腾讯云代金券

          热门产品

          热门推荐

          更多推荐

          Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

          深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

          腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

          Copyright © 2013 - 2024 Tencent Cloud.

          All Rights Reserved. 腾讯云 版权所有

          登录 后参与评论