Recording how many times a user failed login

How would you design you database if say you had a table called user:

User_Table : id, username, password, password_salt, email, email_salt

Where would you store how many times they have failed l开发者_开发问答ogin and the last time they failed login?

total_failed, last_failed_login_time

I also want to record whether their email address is validated or not?

What would be the best way (not necessarily normalized as that involves using a lot of joins) of doing this? What do you suggest?

I would store total_failed and last_failed_login_time on the User_Table. You can updated it whenever they fail a login. I think email_validated could be stored there too, though I am not sure exactly what you mean by that.

Both total_failed and last_failed_login_time are pretty strange fields to store. Typically login failures are used to help detect attacks on a system.

But wouldn't Total failed simply creep up over time to the point that it would just be a rough measure of how long and how often a given user has been accessing the system, as a typical user will somtimes mistype their password. So large numbers in this field won't tell much.

Last_failed_login_time doesn't seem to confer much either since it wipes out any prior data one might be interested.

Maybe I'm missing the point of tracking this data?

You can store both of those fields as part of the user record. The only thing making you non-normalized (to 5NF, anyway; you are normalized to 3NF) is the fact that you'd need to make last_failed_login_time nullable.

The same is true for email_validated. It can be stored as part of the row.

First, this kind of logging might be better done by the operating system as part of comprehensive security auditing.

From the point of view of database design, there's nothing wrong with storing total_failed and last_failed_login_time in the user table. But from the network administrator point of view there might be.

Failed logins can be an indicator that you've been targeted for cracking. As a network administrator, I'd rather monitor the number and distribution (number by time of day) of both successful and unsuccessful logins. To do that, they'd have to be stored in a separate table.

Instead of using summary columns for this data, you might want to implement a login attempt table instead.

You could create a table that tracked the user name, the date the login attempt was made, the status of that login attempt (Success, Password Incorrect, Unknown UserName, etc.), and any other pertinent information related to the attempt itself. Including the (attempted) user name would be necessary, since the user ID would be NULL for any attempts using unknown user names.

This structure would allow you to obtain the information you are asking for, as well as other security, auditing, and statistical reporting.