Finding Debug Enabled on Profiles at DB level and Validating Font End user password in Oracle Apps

Finding Trace Enabled on Concurrent Program at DB level

We can use the below query to find the trace enabled at DB level.

 Select user_concurrent_program_name

 from applsys.fnd_concurrent_programs fp,

 applsys.fnd_concurrent_programs_tl fct

 where fp.concurrent_program_id = fct.concurrent_program_id

 and enable_trace <> 'N';

 

 

Validating Font End user password in Oracle Apps

If we need to validate the front end user password is working fine or not then we can fire the below query to check the authentication of password.

Login to Database as Apps user.

SQL> select fnd_web_sec.validate_login('GUEST','welcome') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','WELCOME')
--------------------------------------------------------------------------------
Y

 

 

 

Finding Debug Enabled on Profiles at DB level

Below query can be useful for finding the Debug Enabled on Profiles at Site,Application,Responsibilty and User Level.

select fpot.user_profile_option_name profile_name
, 'Site' lo
, 'SITE' lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10001 /* Site Level */
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'Apps' lo
, fa.application_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_APPLICATION_TL fa
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10002 /* Application Level */
and fpov.level_value = fa.application_id
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'Resp' lo
, frt.responsibility_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_RESPONSIBILITY_TL frt
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and frt.language = Userenv('Lang')
and fpot.language = Userenv('Lang')
and fpov.level_id = 10003 /* Responsibility Level */
and fpov.level_value = frt.responsibility_id
and fpov.level_value_application_id = frt.application_id
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'User' lo
, fu2.user_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_USER fu2
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10004 /* User Level */
and fpov.level_value = fu2.user_id
and fpot.language = Userenv('Lang')
and fpo.end_date_active is null
order by profile_name, lo, lov;

 

 

Thanks 

Srini