Query to Find oracle EBS failed login attempts

Before moving directly into query for oracle EBS failed login attempts and oracle ebs user login history, lets find out how to enable signon audit in EBS to collect this data and other important stuff

Introduction to EBS Signon Auditing

Oracle EBS has lot of auditing features to check the information of user login to the system,unsuccessful login attempts, Responsibility usage ,form usage and many more.

Auditing is enabled through the profile Sign-On:Audit Level
Here are the various settings possible

i) None : It means do not audit any users who sign on to Oracle Applications.
ii) User :it means who signs on to your system, the times users log on and off
iii) Responsibility: It means the User level audit functions + the responsibilities users choose + how much time users spend using each responsibility
iv) Form : It means performs the Responsibility level audit functions + the forms users choose + how long users spend using each form + form accesses to APPLSYS.FND_LOGINS, APPLSYS.
FND_LOGIN_RESPONSIBILITIES and APPLSYS. FND_LOGIN_RESP_FORMS, respectively

It is recommended to to set this profile to Form

We can check the auditing level in the system by checking the Profile option

set linesize 140
set pagesize 132
col "PROFILE NAME" format A30
col "Update Time" format A22
col VALUE format a35;
col USER format a30;
col LEVEL format a6;
select
n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001,'Site',10002,'Application',10003,'Responsibility',10004,'User',10005,'Server',10006,'Organization','UnDef') "LEVEL",
v.profile_option_value VALUE,
to_char(v.LAST_UPDATE_DATE,'DD-MON-YYYY:hh24:mi:ss') "Update Time",
fu.user_name "USER"
from apps.fnd_profile_options p,
apps.fnd_profile_option_values v,
apps.fnd_profile_options_tl n,
apps.fnd_user fu
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
AND v.last_updated_by=fu.user_id
and upper(n.user_profile_option_name) like upper('%SIGNONAUDIT:LEVEL%')
and v.level_id= 10001;

Here is Oracle Support script to check for Recommended Auditing in EBS

column "Internal Name" format A23
column "Profile Name" format A30
column "Profile Level" format a15
column "Profile Context" format a25
column "Value" format A10 wrap
column "Last Updated" format a12
select p.profile_option_name "Internal name",
n.user_profile_option_name "Profile name",
to_char(v.last_update_date,'DD-MON-RR') "Last Updated",
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') "Profile Level",
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "Profile Context",
v.profile_option_value "Value",
decode(p.profile_option_name,
'AFLOG_ENABLED','Debug Logging should be On at Site level',
'AFLOG_LEVEL','Logging should be set to at least Unexpected at Site level',
'SIGNONAUDIT:LEVEL','Sign-on audit should be set to Forms at Site level',
'') "Recommendation"
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and n.language = 'US'
and ((p.profile_option_name = 'AFLOG_ENABLED' and v.level_id=10001 and v.profile_option_value !='Y') -- Recommend that logging is set to Unexpected at Site level
or (p.profile_option_name = 'AFLOG_LEVEL' and v.level_id=10001 and v.profile_option_value > 6) -- Recommend that logging is set to at least Unexpected at Site level
or (p.profile_option_name = 'SIGNONAUDIT:LEVEL' and v.level_id=10001 and v.profile_option_value !='D') -- Recommend that sign-on audit be set to Forms at Site level
)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by p.profile_option_name, "Profile Level";

select 'audit_trail be set to DB or FILE' "Recommendation"
from v$parameter
where name ='audit_trail' and value='NONE';
select 'audit_sys_operations should be set to TRUE' "Recommendation"
from v$parameter
where name ='audit_sys_operations' and value != 'TRUE';

Retrieve Signon Audit Records Using Reports

EBS comes with many  standard reports to access signon, unsuccessful signon, responsibility usage, form usage and concurrent request usage. Access these reports through the system administrator responsibility.

The Unsuccessful login report can be generated through concurrent request also

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.

Here are the some report names which comes with EBS
Signon Audit Concurrent Requests
Signon Audit Forms
Signon Audit Responsibilities
Signon Audit Unsuccessful Logins
Signon Audit Users

Retrieve Signon Audit Records Using SQL

The system stores end-user access data in the following tables. Develop SQL scripts to query these tables to generate reports

APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITIES
APPLSYS.FND_LOGIN_RESP_FORMS
APPLSYS.FND_UNSUCCESSFUL_LOGINS
FND_CONCURRENT_REQUESTS
ICX.ICX_FAILURES

If you have set up auditing in Oracle E-Business Suite then you can monitor the unsuccessful logins from the applsys.FND_UNSUCCESSFUL_LOGINS table.For example, the unsuccessful logins for the last week( oracle ebs failed login attempts)

SELECT B.USER_NAME, U.ATTEMPT_TIME
FROM applsys.FND_UNSUCCESSFUL_LOGINS U, applsys.FND_USER B
WHERE U.USER_ID = B.USER_ID
--AND B.USER_NAME LIKE NVL(:USER_NAME, '%')
AND TRUNC(U.ATTEMPT_TIME) > TRUNC(SYSDATE-7)
ORDER BY U.ATTEMPT_TIME;

Important Note

• The system automatically stores unsuccessful logon attempts in the APPLSYS.FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES tables.
• The ICX_FAILURES table holds more information than the FND_UNSUCCESSFUL_LOGINS. Both the FND_UNSUCCESSFUL_LOGINS and ICX_FAILURES tables contain unsuccessful logins via the Personal Home Page (Self Service/Web Interface).
• Failed Forms logins are logged only to the FND_UNSUCCESSFUL_LOGINS table. This functionality cannot be disabled.

Here is interesting Script From Oracle Support

The below Query showing unsuccessful logins for local users in E-Business Suite(oracle EBS failed login attempts)

set pagesize 1000
set linesize 200

select u.user_name,
ful.user_id,
to_char(attempt_time,'DD-MON-RRRR HH24:MI:SS') attempt_time
from fnd_unsuccessful_logins ful,
fnd_user u
where ful.user_id = u.user_id (+)
order by attempt_time;

Below query can be used to find oracle ebs user login history for particular user

SELECT b.user_name username,
ROUND((a.last_connect-a.first_connect) * 24 * 60,2) minutes ,
TO_CHAR(a.first_connect, 'DD-MON-YYYY') Firstconnect 
FROM apps.icx_sessions a,
apps.fnd_user b
WHERE a.user_id =b.user_id
AND b.user_name ='&username'
AND a.first_connect BETWEEN to_date('&sincewhen','DD-MON-YYYY') AND sysdate;

Below query can be used to generate oracle ebs user login history for all users

SELECT b.user_name username,
ROUND((a.last_connect-a.first_connect) * 24 * 60,2) minutes ,
TO_CHAR(a.first_connect, 'DD-MON-YYYY') Firstconnect 
FROM apps.icx_sessions a,
apps.fnd_user b
WHERE a.user_id =b.user_id
AND b.user_name NOT IN ('GUEST','SYSADMIN')
AND a.first_connect BETWEEN to_date('&1','DD-MON-YYYY') AND sysdate;

Purge signon audit data

It is recommended to regularly purge these table data using the Purge Signon Audit Data concurrent program. The current program purges all audit records older than a user supplied date. Run this concurrent program between once a week and once a month, retaining 30 to 90 days of records. This concurrent program purges the following tables:
FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS

Periodically archive and truncate the FND_LOGIN% tables

Hope you like this detail on signon audit details in EBS, oracle ebs failed login attempts query,oracle EBS user login history,how to purge signon audit data

Related Articles
oracle apps queries for DBA : This page contains the very useful and practical Top 30 Useful oracle apps queries for APPS DBA to help in day to day administration activities
Profile options in Oracle Apps : Check out this page for a deep dive on Profile options in Oracle Apps,How to change it using FND_PROFILE API ,direct query
JAVA/JRE in Oracle Apps : Frequently used questions for JAVA/JRE in Oracle Apps. Where are java are installed EBS,what all versions are supported
How to Reload APPS Java Class Objects in R11i/R12
Audit trail in Oracle applications
How To Check The E-Business Suite User Login Times Before The “Signon:Audit Level” Profile Option Was Enabled (Doc ID 1364259.1)