Forum Stats

  • 3,840,371 Users
  • 2,262,594 Discussions
  • 7,901,249 Comments

Discussions

User account did not get locked out after three failed attempts

yoonas
yoonas Member Posts: 2,237 Gold Trophy
edited Feb 11, 2020 10:45AM in Database Security - General

Hi,

OS: Windows 2012 standard

db: 12.1.0.2.0

Can someone shed some light on the issue i am not able to figure out.

We have a profile set to lock account after three failed attempts

1.png

This profile is assigned to CM1 user

2.png

DBA_AUDIT_SESSION has  return code 1017 instead of 28000 after three failed attempts

3.png

Regards,

Yoonas

Answers

  • andrewmy
    andrewmy Member Posts: 676 Silver Badge
    edited Feb 7, 2020 12:36AM

    I suspect it did get locked, but then unlocked itself again. It is because of this setting

    PASSWORD_LOCK_TIME = 1  (Day)

    This means when an account is locked due to invalid login attempts, it will be opened again after one day. If you want it to remain locked until someone unlocks it, this value should be set to UNLIMITED

    Reference:

    https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#i2084338

  • yoonas
    yoonas Member Posts: 2,237 Gold Trophy
    edited Feb 7, 2020 2:04AM

    Hi Andewmy,

    Thanks for the reply

    What i am wondering here why the account did not get locked after three attempts.

    Please see records from DBA_AUDIT_SESSION extended timestamp which has 4 records with 1017 return code.

    after three attempts if user tries to logon with wrong credentials it should get locked and this table should have 28000 instead of 1017?

  • andrewmy
    andrewmy Member Posts: 676 Silver Badge
    edited Feb 7, 2020 2:20AM

    Do you have a record when those settings were applied to that user? Those audit records are more than month old.

    You can test this for yourself by creating a test id, assign the same profile, simulate the three failed logins, followed by a fourth failed login. All should be recorded in the audit log

  • yoonas
    yoonas Member Posts: 2,237 Gold Trophy
    edited Feb 7, 2020 2:42AM

    Auditing team has found this , now i need to give an explanation. This profile has been in place for quite a long time.

    pastedImage_1.png

    audit session records for the same user account shows how the account gets locked after three attempts.

  • andrewmy
    andrewmy Member Posts: 676 Silver Badge
    edited Feb 10, 2020 7:46PM

    Are you asking about the other entries e.g. 14-Oct which are not followed by a ORA-28000? These may be because of the PASSWORD_LOCK_TIME = 1 which automatically unlocks the account after one day and the user tried again and got another three attempts. The only one that is puzzling  is the 24-Dec set of entries where the user managed four attempts with wrong password.

    My advice is to fix the PASSWORD_LOCK_TIME and continue to monitor and also check the audit for any manual account unlocks.

  • yoonas
    yoonas Member Posts: 2,237 Gold Trophy
    edited Feb 11, 2020 10:45AM

    This is not an expected behaviour so i have an SR for this, i will update once we conclude.

    Thanks for your replies