How to find the unsuccessful login to application in last week

Last updated on July 28th, 2018 at 02:30 pm

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 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 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.

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

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;

 

Purge Audit Records
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

Leave a Reply