Oracle Apps 11i/R12 provides standard Concurrent Manager request “Signon Audit Unsuccessful Logins” to report all failed login attempts for 11i/R12 but if your Oracle E-Business Suite (11i/R12) is integrated with Oracle Single Sign-On (where 11i/R12 authentication is happening at SSO) then you need to look in to SSO table.
Do not use “Signon Audit Unsuccessful Logins” for Apps integrated with SSO as this will list failed/unsuccessful Login attempts only for Local Login attempts.
.
How to audit/list failed/unsuccessful login attempts for Oracle Single Sign-On Server or Apps (11i/R12) integrated with SSO
Currently Oracle SSO (Single Sign-On) Server does not have any auditing capability. There is some information available in ias console page which lists failed login attempts in last 24 hours ( Click here for more )
All Single Sign-On attempts are recorded in SSO Audit tables and you can use SQL to build your report
orasso.wwsso_audit_log_table$
orasso.wwsso_audit_log_table_t
Q. How to find failed login attempts in last 7 days
SQL> select USER_NAME||’ – ‘||MESSAGE||’ – ‘||to_char(LOG_DATE,’dd.mm.yyyy hh24:mi:ss’)||’ – ‘||IP_ADDRESS “Login Failures”
from ORASSO.WWSSO_AUDIT_LOG_TABLE$
where log_date > (sysdate – 7)
and MESSAGE = ‘Login failed’
order by log_date;
Q. How to get a list of users last login date ?
SQL>select user_name, max(log_date) from orasso.wwsso_audit_log_table$ where message like ‘Login%’ group by user_name;
Q. List of users accounts where the passwords has been wrongly entered
SQL> select user_name, message, log_date from orasso.wwsso_audit_log_table$ where message like ‘Login failed’ ;
.
How to audit/list failed/unsuccessful login attempts for Standalone E-Business Suite 11i/R12 ?
If you are using standalone Oracle Apps 11i/R12 (No integration with Oracle SSO Server) then use standard request to report failed login attempts
Profile Option related to sign-on Auditing in 11i/R12:
a) Sign-On:Audit Level -> allows you to select a level at which to audit users who sign on to Oracle Applications.
You can set this profile option to None, User, Responsibility, and Form.
i) None means do not audit any users who sign on to Oracle Applications.
ii) Auditing at the User level tracks: who signs on to your system, the times users log on and off
iii) Auditing at the Responsibility level performs the User level audit functions + the responsibilities users choose + how much time users spend using each responsibility
iv) Auditing at the Form levelperforms the Responsibility level audit functions + the forms users choose + how long users spend using each form + System Administrator visible, updatable at all levels.
b) Sign-On:Notification Set this to Yes to enable Auditing
.
Available Concurrent Requests :
a) Signon Audit Unsuccessful Logins– Use this report to view who unsuccessfully attempted to sign on to Oracle Applications as another user. An unsuccessful login occurs when a user enters a correct username but an incorrect password. You can generate Signon Audit Unsuccessful Logins Reports for any users, regardless of whom you are auditing.
b) Signon Audit Users – Use this report to view who signs on and for how long
.
Table Involved :
a) FND_LOGINS
For more information visit Chapter 5 User and Data Auditing here
.
References
