Custom Password Policy Stored Procedure Needed
-
I wanted to check here first to try and avoid re-inventing the wheel as much as possible. What I need to do is create a stored procedure that will perform some fairly standard password validity\complexity checks. The custom windows OS or SQL server password filters will not work for me for many reasons that I won't go into here, not least of which the SQL Server version is 2000 SP4 and the network DC is Win2000.
The stored procedure needs to check the following password complexity rules:
1) Passwords must include a combination of:
a) At least 'n' uppercase letter(s) (A to Z)
b) At least 'n' lowercase letter(s) (a to z)
c) At least 'n' numeric OR special character(s) (such as 0-9,!,$,#,%, etc.).
2) Password minimum length must be 'n' characters.
Where 'n' in each case above is a variable value based on values coming from a dynamic password policy control table.
I can handle #2 simply enough, it's #1 that feels like it can get very complicated and what I am really looking for existing examples of. The code example I am looking for doesn't necessarily need to consider the variables, mostly the checking of the counts of each type.
I don't have permissions to create extended permissions to enable use of regular expression tools and I think the requirement for the variables kills that approach anyway - surely someone must have had to code something like this up before? If so I hope you're gracious enough to share it! The stored procedure will accept one password only and return an invalid code if the password does not pass the checks.
Here is a sample of the password policy table:
CREATE TABLE dbo.password_policy(
min_pw_lengthtinyint NOT NULL,
ucase_chars_requiredtinyint NOT NULL,
lcase_chars_requiredtinyint NOT NULL,
numeric_chars_requiredtinyint NOT NULL,
special_chars_requiredtinyint NOT NULL,
special_charsvarchar(50)
)
GO
INSERT INTO dbo.password_policy
VALUES (6, 1, 1, 0, 1, '0123456789~`!@#$%^&*()_-+=|\{}[]:;"<>.?/')
I'm thinking that putting the input password into a temporary table with one character per row is the way to go to get the counts of occurrences of them in the special_chars column (that is IF I have to code this myself from scratch)
Cheers!
maddog -
You can do a loop where you cycle through the password that has been entered one character at a time using SUBSTRING. Then you can use a set of nested IFs and the ASCII() function to test for upper case, lower case, and numeric. One final nested IF for use in testing special characters. If you have the special characters read into a variable, you can loop through that variable one character at a time and do a comparison. All the meanwhile you can have the variables to keep count of the number of "hits" for each category. Hopefully this makes sense.
K. Brian Kelley
@kbriankelley -
Heh... loops... IF's... RBAR...
The hard part is figuring out how many of each character type is in the password and the following code figure that out nicely. You can probably figure the rest out pretty easily...
DECLARE @Password VARCHAR(20)SET @PassWord ='Jeff_Moden77'
SELECT SUM(CASE WHEN SUBSTRING(@PassWord,t.N,1) LIKE '[A-Z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS UpperCaseCount,
SUM(CASE WHEN SUBSTRING(@PassWord,t.N,1) LIKE '[a-z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS LowerCaseCount,
SUM(CASE WHEN p.Special_chars LIKE '%'+SUBSTRING(@PassWord,t.N,1)+'%' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS SpecialCount
FROM dbo.Tally t
LEFT JOIN dbo.Password_Policy p
ON p.Special_chars LIKE '%'+SUBSTRING(@PassWord,t.N,1)+'%' COLLATE Latin1_General_BIN
WHERE N <= LEN(@PassWord)
If you don't know what a "Tally" table is or don't have one, now's the time to find out about what it is, how it works, and how to build one. Please see the following...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I would disagree that this is RBAR. RBAR implies you're doing a set-based type of operation. Checking a password for complexity, at the end of the day (since you still have to do multiple comparisons) is a procedural operation. Not to say that the tally table isn't a nice technique, but the procedural operations are the most straight-forward.
K. Brian Kelley
@kbriankelley -
Thanks guys. I guess it's a good thing to always be in the set-based mindset, although Brian is correct in this case we're not dealing with any iterations between rows in actual tables (one static value compared to some values in a single-row table) so there is no RBAR involved really unless breaking down the characters in the input values and\or comparison values in the table row into separate derived rows in a table and processing them iteratively is considered RBAR. Even so, I had seen the tally tables used elegantly before in this forum and ended up going with Jeff's example for my solution simply because it cuts down the number of steps and IF conditions. The core of what I ended up with is:
DECLARE @Password varchar(20)SET @Password = '!My_Pwd99#'
-- Set up tally table to assist character-by-character processing
SELECT TOP 20
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
-- Declare 'count' variables to be tested later
DECLARE @UCaseCount int,
@LCaseCount int,
@NumbrCount int,
@SCharCount int
-- Get counts of character categories using fixed collation
SELECT@UCaseCount = SUM(CASE WHEN SUBSTRING(@Password,t.N,1) LIKE '[A-Z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END),
@LCaseCount = SUM(CASE WHEN SUBSTRING(@Password,t.N,1) LIKE '[a-z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END),
@NumbrCount = SUM(CASE WHEN SUBSTRING(@Password,t.N,1) LIKE '[0-9]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END),
@SCharCount = SUM(CASE WHEN p.special_chars LIKE '%'+SUBSTRING(@Password,t.N,1)+'%' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END)
FROM#Tally t
LEFT JOIN dbo.password_policy p
ON p.special_chars LIKE '%'+SUBSTRING(@Password,t.N,1)+'%' COLLATE Latin1_General_BIN
WHEREt.N <= LEN(@Password)
-- Display counts (temporary for testing)
SELECT@UCaseCount 'UCase', @LCaseCount 'LCase', @NumbrCount 'Numbr', @SCharCount 'SChar'
DROP TABLE #Tally
What I would be interested in finding out is if I can set the collation for the session at one time instead of having it set in each column\value comparison occurrence. Our server default collation is Latin_General. I looked into the SET statements but didn't find anything other than SET LANGUAGE which doesn't look to apply.
Thanks again for the great suggestions.
Cheers all,
maddog -
K. Brian Kelley (11/14/2008)
I would disagree that this is RBAR. RBAR implies you're doing a set-based type of operation. Checking a password for complexity, at the end of the day (since you still have to do multiple comparisons) is a procedural operation. Not to say that the tally table isn't a nice technique, but the procedural operations are the most straight-forward.No... RBAR implies that you are NOT doing a set based operation... checking 1 character at a time in a single "row" or variable using a While Loop qualifies as RBAR and is a fair bit slower over the long haul than the set based solution offered by the "psuedo-cursor" of the Tally table. Trust me... I invented the term, remember? 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
But see, you put row in quotes meaning you are shoehorning in the definition! Aha! We could say CBAC although that doesn't flow as well (for either character by agonizing character or column by agonizing column). 😉
K. Brian Kelley
@kbriankelley -
BWAA-HAAA! CBAC would work! Maybe "Character Reading And Processing" fits better. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
On a related note, here is the procedural based code:
DECLARE @Password varchar(20)DECLARE @i tinyint
DECLARE @j-2 tinyint
DECLARE @len tinyint
DECLARE @len_sc tinyint
DECLARE @Numbers tinyint
DECLARE @Capitals tinyint
DECLARE @Lowercase tinyint
DECLARE @Special tinyint
DECLARE @SpecialChars varchar(50)
SET @Password = '!My_Pwd99#'
SET @SpecialChars = (SELECT special_chars FROM dbo.password_policy)
SET @len = LEN(@Password);
SET @len_sc = LEN(@SpecialChars);
SET @Numbers = 0;
SET @Capitals = 0;
SET @Lowercase = 0;
SET @Special = 0;
SET @i = 0;
WHILE (@i < @len)
BEGIN
SET @i = @i + 1;
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 48 AND 57
SET @Numbers = @Numbers + 1;
ELSE
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 65 AND 90
SET @Capitals = @Capitals + 1;
ELSE
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 97 AND 122
SET @Lowercase = @Lowercase + 1;
SET @j-2 = 0
WHILE (@j < @len_sc)
BEGIN
IF SUBSTRING(@SpecialChars, @j-2, 1) = SUBSTRING(@Password, @i, 1)
BEGIN
SET @Special = @Special + 1;
BREAK;
END
END
END
SELECT @Capitals 'Ucase', @Lowercase 'Lcase', @Numbers 'Numbr', @Special 'Schar'
GO
Did a quick execution plan cost of the two methods. The tally table method costs 0.01677993 (create the tally table) + 0.007792821 (do the comparison) = 0.024572751. The procedural = 0.003290117. Now if you had the tally table in place, you still get 0.00779 vs. 0.00329. At least, those are the results I got with STATISTICS PROFILE ON. YMMV.
K. Brian Kelley
@kbriankelley -
Heh... you don't actually believe the costs in the execution plan, do you? The execution plan for WHILE loops and other forms of RBAR are only for the FIRST ITERATION of the loop... not all the interations. I've got a couple of pieces of code to show you... I'll be right back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well both methods proposed I think are cleaner than my original thinking - I knew up front the tally approach may have applicability but did not want to put my head around it yet, but at the same time I knew there must be more of a set-based method rather than the pure procedural approach.
Before seeing the proposed solutions I was heading towards creating a permanent table of ASCII characters, with one row per character, with identifier bit columns for IsNumeric, IsSpecialChar, IsLCase, and IsUCase, then for the actual processing in a WHILE loop (using ASCII representation of each password character) get the SUM(counts) of the bits for each category if the password ASCII character RIGHT JOINS to the ASCII table. The end result would have been a combination of a bit of both methods but I'm sure a lot messier.
I do appreciate the comparison testing and the suggested approaches. Interesting that the procedural code wins the speed contest under the conditions given for the test case, but as usual I'm sure it depends on what is being done and how much data is involved.
Cheers,
maddog -
Just to be sure, you're calling the WHILE loop the "procedural" code, right? If you're giving the "win" to the WHILE loop based on what you found in the execution plan, then I'll have to challenge that because, as you'll see in the following, the execution plan is frequently dead wrong... based on both the Estimated and Actual Execution Plans of the following code, which one would you say "won" the race? When you've made your decision, look at the messages tab for a big surprise!
-- Each code example creates 10 years worth of dates and puts them into a throwaway-- variable to measure the speed of the process instead of the speed of display.
SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by (Name with-held)
--=======================================================================================
PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
SET @DateVal = '2008-01-01'
;with mycte as
(
select @DateVal AS DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < DATEADD(yy, 10, @DateVal)
)
select @Bitbucket = d.dateval
from mycte d
OPTION (MAXRECURSION 0)
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
GO
--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Bitbucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
SET @StartDate = '2008-01-01'
SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,10,@StartDate)))
@Bitbucket = @StartDate-1+t.N
FROM Tally t
ORDER BY N
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... here's the code I used to compare the WHILE loop solution you came up with against the Tally table version I posted. I had to increase the size of the password by 20X just so it would show something besides "0" in the trace for both...
Here's the 20X code... I commented out the part where you calculate the number of digits just to give the WHILE loop a fair chance 😛
--===== Procedural code
DECLARE @Password varchar(100)
DECLARE @i tinyint
DECLARE @j-2 tinyint
DECLARE @len tinyint
DECLARE @len_sc tinyint
DECLARE @Numbers tinyint
DECLARE @Capitals tinyint
DECLARE @Lowercase tinyint
DECLARE @Special tinyint
DECLARE @SpecialChars varchar(50)
SET @PassWord = REPLICATE('!My_Pwd99#',20)
SET @SpecialChars = (SELECT special_chars FROM dbo.password_policy)
SET @len = LEN(@Password);
SET @len_sc = LEN(@SpecialChars);
SET @Numbers = 0;
SET @Capitals = 0;
SET @Lowercase = 0;
SET @Special = 0;
SET @i = 0;
WHILE (@i < @len)
BEGIN
SET @i = @i + 1;
-- IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 48 AND 57
-- SET @Numbers = @Numbers + 1;
-- ELSE
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 65 AND 90
SET @Capitals = @Capitals + 1;
ELSE
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 97 AND 122
SET @Lowercase = @Lowercase + 1;
SET @j-2 = 0
WHILE (@j < @len_sc)
BEGIN
IF SUBSTRING(@SpecialChars, @j-2, 1) = SUBSTRING(@Password, @i, 1)
BEGIN
SET @Special = @Special + 1;
BREAK;
END
END
END
SELECT @Capitals 'Ucase', @Lowercase 'Lcase', @Special 'Schar'
GO
--===== Set base code with TALLY table
DECLARE @Password VARCHAR(100)
SET @PassWord = REPLICATE('!My_Pwd99#',20)
SELECT SUM(CASE WHEN SUBSTRING(@PassWord,t.N,1) LIKE '[A-Z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS UpperCaseCount,
SUM(CASE WHEN SUBSTRING(@PassWord,t.N,1) LIKE '[a-z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS LowerCaseCount,
SUM(CASE WHEN p.Special_chars LIKE '%'+SUBSTRING(@PassWord,t.N,1)+'%' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS SpecialCount
FROM dbo.Tally t
LEFT JOIN dbo.Password_Policy p
ON p.Special_chars LIKE '%'+SUBSTRING(@PassWord,t.N,1)+'%' COLLATE Latin1_General_BIN
WHERE N <= LEN(@PassWord)
GO
--========================================================================
GO
...and here's the trace... Tally table wins for duration, CPU usage, and Rowcount. The reads are all from cache.
Here's the 100x code...
--===== Procedural code
DECLARE @Password varchar(8000)
DECLARE @i SMALLINT
DECLARE @j-2 SMALLINT
DECLARE @len SMALLINT
DECLARE @len_sc SMALLINT
DECLARE @Numbers SMALLINT
DECLARE @Capitals SMALLINT
DECLARE @Lowercase SMALLINT
DECLARE @Special SMALLINT
DECLARE @SpecialChars varchar(50)
SET @PassWord = REPLICATE('!My_Pwd99#',100)
SET @SpecialChars = (SELECT special_chars FROM dbo.password_policy)
SET @len = LEN(@Password);
SET @len_sc = LEN(@SpecialChars);
SET @Numbers = 0;
SET @Capitals = 0;
SET @Lowercase = 0;
SET @Special = 0;
SET @i = 0;
WHILE (@i < @len)
BEGIN
SET @i = @i + 1;
-- IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 48 AND 57
-- SET @Numbers = @Numbers + 1;
-- ELSE
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 65 AND 90
SET @Capitals = @Capitals + 1;
ELSE
IF ASCII(SUBSTRING(@Password, @i, 1)) BETWEEN 97 AND 122
SET @Lowercase = @Lowercase + 1;
SET @j-2 = 0
WHILE (@j < @len_sc)
BEGIN
IF SUBSTRING(@SpecialChars, @j-2, 1) = SUBSTRING(@Password, @i, 1)
BEGIN
SET @Special = @Special + 1;
BREAK;
END
END
END
SELECT @Capitals 'Ucase', @Lowercase 'Lcase', @Special 'Schar'
GO
--===== Set base code with TALLY table
DECLARE @Password VARCHAR(8000)
SET @PassWord = REPLICATE('!My_Pwd99#',100)
SELECT SUM(CASE WHEN SUBSTRING(@PassWord,t.N,1) LIKE '[A-Z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS UpperCaseCount,
SUM(CASE WHEN SUBSTRING(@PassWord,t.N,1) LIKE '[a-z]' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS LowerCaseCount,
SUM(CASE WHEN p.Special_chars LIKE '%'+SUBSTRING(@PassWord,t.N,1)+'%' COLLATE Latin1_General_BIN THEN 1 ELSE 0 END) AS SpecialCount
FROM dbo.Tally t
LEFT JOIN dbo.Password_Policy p
ON p.Special_chars LIKE '%'+SUBSTRING(@PassWord,t.N,1)+'%' COLLATE Latin1_General_BIN
WHERE N <= LEN(@PassWord)
GO
--========================================================================
GO
... and here's the trace for the 100x runs...
The Tally table beats the pants off the WHILE loop.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also, I never suggested making a Tally table on the fly... a permanent Tally table is really the way to go, in most cases. Exception to that rule is if you use the same method I always use to make a Tally table but make it as a CTE ROW_NUMBER instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh yeah... here's the proof that the Actual Execution Plan for the WHILE loop contains costs for only 1 row or, in this case, one character... that's what the wicked skinny lines mean. Any time you see that, it's the footprint for RBAR... 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply