Custom Password Policy Stored Procedure Needed

Question

Custom Password Policy Stored Procedure Needed

Viewing 12 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

That looks close to what I have in my calendar view at work. I'll have to check it later, as I am at the Y right now and need to get ready to leave. My younger two should be finishing their swim lesson, and the older of the two is going to the BYU vs Air Force game today and the USAFA.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Jeff Moden (11/15/2008)
K. Brian Kelley (11/14/2008)
And I'll acquiesce, because over a run of 20,000 with a permanent tally table (Jeff's condition, and that makes sense since that's the costliest operation), the original password given was 20 sec for Tally vs. 21 sec for the iterative. For "password10" however, it was 20 sec vs 20 sec. For "passwordis" the results are also 20 vs. 20. But when you kick up to 30,000 iterations, you see the Tally table creep ahead by a second.
Um... if you're gonna make claims like that, you really need to show the rest of the code you used to test. For example, there is no way that, in it's current condition, the code you just posted could be used to do anything more than a single password. You either need to convert it to a function or you need to add another nest to your WHILE loop. Using GO 20000 isn't the same as running the Tally table solution as a set based solution.
Heh... so I'm not going to let you acquiesce yet...

I wrapped both in a while loop retesting the same password. But it should be pointed out neither snippet of code technically meet the solution, though. After all, at some point you have to do comparisons with the criteria. 🙂

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Jeff Moden (11/15/2008)
Let's up the anti a bit... let's actually solve the MadDogs' (the OP) original request and time it against 100,000 rows of 10 character passwords. It's gotta be done in 100% T-SQL and you have to use procedural code and I have to use the Tally table without WHILE loops, but other than that, no rules.
Heh... let the game begin. 😛

No reason to up the ante. I already agreed your solution was faster in T-SQL with the limited testing I did. I did real simple passwords of 10 character length. As the character length increases, the iterative code will lose ground. So if you're doing passphrases or randomly generated passwords of any decent length, it will continue to perform worse. 🙂

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

K. Brian Kelley (11/15/2008)
I wrapped both in a while loop retesting the same password.

Heh... you used procedureal code to drive a set based solution. :hehe: Ok... I guess I'll have to do this on my own. 😉

--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)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Jeff Moden (11/15/2008)
K. Brian Kelley (11/15/2008)
I wrapped both in a while loop retesting the same password.
Heh... you used procedureal code to drive a set based solution. :hehe: Ok... I guess I'll have to do this on my own. 😉

Yes, I did, because it fit the methodology. I wanted to see what the duration would be if I ran each technique 10,000 then 20,000 then 30,000 times, one execution at a time, because that was a very simple way of proving one over the other. And to be blunt, I've already agreed your solution was best. I put a post in to try and help someone, not to get my remarks repeatedly met with condescending and insulting language. At this point, I wish I hadn't bothered at all.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Sorry Brian... didn't mean to sound condescending or for you to take any of this as an insult.

The last point was that you used a form of RBAR to drive my method and the RBAR made it look like that it was a close race. Remember?

K. Brian Kelley (11/14/2008)
And I'll acquiesce, because over a run of 20,000 with a permanent tally table (Jeff's condition, and that makes sense since that's the costliest operation), the original password given was [font="Arial Black"]20 sec for Tally vs. 21 sec for the iterative[/font]. For "password10" however, it was 20 sec vs 20 sec. For "passwordis" the results are also 20 vs. 20. But when you kick up to 30,000 iterations, you see the Tally table creep ahead by a second.

It's not the close race that you said it was in the quote above... The point I was trying to make was that you tested wrong... you used my code in a loop instead of doing it set based and that made it look like they were close and that maybe doing the set based method wasn't worth it. Well, the set based method solves not only the counts, but it solves it for 100,000 rows for all the requirements and a few more in about 10 seconds... the about 10 seconds faster than the loop method took on a 5th of the rowcount. Here's the code...

First, the test bed...

--===== Create and populate a 1,000,000 row password test table including all of the "legal" characters

-- and two illegal characters (comma and single quote).

DECLARE @Characters VARCHAR(100),

@Length INT

SELECT @Characters = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789~`!@#$%^&*()_-+=|\{}[]:;"<>.?/,''',

@Length = LEN(@Characters)

SELECT TOP 100000

RowNum = IDENTITY(INT,1,1),

Password = SUBSTRING(@Characters,ABS(CHECKSUM(NEWID()))%@Length+1,1)

+ SUBSTRING(@Characters,ABS(CHECKSUM(NEWID()))%@Length+1,1)

INTO #Password

FROM Master.dbo.SysColumns t1

CROSS JOIN Master.dbo.SysColumns t2

--===== Add a primary key in case there are any dupe passwords

ALTER TABLE #Password

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== "Wound" the length on some passwords so they're too short

UPDATE #Password

SET Password = LEFT(Password,ABS(CHECKSUM(NEWID()))%60)

... and here's the code which could easily be turned into a stored procedure. It not only meets all of the original requirements, but it also checks for and counts illegal characters...

--===== Start the CPU and duration timers

SET STATISTICS TIME ON

--===== Supress the auto-display of rowcounts for appearance

SET NOCOUNT ON

--===== Declare local variables that match the policy table

-- and populate them from that table.

DECLARE @Min_Pw_Length TINYINT,

@UCase_Chars_Required TINYINT,

@LCase_Chars_Required TINYINT,

@Special_Chars_Required TINYINT,

@Special_Chars VARCHAR(50)

SELECT @Min_Pw_Length = Min_Pw_Length,

@UCase_Chars_Required = UCase_Chars_Required,

@LCase_Chars_Required = LCase_Chars_Required,

@Special_Chars_Required = Special_Chars_Required,

@Special_Chars = Special_Chars

FROM dbo.Password_Policy

--===== Create a report of passwords that do not meet the requirements of the Policy table and the reasons why.

SELECT d.RowNum,

d.Password,

CASE WHEN d.Length < @Min_Pw_Length THEN @Min_Pw_Length - d.Length ELSE 0 END AS CountShort,

CASE WHEN d.CountUpper < @UCase_Chars_Required THEN @UCase_Chars_Required - d.CountUpper ELSE 0 END AS CountMissingUpper,

CASE WHEN d.CountLower < @LCase_Chars_Required THEN @LCase_Chars_Required - d.CountLower ELSE 0 END AS CountMissingLower,

CASE WHEN d.CountSpecial < @Special_Chars_Required THEN @Special_Chars_Required - d.CountSpecial ELSE 0 END AS CountMissingSpecial,

CASE WHEN d.CountUpper+d.CountLower+d.CountSpecial <> d.Length THEN d.Length - (d.CountUpper+d.CountLower+d.CountSpecial) ELSE 0 END AS CountIllegal

--,Length,CountUpper,CountLower,CountSpecial,d.CountUpper+d.CountLower+d.CountSpecial --For troubleshooting

FROM

(--==== Preaggregate the counts like before.

-- I used Brian's method to determine upper/lower case because the OP didn't like the COLLATE clause I used previously.

SELECT jt.RowNum,

jt.Password,

SUM(CASE WHEN ASCII(SUBSTRING(jt.Password,t.N,1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END) AS CountUpper,

SUM(CASE WHEN ASCII(SUBSTRING(jt.Password,t.N,1)) BETWEEN 97 AND 122 THEN 1 ELSE 0 END) AS CountLower,

SUM(CASE WHEN @Special_Chars LIKE '%'+CHAR(3)+SUBSTRING(jt.Password,t.N,1)+'%' ESCAPE CHAR(3) THEN 1 ELSE 0 END) AS CountSpecial,

LEN(Password) AS Length

FROM dbo.Tally t

CROSS JOIN dbo.#Password jt

WHERE t.N <= LEN(jt.Password)+1 --(+1) takes care of blank passwords, faster than using OR LEN(jt.Password) = 0

GROUP BY jt.RowNum, jt.Password)d

WHERE d.Length < @Min_Pw_Length --PW not long enough

OR d.CountUpper < @UCase_Chars_Required --Not enough uppercase characters

OR d.CountLower < @LCase_Chars_Required --Not enough lowercase characters

OR d.CountSpecial < @Special_Chars_Required --Not enough special characters

OR d.CountUpper+d.CountLower+d.CountSpecial <> d.Length --Illegal characters present

--===== Turn the timers off

SET STATISTICS TIME OFF

Here's the time output I get...

[font="Courier New"]SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 7828 ms, elapsed time = 9708 ms.[/font]

And, that time of less than 10 seconds was my whole point... I wasn't trying to make a fool of you or anything... I just wanted everyone to know that RBAR doesn't even come close to a good set based method like you suggested. I wanted them to know that it's always worth using set based methods because it blows RBAR away by a factor of at least 10.

Sorry you took it all the wrong way.

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

MadDogs... sorry all of that took so long. The code above solves your problem. Appologies for the delay.

--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)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

Jeff Moden (11/15/2008)
It's not the close race that you said it was in the quote above... The point I was trying to make was that you tested wrong... you used my code in a loop instead of doing it set based and that made it look like they were close and that maybe doing the set based method wasn't worth it. Well, the set based method solves not only the counts, but it solves it for 100,000 rows for all the requirements and a few more in about 10 seconds... the about 10 seconds faster than the loop method took on a 5th of the rowcount. Here's the code...

The reason for my methodology was a simple one, and I don't think I tested it wrong. Maybe I'm assuming the wrong scenario, but then again, when do you usually check password complexity (especially given the part about SQL Server and Windows OS filters not being enough)? I made the assumption the check was being made when a password was first set or changed. Which is one password at a time due in SQL Server 2000 to an sp_password or an sp_addlogin execution. So if you've got 15 people changing/setting their passwords, even if it's all concurrent, that's 15 different executions of 1, not 1 set based execution of 15. That's the scenario I was testing for, which is what drove my methodology.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

You still miss the point... even if it's for one password, you shouldn't use RBAR to do it. Someone in a pinch will find the code and use it on something much larger.

--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)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

No, I got that point, I think you might be missing mine. I'm not debating which method to use. The tally table/set-based one is clearly superior. I was speaking to when I applied the testing methodology, and the reason I went to a loop is because regardless of method, a password complexity check is going to be RBAR in an actual situation. You're going to get one password change at a time. So for testing purposes, the RBAR type of WHILE loop more closely simulates "actual working conditions." And in this type of scenario at small runs the performance difference may be masked, but even at 20,000 consecutive runs, when it got past the most simplest case (all lowercase, meaning only one decision on the nested IF tree), I started to see a divergence in time in favor of the set-based method.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Ah..., sorry Brian. I was, in fact, missing your point. Now I understand what you meant and why. Thanks.

--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)

maddogs SSCrazy Points: 2474 More actions · Answer 12

Well I was perfectly happy with the first couple of posts (you had me at 'ASCII()' and 'Tally' :P) but regret that my unfortunate use of the word 'win' precipitated a performance battle. Should have known better in this forum - and this is meant in a positive way as I have often learned many things in the course of the discussions.

I definitely could have described it bettter but Brian's read of the situation describes the actual business scenario at hand - the app in question is an internal one to the company with maybe 50 users logged in at any one time, and maybe 10 people at most day will change thier passwords. The password filtering SP will process one of these changed passwords at a time. It will not be checked at the login creation time but only when passwords are changing. There are three points where a user can change their password for the app in question so the filter SP will be called from each of these points.

I am hanging onto Jeff's last complete SP post that has the capability to process multiple passwords and also inform the user exactly what is bad about each password for future use. However, with slight alterations borrowed from that post I am probably going to stick with the code posted in my original code post, as there is no requirement to tell the user what is bad at this time; I will simply get the counts as described and then check them individually with IF logic and bail with a bad error code as soon as the first bad condition is reached. I do want to inform the user of what the requirements are when they are barked at - so because the password filtering is dynamic I will build a string telling them what the requirements are based on the dynamic filtering values and send the string back in an output parameter from the SP. I definitely don't want the three different front end entry points hardcoding the requirements as that defeats the purpose of the centralized dynamic password filtering concept in this case.

Again I appreciate all the valuable information provided in this thread.

Cheers to all,

maddog