Spis treści:

Kategoria:SQL Server


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
(SELECTFROM (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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.