May 7, 2014 at 11:50 am
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 ..
May 7, 2014 at 12:25 pm
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
😎
May 7, 2014 at 12:30 pm
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 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/
May 7, 2014 at 12:39 pm
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.
May 7, 2014 at 1:01 pm
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 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/
May 7, 2014 at 1:01 pm
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:ermm: 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.
😎
May 7, 2014 at 1:13 pm
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.
May 7, 2014 at 1:25 pm
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 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/
May 7, 2014 at 1:32 pm
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 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/
May 7, 2014 at 3:22 pm
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
May 7, 2014 at 4:18 pm
I know that this might not be constructive, but I had to ask.
Why do you want that security measure to avoid repeated passwords, but you limit your passwords to 10 characters and store them in plain text? Would it be just to give you a false security?
To avoid being useless, I guess that you were after something like this, but remember that keeping your system the way it is now, might cause security/privacy issues:
CREATE TABLE #Password_His
(
playerid int not null,
password nchar(10) not null,
PasswordDate datetime
);
INSERT #Password_His
VALUES( 1, 'Password01', GETDATE() - 11),
( 2, 'Password02', GETDATE() - 10),
( 3, 'Password03', GETDATE() - 9),
( 4, 'Password04', GETDATE() - 8),
( 5, 'Password05', GETDATE() - 7),
( 6, 'Password06', GETDATE() - 6),
( 7, 'Password07', GETDATE() - 5),
( 8, 'Password08', GETDATE() - 4),
( 9, 'Password09', GETDATE() - 3),
( 10, 'Password10', GETDATE() - 2),
( 11, 'Password11', GETDATE() - 1);
DECLARE @Exists bit = 0,
@Password nchar(10) = 'Password04';
SELECT @Exists = 1
FROM (
SELECT TOP 10 [password]
FROM #Password_His
ORDER BY PasswordDate DESC)x
WHERE [password] = @Password;
SELECT @Exists;
GO
DROP TABLE #Password_His;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply