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! |
|
|
|
|
|
|
|
|
Generate Password Procedure