Início / dba / Perguntas / 108439
Accepted
JGA
Asked: 2015-07-30 06:07:20 +0800 CST

Função gerador de senha

  • 772

Qual é a melhor maneira de criar uma função de função definida pelo usuário do gerador de senha no SQL Server se não for possível usar funções não determinísticas dentro das funções?

Gostaria de criar uma função que retorne um varchar(10) gerado aleatoriamente usando caracteres de uma determinada string como esta: "1234567890QWERTYUIOPASDFGHJKLZXCVBNM"

Tenho minhas próprias ideias que funcionam, mas não são soluções muito naturais.

A ideia por trás da função é que, se alguém exigir uma redefinição de senha, eu poderia fazer o seguinte:

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

Uma função seria mais fácil de implantar e reutilizar do que uma tabela separada de senhas pré-alocadas.

sql-server functions

3 respostas

  1. Best Answer

    Você tem algumas opções (TL;DR -> a função de trabalho está no final da Opção nº 2, MAS também consulte a seção Atualizar após a Opção nº 3!):

    1. Se você está bem com apenas valores hexadecimais (0 - 9 e A - F), basta chamar CRYPT_GEN_RANDOM , que foi introduzido no SQL Server 2008:

      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]

      retorna:

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

      Como você pode ver, você precisa usar a opção de formato 2na CONVERTfunção para que ela não forneça caracteres que, embora mais diversos, possam ser mais difíceis de digitar, e você não obtenha o 0x. Mas você também pode ver que, mesmo com uma única linha, várias chamadas obterão valores de retorno diferentes (assim como NEWID()).

      Observe os valores de "comprimento" passados ​​para a CRYPT_GEN_RANDOMfunção. Como um byte hexadecimal tem 2 caracteres, você só precisa gerar um valor de 5 bytes. O CONVERT(VARCHAR...usa um "comprimento" de 10, pois cada byte hexadecimal se torna um caractere. E CONVERT(NVARCHAR...usa um "comprimento" de 20, pois a cada 2 bytes se tornará um caractere (ao executar isso em um banco de dados com um agrupamento padrão que não termina em _SC, que é a maioria dos casos).

    2. Você sempre pode criar seu próprio algoritmo e passar o valor CRYPT_GEN_RANDOMpara o aspecto aleatório. AND , se você puder estruturar seu algoritmo para ser único , de SELECTmodo que possa ser feito em uma função com valor de tabela embutida / iTVF (geralmente usando CTEs), então tecnicamente você pode usar uma função não determinística (pelo menos efetivamente), pois o chamada de função passará na expressão em vez do resultado dessa expressão, como acontece com TVFs de várias instruções e UDFs escalares:

      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

      E então execute os testes:

      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

      E só para ter certeza sobre várias linhas em um iTVF:

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

      retorna:

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

      Colocando todas essas informações em prática, podemos fazer o seguinte em relação à solicitação original:

      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];

      E depois execute da seguinte forma:

      DECLARE @PasswordLength INT = 10;

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

      CUIDADO: Consulte o Item nº 2 abaixo relacionado à execução disso para várias linhas!

    3. Você pode criar uma função SQLCLR que teria acesso à funcionalidade de randomização. A principal desvantagem aqui é que uma função Scalar tem muito mais probabilidade de ter seu valor de retorno armazenado em cache do que uma iTVF.

    Atualizar

    1. Dado o contexto recém-fornecido para uso desta função da atualização da pergunta:

      A ideia por trás da função é que, se alguém exigir uma redefinição de senha, eu poderia fazer o seguinte:

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

      podemos abordar as seguintes preocupações, conforme expressas em um comentário sobre esta resposta:

      • É preciso passar um parâmetro para a função com um valor aleatório. Isso reforça que é necessário conhecer uma maneira de criar strings aleatórias para usar a função...

        Verdadeiro. E é reconhecidamente um pouco inconveniente. No entanto, isso não nega a capacidade dessa abordagem de resolver o problema em questão. Requer apenas alguns comentários/documentação sobre como usar corretamente esta função.

      • ...a função não poderia ser executada dentro de outra função porque você não teria permissão para usar uma função não determinística para gerar valores aleatórios dentro da função container.

        Verdadeiro. Mas não há necessidade de chamar essa função dentro de outra função. Isso não precisa ser uma função escalar para ser usado em um 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. Com relação à manipulação de várias linhas (consulte o teste de várias linhas na Opção 2 acima), funções de qualquer tipo (criadas pelo usuário, pelo menos) -- Scalar UDF, Multistatement TVF, iTVF, SQLCLR UDF ou SQLCLR TVF -- não são garantido para executar por linha ! Talvez seja necessário ser criativo para convencer o Otimizador de consulta a não armazenar em cache o resultado, mas pode haver casos em que nada funciona. O exemplo a seguir mostra o mesmo iTVF sendo chamado duas vezes. Passar apenas @PasswordLengthfaz com que o valor de saída seja armazenado em cache. No entanto, quando você passa uma fórmula que incorpora um campo da linha atual, ela pode se comportar como você espera:

      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

      retorna:

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

    3. O ponto feito por @BaconBits em um comentário sobre a questão, a respeito de ser melhor não armazenar senhas legíveis, é um ponto válido e bom. No entanto, as circunstâncias podem impedir que essa opção seja possível, ou pelo menos possível neste momento.

    • 8

  2. Mais uma opção é usar view.

    Uso:

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

    Código:

    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. Eu uso isso

    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)

    Mas como o SQL-Server é tão maravilhoso, você não pode usar NEWID em uma função escalar, então criei uma visão adicional "dbo.random". Como abaixo:

    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

relate perguntas