June 10, 2012 at 5:31 am
Hi,
I Have 2 table :
The first Contain's : Username , Password, firstname etc'...
I need to build an after update\insert Trigger that will contain 10 lastest username password.
I'm Thinking building a table that contain: Id,Username,Pass_1,Pass_2,Pass_3....
How can i know when was my last column change? In order to know where to input to latest password.
Like First in last out model..
??
Thank you very much The best Forum in the World. 😎
June 10, 2012 at 5:44 am
Table password history:
UserID
Password : varbinary (you are hashing passwords of course)
PasswordStartDate
PasswordEndDate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 9:04 am
yanivkahana10 (6/10/2012)
Hi,I Have 2 table :
The first Contain's : Username , Password, firstname etc'...
I need to build an after update\insert Trigger that will contain 10 lastest username password.
I'm Thinking building a table that contain: Id,Username,Pass_1,Pass_2,Pass_3....
How can i know when was my last column change? In order to know where to input to latest password.
Like First in last out model..
??
Thank you very much The best Forum in the World. 😎
To add to what Gail said, you are hashing the password right and not storing it in plain text?
The best way to do this is to normalize your data. Do not use a structure like Pass_1, Pass_2 etc. The challenges you are facing in figuring out how to deal with this should make it clear enough this is a bad approach.
Instead create a password history table with PwdHistoryID, Username (hopefully this is the primary key and not the actual username which might change), PwdHash, DateCreated.
Now the top 10 ordered by DateCreated are the last 10. When entering a new one just delete where the Row_Count() > 10.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2012 at 1:50 pm
Thank you !
I didn't thought about it this way!!
June 11, 2012 at 1:59 pm
Just to reiterate and emphasise....
You are hashing those passwords according to all recommended practices? Strong hash with salt? If not, see what happened to LinkedIn and imagine it happening to you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 4:00 pm
Given the LinkedIn+eHarmony+Last.FM breach bring people's awareness back to password choices, I'll link to my long prior post in the below thread:
Should we outsource identity management
It boils down to: to authenticate, use tens or hundreds of thousands of iterations of well salted hashing with a well known PBKDF2 function (not available in SQL Server, but definitely available in .NET). Before accepting a new password, check it against hybrid dictionary attacks first, as well as what you are doing, which is checking for reuse.
Note that your "check against the previous 10 passwords", combined with the complexity requirements you probably have along with that, are going to end up with users (probably more than one) choosing, in order:
P@$$w0rd1
P@$$w0rd2
P@$$w0rd3
...
P@$$w0rd35
None of which are worthwhile passwords, and aren't worthwhile passwords whether you hash them (like LinkedIn did), salt them (which LinkedIn didn't), or even salt them and run them through a million PBKDF2 hashing iterations (which LinkedIn didn't) - they're still going to be cracked in a very, very short time.
June 12, 2012 at 4:46 am
Thank you.
As you see my level at this moment as you can imagine that this is not for a real live system,
only a practice system.
and you really sent me to do some work 😛
I will change my code and post it soon.
Best The best
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply