to stop unlocking an account from if failed attempts is more than 5 within 24hrs

[kamabotti khan]

hi

I am trying to stop from unlocking an account for a domain if the user has made attempts more than 5 times within 24hrs.I have added the check for five times taking the count from the table where failure or success log is stored but I wnt an additional condition that is ,if the five attempts is within period of 24hrs

final String CHECK_UNLOCK_ATTEMPTS = "SELECT count(*) FROM test WHERE user_name = '"
+ userName
+ "' AND domain_name = '"
+ domainName
+ "' "
+ "and log_time <= SYSDATE - 1";

// check if the session user has reached more than 5 attempts of unlock
SqlRowSet rs_check_unlock_attempts = jt.queryForRowSet(CHECK_UNLOCK_ATTEMPTS);
int unlock_attempts = 0;

while (rs_check_unlock_attempts.next())
{
unlock_attempts = rs_check_unlock_attempts.getInt(1);
}

if (unlock_attempts > 5)-------------here checking for greater than 5 attempts but have to check for if within 24hrs
model.put("errorStr", "You have reached Maxium Attempts of unlock");

}

Please help me on how to do this.


Thanks

[Jilesh Lakhani]

This isn't Java Question, however since you trying this using SQL.

I assume you are using MSSQL as your DB.
The query can be something like this
SELECT count(1)
FROM <Table Name>
WHERE <your other conditions>
AND log_time >= DATEADD(hh, SYSDATE, -24 /*This is your condition to get the records in past 24 hrs you can also add the Group by clause and remove count option to see if all returns login failed..*/

[kamabotti khan]

I am Using ORACLE as my db


Thanks

[Winston Gutkowski]

kamabotti khan wrote:I am trying to stop from unlocking an account for a domain if the user has made attempts more than 5 times within 24hrs.

Being an old DBA, I'd point out that this could actually be done entirely through the database by keeping an AccessLog table whose key is Table + Datetime and has a single boolean value Successful (in fact Oracle may have something of that sort already in its meta-tables; you might want to check).

Then you could do the whole thing with a basic SELECT statement.

Winston