Solved

Check last three passwords

Posted on 2014-04-17
13
273 Views
Last Modified: 2014-04-23
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
0
Comment
Question by:prosit
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40007083
DECLARE @Username VARCHAR(50),
   @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
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40007084
SET ROWCOUNT 3
SELECT  logdate, username, dbo.decrypt(password), logdate  
FROM seclog
WHERE username = 'testuser' AND Success = True
ORDER BY logdate desc
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40007106
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 2

Author Comment

by:prosit
ID: 40007123
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:

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 

Open in new window



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.)
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40007125
Don't store passwords! Store hashes of them. You may use HASHBYTES() with an appropriate salt.
0
 
LVL 2

Author Comment

by:prosit
ID: 40007128
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
0
 
LVL 2

Author Comment

by:prosit
ID: 40007134
Ste5an,

Ok, i'll look into that, right now they are encrypted though, and decrypted ONLY on the SQL server.

~j
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 40007170
If you aren't placing a semi-colon at the end of your SQL statements in the procedure according to ANSI standard then you will need to prepend one before "WITH".

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 

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40007171
@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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40007205
SELECT u.username, s.password_plain
--SELECT u.username, dbo.encrypt(s.password_plain) 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'
0
 
LVL 2

Author Comment

by:prosit
ID: 40007222
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
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40007272
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.
0
 
LVL 2

Author Closing Comment

by:prosit
ID: 40018212
Thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now