Generowanie losowych haseł w SQL Server

Weryfikacja użytkowników systemu za pomocą hasła nie jest rzadkością. Hasła są najczęściej wybierane przez użytkowników, co pozwala im lepiej je zapamiętać. W wielu przypadkach hasła generowane są przez wastwę inną niż baza danych, szyfrowane kluczem jednokierunkowym i w takiej zaszyfrowanej postaci zapisywane w bazie danych. Pomimo tego, postanowiłem zająć się tym tematem od strony SQL Server. Nie dlatego, że takie rozwiązanie jest najlepsze, bo trudno jednoznacznie wskazać. Dlatego, żeby pokazać, że można. A przy okazji, żeby pokazać kilka ciekawych technik, które warto znać.

Przykładowa implementacja

Przyjrzyjmy się zatem jednemu z możliwych rozwiązań zaprezentowanemu na poniższym listingu:

--Znaczenie bitów @usedSets, od najmniej znaczącego
--Bit 0 (1) - cyfry 0-9
--Bit 1 (2) - małe litery
--Bit 2 (4) - duże litery
--Bit 3 (8) - symbole
CREATE PROC dbo.GeneratePassword
(
  @usedSets int=15,--Patrz komentarz powyżej
  @length int=8,--Długość hasła
  @password varchar(100) OUTPUT--Rezultat
)
AS
BEGIN
DECLARE @nums char(10)='0123456789';
DECLARE @letters char(26)='abcdefghijklmnopqrstuvwxyz';
DECLARE @symbols char(10)='!@#$%^&*?+';

DECLARE @subset nvarchar(80)='';
--Dołącz do zbioru możliwych wartości te zestawy,
--które zostały wskazane polem bitowym @usedSets
IF (@usedSets&1=1)
  SET @subset+=@nums;
IF (@usedSets&2=2)
  SET @subset+=@letters;
IF (@usedSets&4=4)
  SET @subset+=UPPER(@letters);
IF (@usedSets&8=8)
  SET @subset+=@symbols;

--Gdy zbiór znaków jest pusty, użyj pełnego zestawu
--Obejmuje to przypadek błędnie podanego parametru
IF (LEN(@subset)=0)
BEGIN
  SET @subset=@nums+@letters+UPPER(@letters)+@symbols;
  SET @usedSets=1+2+4+8;
END

--Zabdaj o to, by hasła były czytelne
--Zlikwiduj te znaki, które mogą stwarzać problemy po połączeniu zestawów
IF (@usedSets&6=6)--małe i duże litery - pomiń l i I
BEGIN
  SET @subset=REPLACE(@subset,'l','');
  SET @subset=REPLACE(@subset,'I','');
END
IF (@usedSets&5=5)--cyfry i duże litery - pomiń 0 i O
BEGIN
  SET @subset=REPLACE(@subset,'0','');
  SET @subset=REPLACE(@subset,'O','');
END

DECLARE @subsetLength int=LEN(@subset);
DECLARE @ret varchar(100)='';
WITH Ten AS
--Dziesięcioelementowa tabela inline
(SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) Num(N)),
Hundred AS
--Stuelementowa tabela uzyskana na podstawie poprzedniej
--Jeżeli potrzebujemy haseł dłuższych niż 100, należy spotęgować zbiór
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) N FROM Ten CROSS JOIN Ten T2)
--Skumuluj kolejne znaki w zmiennej @ret
--Znaki wybierane są losowo z połączonego zbioru @subset
--TOP ogranicza ilość znaków, które będą skumulowane.
--Pozwala także przedwcześnie przerwać obliczenia.
--Nie ma więc obaw, że wszystkie rekordy z tabeli Hundred będą przetwarzane
SELECT TOP (@length) @ret=@ret+
  SUBSTRING(@subset, 1+CAST(RAND(CHECKSUM(NEWID()))*@subsetLength AS int), 1)
FROM Hundred

SET @password=@ret
END

Kod jest dość mocno ubarwiony komentarzami, więc nie powinno być problemów ze zrozumieniem jego działania.

Przykład użycia

Procedura ma dwa parametry domyślne i jeden parametr wyjściowy. Sprawia to, że można z niej korzystać na kilka sposobów. Na początek przyjrzyjmy się wywołaniu korzystającemu z nazwanych parametrów:

DECLARE @pass varchar(100)
EXEC dbo.GeneratePassword
    @usedSets = 7,
    @length = 15,
    @password = @pass OUTPUT
SELECT @pass as Hasło

Jeżeli parametry podawane są w naturalnej kolejności, bez omijania jakiegokolwiek z nich, można pominąć nazwy:

DECLARE @pass varchar(100)
EXEC dbo.GeneratePassword 7, 15, @pass OUTPUT
SELECT @pass as Hasło

Wynikiem działania procedury będzie rezultat podobny do zaprezentowanego poniżej - hasło składające się z cyfr (bit 0, wartość 1) oraz małych (bit 1, wartość 2) i dużych (bit 2, wartość 4) liter. Hasło będzie się składało z 15 znaków.

Hasło
9q3ku4uc5EeXayF

Wstawienie wartości domyślnych pozwala na wywołanie procedury również w inny, bardzo prosty sposób:

DECLARE @pass varchar(100)
EXEC dbo.GeneratePassword
@password = @pass OUTPUT
SELECT @pass as Hasło

Wystarczy przekazać tylko parametr wyjściowy - reszta będzie domyślna. Wynikiem będzie hasło składające się z 8 znaków, w tym cyfry, małe i wielkie litery oraz symbole wyszczególnione wewnątrz procedury.

Hasło
Gg5+NF3$

Można również przekazać tylko część parametrów, tak jak w poniższym przykładzie:

DECLARE @pass varchar(100)
EXEC dbo.GeneratePassword
@usedSets = 5,
@password = @pass OUTPUT
SELECT @pass as Hasło

Tym razem wygenerowane hasło będzie miało 8 znaków (wartość domyślna), a będzie się składało z cyfr (bit 0, wartość 1) i dużych liter (bit 2, wartość 4). Przykładowe hasło będzie podobne do poniższego:

Hasło
LTDSU8GF

Jak wspomniałem we wstępie, w procedurze skorzystano z kilku interesujących technik. Warto im się przyjrzeć bliżej.

Kategoria:SQL Server

Paweł Dyl, 2013-12-20

Spis treści:

Generowanie losowych haseł w SQL Server

Przykładowa implementacja

Przykład użycia

Brak komentarzy - bądź pierwszy