主页 / dba / 问题 / 108439
Accepted
JGA
Asked: 2015-07-30 06:07:20 +0800 CST

密码生成器功能

  • 772

如果无法在函数中使用非确定性函数，那么在 SQL Server 中创建密码生成器用户定义函数的最佳方法是什么？

我想创建一个函数，该函数使用给定字符串中的字符返回随机生成的 varchar(10)，例如：“1234567890QWERTYUIOPASDFGHJKLZXCVBNM”

我有自己的想法，但不是很自然的解决方案。

该功能背后的想法是，如果有人需要重置密码，我可以执行以下操作：

UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

一个函数比一个单独的预分配密码表更容易部署和重用。

sql-server functions

3 个回答

  1. Best Answer

    您有几个选项（TL；DR -> 工作功能在选项 #2 的末尾，但另请参阅选项 #3 之后的更新部分！）：

    1. 如果您只使用十六进制值（0 - 9 和 A - F），您可以调用SQL Server 2008 中引入的CRYPT_GEN_RANDOM ：

      SELECT CRYPT_GEN_RANDOM(5) AS [Hex],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS [HexStringWithout0x],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS [Translated-ASCII],
       CONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS [Translated-UCS2orUTF16]

      返回：

      Hex             HexStringWithout0x    Translated-ASCII    Translated-UCS2orUTF16
0x4F7D9ABBC4    0ECF378A7A            ¿"bü<ݱØï           튄ꆠ䤅㫘ᓟ멿ৢ폫씎䛯

      如您所见，您需要使用函数2上的 format 选项，CONVERT这样它就不会为您提供虽然更多样化但可能更难输入的字符，并且您不会获得领先的0x. 但是你也可以看到，即使只有一行，多次调用也会得到不同的返回值（就像NEWID()）。

      请注意传递给CRYPT_GEN_RANDOM函数的“长度”值。由于一个十六进制字节是 2 个字符，因此您只需要生成一个 5 字节的值。使用CONVERT(VARCHAR...10 的“长度”，因为每个十六进制字节都变成一个字符。并且CONVERT(NVARCHAR...使用 20 的“长度”，因为每 2 个字节将成为一个字符（在大多数情况下，在默认排序规则不以 结尾的数据库中运行它时）。_SC

    2. 您始终可以创建自己的算法并传入CRYPT_GEN_RANDOM随机方面的值。并且，如果您可以将您的算法构造为单一的SELECT，以便可以在内联表值函数/iTVF（通常使用 CTE）中完成，那么您在技术上可以使用非确定性函数（至少有效），因为函数调用将传入表达式而不是该表达式的结果，就像它对多语句 TVF 和标量 UDF 所做的那样：

      USE [tempdb];
GO

CREATE FUNCTION dbo.GeneratePassword_InlineTVF(@RandomValue VARBINARY(10))
RETURNS TABLE
AS RETURN
  SELECT CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2) AS [HowRandomAmI?];
GO

CREATE FUNCTION dbo.GeneratePassword_MultistatementTVF(@RandomValue VARBINARY(10))
RETURNS @Result TABLE ([HowRandomAmI?] VARCHAR(100))
AS
BEGIN
  INSERT INTO @Result ([HowRandomAmI?])
         VALUES (CONVERT(VARCHAR(20), @RandomValue, 2) +
                 '~~' +
                 CONVERT(VARCHAR(20), @RandomValue, 2));
  RETURN;
END;
GO

CREATE FUNCTION dbo.GeneratePassword_ScalarUDF(@RandomValue VARBINARY(10))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2);
END;
GO

      然后运行测试：

      SELECT * FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10));
-- 27169EEC2B9CF7CC2731~~E9A95F49060BD41C0FF6

SELECT * FROM tempdb.dbo.GeneratePassword_MultistatementTVF(CRYPT_GEN_RANDOM(10));
-- 68EBC132814EA78602DE~~68EBC132814EA78602DE

SELECT tempdb.dbo.GeneratePassword_ScalarUDF(CRYPT_GEN_RANDOM(10)) AS [HowRandomAmI?];
-- 702DAF1C441C42FFBF5F~~702DAF1C441C42FFBF5F

      只是为了确定 iTVF 中的多行：

      SELECT TOP 5 rnd.[HowRandomAmI?]
FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10)) rnd
CROSS JOIN [master].[sys].[objects];

      返回：

      HowRandomAmI?
---------------
1D57F0ABFDE44BCAED00~~AD57E9E2FF01768BB86F
264674BE1C9ABBC1572E~~6C75CD4D472935FDFA40
CB54CC6BB5F31F42FDEA~~B3EC7061027FCD36C9AC
44525355FC15655C1F4D~~4DDF874CD06BC4F2D7A4
0E51B6364F193F588C93~~08E2ED40ED9752267EF7

      将所有这些信息付诸实践，我们可以针对原始请求执行以下操作：

      CREATE FUNCTION dbo.GeneratePassword_Real(@RandomValue VARBINARY(30))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH base(item) AS
  (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
  ), items(item) AS
  (
    SELECT NULL
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT (
     SELECT TOP (LEN(@RandomValue))
            SUBSTRING('1234567890QWERTYUIOPASDFGHJKLZXCVBNM',
                      (CONVERT(TINYINT, SUBSTRING(@RandomValue, 1, 1)) % 36) + 1,
                      1) AS [text()]
     FROM   items
     FOR XML PATH('')
  ) AS [RandomPassword];

      然后执行如下：

      DECLARE @PasswordLength INT = 10;

SELECT * FROM tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength));

      注意：请参阅下面的第 2 项与多行运行相关的内容！

    3. 您可以创建一个可以访问随机化功能的 SQLCLR 函数。这里的主要缺点是标量函数比 iTVF 更有可能缓存其返回值。

    更新

    1. 鉴于问题更新中新提供的使用此功能的上下文：

      该功能背后的想法是，如果有人需要重置密码，我可以执行以下操作：

      UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

      我们可以解决在对此答案的评论中表达的以下问题：

      • 需要将参数传递给具有随机值的函数。这强制要求必须知道一种创建随机字符串的方法才能使用该函数......

        真的。诚然，这有点不方便。但是，这并不否定这种方法解决手头问题的能力。它只需要一些关于如何正确使用此功能的评论/文档。

      • ...该函数无法在另一个函数中执行，因为不允许您使用非确定性函数在容器函数内生成随机值。

        真的。但是没有必要在另一个函数中调用这个函数。这不需要是标量函数就可以在 中使用UPDATE：

        USE [tempdb];
CREATE TABLE #Users (UserID INT NOT NULL PRIMARY KEY, [Password] VARCHAR(20));
INSERT INTO #Users (UserID, [Password]) VALUES (1, 'a'), (2, 'b');

DECLARE @PasswordLength INT = 10;

UPDATE tmp
SET    tmp.[Password] = pass.RandomPassword
FROM   #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
WHERE  tmp.UserID = 1;

SELECT * FROM #Users;

    2. 关于处理多行（请参阅上面选项 2 中的多行测试），任何类型的函数（至少是用户创建的）——标量 UDF、多语句 TVF、iTVF、SQLCLR UDF 或 SQLCLR TVF——都不是保证每行运行！您可能需要在如何说服查询优化器不缓存结果方面发挥创意，但可能会出现没有任何效果的情况。以下示例显示了相同的 iTVF 被调用两次。传入只会@PasswordLength导致输出值被缓存。但是，当您传入包含当前行中的字段的公式时，它的行为可能与您预期的一样：

      DECLARE @PasswordLength INT = 10;
SELECT pass.RandomPassword, pass2.RandomPassword
FROM [master].[sys].[objects] sac
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM((sac.[object_id] % 1)
            + @PasswordLength)) pass2

      返回：

      RandomPassword    RandomPassword
--------------  --------------
KX1M1NSKE7        8I1OK6TJI3
KX1M1NSKE7        S0RA6AWKAJ
KX1M1NSKE7        MKXV9UAB0Y
KX1M1NSKE7        4RQ2K6O1RP
KX1M1NSKE7        8J8PKJP7K2

    3. @BaconBits 在对该问题的评论中提出的观点是，最好不要存储可读密码，这是一个有效且好的观点。但是，情况可能会阻止该选项成为可能，或者至少在此时是可能的。

    • 8

  2. 另一种选择是使用视图。

    用法：

    UPDATE Users SET Password = dbo.GeneratePassword(DEFAULT, DEFAULT, DEFAULT) WHERE UserID = @UserID

    代码：

    CREATE VIEW dbo.RandomNoise
AS
-- helper view for accessing CRYPT_GEN_RANDOM from UDF
-- see dbo.GeneratePassword for details about 2048 value
SELECT CRYPT_GEN_RANDOM(2048) Bytes
GO

CREATE FUNCTION dbo.GeneratePassword(@letters INT = NULL, @numbers INT = NULL, @symbols INT = NULL)
RETURNS VARCHAR(256)
AS
BEGIN
    -- actual defaults
    IF @letters IS NULL SET @letters = 6
    IF @numbers IS NULL SET @numbers = 3
    IF @symbols IS NULL SET @symbols = 1

    IF @letters < 0 SET @letters = 0
    IF @numbers < 0 SET @numbers = 0
    IF @symbols < 0 SET @symbols = 0

    IF @letters > 256 SET @letters = 256
    IF @numbers > 256 SET @numbers = 256
    IF @symbols > 256 SET @symbols = 256

    DECLARE @letterSet VARCHAR(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
            @numberSet VARCHAR(10) = '0123456789',
            @symbolSet VARCHAR(20) = '!@#$%^&*()_+-=[]{}',
            @pwdLen INT = @letters + @numbers + @symbols

    IF @pwdLen > 256 SET @pwdLen = 256

    -- 256 is max possible password length
    DECLARE @pwd VARCHAR(256) = ''

    -- 2048 = (4 bytes rnd set + 4 bytes rnd position in set) * 256 max password length
    DECLARE @noise VARBINARY(2048) = (SELECT n.Bytes FROM dbo.RandomNoise n)

    DECLARE @i INT = 0

    WHILE @i < @pwdLen
    BEGIN
        SET @i = @i + 1

        -- [0,1)
        DECLARE @who FLOAT = (CAST(CAST(SUBSTRING(@noise, @i    , 4) AS INT) AS FLOAT) + 2147483648.0) / 4294967296.0,
                @pos FLOAT = (CAST(CAST(SUBSTRING(@noise, @i + 4, 4) AS INT) AS FLOAT) + 2147483648.0) / 4294967296.0

        IF @who < (@letters / CAST(@letters + @numbers + @symbols AS FLOAT))
        BEGIN
            SET @pwd = @pwd + SUBSTRING(@letterSet, CAST(@pos * LEN(@letterSet) + 1 AS INT), 1)
            SET @letters = @letters - 1
        END
        ELSE
        IF @who < ((@letters + @numbers) / CAST(@letters + @numbers + @symbols AS FLOAT))
        BEGIN
            SET @pwd = @pwd + SUBSTRING(@numberSet, CAST(@pos * LEN(@numberSet) + 1 AS INT), 1)
            SET @numbers = @numbers - 1
        END
        ELSE
        BEGIN
            SET @pwd = @pwd + SUBSTRING(@symbolSet, CAST(@pos * LEN(@symbolSet) + 1 AS INT), 1)
            SET @symbols = @symbols - 1
        END
    END

    RETURN @pwd
END
GO
    • 2

  3. 我用这个

    DECLARE @__numberOfUpperCaseCharacters int 
DECLARE @__numberOfLowerCaseCharacters int 
DECLARE @__numberOfNumericCharacters int 
DECLARE @__numberOfSpecialCharacters int 


SET @__numberOfUpperCaseCharacters = 1  
SET @__numberOfLowerCaseCharacters = 5 
SET @__numberOfNumericCharacters = 1 
SET @__numberOfSpecialCharacters = 1 


;WITH CTE_UpperCaseCharacters AS 
(
    SELECT 
         CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomUpperCaseCharacter  
        ,1 AS cnt 
    WHERE @__numberOfUpperCaseCharacters > 0 

    UNION ALL 

    SELECT 
         CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomUpperCaseCharacter  
        ,CTE_UpperCaseCharacters.cnt + 1 AS cnt 
    FROM CTE_UpperCaseCharacters 
    WHERE CTE_UpperCaseCharacters.cnt < @__numberOfUpperCaseCharacters 

)
,CTE_LowerCaseCharacters AS 
(
    SELECT 
         CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomLowerCaseCharacter  
        ,1 AS cnt 
    WHERE @__numberOfLowerCaseCharacters > 0 

    UNION ALL 

    SELECT 
         CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) AS RandomLowerCaseCharacter  
        ,CTE_LowerCaseCharacters.cnt + 1 AS cnt 
    FROM CTE_LowerCaseCharacters 
    WHERE CTE_LowerCaseCharacters.cnt < @__numberOfLowerCaseCharacters 

)
,CTE_Numbers AS 
(
    SELECT 
        CAST
        (
            (
                ABS
                (
                    CHECKSUM(NEWID())
                )
                %10
            ) 
            AS varchar(1)
        ) AS RandomInteger 
        ,1 AS cnt 
    WHERE @__numberOfNumericCharacters > 0 

    UNION ALL 

    SELECT 
        CAST
        (
            (
                ABS
                (
                    CHECKSUM(NEWID())
                )
                %10
            ) 
            AS varchar(1)
        ) AS RandomInteger 
        ,CTE_Numbers.cnt + 1 AS cnt 
    FROM CTE_Numbers 
    WHERE CTE_Numbers.cnt < @__numberOfNumericCharacters 
)
,CTE_SpecialCharacters AS 
(
    SELECT 
         CHAR(ASCII('!')+(ABS(CHECKSUM(NEWID()))%15)) AS RandomSpecialCharacter  
        ,1 AS cnt 
    WHERE @__numberOfSpecialCharacters > 0 

    UNION ALL 

    SELECT 
         CHAR(ASCII('!')+(ABS(CHECKSUM(NEWID()))%15)) AS RandomSpecialCharacter  
        ,CTE_SpecialCharacters.cnt + 1 AS cnt 
    FROM CTE_SpecialCharacters 
    WHERE CTE_SpecialCharacters.cnt < @__numberOfSpecialCharacters  

)
,CTE AS 
(
    SELECT RandomSpecialCharacter AS RandomCharacter, cnt  
    FROM CTE_SpecialCharacters 
    UNION ALL 
    SELECT RandomUpperCaseCharacter AS RandomCharacter, cnt  
    FROM CTE_UpperCaseCharacters 
    UNION ALL 
    SELECT RandomLowerCaseCharacter AS RandomCharacter, cnt  
    FROM CTE_LowerCaseCharacters 
    UNION ALL 
    SELECT RandomInteger AS RandomCharacter, cnt  
    FROM CTE_Numbers 
)
SELECT 
    (
        SELECT RandomCharacter AS [text()]
        FROM CTE 
        ORDER BY NEWID()
        FOR XML PATH(''), TYPE 
    ).value('.', 'varchar(50)') AS foo 
-- OPTION (MAXRECURSION 0)

    但是因为 SQL-Server 太棒了，你不能在标量函数中使用 NEWID，所以我创建了一个额外的视图“dbo.random”。如下：

    IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Random]'))
    EXECUTE('CREATE VIEW dbo.Random AS SELECT NEWID() AS [RUID]');


GO 



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_GenerateRandomPassword]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_GenerateRandomPassword]
GO




-- ========================================================================
-- Author:        ME
-- Create date:   29.11.2016
-- Last modified: 29.11.2016
-- Description:   Erstellen eines zufälligen Passworts
-- ========================================================================

-- PRE:   Anzahl Buchstaben in spezifizierter Kombination 
-- POST:  Generiere Liste mit jeweiliger Anzahl Buchstaben. 
--        Gibt string(shuffle(array(lower + upper + numeric + special))) zurück 
-- PRINT dbo.fu_GenerateRandomPassword ( 31, 12, 2010)
CREATE  FUNCTION [dbo].[fu_GenerateRandomPassword](@__numberOfLowerCaseCharacters as int, @__numberOfUpperCaseCharacters as int, @__numberOfNumericCharacters as int, @__numberOfSpecialCharacters as int) 
    RETURNS varchar(50) 
AS
BEGIN 
    DECLARE @__retValue varchar(50); 


    --DECLARE @__numberOfLowerCaseCharacters int; 
    --SET @__numberOfLowerCaseCharacters = 5; 

    --DECLARE @__numberOfUpperCaseCharacters int; 
    --SET @__numberOfUpperCaseCharacters = 1; 

    --DECLARE @__numberOfNumericCharacters int; 
    --SET @__numberOfNumericCharacters = 1; 

    --DECLARE @__numberOfSpecialCharacters int; 
    --SET @__numberOfSpecialCharacters = 1; 


    WITH CTE_UpperCaseCharacters AS 
    (
        SELECT 
             CHAR(ASCII('A')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomUpperCaseCharacter  
            ,1 AS cnt 
        WHERE @__numberOfUpperCaseCharacters > 0 

        UNION ALL 

        SELECT 
             CHAR(ASCII('A')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomUpperCaseCharacter  
            ,CTE_UpperCaseCharacters.cnt + 1 AS cnt 
        FROM CTE_UpperCaseCharacters 
        WHERE CTE_UpperCaseCharacters.cnt < @__numberOfUpperCaseCharacters 

    )
    ,CTE_LowerCaseCharacters AS 
    (
        SELECT 
             CHAR(ASCII('a')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomLowerCaseCharacter  
            ,1 AS cnt 
        WHERE @__numberOfLowerCaseCharacters > 0 

        UNION ALL 

        SELECT 
             CHAR(ASCII('a')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%25)) AS RandomLowerCaseCharacter  
            ,CTE_LowerCaseCharacters.cnt + 1 AS cnt 
        FROM CTE_LowerCaseCharacters 
        WHERE CTE_LowerCaseCharacters.cnt < @__numberOfLowerCaseCharacters 

    )
    ,CTE_Numbers AS 
    (
        SELECT 
            CAST
            (
                (
                    ABS
                    (
                        CHECKSUM((SELECT RUID FROM Random))
                    )
                    %10
                ) 
                AS varchar(1)
            ) AS RandomInteger 
            ,1 AS cnt 
        WHERE @__numberOfNumericCharacters > 0 

        UNION ALL 

        SELECT 
            CAST
            (
                (
                    ABS
                    (
                        CHECKSUM((SELECT RUID FROM Random))
                    )
                    %10
                ) 
                AS varchar(1)
            ) AS RandomInteger 
            ,CTE_Numbers.cnt + 1 AS cnt 
        FROM CTE_Numbers 
        WHERE CTE_Numbers.cnt < @__numberOfNumericCharacters 
    )
    ,CTE_SpecialCharacters AS 
    (
        SELECT 
             CHAR(ASCII('!')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%15)) AS RandomSpecialCharacter  
            ,1 AS cnt 
        WHERE @__numberOfSpecialCharacters > 0 

        UNION ALL 

        SELECT 
             CHAR(ASCII('!')+(ABS(CHECKSUM((SELECT RUID FROM Random)))%15)) AS RandomSpecialCharacter  
            ,CTE_SpecialCharacters.cnt + 1 AS cnt 
        FROM CTE_SpecialCharacters 
        WHERE CTE_SpecialCharacters.cnt < @__numberOfSpecialCharacters  

    )
    ,CTE AS 
    (
        SELECT RandomSpecialCharacter AS RandomCharacter, cnt  
        FROM CTE_SpecialCharacters 
        UNION ALL 
        SELECT RandomUpperCaseCharacter AS RandomCharacter, cnt  
        FROM CTE_UpperCaseCharacters 
        UNION ALL 
        SELECT RandomLowerCaseCharacter AS RandomCharacter, cnt  
        FROM CTE_LowerCaseCharacters 
        UNION ALL 
        SELECT RandomInteger AS RandomCharacter, cnt  
        FROM CTE_Numbers 
    )

    SELECT 
        @__retValue = (
            SELECT RandomCharacter AS [text()]
            FROM CTE 
            ORDER BY (SELECT RUID FROM Random)
            FOR XML PATH(''), TYPE 
        ).value('.', 'varchar(50)') 
    -- OPTION (MAXRECURSION 0) 
    ;

    RETURN @__retValue; 
END


GO
    • 0

相关问题