Finding out who is connecting database with wrong password

DB: Oracle RAC
OS: Solaris SPARC 10u11 64bit

Question: Recently we modified our application user’s password. We must guarantee all application programs are using the new password because the account will be locked after several failed login attempts due to security regulations. We need to find out who is using invalid password.

Answer: Fortunately our auditing policy is ‘Audit create session whenever not successful’ and audit_trail is set to OS. Therefore our auditing files are located in audit_file_dest directory, namely $ORACLE_BASE/admin/adump. My program is based on the audit files.

ORA-01017: invalid username/password; logon denied
ORA-28000: the account is locked

ls -ltr | egrep "Jun 5|Jun 4 2" | awk '{print $NF}' \
| xargs -i grep HOST= {} | grep -v 28000 | awk '{print $23}' | grep -v grid | \
awk -F= '{print $4}' | sed 's/)(PORT//g' | sort | uniq -c | sort -k1n

The logic is we identify failed login period, namely between June 4 20 PM and Jun 5. Then we rule out ORA-28000 errors and pick the IP addresses that we need and order them in terms of failed login attempts.


Leave a Reply

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

You are commenting using your 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