Implementing Login Speed Checks
I need to perform a login speed test for a web service. The service is in ruby and the database is MySql.
I can imagine bad ways to do this. For example, you have a table that stores every time a login attempt was made and whether or not it was successful, and every time a user tries to login, it queries that table for the last login attempts and runs with it some simple algorithm. However, this appears to be clearly ineffective. Each login attempt makes a selection and insertion on a rather large table, which will slow down the system as a whole.
Better to say the value n is hardcoded as three or whatever, and in the user table (where the password verifiers are stored) add n columns that contain the last n login attempts. This removes the extra select statement and the user table appears to be much shorter than the login attempts table. However, it loses a lot of data, which could be interesting if the algorithm had to change.
At this point, I'm leaning towards a variation on this structure, which puts one text field in a table using password verifiers, and that text field contains a serialized object that is an array of login attempts records. When you try to log in, this field will be parsed and overwritten. This solves the problem of fixed n while avoiding the need to query a very large table. However, reading text fields from the database certainly has poor disk access characteristics, which could ultimately make it a bad decision as well.
Finally, another possibility is to use a database protected by MySQL MySql files (whose name I don’t know), but I know almost nothing about it, except that it should be efficient when querying log files.
My question is for: How are login speeds usually checked?
Update 1:
I had to determine the speed of the check. The speed check for login attempts tracks both the number of consecutive failures and the time frame in which successive failures occurred. The first answer points to a solution with these properties. I wonder, however, if it stores enough information to provide flexibility in speed testing. I have never built a system like this, my concern is that I miss the important aspects of speed checking that I would like to consider when I started building it ...
a source to share
In the end I built something similar to what John Bocker suggested. The table used to authenticate users has two new columns - failed_login_count
and first_failed_login
, which are DateTime objects. Every time the user successfully logs in, it failed_login_count
is reset to 0, and first_failed_login
is reset to null
if they haven't already. Each time a failed login attempt occurs, the value is failed_login_count
incremented. If it was 0, first_failed_login
gets the current time. Every time the user tries to log in, the speed check occurs before the password is checked. If failed_login_count
greater than 0, it is divided by the time difference between the current time and first_failed_login
. If this value is greater than the maximum allowed speed, the speed check is not performed.
Also, I am aggressively logging failed login attempts and failed speed checks for future offline processing so that I can track account connection attempts and disable accounts that have too many failed login attempts without having to track down the details required for these calculations in the database itself.
a source to share
I believe in the default asp.net membership provider, there is a column in one of the custom tables called FailedPasswordAttemptCount and it is sure that it just increments each failed attempt and then resets to 0 on success. With this column and the LastLoginAttemptTimestamp column, you can block users for a specific time.
a source to share
I can imagine bad ways to do this. For For example, there is a table that stores each time of login attempts and whether or not it was successful and every time the user tries to login, query this table for the last login n tries and runs some simple algorithm against this. This seems pretty straightforward but ineffective. every login attempt makes a choice and stick on a rather large table that will slow down the system as a whole.
If you know what the maximum speed is, you can easily clear out many entries in the failed login table on a regular basis (the daily amount will be sufficient if you don't expect tens of thousands of failed logins a day).
It would be a good idea to consider how many logins and bad logins you expect to have per day.
a source to share