There have been a couple of questions about limiting login attempts, but none have really discussed the advantages or disadvantages or different ways of storing the record of login attempts (most have focused on the issue of throttling vs captchas, for instance, which is not what I'm interested in). Instead, I'm trying to figure out the best way to record this information in a database that would be useful but not impact performance too much.
Approach 1) Create a table that would record: ip address, username tried, and attempt time. Then when a user tries to login, I run a select to determine how many times a username was tried in the last 5 minutes (or how many times a particular ip address tried in the last 5 minutes). If over a certain amount, then either throttle the login attempts or display a captcha.
This allows throttling based both on username and on ip address (if the attack is trying many different usernames with a single password), and creates an easily auditable record of what happened, but requires an INSERT for every login attempt, and at least one extra SELECT.
Approach 2) Create a similar table that records the number of failed attempts and the last login time. This could result in a smaller database table and faster SELECT statements (no COUNT needed), but offers a little less control and also less data to analyze.
Approach 3) Similar to approach 2 but store this information in the User table itself. This means that an additional SELECT statement would not be needed, although it would add extra, perhaps unnecessary information to the users table. It also wouldn't allow the extra control and information that approach 1 would offer.
Additional approaches: store the login attempts in a mysql MEMORY table or a sqlite in-memory table. This would reduce performance loss due to disk performance, but still requires database calls, and wouldn't allow long term auditing of login attempts because the data wouldn't be persistent.
Any thoughts on a good way to do this or what you yourself have implemented?