prosit
asked on
Check last three passwords
Hello,
I have a security log that I store (encrypted) the passwords used to log in.
When users are required to change their password every three months they cannot use one of the previous three, and I'm having trouble writing that query...
Best I could come up with was getting the third top date and then search within that time frame, but is there a better way? Not sure how to structure that together in one line which is what I'd really like to do.
This would get me the three last used passwords, but I still need to test against the supplied one...
SELECT TOP 3 logdate, username, dbo.decrypt(password), logdate
FROM seclog
WHERE username = 'testuser' AND Success = True
ORDER BY logdate desc
Hope this makes sense :)
Thanks
~j
I have a security log that I store (encrypted) the passwords used to log in.
When users are required to change their password every three months they cannot use one of the previous three, and I'm having trouble writing that query...
Best I could come up with was getting the third top date and then search within that time frame, but is there a better way? Not sure how to structure that together in one line which is what I'd really like to do.
This would get me the three last used passwords, but I still need to test against the supplied one...
SELECT TOP 3 logdate, username, dbo.decrypt(password), logdate
FROM seclog
WHERE username = 'testuser' AND Success = True
ORDER BY logdate desc
Hope this makes sense :)
Thanks
~j
SET ROWCOUNT 3
SELECT logdate, username, dbo.decrypt(password), logdate
FROM seclog
WHERE username = 'testuser' AND Success = True
ORDER BY logdate desc
SELECT logdate, username, dbo.decrypt(password), logdate
FROM seclog
WHERE username = 'testuser' AND Success = True
ORDER BY logdate desc
You need a separate table to store the password history for a user. It makes no sense to scan log history to get that data.
ASKER
BriCrowe:
Hm, here's how I modified your example, can't get it running though... maybe I'm doing it wrong. So @Username and @NewPassword I assumed was the input so I set the im the next line.
pw4 should be an allowed password, pw3 should not based on date used.
When running below I get:
plusone3055:
Thank you, and that does give me the last three, however, it also needs to check that the given password is not part of those three. (or is part which ever way it is.)
Hm, here's how I modified your example, can't get it running though... maybe I'm doing it wrong. So @Username and @NewPassword I assumed was the input so I set the im the next line.
pw4 should be an allowed password, pw3 should not based on date used.
When running below I get:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
DECLARE @Username VARCHAR(50),
@NewPassword NVARCHAR(20);
set @username = 'jf'
set @NewPassword = 'pw3'
WITH ctePassword
AS
(
SELECT logdate, username, dbo.decrypt(password) AS Password,
ROW_NUMBER () OVER (PARTITION BY username ORDER BY logdate DESC) AS PwdIndex
FROM seclog
WHERE username = @Username
)
SELECT CASE WHEN cte.Password = @NewPassword THEN 1 ELSE 0 END AS Match
FROM ctePassword AS cte
WHERE PwdIndex <= 3
plusone3055:
Thank you, and that does give me the last three, however, it also needs to check that the given password is not part of those three. (or is part which ever way it is.)
Don't store passwords! Store hashes of them. You may use HASHBYTES() with an appropriate salt.
ASKER
ScottPletcher:
Ok, that's fine, however it doesn't really change the query problem does it? I'll structure it anyway it's easiest.
~j
Ok, that's fine, however it doesn't really change the query problem does it? I'll structure it anyway it's easiest.
~j
ASKER
Ste5an,
Ok, i'll look into that, right now they are encrypted though, and decrypted ONLY on the SQL server.
~j
Ok, i'll look into that, right now they are encrypted though, and decrypted ONLY on the SQL server.
~j
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@funch: This makes no difference from the security point of view. It's possible to look at them, and this is already the problem. Especially, as you may know, users often use the same passwords for different services.
SELECT u.username, s.password_plain
--SELECT u.username, dbo.encrypt(s.password_pla in) AS password
FROM users u
CROSS APPLY (
SELECT DISTINCT TOP (3) username, dbo.decrypt(password) AS password_plain
FROM seclog
WHERE
s.username = u.username AND
s.Success = True
ORDER BY logdate DESC
) AS s
WHERE
u.username = 'testuser'
--SELECT u.username, dbo.encrypt(s.password_pla
FROM users u
CROSS APPLY (
SELECT DISTINCT TOP (3) username, dbo.decrypt(password) AS password_plain
FROM seclog
WHERE
s.username = u.username AND
s.Success = True
ORDER BY logdate DESC
) AS s
WHERE
u.username = 'testuser'
ASKER
BriCrowe,
Duh... sorry!
working... thank you!!!
Ste5an,
I agree 100% with you, and it's no excuse (even though I'm about to use it) but currently their passwords are stored in clear text.
Only the developer (and there are only one) will have access to the encrypted passwords and he owns the company so... it's as secure as they're willing to pay for it to be.
However, I will suggest that. Will any given string be converted to a different Hashbytes and are they un-decryptable (prolly not a word) once converted?
I appreciate your concern.
~j
Duh... sorry!
working... thank you!!!
Ste5an,
I agree 100% with you, and it's no excuse (even though I'm about to use it) but currently their passwords are stored in clear text.
Only the developer (and there are only one) will have access to the encrypted passwords and he owns the company so... it's as secure as they're willing to pay for it to be.
However, I will suggest that. Will any given string be converted to a different Hashbytes and are they un-decryptable (prolly not a word) once converted?
I appreciate your concern.
~j
Use a different salt per user. Then you'll get even different hashes in the case that two users have used the same password.
A hash is per definition a one-way function. Thus you cannot restore the password from it. But it may happen in rare cases (p << 1) that two different passwords, either with or without salt, get the same hash. This is called a collision.
This allows so called collision attacks. This means when you probe just enough different inputs you'll get another one, which will be accepted. But this is under normal circumstance not a viable way, cause it requires years (at least months) to get one.
A hash is per definition a one-way function. Thus you cannot restore the password from it. But it may happen in rare cases (p << 1) that two different passwords, either with or without salt, get the same hash. This is called a collision.
This allows so called collision attacks. This means when you probe just enough different inputs you'll get another one, which will be accepted. But this is under normal circumstance not a viable way, cause it requires years (at least months) to get one.
ASKER
Thanks
@NewPassword NVARCHAR(20);
WITH ctePassword
AS
(
SELECT logdate, username, dbo.decrypt(password) AS Password,
ROW_NUMBER () OVER (PARTITION BY username ORDER BY logdate DESC) AS PwdIndex
FROM seclog
WHERE username = @Username
)
SELECT CASE WHEN cte.Password = @NewPassword THEN 1 ELSE 0 END AS Match
FROM ctePassword AS cte
WHERE PwdIndex <= 3