阅读量:0
目录
2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度
2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度
MySQL 版本：8.0.16
需求：
- 批量生成 100 个密码。
- 密码长度统一为 16 个字符。
- 密码中包含数字、大小写字母、特殊字符。
一、初始实现
1. 创建生成密码的函数
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）安装密码验证插件
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）设置密码验证策略参数
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）批量生成密码并获取强度
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. 创建生成密码的函数
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 rand_min int default 1; declare rand_max int default length(allowedchars); declare regexp_str varchar(128) default '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-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(rand_min + rand() * (rand_max - rand_min)), 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]) 判断特殊字符是否存在。
.{16} 代表任意 16 个字符。
$ 代表结尾。
2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度
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. 卸载密码验证插件
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>