[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

databse_accountsLock

i need to lock a few ad hoc database accounts after 3 failed logins within 15 mins. After 15 mins the accounts gets automatically unlcoked.

Since oracle dose not seem to have a parameter for this, is it possible to schedule a job that monitors the locked accounts using a lock timestamp and unlock them automatically using DDL.

is this a security hole?
0
sam15
Asked:
sam15
  • 14
  • 11
  • 6
  • +1
3 Solutions
 
sdstuberCommented:
I'll answer them in reverse order - yes it's a security hole.  A determined hacker simply needs to wait 15 minutes to get another try.
It's a discouraging wall but it's not an absolute barrier.
0
 
sdstuberCommented:
to track failed logons, turn auditing on.

audit session whenever not successful ;

make sure your init parameter audit_trail  contains DB, that way the audits will be visible in dba_audit_trail


to find users that have failed to logon 3 times in the past 15 minutes use this...

SELECT username, COUNT(*)
  FROM dba_audit_trail
 WHERE action_name = 'LOGON' AND returncode != 0 AND timestamp > SYSDATE - 15 / 1440
GROUP BY username;

then loop through them

ALTER USER the_user_name ACCOUNT LOCK;
0
 
sdstuberCommented:
to find accounts locked for more than 15 minutes...

select username from dba_users where account_status  = 'LOCKED' and lock_date > sysdate - 15/1440;


then loop through those and

ALTER USER the_user_name ACCOUNT UNLOCK
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
slightwv (䄆 Netminder) Commented:
For this to work you would need the job to fire off every minute or some accounts could be locked more than 15 minutes and you would fail to lock some and they could get a LOT more than 3 attempts.

Is that a hard requirement or do you have some room to change it?

I would use user profiles to lock the account after 3 tries regardless of the time period.

Then come up with an acceptable unlock 'window' say, 15-30 minutes.  Then you can run the unlock job every 15 minutes using the SQL above.

Why the 15-30?

at 00:00 the unlock job runs
at 00:01 I lock my account
at 00:15 the unlock job runs (my account has only been locked 14 minutes)
at 00:30 the unlock job runs and catches my account
0
 
johnsoneSenior Oracle DBACommented:
Can't this be done with setting parameters in a profile?

Setting FAILED_LOGIN_ATTEMPTS to 3 and PASSWORD_LOCK_TIME to 0.0104 should do what they want.

I know FAILED_LOGIN_ATTEMPTS will work, but I never set PASSWORD_LOCK_TIME to that small a number.  You would have to try it out.  It is in number of days, but that fraction should be about 15 minutes.
0
 
sdstuberCommented:
it won't  handle both lock and unlock timing windows.

I agree you could do part of it with profiles but, since you'll need some sort of monitoring job anyway, I think it's easiest, from a maintenance standpoint to put all the logic in one process
0
 
johnsoneSenior Oracle DBACommented:
I missed the timing window on the lock in the original question.  The profile wouldn't be able to handle that.
0
 
sam15Author Commented:
Here are the NIST requirements. I may have mixed the unlock issue

1) " Implement technical control so that system must lock the user account if 3 failed passowrd attempts occur within 15-minute period for user passwords".
2) " Implement technical control so that system must automatically lock the account for at least 15 minutes when the maximum number of unsuccessful log in attpemts has been exceede".

using profile and FAILED_LOGIN_ATTEMPTS to 3 and PASSWORD_LOCK_TIME=0.014 will lock the account for 15 minutes but is not checking if the number of attempts were within 15 minutes. so if 1st attempt was 1:00 pm  and 2nd attempt was 1:30 pm, and 3rd attempt is 2:00 pm it will lock it.

http://www.oracle-base.com/articles/misc/BasicSecurityMeasuresForOracle.php

if we look at sdstuber job, we have to run this every minute. but if user logs in 10 times withing 50 seconds it still would not work. You have to run it every second which is not practical.

and then I need another job that unlock account if user tries after 15 mins. It does not sound there is a practical way for doing this.

to find users that have failed to logon 3 times in the past 15 minutes use this...

SELECT username, COUNT(*)
  FROM dba_audit_trail
 WHERE action_name = 'LOGON' AND returncode != 0 AND timestamp > SYSDATE - 15 / 1440
GROUP BY username;

then loop through them

ALTER USER the_user_name ACCOUNT LOCK;
0
 
sdstuberCommented:
>> but if user logs in 10 times withing 50 seconds it still would not work.

so,  despite my attempt to put everything into one job,  looks like you'll have to use profile too

put a limit of 3 in the profile  AND use the query above to also do checks for failed attempts
0
 
slightwv (䄆 Netminder) Commented:
At this point I might create my own user_login tracking table and a system logon trigger.
0
 
sdstuberCommented:
that won't work.


logon triggers are "AFTER LOGON"

which never happens if you fail to log on
0
 
slightwv (䄆 Netminder) Commented:
>>that won't work.

ah yes.  crap...

>>AND use the query above to also do checks for failed attempts

But won't the job have to pretty much run constantly to reduce the 'timing window' issue?

NIST is pretty specific in their requirements.

I'm honestly not sure how to pull this off.

Do you have Oracle Support?  You might need to ask them how to meet this requirement.
0
 
johnsoneSenior Oracle DBACommented:
You can use a system trigger.  I has to be on a SERVERERROR trigger.

There is an example here:

http://psoug.org/reference/system_trigger.html



What if there are 2 logon failures, 1 successful login, then 1 additional failed login within 15 minutes?  Would that cause the account to be locked?
0
 
slightwv (䄆 Netminder) Commented:
>>Would that cause the account to be locked?

I believe the NIST standard is consecutive failures.  At least the parts I had to meet are.  I just don't have the 15 minute constraint.
0
 
sdstuberCommented:
>>> But won't the job have to pretty much run constantly to reduce the 'timing window' issue?

no

if you have a profile that locks after 3 attempts, then it doesn't matter how slow or how fast the locks happen, it will lock the account.

however, if the 3 failures happen slowly (16 minutes or more) you don't want the profile lock to stick around,  so your job comes through an unlocks it.
you can do that every minute.  The reason I want the lock query to remain though is because if they keep trying every few seconds.  You'll have hundreds of failures in 15 minutes.

The profile will lock after the first 3.  But you want to make sure the lock stays in place because they continued to fail within the past 15 minutes so you don't want to open it back up just because it failed a while ago.
The only time you ever unlock is when the user becomes idle for a little while after failing (they gave up)

0
 
sdstuberCommented:
you still want the job to run fairly frequently though.  Probably once per minute. So you stay on top of both locking and unlocking.
0
 
slightwv (䄆 Netminder) Commented:
>>you still want the job to run fairly frequently though.

That is what I was getting at.  If the job runs every 15 minutes, you can have up to a 29 minute window and I'm still not sure the 'lock' will happen correctly:

00:00 failure1
00:01 failure2
00:25 failure3

Account should not lock.
0
 
sdstuberCommented:
yes

 is once per minute a problem?

these should be lightweight queries unless there are many users with many failures so I don't expect it to hiccup anybody's system
0
 
slightwv (䄆 Netminder) Commented:
>> is once per minute a problem?

In the above example adding two attempts in a single minute:

00:00:00 failure1
00:01:00 failure2
00:25:00 failure3
00:25:30 success

with the profile, the account is locked.
0
 
sdstuberCommented:
yes, for about about 1 minute, then the job unlocks it

I thought I mentioned that already,  sorry,  on reviewing looks like I left that out.

THat's actually why I wanted everything in one job to keep track of
0
 
slightwv (䄆 Netminder) Commented:
If the servererror trigger captures failed logins, I'm back to a custom user_lock table to track everything.

I just don't have the time to test it.
0
 
sdstuberCommented:
how does a user_lock table get you more than logon audit records?

you're still doing the same basic query, just changing table/column names

I don't see the difference.  
0
 
slightwv (䄆 Netminder) Commented:
You don't have to worry about turning on auditing, purging records, etc...

Granted, if must meet NIST login requirements, they likely already have auditing turned on.

But if not:

You would only have a single row per user.

I'm thinking single table, two columns: user, failed_attempts.

The after login trigger would reset the counter.  The servererror trigger would increment/lock the account.
0
 
sdstuberCommented:
now that I see your design,  ok

I'll write it up for you  :)
0
 
sam15Author Commented:
i want to make sure i understood your propsed solution. there are 2 options to meets the NIST reqts:

1) using standard auditing and DBA_AUDIT_TRAIL and a profile and a job that runs every 1 min to unlock accounts that hve been locked over 15 mins.

2) using two triggers and a custom table to track falied logins. the triggers are afterlogon and on system error.

Am i correct?
0
 
johnsoneSenior Oracle DBACommented:
Personally, this would be my approach:

Trigger on system error with a custom table to track login failures and lock the account if there are 3 failures in the last 15 minutes.

Trigger on after logon to delete the records from the custom table for the user.

Profile that unlocks the accounts after 15 minutes.


The table would have 2 columns, user and failed login time.
0
 
sam15Author Commented:
woudl the system error fire up on user failed login? Does not get fired for otehr events too that may not be due to a failed login?

YOur steps also do not seem to need a job that runs every minute to lock/unlock accounts. correct?
it seems a trigger will lock account whenever needed and the profile will unlock it.
0
 
sdstuberCommented:
it would get fired for any error, but inside the trigger you can check which error and ignore the rest
0
 
slightwv (䄆 Netminder) Commented:
>>correct?

Correct.
0
 
sdstuberCommented:
with option 2 - you still need the job in option 1 to unlock the accounts after they have been idle for 15 minutes
0
 
johnsoneSenior Oracle DBACommented:
As stated, the error trigger would fire for all errors.  You would just have to pick the few that pertain to what you are looking for.  The link I posted previously has an example of a failed login trigger, it logs to a table, so you have to modify it to do exactly what you need, but it is a good place to start.

With the method I outlined, you wouldn't need a job outside the database.  However you would need to know where all the pieces are for maintenance.  There are 2 triggers and a profile setting that need to be maintained.

Also, you need to qualify if the SYS and SYSTEM accounts are included in the locking of accounts.  If you locked out those accounts you could have issues.
0
 
sam15Author Commented:
<<with option 2 - you still need the job in option 1 to unlock the accounts after they have been idle for 15 minutes >>

I thought the profile parameter "PASSWORD_LOCK_TIME=15" will take care of that automatically.
0
 
slightwv (䄆 Netminder) Commented:
>><<with option 2 - you still need the job in option 1 to unlock the accounts after they have been idle for 15 minutes >>

This comment was actually for http:#a37053617 not http:#a37057035.

>>will take care of that automatically.

it will.
0
 
sam15Author Commented:
Are system error triggers different than regular trigger because i need to run ALTER USER DDL.

Normally triggers do not allow COMMIT and the calling routine has to do it.

-- trigger to trap unsuccessful logons
CREATE OR REPLACE TRIGGER logon_failures
AFTER SERVERERROR
ON DATABASE

BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO connection_audit
    (login_date, user_name)
    VALUES
    (SYSDATE, 'ORA-1017');
  END IF;
END logon_failures;
0
 
slightwv (䄆 Netminder) Commented:
The docs are always your friend:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25519/triggers.htm#CHDHFBBG

AFTER SERVERERROR

Starts separate transaction and commits it after firing triggers.
0
 
sam15Author Commented:
Truly excellent answer.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 14
  • 11
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now