SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Password History Table


Password History Table

Author
Message
computer24hr
computer24hr
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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 ..
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42172 Visits: 19526
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
Cool
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65264 Visits: 17979
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 Modens 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)
computer24hr
computer24hr
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65264 Visits: 17979
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 Modens 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)
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42172 Visits: 19526
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 passwordErmm 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.
Cool
computer24hr
computer24hr
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65264 Visits: 17979
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65264 Visits: 17979

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 Modens 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)
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75527 Visits: 40987
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search