Audit Create Session Logins
I had an interesting security breach the other day where a developer who had sql access to the production environment gave out his access to basically “everybody”. There was a huge panic and at the end of the day, I found a tip from Burleson at dba-oracle.com that showed that you can easily turn on auditing for session creation and it gives a wonder account of the people that are failing to login because of the changed credentials.
First you need to set the audit parameters on and bounce the system if it isn’t.
from SQL*Plus: audit create session whenever not successful;
Now when you query: Select * from dba_audit_trail; you will get a listing of the folks that have failed to create a session because of incorrect credentials.