| Author |
Topic |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2006-08-11 : 15:19:29
|
| I am looking for some help to write a query.TableA has two columnsuserpasswordEvery user needs to have a different password. There are currently about 100000 records in tableA, I wanted to write a query that would change the password of a user, but to make sure that password didn't already exists in the table.The following query is what I wrote to update TableA with the new password, but how do I add the constratint to the query so that it verifies that the password doesn't already exists in the table? the password has to contain special characters, so that's why I am adding the #update TABLEAset password= (Select replace (substring (Cast(newid() as char (36)),6,8),'-','#'))where USER='000012345'Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-11 : 16:14:46
|
| It is not a good security practice to force all users to have different passwords.If a user tries to change their password and you disallow it for that reason, you have given them a password that is vaild for at least one other users account.CODO ERGO SUM |
 |
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2006-08-14 : 08:35:43
|
| Thanks for the advice, however, users are not allowed to change their passwords in our system. They are generated and sent to the users. In generating though, I just want to make sure the password doesn't already exist. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-14 : 09:30:25
|
| mdhingra01,This may be inefficient :U may do it in ur way,then query, for the records having duplicate pswds (u can get this to a derived table)Do it recursively until u won't have any duplicates.Or u can query the original table and insert into a different table having the same structure as of original table. When inserting u can check for duplicatesSrinika |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2006-08-14 : 10:18:57
|
| What I am really looking for is how to store the new password as a variable and then query the Table A to see if that new password exists in the table. If not then insert/update the record.I really need help to define the DECLARE and SET statments and the IF.DECLARE @Password varcahr (8)SET @password= replace (substring (Cast(newid() as char (36)),6,8),'-','#')IF@password exists in TABLEA then reset passwordelse insert/update TableA |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-14 : 11:25:10
|
quote:
Originally posted by mdhingra01
What I am really looking for is how to store the new password as a variable and then query the Table A to see if that new password exists in the table. If not then insert/update the record.I really need help to define the DECLARE and SET statments and the IF.DECLARE @Password varcahr (8)SET @password= replace (substring (Cast(newid() as char (36)),6,8),'-','#')IF@password exists in TABLEA then reset passwordelse insert/update TableA
U may be looking for a solution using Cursors!!This way is farely inefficient.Srinika |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-14 : 12:21:50
|
| [code]if not exists ( select * from TABLEA where password = @password ) begin -- password does no exist update ... endelse begin ... password exists, so whatever... end[/code]Since the # is always in the same spot and is always a #, it doesn't provide much security. If fact, you are really just reducing the password length to 7 characters, since everyone will soon figure that out. You need to vary both the position and value of the special character.The use of newid() to generate the password characters means you only have 16 possible characters for the password, so the password is actually fairly weak, with 268,435,456 possible values(16 to the 7th power). If you used 26 letters, 10 numbers, and 10 special characters to pick the password from, 8 charaqcters would give you 20,047,612,231,936 possible values (46 to the 8th power).CODO ERGO SUM |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2006-08-14 : 13:32:32
|
| I originally wanted to did not want the special character in the same spot, but didn't know how to do this. What would the function be generate a more secure password?Also, the else part, how would I instruct the procedure to generate a new password and loop through the process from the begining?Thanks. |
|
|
|
|
lookup and update