Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-08 : 23:16:18
|
Stored procedure P_GENERATE_PASSWORDS returns a list of randomly generated passwords designed to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters. It can generate from 1 to 10,000 passwords as a result set.
The passwords are meant to be somewhat mnemonic by generating syllables consisting of an uppercase consonant, followed by a lower case vowel, and a lowercase consonant. A single number or special character separates syllables, except in the case of 2 syllables. If there are only 2 syllables, they will be separated by a number and a special character.
Input parameters @SYLLABLE_COUNT and @PASSWORD_COUNT determine the password length and the number of passwords.
if objectproperty(object_id('dbo.P_GENERATE_PASSWORDS'),'IsProcedure') = 1 begin drop procedure dbo.P_GENERATE_PASSWORDS end go create procedure dbo.P_GENERATE_PASSWORDS ( @SYLLABLE_COUNT int = null , @PASSWORD_COUNT int = null , @PASSWORD_STRENGTH float = null output ) as
/* Procedure Name: P_GENERATE_PASSWORDS
Procedure Description:
P_GENERATE_PASSWORDS returns a list of randomly generated passwords designed to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters.
The passwords are meant to be somewhat mnemonic by generating syllables consisting of an uppercase consonant, followed by a lower case vowel, and a lowercase consonant. Syllables are separated by a single number or special character, except in the case of 2 syllables. If there are only 2 syllables, the syllables will be separated by a number and a special character.
Passwords can be from 2 to 8 syllables in length.
Input parameter @SYLLABLE_COUNT is the total syllables in each output password. The value of @SYLLABLE_COUNT must be between 2 and 8. If it is < 2 or null, it is set to 3. If it is > 8 it is set to 8.
Input parameter @PASSWORD_COUNT is the total passwords to be returned. The value of @SYLLABLE_COUNT must be between 1 and 10,000. If it is < 1, it is set to 1. If it is null, it is set to 10. If it is > 10,000 it is set to 10,000.
Output parameter @PASSWORD_STRENGTH returns the total possible passwords that are possible for the selected @SYLLABLE_COUNT.
*/
set nocount on
-- Set password syllable count set @SYLLABLE_COUNT = case when @SYLLABLE_COUNT is null then 3 when @SYLLABLE_COUNT < 2 then 3 when @SYLLABLE_COUNT > 8 then 8 else @SYLLABLE_COUNT end
-- Set password count set @PASSWORD_COUNT = case when @PASSWORD_COUNT is null then 10 when @PASSWORD_COUNT < 1 then 1 when @PASSWORD_COUNT > 10000 then 10000 else @PASSWORD_COUNT end
declare @con varchar(200) declare @vowel varchar(200) declare @special varchar(200) declare @num varchar(200) declare @special_only varchar(200) declare @con_len int declare @vowel_len int declare @special_len int declare @num_len int declare @special_only_len int declare @strings int
-- set character strings for password generation select @con = 'bcdfghjklmnpqrstvwxyz', @vowel = 'aeiou', @num = '1234567890', @special_only = '~!@#$%^&*()_+-={}|[]\:;<>?,./'
set @special = @num+@special_only
-- set string lengths select @con_len = len(@con), @vowel_len = len(@vowel), @special_len = len(@special), @num_len = len(@num), @special_only_len = len(@special_only) , @strings = case when @SYLLABLE_COUNT < 3 then 2 else @SYLLABLE_COUNT-1 end
--select @con, @vowel, @special, @num, @special_only, --SELECT @con_len, @vowel_len, @special_len, @num, @special_only_len, @strings
-- Declare number tables to generate rows declare @num1 table (NUMBER int not null primary key clustered) declare @num2 table (NUMBER int not null primary key clustered) declare @num3 table (NUMBER int not null primary key clustered)
declare @rows_needed_root int set @rows_needed_root = convert(int,ceiling(sqrt(@PASSWORD_COUNT)))
-- Load number 0 to 16 insert into @num1 (NUMBER) select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 order by 1
-- Load table with numbers zero thru square root of the number of rows needed +1 insert into @num2 (NUMBER) select NUMBER = a.NUMBER+(10*b.NUMBER) from @num1 a cross join @num1 b where a.NUMBER+(10*b.NUMBER) < @rows_needed_root order by 1
-- Load table with the number of passwords needed insert into @num3 (NUMBER) select NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER) from @num2 a cross join @num2 b where a.NUMBER+(@rows_needed_root*b.NUMBER) < @PASSWORD_COUNT order by 1
-- Declare password string table declare @p table ( number int not null primary key clustered, m1 varchar(10) not null, m2 varchar(10) not null, m3 varchar(10) not null, m4 varchar(10) not null, m5 varchar(10) not null, m6 varchar(10) not null, m7 varchar(10) not null, m8 varchar(10) not null, s1 varchar(10) not null, s2 varchar(10) not null, s3 varchar(10) not null, s4 varchar(10) not null, s5 varchar(10) not null, s6 varchar(10) not null, s7 varchar(10) not null )
insert into @p select NUMBER, -- M1 through M8 will be syllables composed of a single randomly selected -- uppercase consonant, a single randomly selected lowercase vowel, -- followed by as single randomly selected lowercase consonant. m1 = upper(substring(@con, (R11%@con_len)+1,1))+ substring(@vowel,(R12%@vowel_len)+1,1)+ substring(@con, (R13%@con_len)+1,1), m2 = upper(substring(@con, (R21%@con_len)+1,1))+ substring(@vowel,(R22%@vowel_len)+1,1)+ substring(@con, (R23%@con_len)+1,1), m3 = upper(substring(@con, (R31%@con_len)+1,1))+ substring(@vowel,(R32%@vowel_len)+1,1)+ substring(@con, (R33%@con_len)+1,1), m4 = upper(substring(@con, (R41%@con_len)+1,1))+ substring(@vowel,(R42%@vowel_len)+1,1)+ substring(@con, (R43%@con_len)+1,1), m5 = upper(substring(@con, (R51%@con_len)+1,1))+ substring(@vowel,(R52%@vowel_len)+1,1)+ substring(@con, (R53%@con_len)+1,1), m6 = upper(substring(@con, (R61%@con_len)+1,1))+ substring(@vowel,(R62%@vowel_len)+1,1)+ substring(@con, (R63%@con_len)+1,1), m7 = upper(substring(@con, (R71%@con_len)+1,1))+ substring(@vowel,(R72%@vowel_len)+1,1)+ substring(@con, (R73%@con_len)+1,1), m8 = upper(substring(@con, (R81%@con_len)+1,1))+ substring(@vowel,(R82%@vowel_len)+1,1)+ substring(@con, (R83%@con_len)+1,1),
-- S1 through S7 will each be a single randomly selected -- number or special character. At least one of the used -- columns will be a number and one will be a special character.
s1 = case when NUMBER_COL = 1 then substring(@num,(RS1%@num_len)+1,1) when SPECIAL_COL = 1 then substring(@special_only,(RS1%@special_only_len)+1,1) else substring(@special,(RS1%@special_len)+1,1) end, s2 = case when NUMBER_COL = 2 then substring(@num,(RS2%@num_len)+1,1) when SPECIAL_COL = 2 then substring(@special_only,(RS2%@special_only_len)+1,1) else substring(@special,(RS2%@special_len)+1,1) end, s3 = case when NUMBER_COL = 3 then substring(@num,(RS3%@num_len)+1,1) when SPECIAL_COL = 3 then substring(@special_only,(RS3%@special_only_len)+1,1) else substring(@special,(RS3%@special_len)+1,1) end, s4 = case when NUMBER_COL = 4 then substring(@num,(RS4%@num_len)+1,1) when SPECIAL_COL = 4 then substring(@special_only,(RS4%@special_only_len)+1,1) else substring(@special,(RS4%@special_len)+1,1) end, s5 = case when NUMBER_COL = 5 then substring(@num,(RS5%@num_len)+1,1) when SPECIAL_COL = 5 then substring(@special_only,(RS5%@special_only_len)+1,1) else substring(@special,(RS5%@special_len)+1,1) end, s6 = case when NUMBER_COL = 6 then substring(@num,(RS6%@num_len)+1,1) when SPECIAL_COL = 6 then substring(@special_only,(RS6%@special_only_len)+1,1) else substring(@special,(RS6%@special_len)+1,1) end, s7 = case when NUMBER_COL = 7 then substring(@num,(RS7%@num_len)+1,1) when SPECIAL_COL = 7 then substring(@special_only,(RS7%@special_only_len)+1,1) else substring(@special,(RS7%@special_len)+1,1) end from ( select aaaa.*, -- Select random columns numbers to force at least -- one special character and one number character -- in each password NUMBER_COL = (X1%@strings)+1 , SPECIAL_COL = ((((X2%(@strings-1))+1)+X1)%@strings)+1 from ( select top 100 percent NUMBER, -- Generate random numbers for password generation R11 = abs(convert(bigint,convert(varbinary(20),newid()))), R12 = abs(convert(bigint,convert(varbinary(20),newid()))), R13 = abs(convert(bigint,convert(varbinary(20),newid()))), R21 = abs(convert(bigint,convert(varbinary(20),newid()))), R22 = abs(convert(bigint,convert(varbinary(20),newid()))), R23 = abs(convert(bigint,convert(varbinary(20),newid()))), R31 = abs(convert(bigint,convert(varbinary(20),newid()))), R32 = abs(convert(bigint,convert(varbinary(20),newid()))), R33 = abs(convert(bigint,convert(varbinary(20),newid()))), R41 = abs(convert(bigint,convert(varbinary(20),newid()))), R42 = abs(convert(bigint,convert(varbinary(20),newid()))), R43 = abs(convert(bigint,convert(varbinary(20),newid()))), R51 = abs(convert(bigint,convert(varbinary(20),newid()))), R52 = abs(convert(bigint,convert(varbinary(20),newid()))), R53 = abs(convert(bigint,convert(varbinary(20),newid()))), R61 = abs(convert(bigint,convert(varbinary(20),newid()))), R62 = abs(convert(bigint,convert(varbinary(20),newid()))), R63 = abs(convert(bigint,convert(varbinary(20),newid()))), R71 = abs(convert(bigint,convert(varbinary(20),newid()))), R72 = abs(convert(bigint,convert(varbinary(20),newid()))), R73 = abs(convert(bigint,convert(varbinary(20),newid()))), R81 = abs(convert(bigint,convert(varbinary(20),newid()))), R82 = abs(convert(bigint,convert(varbinary(20),newid()))), R83 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS1 = abs(convert(bigint,convert(varbinary(20),newid()))), RS2 = abs(convert(bigint,convert(varbinary(20),newid()))), RS3 = abs(convert(bigint,convert(varbinary(20),newid()))), RS4 = abs(convert(bigint,convert(varbinary(20),newid()))), RS5 = abs(convert(bigint,convert(varbinary(20),newid()))), RS6 = abs(convert(bigint,convert(varbinary(20),newid()))), RS7 = abs(convert(bigint,convert(varbinary(20),newid()))),
X1 = convert(bigint,abs(convert(int,convert(varbinary(20),newid())))), X2 = convert(bigint,abs(convert(int,convert(varbinary(20),newid())))) from @num3 aaaaa order by aaaaa.NUMBER ) aaaa ) aaa order by aaa.NUMBER
-- Compute password strength as the total possible passwords -- for the selected number of syllables. select @PASSWORD_STRENGTH = power((@con_len*@con_len*@vowel_len)*1E,@SYLLABLE_COUNT*1E)* (@special_only_len*@num_len*1E)* case when @strings < 3 then 1E else power(@special_len*1E,(@strings-2)*1E) end
-- Declare output table declare @PASSWORD table ( NUMBER int not null identity(1,1) primary key clustered, [PASSWORD] varchar(32) not null )
insert into @password ([PASSWORD]) select top 100 percent [PASSWORD] from ( select distinct [PASSWORD] = convert(varchar(32), case when @SYLLABLE_COUNT = 2 then m1+s1+s2+m2 else substring(m1+s1+m2+s2+m3+s3+m4+s4+m5+s5+m6+s6+m7+s7+m8 ,1,(@SYLLABLE_COUNT*4)-1) end) from @P ) a where -- Verify at least one number in password [PASSWORD] like '%[1234567890]%' and -- Verify at least one special character in password [PASSWORD] like '%[^a-z1234567890]%' order by newid()
select * from @password order by NUMBER
return 0 go grant execute on dbo.P_GENERATE_PASSWORDS to public
go
-- Test Script declare @SYLLABLE_COUNT int declare @PASSWORD_COUNT int declare @PASSWORD_STRENGTH float
select @SYLLABLE_COUNT = 2 , @PASSWORD_COUNT = 5 print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+ ', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS @SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH) print ''
select @SYLLABLE_COUNT = 3 , @PASSWORD_COUNT = 6 print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+ ', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS @SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH) print ''
select @SYLLABLE_COUNT = 5 , @PASSWORD_COUNT = 7 print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+ ', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS @SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH) print ''
select @SYLLABLE_COUNT = 8 , @PASSWORD_COUNT = 20 print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+ ', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)
exec dbo.P_GENERATE_PASSWORDS @SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output
print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH) print ''
Results of Test Script:
@SYLLABLE_COUNT = 2, @PASSWORD_COUNT = 5 NUMBER PASSWORD ----------- -------------------------------- 1 Tis|2Fun 2 Miy5]Fib 3 Bay1|Puz 4 Tel3.Pus 5 Duq0@Roy
@PASSWORD_STRENGTH = 1.40999e+009 @SYLLABLE_COUNT = 3, @PASSWORD_COUNT = 6 NUMBER PASSWORD ----------- -------------------------------- 1 Qab@Kaz0Lan 2 Sav1Tig]Hat 3 Pah6Fic|Cic 4 Buz7Viz=Mec 5 Vig^Wah9Xuf 6 Qew2Mif^Mix
@PASSWORD_STRENGTH = 3.10902e+012 @SYLLABLE_COUNT = 5, @PASSWORD_COUNT = 7 NUMBER PASSWORD ----------- -------------------------------- 1 Mux4Zor_Jog{Vec,Bih 2 Ker1Qem[Gat,Hut|Zif 3 Red}Ciq5Ber%Son:Qej 4 Cov@Doz8Zow\Fic>Pos 5 Tad0Bek&Fug_Kiv9Rez 6 Pil1Nul$Vil~Koh_Xel 7 Zuk4Gir&Yep|Ned)Sap
@PASSWORD_STRENGTH = 2.29917e+022 @SYLLABLE_COUNT = 8, @PASSWORD_COUNT = 20 NUMBER PASSWORD ----------- -------------------------------- 1 Biz&Xak9Gew{Vuf[Tix;Qap-Bik{Vay 2 Rof<Job*Fax-Niq/Zew9Pah:Bag(Zok 3 Noh1Nor7Rul5Fon@Mig>Xod.Lay.Maq 4 Piw:Keb}Rod8Yah}Vaw\Let@Yoq9Sav 5 Hav@Qer/Met7Zig&Jiw4Pot-Fod(Zat 6 Bid_Lal+Bay3Fos9Fez\Faw!Kad4Zok 7 Qar-Kig-Lem3Yeq?Xuj7Zun,Xid=Xel 8 Biq6Jot:Caj(Xun2Kup[Fax|Gec,Xon 9 Yac7Nox^Woy~Wag0Xan\Hil3Cab/Nit 10 Pod+Kor%Fov7Vil,Dor:Xoq!Kel3Poq 11 Goc)Roz7Ruq/Pad8Jeh*Xaj&Dew{Duy 12 Sik/Ruj@Wiv9Qik[Sub=Qim,Ned:Qit 13 Les9Har&Ceb5Heg^Fov0Vaf1Fuf[Maq 14 Deg6Yiw$Peg:Wuj7Woc_Mip|Kam9Zus 15 Nix^Dev%Qoj=Seq[Jig6Lig}Day-Ric 16 Dux;Woy=Zud1Mak5Yej$Kav2Mek5Buh 17 Yuv8Mor9Wix&Giq5Zar@Nuk$Pey<Lok 18 Dem~Kof-Yoq(Xig$Tew\Fun7Meq2Kik 19 Caq1Qag{Pes{Gex|Til=Vuk7Tig1Vur 20 Miw)Law}Tun2Lop.Jix#Riq|Yat$Juc
@PASSWORD_STRENGTH = 1.46214e+037
CODO ERGO SUM |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-09 : 03:23:43
|
But will those passwords be easy to remember? Unless those are easy to remember, the complete purpose of having password is defeated.
Harsh Athalye India. "The IMPOSSIBLE is often UNTRIED" |
 |
|
Kristen
Test
22859 Posts |
Posted - 2007-02-09 : 04:34:51
|
"But will those passwords be easy to remember"
I think MVJs:
"The passwords are meant to be somewhat mnemonic ..."
works well. Clearly not for a 56 character password!, but the 2 & 3 syllable ones are easy to remember. And for a machine-only interface the longer ones will be fine, of course.
Kristen |
 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-09 : 04:56:55
|
i use this and it works pretty well with some modifications: http://vyaskn.tripod.com/code/password.txt
Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-09 : 09:38:35
|
I was trying to strike a compromise between creating strong passwords that are as easy as possible to remember, while still meeting the password complexity rules of a typical Windows domain. It’s not a trivial problem to solve, because easy to remember is a highly subjective thing.
The idea of using syllables is something I borrowed (stole outright) from the VMS operating system. It has a generate password feature that returns a list of mnemonic passwords that you can select a password from.
I think the 2 and 3 syllable passwords are fine for Windows accounts where you have lockouts after a reasonable number of failed attempts. I would recommend 4 or more syllables for SQL Server passwords where someone can make an unlimited number of attempts at guessing a password without a lockout.
If anyone has any suggestions on how to improve it while still meeting the objectives, I would be interested in hearing them.
CODO ERGO SUM |
 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-09 : 09:57:26
|
this a bit modified vyas's sproc i use. I had to generate 5000 simple readable passwords with this and it was up to the chanllenge 
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[RandomPassword] ( @len int = 8, --Length of the password to be generated @password_type char(7) = 'simple' --Default is to generate a simple password with lowecase letters. --Pass anything other than 'simple' to generate a complex password. --The complex password includes numbers, special characters, upper case and lower case letters ) AS DECLARE @password varchar(25), @type tinyint, @bitmap char(5) SET @password='' SET @bitmap = 'uaeio' -- @bitmap contains all the vowels, which are a, e, i, o and u. -- These vowels are used to generate slightly readable/rememberable simple passwords
WHILE @len > 0 BEGIN IF @password_type = 'simple' --Generating a simple password BEGIN IF (@len%2) = 0 --Appending a random vowel to @password begin -- (RAND() * (4)) -> 4 = len(@bitmap) - 1, it's hardcoded for speed SET @password = @password + SUBSTRING(@bitmap,CONVERT(int,ROUND(1 + (RAND() * (4)),0)),1) end ELSE --Appending a random alphabet begin -- we eliminte the possibility that char from bitmap and the random one are same declare @char char(1) set @char = CHAR(ROUND(97 + (RAND() * (25)),0)) while @bitmap like '%' + @char + '%' begin set @char = CHAR(ROUND(97 + (RAND() * (25)),0)) end SET @password = @password + @char end END ELSE --Generating a complex password BEGIN SET @type = ROUND(1 + (RAND() * (3)),0)
IF @type = 1 --Appending a random lower case alphabet to @password SET @password = @password + CHAR(ROUND(97 + (RAND() * (25)),0)) ELSE IF @type = 2 --Appending a random upper case alphabet to @password SET @password = @password + CHAR(ROUND(65 + (RAND() * (25)),0)) ELSE IF @type = 3 --Appending a random number between 0 and 9 to @password SET @password = @password + CHAR(ROUND(48 + (RAND() * (9)),0)) ELSE IF @type = 4 --Appending a random special character to @password SET @password = @password + CHAR(ROUND(33 + (RAND() * (13)),0)) END
SET @len = @len - 1 END
-- here we add one number to the end of string if the password is simple type. -- we could add it somewhere else with little recoding IF @password_type = 'simple' select @password = LEFT ( @password, len(@password) - 1) + CHAR ( ROUND(48 + (RAND() * (9)),0))
SELECT @password as NewPassword
Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-09 : 10:02:34
|
The SP you created is great, MVJ, but what I was saying that instead keeping keeping bitmap of characters, numbers and special characters, we can have a dictionary of uncommon words and may be mix those words to create simple-to-remember but difficult to guess passwords. (along with random uppercase letters and numbers for additional complexity)
Harsh Athalye India. "The IMPOSSIBLE is often UNTRIED" |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-09 : 10:25:10
|
quote: Originally posted by spirit1
this a bit modified vyas's sproc i use. I had to generate 5000 simple readable passwords with this and it was up to the chanllenge ...
It looks like a good procedure, but it doesn't meet my design objectives.
The simple passwords don't meet the complexity rules:
Simple password --------------- amijebe2 owavega3 iwehiku2 imegebi6 ozaxafa9 alicema1 aqagapo4 inamuca6 esadeza4 ekuniga7
The strong passwords don't look very easy to remember:
Strong password --------------- B4zI1-5U hW4K6KM' 3pNkfp,G I1Q#*E16 3Di5h0a- 4*4T1jH4 3*T8bM9R +71Snm#5 '8bH5(WG 9WN87N1z
CODO ERGO SUM |
 |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-09 : 10:48:26
|
well my version was modified for simple passwords. Users only wanted alphanumerics non-vowel, vowel combo with a number at the end and that it could be kind of pronouncable.
Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-09 : 10:54:36
|
[code]CREATE PROCEDURE dbo.uspCreatePassword ( @UpperCaseItems SMALLINT, @LowerCaseItems SMALLINT, @NumberItems SMALLINT, @SpecialItems SMALLINT ) AS
SET NOCOUNT ON
DECLARE @UpperCase VARCHAR(26), @LowerCase VARCHAR(26), @Numbers VARCHAR(10), @Special VARCHAR(13), @Temp VARCHAR(8000), @Password VARCHAR(8000), @i SMALLINT, @c VARCHAR(1), @v TINYINT
-- Set the default items in each group of characters SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', @LowerCase = 'abcdefghijklmnopqrstuvwxyz', @Numbers = '0123456789', @Special = '!@#$%&*()_+-=', @Temp = '', @Password = ''
-- Enforce some limits on the length of the password IF @UpperCaseItems > 2000 SET @UpperCaseItems = 2000
IF @LowerCaseItems > 2000 SET @LowerCaseItems = 2000
IF @NumberItems > 2000 SET @NumberItems = 2000
IF @SpecialItems > 2000 SET @SpecialItems = 2000
-- Get the Upper Case Items SET @i = ABS(@UpperCaseItems)
WHILE @i > 0 AND LEN(@UpperCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1, @c = SUBSTRING(@UpperCase, @v, 1), @UpperCase = CASE WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '') ELSE @UpperCase END, @Temp = @Temp + @c, @i = @i - 1
-- Get the Lower Case Items SET @i = ABS(@LowerCaseItems)
WHILE @i > 0 AND LEN(@LowerCase) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1, @c = SUBSTRING(@LowerCase, @v, 1), @LowerCase = CASE WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '') ELSE @LowerCase END, @Temp = @Temp + @c, @i = @i - 1
-- Get the Number Items SET @i = ABS(@NumberItems)
WHILE @i > 0 AND LEN(@Numbers) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1, @c = SUBSTRING(@Numbers, @v, 1), @Numbers = CASE WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '') ELSE @Numbers END, @Temp = @Temp + @c, @i = @i - 1
-- Get the Special Items SET @i = ABS(@SpecialItems)
WHILE @i > 0 AND LEN(@Special) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1, @c = SUBSTRING(@Special, @v, 1), @Special = CASE WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '') ELSE @Special END, @Temp = @Temp + @c, @i = @i - 1
-- Scramble the order of the selected items WHILE LEN(@Temp) > 0 SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1, @Password = @Password + SUBSTRING(@Temp, @v, 1), @Temp = STUFF(@Temp, @v, 1, '')
SELECT @Password[/code]If any of the parameters are passed onto the stored procedure as negative value, it is interpreted that you do not want a duplicate character from that specific group of characters. A positive value can get a duplicate value in that group of characters.
Peter Larsson Helsingborg, Sweden |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-09 : 11:38:41
|
quote: Originally posted by harsh_athalye
The SP you created is great, MVJ, but what I was saying that instead keeping keeping bitmap of characters, numbers and special characters, we can have a dictionary of uncommon words and may be mix those words to create simple-to-remember but difficult to guess passwords. (along with random uppercase letters and numbers for additional complexity)
Harsh Athalye India. "The IMPOSSIBLE is often UNTRIED"
Using a dictionary would be very hard to implement, especially considering things like different national languages; I don’t feel like keying in a million or so uncommon words.
Dictionary based attacks are one of the most common methods of breaking passwords, so I think it would be a big security hole. If you use combinations of words to increase the password strength, I think you would end up with a password that is at least as hard to remember (and type!) as the randomly generated passwords from my procedure.
The simple 2 syllable passwords from my procedure have 1.4 billion possible combinations, and 3 syllable passwords have 3.1 trillion possible combinations, even if you know the rules used to generate the password. Certainly not as strong as a completely random password, but harder to guess than your birthday, and easier to remember than dEOY&1nx, Wiz*R3hW, or RS*gdE9n.
CODO ERGO SUM |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-09 : 12:12:12
|
quote: Originally posted by Peso
CREATE PROCEDURE dbo.uspCreatePassword...]
This proc will certainly deliver complex passwords, but they don’t look easy to remember to me.
Password --------- u#n4PesP vYqI0tc( XEhm1x_y #zaqyX4P FsAx8qn! NnTy6_op x7+JXycs +UZz1hfz (SSk4agv My$0gdwH
CODO ERGO SUM |
 |
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-20 : 23:21:31
|
what I often use for passwords that are easy to remember but hard to guess is the first letter of each word of an easy-to-remember sentence. like "SQLTeam is the place to get your questions answered" would be Sitptgyqa. maybe add some punctuation for good measure: F,md,idgad! = Frankly, my dear, I don't give a damn!
might not be so useful for what you are doing here since you are looking for an algorithm though.
www.elsasoft.org |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-02-23 : 12:48:04
|
Interesing challenge. I think a good method of generating passwords which are secure, but easily remembered, would be to use 2nd-order or 3rd-order letter approximation and then substitute a few look-alike symbols for some of the characters. This wouls be easier than storing hundreds or thousands of sample words, and might be more readable than the ones in MVJ's original post. Martin Gardner wrote an article on word approximation, testing several methods for generating pseudo-words. One of the methods he used was MVJ's alternation consonant/vowel algorithm, but he considered this less "realistic" than 3rd-order approximation.
STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-23 : 13:55:25
|
Also, you have to remember my requirement to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters.
I would be interested in a link to Martin Gardner's article if you know it.
CODO ERGO SUM |
 |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-02-23 : 22:10:15
|
I looked for a link, but could find nothing. I was surprised to find very little on word approximation at all. I'll do some more searching, because it was an interesting article.
STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
 |
|
|
|
|
|
|