Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Password History Table Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 11:50 PM
Points: 9, Visits: 46
hey,

I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....

I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.

Would love to have your help in the script , I searched a lot but could not find something similar in my opinion need SP for it, If you think this trigger would love your opinion.

- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).

Would love to have your help or a link that can help ..

Post #1568628
Posted Wednesday, May 7, 2014 12:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 2,208, Visits: 5,948
computer24hr (5/7/2014)
hey,

I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....

I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.

Would love to have your help in the script , I searched a lot but could not find something similar in my opinion need SP for it, If you think this trigger would love your opinion.

- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).

Would love to have your help or a link that can help ..


Quick questions:
1. When you say that each password should be unique, do you mean for each user or in general?
2. Are you storing the passwords in plain text? Reason I ask is there would be a different approach in determining the uniqueness.
3. A trigger or an insert procedure can handle the numbers of records in history, no problem there.
4. What are the volumes?
5. What is the churn rate (rate of changes)
6. How busy is the system as percentage of capacity
Post #1568644
Posted Wednesday, May 7, 2014 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
computer24hr (5/7/2014)
hey,

I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....

I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.

Would love to have your help in the script , I searched a lot but could not find something similar in my opinion need SP for it, If you think this trigger would love your opinion.

- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).

Would love to have your help or a link that can help ..



When you say your password is a computed column I hope you meant to say encrypted? Honestly it should be a hash instead of encrypted clear text.

Doing this sort of thing is pretty common and not that complicated. You can use either ROW_NUMBER or LEAD/LAG (since you are on 2012) to determine the most recent X rows.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568647
Posted Wednesday, May 7, 2014 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 11:50 PM
Points: 9, Visits: 46
Password, it should be unique to each user name.
The password is stored as nvarchar(10) it's text.

I thought the SP, what do you think?

Relatively low amount of changes but I need to allow this user, I have no amount of users so you can ignore the volume table or the number of users that is under construction right now.
Post #1568652
Posted Wednesday, May 7, 2014 1:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
computer24hr (5/7/2014)
Password, it should be unique to each user name.



Why can't more than one user have the same password? So what happens if a different user enters somebody else's password? Think about how silly the validation message will be. "I'm sorry but this password has been used by somebody else.". Woohoo!!!! Now I know half of the information required to login as another user.


The password is stored as nvarchar(10) it's text.


Passwords should NOT be stored as plain text. There are just too many ways this is a bad idea. You should store the salted hash of a value. This protects your user's sensitive information. Keep in mind that many people only have a few passwords. If you store their email address and password in plain text it is very likely you can access many many other things for this person. Storing passwords securely is a two way benefit. You protect the user data AND you protect you and your company from being the source of identity theft of your users.


I thought the SP, what do you think?

Relatively low amount of changes but I need to allow this user, I have no amount of users so you can ignore the volume table or the number of users that is under construction right now.


Not sure what you mean here at all.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568660
Posted Wednesday, May 7, 2014 1:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 2,208, Visits: 5,948
computer24hr (5/7/2014)
Password, it should be unique to each user name.
The password is stored as nvarchar(10) it's text.

I thought the SP, what do you think?

Relatively low amount of changes but I need to allow this user, I have no amount of users so you can ignore the volume table or the number of users that is under construction right now.


This is straight forward BUT 10 character password does not sound good! Of course this is your responsibility and your decision.

A stored procedure is perfect, now all you need to do is to provide a table structure and some sample data.

I strongly recommend and second Sean's comment, do not store un-encrypted passwords. My preference is to use a seeded hash, as the hashing process is quick but non reversible.
Post #1568661
Posted Wednesday, May 7, 2014 1:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 11:50 PM
Points: 9, Visits: 46
I know it's a problem that is not encrypted, but ultimately will table encryption.

table: ut_Password

columns:
playerid (FK, int, not null)
password (nchar(10), not null)
PasswordDate (datetime)

I understand about what I should do, but I could not take it inspiring example, if a similar script ready I see it would be easy to understand.
Post #1568666
Posted Wednesday, May 7, 2014 1:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
computer24hr (5/7/2014)
I know it's a problem that is not encrypted, but ultimately will table encryption.


No offense intended but it isn't a problem. It is border line criminal. It is just so easy to protect the data that there is no excuse for not doing it.




_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568667
Posted Wednesday, May 7, 2014 1:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545

table: ut_Password

columns:
playerid (FK, int, not null)
password (nchar(10), not null)
PasswordDate (datetime)

I understand about what I should do, but I could not take it inspiring example, if a similar script ready I see it would be easy to understand.


Not sure exactly what you are looking for here. Are you trying to figure out if the table already has 10 rows for a given user and if so what do you want to happen? I have to say, this seems a lot like homework as much of the requirements sound that way.



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568671
Posted Wednesday, May 7, 2014 3:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 12,905, Visits: 32,161
i believe you should simply do like windows AD does, and store the hash of the password, and not the password itself.

then you compare the potential new password to the hash;
maybe save the last ten passwords as a delimited string of hash values, which you cna split with dbo.DelimitedSplit8k, and compare all in that virtual table.

edit: Sean already suggested the same, of saving salted hashes of the password.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1568712
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse