My database account keeps getting locked out!

(This example is specific to Oracle, but this can happen with any database, well, probably anything with a password and password expiration.)

This seems to come up every two to three years at work and I thought it would be worth posting for anyone who hasn’t encountered it.

The customer complains that his password keeps getting locked out or that it just doesn’t work regularly. It often seems to happen randomly at any time of day. Unfortunately the customer also uses built-in “forgot my password” functionality because he needs to get the work done – thus whenever you check the database the account status is open. You also checked and the customer says they haven’t given their password out to anyone. You request that the customer please try to contact you and wait for you to examine things next time it happens. At this point you should also check your listener log (or other connection log).

When it happens again and you get to look, you see this:

SQL> select username, account_status, profile from dba_users where username like 'IAM%';

USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------ 
IAMLOCKED                      LOCKED(TIMED)                    COMPANY_STD

SQL> select * from dba_profiles where profile = 'COMPANY_STD' order by 1,2;
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------------------------
...
COMPANY_STD                    PASSWORD_GRACE_TIME              PASSWORD 5
COMPANY_STD                    PASSWORD_LIFE_TIME               PASSWORD 60
COMPANY_STD                    PASSWORD_LOCK_TIME               PASSWORD 1
COMPANY_STD                    PASSWORD_REUSE_MAX               PASSWORD DEFAULT
COMPANY_STD                    PASSWORD_REUSE_TIME              PASSWORD DEFAULT
COMPANY_STD                    PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G
...
16 rows selected.

SQL> alter user IAMLOCKED account unlock;
User altered.
SQL> select username, account_status, profile from dba_users where username like 'IAM%';
USERNAME                       ACCOUNT_STATUS                   PROFILE
------------------------------ -------------------------------- ------------------------------
IAMLOCKED                      OPEN                             COMPANY_STD

Based on this you know that the customer’s account is being locked out on multiple incorrect password attempts – LOCKED(TIMED).

This is where your listener log (or other log with connections for non-Oracle) comes in handy. Note, you can always check this right away and chances are you’ll see something of value.

tail -100f listener.log | grep -100f IAMLOCKED
19-APR-2013 09:40:35 * (CONNECT_DATA=(SERVICE_NAME=MYDB)(CID=(PROGRAM=C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE)(HOST=COCMCDTSEC2185)(USER=IAMLOCKED))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.31.190.188)(PORT=1417)) * establish * MYDB * 0
19-APR-2013 09:40:35 * (CONNECT_DATA=(SERVICE_NAME=MYDB)(CID=(PROGRAM=C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE)(HOST=COCMCDTSEC2185)(USER=IAMLOCKED))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.31.190.188)(PORT=1418)) * establish * MYDB * 0
19-APR-2013 09:40:47 * (CONNECT_DATA=(SERVICE_NAME=MYDB)(CID=(PROGRAM=C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE)(HOST=COCMCDTSEC2185)(USER=IAMLOCKED))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.31.190.188)(PORT=1419)) * establish * MYDB * 0
19-APR-2013 09:40:47 * (CONNECT_DATA=(SERVICE_NAME=MYDB)(CID=(PROGRAM=C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE)(HOST=COCMCDTSEC2185)(USER=IAMLOCKED))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.31.190.188)(PORT=1420)) * establish * MYDB * 0
19-APR-2013 10:16:55 * (CONNECT_DATA=(SERVICE_NAME=MYDB)(CID=(PROGRAM=C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE)(HOST=COCMCDTSEC2185)(USER=IAMLOCKED))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.31.190.188)(PORT=1543)) * establish * MYDB * 0
19-APR-2013 10:17:49 * (CONNECT_DATA=(SERVICE_NAME=MYDB)(CID=(PROGRAM=C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE)(HOST=COCMCDTSEC2185)(USER=IAMLOCKED))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.31.190.188)(PORT=1546)) * establish * MYDB * 0

At times there are many logon attempts from the same IP very close together.

As this point you know that someone is making multiple connections attempts. The question is finding out who it is. For all you know it could be someone trying to hack into the system through their account, or in this case, the user has a tool that they use where they have saved their password. Forgetting this, when they try to use it, or when it runs an automatic task the tool times the account out because it is using the wrong password.

In this case the customer had a Microsoft Access database with database links to the Oracle database. The password had been saved in the link. I pointed this out to the customer and also suggested that the tool request the username and password each time someone uses it (of course, their own username and password hopefully.)

3 responses to “My database account keeps getting locked out!

  1. Pingback: My database account keeps getting locked out! | Jed's

  2. Pallavi Priya

    Thank you for the information. It helps.

  3. Thanx for the info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s