EBS R12 -- EBS Security Config Checks

Following is a good set of control scripts derived from the Secure Configuration Guide for Oracle E-Business Suite Release 12..  

Using these scripts , we can verify the proper setting of the fundemantal EBS Security configurations.

According to the output of these script , we can determine the actions required to be taken in order have a secure EBS environment..

On the other hand, increasing the security of EBS is a big task , as it is classified into categories such as Oracle TNS Listener Security, Oracle Database Security, Oracle Application Tier Security, Oracle E-Business Suite Security, Desktop Security and Operating Environment Security..
These scripts are useful on the way to check the Oracle E-Business Suite Security , but using these scripts is not enough to check the EBS security as a whole , as you may expect ..

List unnecessary privs in the APPLSYSPUB account :

select grantor,privilege, table_name
  from  DBA_TAB_PRIVS
 where  grantee = 'APPLSYSPUB'
   and  privilege in ('SELECT','INSERT','EXECUTE','DELETE')
   and  Rtrim(privilege) || ' ON ' || table_name NOT IN
('INSERT ON FND_SESSIONS',
'INSERT ON FND_UNSUCCESSFUL_LOGINS',
'EXECUTE ON FND_DISCONNECTED',
'EXECUTE ON FND_MESSAGE',
'EXECUTE ON FND_PUB_MESSAGE',
'EXECUTE ON FND_SECURITY_PKG',
'EXECUTE ON FND_SIGNON',
'EXECUTE ON FND_WEBFILEPUB',
'SELECT ON FND_APPLICATION',
'SELECT ON FND_APPLICATION_TL',
'SELECT ON FND_APPLICATION_VL',
'SELECT ON FND_LANGUAGES_TL',
'SELECT ON FND_LANGUAGES_VL',
'SELECT ON FND_LOOKUPS',
'SELECT ON FND_PRODUCT_GROUPS',
'SELECT ON FND_PRODUCT_INSTALLATIONS',
'INSERT ON FND_SESSIONS#',
'INSERT ON FND_UNSUCCESSFUL_LOGINS#',
'SELECT ON FND_APPLICATION#',
'SELECT ON FND_APPLICATION_TL#',
'SELECT ON FND_PRODUCT_GROUPS#',
'SELECT ON FND_PRODUCT_INSTALLATIONS#',
'SELECT ON FND_LANGUAGES_TL#');


List database users with default passwords:

set serveroutput on
declare
  l_status varchar(200);
  l_name varchar(200);
begin
-- Check if DBA_USERS_WITH_DEFPWD exists
  SELECT 'view exists'
  into l_status
  FROM all_views
  WHERE VIEW_NAME='DBA_USERS_WITH_DEFPWD';

exception
  when no_data_found then --view doesn't exist
    l_status:='Default DB user password view does not exist (pre-11g DB). See Note 361482.1';
    dbms_output.put_line(l_status);
    dbms_output.put_line('The following select will fail with ORA-04043: object DBA_USERS_WITH_DEFPWD does not exist');
    return;
  when others then
    l_status:='Unexpected Error:'||sqlcode;
    dbms_output.put_line(l_status);
end;
/
select USERNAME, ACCOUNT_STATUS from DBA_USERS
 where USERNAME in ( select USERNAME from DBA_USERS_WITH_DEFPWD )
 order by 1
/

 Show whether credit cards are encrypted in the system:

set serveroutput on

declare

  l_release varchar(50);

  l_status varchar(200);

  l_enc_mode varchar(50);

begin

select release_name

  into l_release

  from fnd_product_groups

  where product_group_name='Default product group';

dbms_output.put_line('EBS level - '||l_release);

dbms_output.put_line('Credit Card Encryption Status');

dbms_output.put_line('-----------------------------');

if substr(l_release,1,7)='11.5.10' then

-- Check if function exists

begin

  SELECT 'function exists' 

  into l_status

  FROM all_procedures

  WHERE OBJECT_NAME='IBY_CC_SECURITY_PUB'

  and PROCEDURE_NAME='ENCRYPTION_ENABLED';

exception

  when no_data_found then --function doesn't exist

    l_status:='WARNING: 11i Encryption patch not installed';

    dbms_output.put_line(l_status);

    return;

  when others then

    raise;

end;

-- Returns a boolean so we have to do this as an anonymous PL/SQL.

execute immediate('begin 

if IBY_CC_SECURITY_PUB.encryption_enabled() then 

 dbms_output.put_line(''Encryption enabled'');

else

  dbms_output.put_line(''WARNING: Encryption not enabled'');

end if;

end;');

elsif substr(l_release,1,2)='12' then --R12

  execute immediate 'select decode(IBY_CREDITCARD_PKG.Get_CC_Encrypt_Mode(),''IMMEDIATE'',''Encryption enabled'',''WARNING: Encryption not enabled'') from dual'

  into l_status;

  dbms_output.put_line(l_status);

  begin --Check for Supplemental credit card data encryption

  dbms_output.put(CHR(10)); 

  dbms_output.put_line('Supplemental Credit Card Data Encryption Status');

  dbms_output.put_line('-----------------------------------------------');

  execute immediate 'select decode(IBY_CREDITCARD_PKG.OTHER_CC_ATTRIBS_ENCRYPTED,''Y'',''Supplemental credit card data encrypted'',''WARNING: Supplemental credit card data is not encrypted'') from dual'

  into l_status;

  dbms_output.put_line(l_status);

  exception

  when others then

  if sqlcode=-904 then --function doesn't exist

    l_status:='Supplemental credit card data supported only for 12.1.2 and above';

    dbms_output.put_line(l_status);

  else

    l_status:='Unexpected Error '||sqlcode;

    dbms_output.put_line(l_status); 

  end if;

  

  end;  --Check for Supplemental credit card data encryption

  

  begin --Check for Enhanced Hashing

  dbms_output.put(CHR(10)); 

  dbms_output.put_line('Enhanced Hashing');

  dbms_output.put_line('----------------');

  execute immediate 'select decode(IBY_SECURITY_PKG.Get_Salt_Version,3,''Enhanced Hashing on'',''WARNING: Enhanced Hashing is not on'') from dual'

  into l_status;

  dbms_output.put_line(l_status);

  exception

  when others then

  if sqlcode=-904 then --function doesn't exist

    l_status:='WARNING: Enhanced hashing function not installed';

    dbms_output.put_line(l_status);

  else

    raise; 

 end if;

  

  end;  --Check for Enhanced Hashing

  

else

  dbms_output.put_line('Unrecognized EBS level!');

end if; 

exception

  when others then

    l_status:='Unexpected Error:'||sqlcode;

    dbms_output.put_line(l_status);

end;

/

Check the status of Hashed Passwords for Application Users :

set serveroutput on
declare
l_status varchar2(50);

begin
dbms_output.put_line('Password Mode');
dbms_output.put_line('---------------------------------------');
execute immediate 'select decode(FND_WEB_SEC.GET_PWD_ENC_MODE,
null,''WARNING:Hashed passwords are not on'',
''Hashed passwords are on'') from dual'
into l_status;
dbms_output.put_line(l_status);
exception
when others then
if sqlcode=-904 then
l_status:='WARNING: Hashed password patch not installed';
else
l_status:='Unexpected Error';
end if;
dbms_output.put_line(l_status);
end;
/

List profiles set incorrectly (error) : 

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
REM -----------------------------------------------
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"
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 like 'F%_VALIDATION_LEVEL' and v.profile_option_value != 'ERROR') -- Validation profiles should be set to ERROR
or (p.profile_option_name = 'FND_SECURITY_FILETYPE_RESTRICT_DFLT' and v.profile_option_value not in ('N','Y')) -- Whitelist behavior (N) recommended, blacklist (Y) is default and should at minimum be on
or (p.profile_option_name = 'FND_DISABLE_ANTISAMY_FILTER' and v.profile_option_value != 'N') -- Antisamy checks should be enabled (N)
or (p.profile_option_name = 'FND_RESTRICT_INPUT' and v.profile_option_value != 'Y') -- Tag scanner should be enabled
or (p.profile_option_name = 'BNE_ALLOW_NO_SECURITY_RULE' and v.profile_option_value != 'N') -- Access to global integrators (integrators without a security rule) should be disabled (N)
)
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";

List site level profiles that are not found :

select rp.profile_name "Missing Site Profiles"

from fnd_profile_options p,

fnd_profile_option_values v,

(select 'FND_VALIDATION_LEVEL' profile_name from dual union

select 'FND_FUNCTION_VALIDATION_LEVEL' profile_name from dual union

select 'FRAMEWORK_VALIDATION_LEVEL' profile_name from dual union

select 'FND_SERVER_SEC' profile_name from dual union

select 'FND_SERVER_IP_SEC' profile_name from dual union

select 'BNE_ALLOW_NO_SECURITY_RULE' profile_name from dual union

--select 'FND_SECURITY_FILETYPE_RESTRICT_DFLT' profile_name from dual union

--select 'FND_DISABLE_ANTISAMY_FILTER' profile_name from dual union

select 'FND_RESTRICT_INPUT' profile_name from dual union

select 'UPLOAD_FILE_SIZE_LIMITS' profile_name from dual

) rp

where rp.profile_name = p.profile_option_name (+)

and p.profile_option_id = v.profile_option_id (+)

and v.level_id (+)=10001

and p.profile_option_name is null;

List profiles set incorrectly ( warning): 

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"

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 in ('FND_DIAGNOSTICS','DIAGNOSTICS','FND_CUSTOM_OA_DEFINTION') and v.profile_option_value != 'N') -- diagnstostics should not be generally enabled

     or (p.profile_option_name = 'FND_SECURITY_FILETYPE_RESTRICT_DFLT' and v.profile_option_value != 'N') -- Whitelist behavior (N) recommended

      )

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";

list users who have access to the  forms, pages, profile options, roles, and permissions listed in the tables in My Oracle Support Document :

REM ----------------------------------------------
REM Get Forms and HTML screens and functions
REM (driven by page path or jsp filename)
REM ----------------------------------------------

column user_name for a20 wrap
column resp_name for a32 wrap
column role_name for a40 wrap
column function_name for a20 wrap
column user_function_name for a35 wrap
column form_name for a10 wrap
column user_form_name for a30 wrap
column webcall for a40 wrap

prompt
prompt =========================================
prompt Forms via responsibilities
prompt =========================================


select distinct fu.user_name user_name,fr.responsibility_name resp_name,fff.function_name,fff.user_function_name, ff.form_name, ff.user_form_name
from fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_vl fr,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
fnd_form_vl ff
where fff.form_id=ff.form_id
and furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
and fr.menu_id = fcmf.menu_id
and fcmf.grant_flag = 'Y'
and fcmf.function_id = fff.function_id
and furg.user_id = fu.user_id
and sysdate between fu.start_date and nvl(fu.end_date, sysdate+1)
and sysdate between fr.start_date and nvl(fr.end_date, sysdate+1)
and fff.function_name in (
select fun.function_name
from fnd_form_functions_vl fun, fnd_form_vl form
where form.form_name in (
'ALRALERT',
'FNDATENT',
'FNDCPMCP',
'FNDCPMPE',
'FNDFFMDC',
'FNDFFMIS',
'FNDFFMVS',
'FNDFFIIF',
'FNDFFIDF',
'FNDFMFUN',
'FNDPOMPO',
'FNDPOMPV',
'FNDPRMPD',
'FNDSCAPP',
'FNDSCDDG',
'FNDSCMOU',
'GMAWFPCL',
'GMAWFCOL',
'PSBSTPTY',
'MSDCSDFN',
'MSDCSDFA',
'MSDAUDIT',
'JTFRSDGR',
'JTFBRWKB',
'JTFGANTT',
'JTFTKOBT',
'JTFGRDMD',
'JTFGDIAG',
'JTFBRWKB',
'FFXWSBQR',
'FFXWSMNG',
'OEXPCFVT',
'OEXDEFWK',
'PAYWSDAS',
'PAYWSDYG',
'WMSRULEF',
'QPXPRFOR',
'QPXPTMAP',
'QACHMDF',
'QAPLMDF',
'PERWSSCP')
and fun.form_id=form.form_id
)
order by 1,2
/

prompt
prompt =========================================
prompt Forms via grants
prompt =========================================


select distinct incrns.name user_name,wur.role_name, fff.function_name,fff.user_function_name, ff.form_name, ff.user_form_name
from fnd_grants fg,
wf_user_roles wur,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
wf_roles wr,
wf_users wu,
wf_users incrns,
fnd_form_vl ff
where fff.form_id=ff.form_id
and fg.menu_id = fcmf.menu_id
and fcmf.function_id = fff.function_id
and fg.grantee_type in ('USER', 'GROUP')
and fg.grantee_key = wur.role_name
and wur.role_name = wr.name
and wur.role_orig_system = wr.orig_system
and wur.role_orig_system_id = wr.orig_system_id
and wur.user_name = wu.name
and wur.user_orig_system = wu.orig_system
and wur.user_orig_system_id = wu.orig_system_id
and wu.parent_orig_system = incrns.parent_orig_system
and wu.parent_orig_system_id = incrns.parent_orig_system_id
and incrns.orig_system in ('FND_USR', 'PER')
and sysdate between fg.start_date and nvl(fg.end_date, sysdate+1)
and sysdate between nvl(wur.start_date, sysdate-1)
and nvl(wur.expiration_date, sysdate+1)
and sysdate between nvl(wr.start_date, sysdate-1)
and nvl(wr.expiration_date, sysdate+1)
and sysdate between nvl(wu.start_date, sysdate-1)
and nvl(wu.expiration_date, sysdate+1)
and sysdate between nvl(incrns.start_date, sysdate-1)
and nvl(incrns.expiration_date, sysdate+1)
and fff.function_name in
(
select fun.function_name
from fnd_form_functions_vl fun, fnd_form_vl form
where fun.form_id=form.form_id and
form.form_name in (
'ALRALERT',
'FNDATENT',
'FNDCPMCP',
'FNDCPMPE',
'FNDFFMDC',
'FNDFFMIS',
'FNDFFMVS',
'FNDFFIIF',
'FNDFFIDF',
'FNDFMFUN',
'FNDPOMPO',
'FNDPOMPV',
'FNDPRMPD',
'FNDSCAPP',
'FNDSCDDG',
'FNDSCMOU',
'GMAWFPCL',
'GMAWFCOL',
'PSBSTPTY',
'MSDCSDFN',
'MSDCSDFA',
'MSDAUDIT',
'JTFRSDGR',
'JTFBRWKB',
'JTFGANTT',
'JTFTKOBT',
'JTFGRDMD',
'JTFGDIAG',
'JTFBRWKB',
'FFXWSBQR',
'FFXWSMNG',
'OEXPCFVT',
'OEXDEFWK',
'PAYWSDAS',
'PAYWSDYG',
'WMSRULEF',
'QPXPRFOR',
'QPXPTMAP',
'QACHMDF',
'QAPLMDF',
'PERWSSCP')
)
order by 1,2
/


prompt
prompt =========================================
prompt HTML pages via responsibilities
prompt =========================================


select distinct fu.user_name user_name,fr.responsibility_name resp_name,fff.function_name,fff.user_function_name, fff.WEB_HTML_CALL webcall
from fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_vl fr,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff
where furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
and fr.menu_id = fcmf.menu_id
and fcmf.grant_flag = 'Y'
and fcmf.function_id = fff.function_id
and furg.user_id = fu.user_id
and sysdate between fu.start_date and nvl(fu.end_date, sysdate+1)
and sysdate between fr.start_date and nvl(fr.end_date, sysdate+1)
and fff.function_name in (
select fun.function_name
from fnd_form_functions_vl fun
where
fun.WEB_HTML_CALL like '%/oracle/apps/ame/dashboard/webui/BusinessDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ame/admin/webui/AdminDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/content/webui/LOEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/adapter/webui/CmiAdapterEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/DataSrcLOVDefnPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/ListCustomSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/QueryTemplParamSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileDefinePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileSummaryPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileUpdatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webuiFunctionCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionCreateDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionSearchPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionUpdatePG%' or
fun.WEB_HTML_CALL like '%ibuaugsp.jsp%' or
fun.WEB_HTML_CALL like '%ibuhacnt.jsp%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjFindMainPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjInsetUpdatePG%' or
fun.WEB_HTML_CALL like '%jsp/fnd/fndhelputil.jsp%' or
fun.WEB_HTML_CALL like '%ieuwplist.jsp%' or
fun.WEB_HTML_CALL like '%jtffmlqq.jsp%' or
fun.WEB_HTML_CALL like '%jtffmltm.jsp%' or
fun.WEB_HTML_CALL like '%weboam/oam/diagnostics/createTest%')
order by 1,2
/


prompt
prompt =========================================
prompt HTML pages via grants
prompt =========================================
select distinct incrns.name user_name,wur.role_name, fff.function_name,fff.user_function_name, fff.WEB_HTML_CALL webcall
from fnd_grants fg,
wf_user_roles wur,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
wf_roles wr,
wf_users wu,
wf_users incrns
where fg.menu_id = fcmf.menu_id
and fcmf.function_id = fff.function_id
and fg.grantee_type in ('USER', 'GROUP')
and fg.grantee_key = wur.role_name
and wur.role_name = wr.name
and wur.role_orig_system = wr.orig_system
and wur.role_orig_system_id = wr.orig_system_id
and wur.user_name = wu.name
and wur.user_orig_system = wu.orig_system
and wur.user_orig_system_id = wu.orig_system_id
and wu.parent_orig_system = incrns.parent_orig_system
and wu.parent_orig_system_id = incrns.parent_orig_system_id
and incrns.orig_system in ('FND_USR', 'PER')
and sysdate between fg.start_date and nvl(fg.end_date, sysdate+1)
and sysdate between nvl(wur.start_date, sysdate-1)
and nvl(wur.expiration_date, sysdate+1)
and sysdate between nvl(wr.start_date, sysdate-1)
and nvl(wr.expiration_date, sysdate+1)
and sysdate between nvl(wu.start_date, sysdate-1)
and nvl(wu.expiration_date, sysdate+1)
and sysdate between nvl(incrns.start_date, sysdate-1)
and nvl(incrns.expiration_date, sysdate+1)
and fff.function_name in
(
select fun.function_name
from fnd_form_functions_vl fun
where
fun.WEB_HTML_CALL like '%/oracle/apps/ame/dashboard/webui/BusinessDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ame/admin/webui/AdminDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/content/webui/LOEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/adapter/webui/CmiAdapterEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/DataSrcLOVDefnPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/ListCustomSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/QueryTemplParamSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileDefinePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileSummaryPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileUpdatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionCreateDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionSearchPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionUpdatePG%' or
fun.WEB_HTML_CALL like '%ibuaugsp.jsp%' or
fun.WEB_HTML_CALL like '%ibuhacnt.jsp%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjFindMainPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjInsetUpdatePG%' or
fun.WEB_HTML_CALL like '%jsp/fnd/fndhelputil.jsp%' or
fun.WEB_HTML_CALL like '%ieuwplist.jsp%' or
fun.WEB_HTML_CALL like '%jtffmlqq.jsp%' or
fun.WEB_HTML_CALL like '%jtffmltm.jsp%' or
fun.WEB_HTML_CALL like '%weboam/oam/diagnostics/createTest%'
)
order by 1,2
/




prompt
prompt =========================================
prompt Users with JTF Roles and Permissions
prompt =========================================


REM ----------------------------------------------------------------------
REM Users with JTF_SYSTEM_ADMIN_ROLE Role
REM ----------------------------------------------------------------------


select PRINCIPAL_NAME "JTF_SYSTEM_ADMIN_ROLE Users" from JTF_AUTH_PRINCIPALS_B
where JTF_AUTH_PRINCIPAL_ID in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID
from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_PRINCIPALS_B jtfpb
where jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfpb.JTF_AUTH_PRINCIPAL_ID
and jtfpb.PRINCIPAL_NAME='JTF_SYSTEM_ADMIN_ROLE')
/


REM ----------------------------------------------------------------------
REM Users with JTF_FM_ADMIN Role
REM ----------------------------------------------------------------------


select PRINCIPAL_NAME "JTF_FM_ADMIN Role Users" from JTF_AUTH_PRINCIPALS_B
where JTF_AUTH_PRINCIPAL_ID in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID
from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_PRINCIPALS_B jtfpb
where jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfpb.JTF_AUTH_PRINCIPAL_ID
and jtfpb.PRINCIPAL_NAME='JTF_FM_ADMIN')
/


REM ----------------------------------------------------------------------
REM Users with CS_Assoc_Ext_Obj_To_Sol Permission
REM ----------------------------------------------------------------------


SELECT PRINCIPAL_NAME "CS_Assoc_Ext_Obj_To_Sol Users" from JTF_AUTH_PRINCIPALS_B where JTF_AUTH_PRINCIPAL_ID
in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_ROLE_PERMS jtfarp, JTF_AUTH_PERMISSIONS_B jtfperb
where
jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfarp.JTF_AUTH_PRINCIPAL_ID and
jtfarp.JTF_AUTH_PERMISSION_ID=jtfperb.JTF_AUTH_PERMISSION_ID and
jtfperb.PERMISSION_NAME ='CS_Assoc_Ext_Obj_To_Sol'
)
/


REM ----------------------------------------------------------------------
REM Users with JTF_ADMIN_PERM Permission
REM ----------------------------------------------------------------------


SELECT PRINCIPAL_NAME "JTF_ADMIN_PERM Users" from JTF_AUTH_PRINCIPALS_B where JTF_AUTH_PRINCIPAL_ID
in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_ROLE_PERMS jtfarp, JTF_AUTH_PERMISSIONS_B jtfperb
where
jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfarp.JTF_AUTH_PRINCIPAL_ID and
jtfarp.JTF_AUTH_PERMISSION_ID=jtfperb.JTF_AUTH_PERMISSION_ID and
jtfperb.PERMISSION_NAME ='JTF_ADMIN_PERM'
)
/

 Check to see if "Server Security" is enabled:

set serveroutput on
declare
l_status varchar2(30);
begin
dbms_output.put_line('Server Security Status');
dbms_output.put_line('-----------------------------');

select 'Server Security is on'
into l_status
from FND_NODES
where server_address = '*'
and server_id='SECURE';

dbms_output.put_line(l_status);

exception
when no_data_found then --server security is off
l_status := 'ERROR:Server Security is off';
dbms_output.put_line(l_status);
when others then
raise;
end;
/

Show if EBS has been configured for HTTPS:

select decode(UPPER(SUBSTR(FND_WEB_CONFIG.PROTOCOL,1,5)),
'HTTPS','SSL/TLS is enabled',
'WARNING: SSL/TLS is not enabled') "SSL Mode"
from dual;

List the default (seeded) applications users that still have their default passwords:

REM You should change all the default passwords, even if the user is "END_DATEd".  

REM Note that this will not list shipped accounts that cannot be logged into 

col "Apps Users - Default Passwords" format a50
select USER_NAME "Apps Users - Default Passwords" from (
select fnd_web_sec.validate_login('AME_INVALID_APPROVER','WELCOME') R, 'AME_INVALID_APPROVER' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('ANONYMOUS','welcome') R, 'ANONYMOUS' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('APPSMGR','C') R, 'APPSMGR' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('ASGADM','ASGADM') R, 'ASGADM' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('ASGADM','welcome') R, 'ASGADM' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('ASGUEST','welcome') R, 'ASGUEST' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('AUTOINSTALL','DATAMERGE') R, 'AUTOINSTALL' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('GUEST','ORACLE') R, 'GUEST' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('IBEGUEST','IBEGUEST2000') R, 'IBEGUEST' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('IBE_ADMIN','MANAGER') R, 'IBE_ADMIN' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('IBE_GUEST','WELCOME') R, 'IBE_GUEST' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('IEXADMIN','COLLECTIONS') R, 'IEXADMIN' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('IRC_EMP_GUEST','WELCOME') R, 'IRC_EMP_GUEST' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('IRC_EXT_GUEST','WELCOME') R, 'IRC_EXT_GUEST' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('MOBADM','C') R, 'MOBADM' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('MOBDEV','C') R, 'MOBDEV' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('MOBILEADM','MOBILEADM') R, 'MOBILEADM' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('MOBILEADM','welcome') R, 'MOBILEADM' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('OP_CUST_CARE_ADMIN','OP_CUST_CARE_ADMIN') R, 'OP_CUST_CARE_ADMIN' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('OP_SYSADMIN','OP_SYSADMIN') R, 'OP_SYSADMIN' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('PORTAL30','PORTAL30') R, 'PORTAL30' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('PORTAL30','portal30_new') R, 'PORTAL30' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('PORTAL30_SSO','portal30_sso_new') R, 'PORTAL30_SSO' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN') R, 'SYSADMIN' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('WIZARD','????UE:?H0UA}?K') R, 'WIZARD' USER_NAME from dual
union ALL 

select fnd_web_sec.validate_login('XML_USER','WELCOME') R, 'XML_USER' USER_NAME from dual
) where R='Y'
order by 1
/