Counting failed attempt

Question

Here is my code:

create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(1),
isLocked number(1)
);

I want to count attempt column and if 5 then isLocked become 1 from 0; How can I make query, view or procedure?

Answer 1

A couple of notes,

Rather than track only failures, it's generally a good idea to know when people sign in, too. There is probably already a table that handles authentication tokens, but there's no reason why failed_login_attempts couldn't contain both successful and unsuccessful authentication attempts.

By having isLocked on a secondary table you leave yourself open to a bunch of crazy hack "solutions" to fix data inconsistencies when an account should be marked as locked or not ... particularly for users who regularly lose their passwords. Logically, if an account is "locked", then that should go on the Account (or User) table so that you're not always hitting a table that will quickly have a lot of data to determine whether a login can take place.

For example:

Account
  id
  name
  email
  ...
  is_locked
  updated_at

Then ...

LoginTXN
  id
  account_id
  success
  source_ip
  created_at

From here you can record all login attempts into LoginTXN (or whatever you decide to call it), mark the success field as 0 on failure, 1 on success (or N and Y), and go from there.

When checking to see how many login attempts have been made, you'll need to do something like:

SELECT COUNT(txn.id)
  FROM LoginTXN txn INNER JOIN (SELECT z.account_id, MAX(z.id) as recent_success
                                  FROM LoginTXN z 
                                 WHERE z.account_id = 999
                                 GROUP BY z.account_id) sok ON txn.account_id = sok.account_id
                                                           AND txn.id > sok.recent_success

Note: This is pseudo-code. Don't simply copy/paste it.

Which will then give you a count. If you want to have conditions like "5 failed attempts in the last hour", thereby locking the account for — at most — 60 minutes, you could do that with a WHERE statement that examines the created_at value in LoginTXN.

Of course, if this is being put into a view or a stored procedure, then an is_locked field on Account may be wholly unnecessary as the authentication rule could be encapsulated completely within the view or stored procedure.

Hope this gives you a couple of pointers that will lead you to a more complete solution.

Friendly Aside: Try to stick with a consistent naming scheme when making tables. If you prefer to use words separated by underscores, like is_locked, then stick with that. If you prefer camel-case, like isLocked, then stick with that. You — and every other developer who works on this code in the future — will appreciate a predictable consistency. As you can probably see from my examples above, I tend to go with camel-cased tables, and underscored columns.