If you ever need to see the failed login attempts on your Oracle XE instance, you can activate the audit_trail parameter.
My specific use case was for a project using Oracle SOA. WebLogic wouldn’t start because of a incorrect data-source configuration and the server output just wouldn’t spit out the username that was failing (I guess that could be a good thing from a security point of view).
In order to activate the audit trail, follow these steps:
- Connect to the database from SQL*Plus, or any database client, and run the show parameter audit_trail query. By default, the output should be NONE. You could even try to see if there are any entries in the dba_audit_trail table – you should get the no rows selected message.
- Set the audit_trail parameter to true by running this command: alter system set audit_trail=”true” scope=spfile;
- Shutdown and restart the database by using the shutdown immediate and startup commands:
- Check to see the audit_trail parameter is now set to TRUE by running the show parameter audit_trail command:
- Login with a bad username/password:
- Reconnect withe sys and check the audit trail by running the below query.
select username, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') from dba_audit_trail;
You should see the failed login attempt captured in the dba_audit_trail table.
