I am SYS

Recently on one of my development databases, my username kept getting locked out and I didn’t know what was doing it. This would happen every hour or so, and soon became far too annoying to just login to the database as sys and unlock my account.

The first step to identifying where these failed login attempts were coming from is to turn on Oracle database auditing. Make sure the audit_trail database parameter is set. In its simplest form set AUDIT_TRAIL=DB. There are other auditing options that can be used, and can be researched at Oracle’s documentation website (tahiti.oracle.com).

Next, turn on auditing on the CREATE SESSION action. This can be set for all users or for specific users. I chose to just audit my username since it was the one in question.

SQL> audit create session by michael;

Now, every attempt to create a session by the user michael will be audited and placed in the sys.aud$ table. This table can hold a lot more auditing information than just sessions, and because of this reason, it can be very hard to query for a specific set of records. When looking at audited sessions, it is easier to use the DBA_AUDIT_SESSION view which gives you just the information necessary for researching sessions.

To find out where my failed login attempts were coming from, I ran the sql query below to see all of my login attempts, failed or successful.

select os_username, username, userhost, timestamp, returncode
from dba_audit_session
where username = 'MICHAEL'
order by timestamp;

os_username, username, userhost, timestamp, returncode
oracle    MICHAEL    db08    26-AUG-09 10.07.23    28000
oracle    MICHAEL    db08    26-AUG-09 10.12.23    28000
oracle    MICHAEL    db08    26-AUG-09 10.17.23    1017

Any records that have a returncode > 0 are failed login attempts. I can see in the first two audited records that the returncode of 28000 is for a locked account. At that point, after 10.12, I altered my user account and unlocked it. The next failed login attempt that came in got a returncode of 1017, which is an invalid password error. Until I decided to fix this problem, at this point after I unlocked my account, there would be 10 records with a returncode of 1017, then all subsequent attempts would be 28000. This is because my user profile has the FAILED_LOGIN_ATTEMPTS set to 10.

Note here that the userhost of ‘db08′ is the database host in this scenario. This fact was strange to me because I knew that I had no processes of any kind on the database host that would access the database with my username, or so I thought. This database was recently refreshed from a production instance, so I knew my password had been recently changed, and was the reason for the failed logins, so I decided to change my password back to the old password to see if the failed login attempts stopped. At this point, I saw a sucessful connection to the database from the same userhost, as evidenced by the returncode of 0.

oracle    MICHAEL    db08    26-AUG-09 10.22.23    0

I then queried v$session for my username and found the session that was established. From there, I determined by looking at the program and module columns the session was coming from the process “emagent@db08 (TNS V1-V3)”. The emagent process is used by grid control to access the database for various reasons. I knew that this wasn’t me logging into the database through OEM though, because the userhost is the host for the OEM instance, and the os_username is not recorded as evidenced by the below record.

(null)    MICHAEL    dbps07    26-AUG-09 11.03.48    0

So what was emagent doing with my old username and password every five minutes? I determined that oem was attempting to run a user defined metric that I created with my old password.

Takeaways:

1. I got to thinking why the failure of the user defined metric wasn’t more evident to me, which is because the user defined metric was for business specific rules that check business data to make sure things are going “normal”. But this metric is always in a critical state in our development database, because the “normal” business doesn’t take place and isn’t being replicated in any way. In our production database user defined metrics, we use a special database login that does not have any password expiration rules so this will not become an issue in other environments. Now I am off to change our development user defined metrics to use that login instead of mine.
2. If you see failed login attempts coming from your database host, and it isn’t immediately evident what is causing them, check your OEM for user defined metrics that may be running against the database.