Re: Record old passwords ?
Hi Tompkins,
Check the below URL, looks like useful for your project.
20) set_password('username','hostname','oldpassword','newpassword');
(version 0.1.1) (version 0.1.4 added oldpassword) -- Changes password for
any user (if current user is root), otherwise changes own password if
current user is not root. can change the password up to 11times in 1 day and
stores the last 5 passwords which were not changed for at least 24hrs. Does
not permit the new password to be the same as any of the old passwords.
Resets update count if more than 24hrs passed from last first update of the
day. Password must be longer than '10 characters (configurable amount
through sec_config.password_length)'. Complexity requirements are set on
sec_config:
1. password_length_check
2. password_dictionary_check
3. password_lowercase_check
4. password_uppercase_check
5. password_number_check
6. password_special_character_check
7. password_username_check
Root user doesn't need to abide to the above password restrictions when
creating a new user since the latter will have to change the password and
set one of his own.
In order for a user to change one's old password, the user needs to supply
the old password apart from the new one as well.
For more details, check the below link
http://code.google.com/p/securich/wiki/Documentation
Thanks,
Suresh Kuna
MySQL DBA
On Fri, Jan 22, 2010 at 11:52 PM, Tompkins Neil
neil.tompk...@googlemail.com wrote:
Hi
Thanks for all the responses. In the end I opted for
a separate UserPasswords table, which records all old passwords. When a
user changes their password, this table is checked. NB All passwords are
stored in SHA256.
Thanks again for your advice.
Regards
Neil
On Wed, Jan 20, 2010 at 12:08 PM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Wednesday 20 January 2010 01:10, Daevid Vincent wrote:
-Original Message-
From: John Meyer [mailto:john.l.me...@gmail.com]
Sent: Monday, January 18, 2010 5:04 PM
To: co...@obviouslymalicious.com; mysql@lists.mysql.com
Subject: Re: Record old passwords ?
Although, on an OT, forcing people to not use a password that they
have recently used is a bad idea. What they eventually do is go with
something like hometown01 hometown02, etc. Or worse, they start
writing down their passwords which is a whole other security problem.
Amen to that. At my work, they require a password change every month,
but
they store the last 6 passwords you used, so I do exactly what you say
--
I
have a logbook and store the same 6 passwords in it and just cycle
them.
Other tricks I do, is use a pattern on the keyboard and just shift
it.
None of this is secure, and I totally know it (although I'm not picking
secret or something as my PW, it's random letters/numbers/symbols).
But
I
hate the policy and I'm kind of a rebel like that. ;-p
Several years ago I worked at a place where users had to change their
windows
password every N month and they kept a long history log of used password.
My solution to this was to write a program that asked me for my current
password and how many previous used password the system remembered. The
program worked like this:
for (n = 0; no_of_stored_password n; n++) {
set_password(random_generated_password);
do_a_short_sleep();
}
set_password(original_password);
... and the problem was solved :)
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
--
Thanks
Suresh Kuna
MySQL DBA
Re: Record old passwords ?
Hi
Thanks for all the responses. In the end I opted for
a separate UserPasswords table, which records all old passwords. When a
user changes their password, this table is checked. NB All passwords are
stored in SHA256.
Thanks again for your advice.
Regards
Neil
On Wed, Jan 20, 2010 at 12:08 PM, Jørn Dahl-Stamnes
sq...@dahl-stamnes.netwrote:
On Wednesday 20 January 2010 01:10, Daevid Vincent wrote:
-Original Message-
From: John Meyer [mailto:john.l.me...@gmail.com]
Sent: Monday, January 18, 2010 5:04 PM
To: co...@obviouslymalicious.com; mysql@lists.mysql.com
Subject: Re: Record old passwords ?
Although, on an OT, forcing people to not use a password that they
have recently used is a bad idea. What they eventually do is go with
something like hometown01 hometown02, etc. Or worse, they start
writing down their passwords which is a whole other security problem.
Amen to that. At my work, they require a password change every month, but
they store the last 6 passwords you used, so I do exactly what you say --
I
have a logbook and store the same 6 passwords in it and just cycle them.
Other tricks I do, is use a pattern on the keyboard and just shift it.
None of this is secure, and I totally know it (although I'm not picking
secret or something as my PW, it's random letters/numbers/symbols). But
I
hate the policy and I'm kind of a rebel like that. ;-p
Several years ago I worked at a place where users had to change their
windows
password every N month and they kept a long history log of used password.
My solution to this was to write a program that asked me for my current
password and how many previous used password the system remembered. The
program worked like this:
for (n = 0; no_of_stored_password n; n++) {
set_password(random_generated_password);
do_a_short_sleep();
}
set_password(original_password);
... and the problem was solved :)
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Record old passwords ?
On Tue, 19 Jan 2010, Tompkins Neil wrote: I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Probably if your users do that, it means they (rightfully) consider A DAMN NUISANCE the fact to be compelled to change password. Abandon the idea. I share their feeling about forcing this change of passwords, and cannot see almost no real life application (unless perhaps one is a spy) which really require this degree of security ! -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 21/01/2010 11:07, Lucio Chiappetti wrote: On Tue, 19 Jan 2010, Tompkins Neil wrote: I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Probably if your users do that, it means they (rightfully) consider A DAMN NUISANCE the fact to be compelled to change password. Abandon the idea. I share their feeling about forcing this change of passwords, and cannot see almost no real life application (unless perhaps one is a spy) which really require this degree of security ! The real life application most commonly encountered where this is necessary is where your organisation wishes to process credit card or other financial data, and needs to be certified as PCI compliant by the banks and card companies in order to be able to process payments via their systems. One of the requirements of PCI compliance is that any login which has access to financial data must have the password changed regularly, with restrictions on reusing recent passwords. Now, you may well argue that the PCI requirements are wrong in this respect, and if so then a lot of people may well agree with you :-) However, unless you are a huge multinational and able to negotiate your own terms with the banks, disagreeing with the requirements doesn't alter the need to comply with them - at least, not if you want to be able to use their payment APIs. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 1/19/2010 7:49 AM, Mark Goodge wrote: On 19/01/2010 14:44, Tompkins Neil wrote: Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Store the date/time that the password was changed, and as well as not alllowing one within the past four passwords you can also disallow one that was last used within the past N days, for whatever value of N you prefer. Mark Keep in mind that if you do this you may be setting yourself up for other security risks (people writing down passwords, etc). If a security measure gets in the way of the right people's ability to access the environment, they will find a way to circumvent it--and screw over your pci compliance in the process. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Record old passwords ?
As an auditor once told me, If you can do your job, then I'm not doing my job. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On Wednesday 20 January 2010 01:10, Daevid Vincent wrote:
-Original Message-
From: John Meyer [mailto:john.l.me...@gmail.com]
Sent: Monday, January 18, 2010 5:04 PM
To: co...@obviouslymalicious.com; mysql@lists.mysql.com
Subject: Re: Record old passwords ?
Although, on an OT, forcing people to not use a password that they
have recently used is a bad idea. What they eventually do is go with
something like hometown01 hometown02, etc. Or worse, they start
writing down their passwords which is a whole other security problem.
Amen to that. At my work, they require a password change every month, but
they store the last 6 passwords you used, so I do exactly what you say -- I
have a logbook and store the same 6 passwords in it and just cycle them.
Other tricks I do, is use a pattern on the keyboard and just shift it.
None of this is secure, and I totally know it (although I'm not picking
secret or something as my PW, it's random letters/numbers/symbols). But I
hate the policy and I'm kind of a rebel like that. ;-p
Several years ago I worked at a place where users had to change their windows
password every N month and they kept a long history log of used password.
My solution to this was to write a program that asked me for my current
password and how many previous used password the system remembered. The
program worked like this:
for (n = 0; no_of_stored_password n; n++) {
set_password(random_generated_password);
do_a_short_sleep();
}
set_password(original_password);
... and the problem was solved :)
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
Hi Thanks for all the replies. For your information, we are going to store passwords using SHA256. I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. I can then pull all the data with one query. I don't envisage that we will need to record the last 20 passwords as a example in the future - so if I need to expand in the future it should not be too involved. Cheers Neil On Tue, Jan 19, 2010 at 1:11 AM, Carlos Proal carlos.pr...@gmail.comwrote: On 1/18/2010 6:52 PM, Colin Streicher wrote: On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. Neil, As others appointed, having another table with old passwords is a good design solution, and can allow you to have more than 4 passwords on your history. But in fact your solution is the best solution for performance and is called denormalization, this solution gives good performance because in 1 read you get all the passwords but has the limitation of be fixed to only 4 passwords (which is not so bad because you can add new columns as needed, you will never have 20 history passwords anyway, do you ?). So, thats the trade, design vs performance, you should pick the best for you. The solution proposed by Colin is another way to do it but, from the good design perspective is NOT a good solution, is what its called a multivalued attribute and all those should be avoided. But again, is up to you. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Record old passwords ?
Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Cheers Neil On Tue, Jan 19, 2010 at 9:14 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi Thanks for all the replies. For your information, we are going to store passwords using SHA256. I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. I can then pull all the data with one query. I don't envisage that we will need to record the last 20 passwords as a example in the future - so if I need to expand in the future it should not be too involved. Cheers Neil On Tue, Jan 19, 2010 at 1:11 AM, Carlos Proal carlos.pr...@gmail.comwrote: On 1/18/2010 6:52 PM, Colin Streicher wrote: On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. Neil, As others appointed, having another table with old passwords is a good design solution, and can allow you to have more than 4 passwords on your history. But in fact your solution is the best solution for performance and is called denormalization, this solution gives good performance because in 1 read you get all the passwords but has the limitation of be fixed to only 4 passwords (which is not so bad because you can add new columns as needed, you will never have 20 history passwords anyway, do you ?). So, thats the trade, design vs performance, you should pick the best for you. The solution proposed by Colin is another way to do it but, from the good design perspective is NOT a good solution, is what its called a multivalued attribute and all those should be avoided. But again, is up to you. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Record old passwords ?
Yes, I was thinking something along these lines e.g can only change password once a day ? Also, what do operating systems like Windows etc do in this respect ? Cheers Neil On Tue, Jan 19, 2010 at 2:53 PM, David Lazo lazo.da...@gmail.com wrote: I would say make it more difficult for the user add another field with a flag or a date and not allow changing the password on the same date. On Tue, Jan 19, 2010 at 9:44 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Cheers Neil On Tue, Jan 19, 2010 at 9:14 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi Thanks for all the replies. For your information, we are going to store passwords using SHA256. I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. I can then pull all the data with one query. I don't envisage that we will need to record the last 20 passwords as a example in the future - so if I need to expand in the future it should not be too involved. Cheers Neil On Tue, Jan 19, 2010 at 1:11 AM, Carlos Proal carlos.pr...@gmail.com wrote: On 1/18/2010 6:52 PM, Colin Streicher wrote: On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. Neil, As others appointed, having another table with old passwords is a good design solution, and can allow you to have more than 4 passwords on your history. But in fact your solution is the best solution for performance and is called denormalization, this solution gives good performance because in 1 read you get all the passwords but has the limitation of be fixed to only 4 passwords (which is not so bad because you can add new columns as needed, you will never have 20 history passwords anyway, do you ?). So, thats the trade, design vs performance, you should pick the best for you. The solution proposed by Colin is another way to do it but, from the good design perspective is NOT a good solution, is what its called a multivalued attribute and all those should be avoided. But again, is up to you. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Record old passwords ?
On 19/01/2010 14:44, Tompkins Neil wrote: Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Store the date/time that the password was changed, and as well as not alllowing one within the past four passwords you can also disallow one that was last used within the past N days, for whatever value of N you prefer. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 19/01/2010 09:14, Tompkins Neil wrote: I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. Do you have a reference for that? Storing past passwords as additional fields like that is inflexible and generally bad database design. I'd be somewhat surprised if PCI compliance really did require it. Mark -- http://mark.goodge.co.uk - blog htp://www.good-stuff.co.uk - stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Record old passwords ?
-Original Message- From: John Meyer [mailto:john.l.me...@gmail.com] Sent: Monday, January 18, 2010 5:04 PM To: co...@obviouslymalicious.com; mysql@lists.mysql.com Subject: Re: Record old passwords ? Although, on an OT, forcing people to not use a password that they have recently used is a bad idea. What they eventually do is go with something like hometown01 hometown02, etc. Or worse, they start writing down their passwords which is a whole other security problem. Amen to that. At my work, they require a password change every month, but they store the last 6 passwords you used, so I do exactly what you say -- I have a logbook and store the same 6 passwords in it and just cycle them. Other tricks I do, is use a pattern on the keyboard and just shift it. None of this is secure, and I totally know it (although I'm not picking secret or something as my PW, it's random letters/numbers/symbols). But I hate the policy and I'm kind of a rebel like that. ;-p It's a tough balance between trying to be secure because you have ID-10t users and not being obnoxious to the end result that you have caused more insecurity. Personally, I would suggest to just enforce strong password rules ( 8 characters, no dictionary words, no 'leet' speek, symbol required, one upper required, one number requred, etc.) and leave it at that. But you had better be enforcing this for something like a bank or medical records. If you're trying to do this for a blog or social network site or something equally trite, then you're doing your users a disservice and only serving to frustrate them. And of course, you NEVER store the actual password. You store a hash of it. Then implement a simple system to generate a new password and mail it, or a token to enable the user to change it if forgotten. ÐÆ5ÏÐ Some people, when confronted with a problem, think 'I know, I'll use XML.' Now they have two problems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Record old passwords ?
Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil
Re: Record old passwords ?
I'm still pretty new on the list, so take it easy on me if I'm way off base. But I think you'd be better off with a table just for old passwords. I think you could get by with four columns: id(primary key), user_id, old_pw, change_date. It should make your validation query and inserts much easier. You could simply select * from oldpwtbl where user_id='theuser' order by change_date desc limit 4; (disregard my poor syntax) to see if they are repeating. One other thing I think would be more secure is to store a hash of the password, instead of the password itself. Anyway, that's my input. Scott On Mon, Jan 18, 2010 at 12:34 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
Using multiple columns to hold essentially the same data is generally a bad idea: Business requirements may change over time, forcing you to change both the schema and your programming logic. Better to use a table consisting of username/changedate/password. One year from now, when your boss/customer decides to up the requirement to six passwords, it will be a simple app change. / Carsten Tompkins Neil skrev: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil !DSPAM:451,4b54a9e956471140923725! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. -- In the stairway of life, you'd best take the elevator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 1/18/2010 5:52 PM, Colin Streicher wrote: On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. Almost always, when you start thinking of fields with numbers at the end of their names, you should move that off to another table. Example: PASSWORD_HISTORY PW_ID USER_ID --foreign key linking to the user table PW_ENTRY PW_ENTRYDATE That way all you have to do is write this query: SELECT * FROM PASSWORD_HISTORY WHERE USER_ID='entry' ORDER BY PW_ENTRYDATE DESC LIMIT 4; Although, on an OT, forcing people to not use a password that they have recently used is a bad idea. What they eventually do is go with something like hometown01 hometown02, etc. Or worse, they start writing down their passwords which is a whole other security problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 1/18/2010 6:52 PM, Colin Streicher wrote: On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. Neil, As others appointed, having another table with old passwords is a good design solution, and can allow you to have more than 4 passwords on your history. But in fact your solution is the best solution for performance and is called denormalization, this solution gives good performance because in 1 read you get all the passwords but has the limitation of be fixed to only 4 passwords (which is not so bad because you can add new columns as needed, you will never have 20 history passwords anyway, do you ?). So, thats the trade, design vs performance, you should pick the best for you. The solution proposed by Colin is another way to do it but, from the good design perspective is NOT a good solution, is what its called a multivalued attribute and all those should be avoided. But again, is up to you. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
