密码需要带特殊字符(二)
此篇博文是这篇密码需要带特殊字符的升级篇,前一篇的存储过程产生的密码随机数,有可能一个随机数就包含有过多的特殊字符,而且第一个字符就有可能是特殊字符。
因此产生此篇,随机密码首字符不能为数字与特殊字符但必须包含且只有一个特殊字符。
另外还修正了特殊字符集长度问题,使用特殊字符集长度减一(LEN(@SpecialCharacter) - 1) 这样当你增减特殊字符集时,再不需多次地方一同修改。
修正前:
SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * 6 + 1,0)),1)
修正后:
SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
完整的存储过程,仅供参考。
usp_RandomPassword
ALTER PROCEDURE [dbo].[usp_RandomPassword]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N''
DECLARE @SpecialCharacter NVARCHAR(255) = N'@#$%&*?' --特殊字符集
DECLARE @HasSpcCht BIT = 0 --判断产生的随机数是否有包含随机数
DECLARE @L INT = 1
DECLARE @R TINYINT,@R1 TINYINT,@R2 TINYINT
WHILE @L <= @Length --循环密码长度,每一位字符将随机产生
BEGIN
IF @L = 1 --随机数第一位只为字母,大写或小写
BEGIN
SET @R = ROUND(RAND() * 1, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
END
END
ELSE
BEGIN
IF @L = @Length AND @HasSpcCht = 0 --当最后一位时,如果没有产生过特殊字符,那为随机数产生一个。
BEGIN
SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
SET @HasSpcCht = 1
END
ELSE
BEGIN
SET @R1 = ROUND(RAND() * 3, 0)
IF @R1 = 0
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 97,0))
IF @R1 = 1
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 65,0))
IF @R1 = 2
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 9 + 48,0))
IF @R1 = 3 --随机产生特殊字符
BEGIN
IF @HasSpcCht = 0 --如果没有产生过,那为随机数产生一个。
BEGIN
SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
SET @HasSpcCht = 1
END
ELSE --如果已经产生过特殊字符,只循环产生字母,或数字
BEGIN
SET @R2 = ROUND(RAND() * 2, 0)
SET @RandomPassword = @RandomPassword + CASE @R2
WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
WHEN 2 THEN CHAR(ROUND(RAND() * 9 + 48,0))
END
END
END
END
END
SET @L = @L + 1 --随机产生一位,随机数位数加一位。
END
SELECT @RandomPassword
END
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR(MAX) = N''
DECLARE @SpecialCharacter NVARCHAR(255) = N'@#$%&*?' --特殊字符集
DECLARE @HasSpcCht BIT = 0 --判断产生的随机数是否有包含随机数
DECLARE @L INT = 1
DECLARE @R TINYINT,@R1 TINYINT,@R2 TINYINT
WHILE @L <= @Length --循环密码长度,每一位字符将随机产生
BEGIN
IF @L = 1 --随机数第一位只为字母,大写或小写
BEGIN
SET @R = ROUND(RAND() * 1, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
END
END
ELSE
BEGIN
IF @L = @Length AND @HasSpcCht = 0 --当最后一位时,如果没有产生过特殊字符,那为随机数产生一个。
BEGIN
SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
SET @HasSpcCht = 1
END
ELSE
BEGIN
SET @R1 = ROUND(RAND() * 3, 0)
IF @R1 = 0
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 97,0))
IF @R1 = 1
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 65,0))
IF @R1 = 2
SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 9 + 48,0))
IF @R1 = 3 --随机产生特殊字符
BEGIN
IF @HasSpcCht = 0 --如果没有产生过,那为随机数产生一个。
BEGIN
SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
SET @HasSpcCht = 1
END
ELSE --如果已经产生过特殊字符,只循环产生字母,或数字
BEGIN
SET @R2 = ROUND(RAND() * 2, 0)
SET @RandomPassword = @RandomPassword + CASE @R2
WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
WHEN 2 THEN CHAR(ROUND(RAND() * 9 + 48,0))
END
END
END
END
END
SET @L = @L + 1 --随机产生一位,随机数位数加一位。
END
SELECT @RandomPassword
END