Search

Personalization and Diagnostics links in EBS 12.1.3



In Oracle Applications there are several options allowing developers to change application behavior without altering code. This can be done by using “Personalizations” and “Diagnostics” links on framework pages as well as forms-based applications:


Framework page:

Forms-based application:

Appearance and behavior of “Personalization” and “Diagnostics” links in R12 is controlled by system profiles and security functions using Role-Based Access Control


1. “Personalize page” link


Framework personalization links are controlled by 3 profiles:


Personalize Self-Service Defn

Set the value to Yes to allow “Personalize page” link to appear at the right top of the OAF page.

FND: Personalization Region Link Enabled

Set the value to Yes to display all the personalization links above each OAF page region.

Set the value to Minimal to display key regional links.

Disable Self-Service Personal

Set the value to Yes will disable OAF personalizations


For security reasons the profile "Personalize Self-Service Defn" should be set to “N” in Production environment.


During the environment clone the activation of the “Personalize page” link to all users having “N” in the profile "Personalize Self-Service Defn" is achieved by the following code:

(The script will set “Y” in the profile at the user level to all those users in low environments)


declare

m_stat boolean;

begin

for prf in (select v.level_value user_id

from fnd_profile_options n,

fnd_profile_option_values v

where n.profile_option_name = 'FND_CUSTOM_OA_DEFINTION'

and n.profile_option_id = v.profile_option_id

and v.level_id = 10004

and v.profile_option_value = 'N')

loop

m_stat := fnd_profile.save('FND_CUSTOM_OA_DEFINTION','Y','USER', prf.user_id);

end loop;

end;


Additionally, here is the query that can be used to report all cases where the profile "Personalize Self-Service Defn" has value “Y”:


select r.level_name,

r.level_actual_value,

to_char(v.last_update_date,'DD/MM/YYYY HH24:MI') last_update_date,

f.user_name updated_by,

v.level_value_application_id,

z.level_actual_value2

from fnd_profile_options n,

fnd_profile_option_values v,

fnd_profile_options_tl t,

fnd_user f,

(select 10001 level_id, 'Site' level_name, 0 level_value, null level_actual_value

from dual

union

select 10002 level_id, 'Application' level_name, application_id level_value, application_name level_actual_value

from fnd_application_tl

where language = 'US'

union

select 10003 level_id, 'Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10004 level_id, 'User' level_name, user_id level_value, user_name level_actual_value

from fnd_user

union

select 10005 level_id, 'Server' level_name, node_id level_value, node_name level_actual_value

from fnd_nodes

union

select 10006 level_id, 'Organization' level_name, organization_id level_value, name level_actual_value

from hr_operating_units

union

select 10007 level_id, 'Server-Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10007 level_id, 'Server-Responsibility' level_name, -1 level_value, '' level_actual_value

from dual) r,

(select 10007 level_id, node_id level_value2, node_name level_actual_value2

from fnd_nodes) z

where n.profile_option_id = v.profile_option_id

and t.profile_option_name = n.profile_option_name

and r.level_value = v.level_value

and r.level_id = v.level_id

and z.level_value2(+) = v.level_value2

and z.level_id(+) = v.level_id

and (z.level_actual_value2 is not null or r.level_actual_value is not null or r.level_name = 'Site')

and v.last_updated_by = f.user_id

and t.language = 'US'

and n.profile_option_name = 'FND_CUSTOM_OA_DEFINTION'

and v.profile_option_value = 'Y'

order by 1,3,4;


For security reasons the profile "FND: Personalization Region Link Enabled" should be set to “N” in Production environment.


Here is the query that can be used to report all cases where the profile "FND: Personalization Region Link Enabled" has value “Y” or “MIN”:


select r.level_name,

r.level_actual_value,

to_char(v.last_update_date,'DD/MM/YYYY HH24:MI') last_update_date,

f.user_name updated_by,

v.level_value_application_id,

z.level_actual_value2

from fnd_profile_options n,

fnd_profile_option_values v,

fnd_profile_options_tl t,

fnd_user f,

(select 10001 level_id, 'Site' level_name, 0 level_value, null level_actual_value

from dual

union

select 10002 level_id, 'Application' level_name, application_id level_value, application_name level_actual_value

from fnd_application_tl

where language = 'US'

union

select 10003 level_id, 'Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10004 level_id, 'User' level_name, user_id level_value, user_name level_actual_value

from fnd_user

union

select 10005 level_id, 'Server' level_name, node_id level_value, node_name level_actual_value

from fnd_nodes

union

select 10006 level_id, 'Organization' level_name, organization_id level_value, name level_actual_value

from hr_operating_units

union

select 10007 level_id, 'Server-Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10007 level_id, 'Server-Responsibility' level_name, -1 level_value, '' level_actual_value

from dual) r,

(select 10007 level_id, node_id level_value2, node_name level_actual_value2

from fnd_nodes) z

where n.profile_option_id = v.profile_option_id

and t.profile_option_name = n.profile_option_name

and r.level_value = v.level_value

and r.level_id = v.level_id

and z.level_value2(+) = v.level_value2

and z.level_id(+) = v.level_id

and (z.level_actual_value2 is not null or r.level_actual_value is not null or r.level_name = 'Site')

and v.last_updated_by = f.user_id

and t.language = 'US'

and n.profile_option_name = 'FND_PERSONALIZATION_REGION_LINK_ENABLED'

and v.profile_option_value in ('Y','MIN')

order by 1,3,4;


2. “Diagnostics” link


To enable “Diagnostics” link on the framework pages you must set the "FND: Diagnostics" profile value to “Y”


For security reasons the profile "FND: Diagnostics" should be set to “N” in Production environment.


During the environment clone the activation of the “Diagnostics” link to all users having “N” in the profile "FND: Diagnostics" is achieved by the following code:

(The script will set “Y” in the profile at the user level to all those users in low environments)


declare

m_stat boolean;

begin

for prf in (select v.level_value user_id

from fnd_profile_options n,

fnd_profile_option_values v

where n.profile_option_name = 'FND_DIAGNOSTICS'

and n.profile_option_id = v.profile_option_id

and v.level_id = 10004

and v.profile_option_value = 'N')

loop

m_stat := fnd_profile.save('FND_DIAGNOSTICS','Y','USER', prf.user_id);

end loop;

end;



Additionally, here is the query you can use to report all cases where the profile "FND: Diagnostics" has value “Y”:


select r.level_name,

r.level_actual_value,

to_char(v.last_update_date,'DD/MM/YYYY HH24:MI') last_update_date,

f.user_name updated_by,

v.level_value_application_id,

z.level_actual_value2

from fnd_profile_options n,

fnd_profile_option_values v,

fnd_profile_options_tl t,

fnd_user f,

(select 10001 level_id, 'Site' level_name, 0 level_value, null level_actual_value

from dual

union

select 10002 level_id, 'Application' level_name, application_id level_value, application_name level_actual_value

from fnd_application_tl

where language = 'US'

union

select 10003 level_id, 'Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10004 level_id, 'User' level_name, user_id level_value, user_name level_actual_value

from fnd_user

union

select 10005 level_id, 'Server' level_name, node_id level_value, node_name level_actual_value

from fnd_nodes

union

select 10006 level_id, 'Organization' level_name, organization_id level_value, name level_actual_value

from hr_operating_units

union

select 10007 level_id, 'Server-Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10007 level_id, 'Server-Responsibility' level_name, -1 level_value, '' level_actual_value

from dual) r,

(select 10007 level_id, node_id level_value2, node_name level_actual_value2

from fnd_nodes) z

where n.profile_option_id = v.profile_option_id

and t.profile_option_name = n.profile_option_name

and r.level_value = v.level_value

and r.level_id = v.level_id

and z.level_value2(+) = v.level_value2

and z.level_id(+) = v.level_id

and (z.level_actual_value2 is not null or r.level_actual_value is not null or r.level_name = 'Site')

and v.last_updated_by = f.user_id

and t.language = 'US'

and n.profile_option_name = 'FND_DIAGNOSTICS'

and v.profile_option_value = 'Y'

order by 1,3,4;


3. Forms-based Applications Diagnostics Menu


There are 2 profiles that control the Help à Diagnostics menu (with Examine, Trace, Debug, Properties, and Custom Code sub-menus) behavior:


"Hide Diagnostics menu entry" - If this profile is set to Yes, the Diagnostics menu is hidden from the user


"Utilities:Diagnostics" determines whether a user can automatically use the Diagnostics submenu items

If set to “Y”, then users can automatically use Diagnostics sub-menu items

If set to “N”, then role based security is checked


Here is the query to determine the value of the "Hide Diagnostics menu entry" profile:


select r.level_name,

r.level_actual_value,

v.profile_option_value,

to_char(v.last_update_date,'DD/MM/YYYY HH24:MI') last_update_date,

f.user_name updated_by,

v.level_value_application_id,

z.level_actual_value2

from fnd_profile_options n,

fnd_profile_option_values v,

fnd_profile_options_tl t,

fnd_user f,

(select 10001 level_id, 'Site' level_name, 0 level_value, null level_actual_value

from dual

union

select 10002 level_id, 'Application' level_name, application_id level_value, application_name level_actual_value

from fnd_application_tl

where language = 'US'

union

select 10003 level_id, 'Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10004 level_id, 'User' level_name, user_id level_value, user_name level_actual_value

from fnd_user

union

select 10005 level_id, 'Server' level_name, node_id level_value, node_name level_actual_value

from fnd_nodes

union

select 10006 level_id, 'Organization' level_name, organization_id level_value, name level_actual_value

from hr_operating_units

union

select 10007 level_id, 'Server-Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10007 level_id, 'Server-Responsibility' level_name, -1 level_value, '' level_actual_value

from dual) r,

(select 10007 level_id, node_id level_value2, node_name level_actual_value2

from fnd_nodes) z

where n.profile_option_id = v.profile_option_id

and t.profile_option_name = n.profile_option_name

and r.level_value = v.level_value

and r.level_id = v.level_id

and z.level_value2(+) = v.level_value2

and z.level_id(+) = v.level_id

and (z.level_actual_value2 is not null or r.level_actual_value is not null or r.level_name = 'Site')

and v.last_updated_by = f.user_id

and t.language = 'US'

and n.profile_option_name = 'FND_HIDE_DIAGNOSTICS'

order by 1,3,4;


In general, it should have “N” value at the site level.


Here is the query to determine the value of the "Utilities:Diagnostics" profile:


select r.level_name,

r.level_actual_value,

v.profile_option_value,

to_char(v.last_update_date,'DD/MM/YYYY HH24:MI') last_update_date,

f.user_name updated_by,

v.level_value_application_id,

z.level_actual_value2

from fnd_profile_options n,

fnd_profile_option_values v,

fnd_profile_options_tl t,

fnd_user f,

(select 10001 level_id, 'Site' level_name, 0 level_value, null level_actual_value

from dual

union

select 10002 level_id, 'Application' level_name, application_id level_value, application_name level_actual_value

from fnd_application_tl

where language = 'US'

union

select 10003 level_id, 'Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10004 level_id, 'User' level_name, user_id level_value, user_name level_actual_value

from fnd_user

union

select 10005 level_id, 'Server' level_name, node_id level_value, node_name level_actual_value

from fnd_nodes

union

select 10006 level_id, 'Organization' level_name, organization_id level_value, name level_actual_value

from hr_operating_units

union

select 10007 level_id, 'Server-Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10007 level_id, 'Server-Responsibility' level_name, -1 level_value, '' level_actual_value

from dual) r,

(select 10007 level_id, node_id level_value2, node_name level_actual_value2

from fnd_nodes) z

where n.profile_option_id = v.profile_option_id

and t.profile_option_name = n.profile_option_name

and r.level_value = v.level_value

and r.level_id = v.level_id

and z.level_value2(+) = v.level_value2

and z.level_id(+) = v.level_id

and (z.level_actual_value2 is not null or r.level_actual_value is not null or r.level_name = 'Site')

and v.last_updated_by = f.user_id

and t.language = 'US'

and n.profile_option_name = 'DIAGNOSTICS'

order by 1,3,4;


In Production it should have “N” at site and all other levels otherwise users will have unrestricted access to the Diagnostics menu items.


Here is the query that can be used to check that no user has “Y” in the "Utilities:Diagnostics" profile at the user level:


select r.level_name,

r.level_actual_value,

to_char(v.last_update_date,'DD/MM/YYYY HH24:MI') last_update_date,

f.user_name updated_by,

v.level_value_application_id,

z.level_actual_value2

from fnd_profile_options n,

fnd_profile_option_values v,

fnd_profile_options_tl t,

fnd_user f,

(select 10001 level_id, 'Site' level_name, 0 level_value, null level_actual_value

from dual

union

select 10002 level_id, 'Application' level_name, application_id level_value, application_name level_actual_value

from fnd_application_tl

where language = 'US'

union

select 10003 level_id, 'Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10004 level_id, 'User' level_name, user_id level_value, user_name level_actual_value

from fnd_user

union

select 10005 level_id, 'Server' level_name, node_id level_value, node_name level_actual_value

from fnd_nodes

union

select 10006 level_id, 'Organization' level_name, organization_id level_value, name level_actual_value

from hr_operating_units

union

select 10007 level_id, 'Server-Responsibility' level_name, responsibility_id level_value, responsibility_name level_actual_value

from fnd_responsibility_tl

where language = 'US'

union

select 10007 level_id, 'Server-Responsibility' level_name, -1 level_value, '' level_actual_value

from dual) r,

(select 10007 level_id, node_id level_value2, node_name level_actual_value2

from fnd_nodes) z

where n.profile_option_id = v.profile_option_id

and t.profile_option_name = n.profile_option_name

and r.level_value = v.level_value

and r.level_id = v.level_id

and z.level_value2(+) = v.level_value2

and z.level_id(+) = v.level_id

and (z.level_actual_value2 is not null or r.level_actual_value is not null or r.level_name = 'Site')

and v.last_updated_by = f.user_id

and t.language = 'US'

and n.profile_option_name = 'DIAGNOSTICS'

and v.profile_option_value != 'N'

order by 1,3,4;


4. Role-Based Access Control


Permissions, Permissions sets and Grants can be created using “Functional Administrator” responsibility


Roles and role assignments can be created using “User Management” responsibility



If the profile "Utilities:Diagnostics" is set to “N” then the access to forms-based applications “Diagnostics” menu is controlled by the security functions using Role-Based Access Control


Here is a query that returns list of existing permissions and permission sets controlling the access to the “Diagnostics” menu items


SELECT distinct m.user_menu_name permission_set, f.user_function_name permission

FROM fnd_form_functions_tl f,

fnd_compiled_menu_functions mf,

fnd_menus_tl m

where f.user_function_name like 'FND Diagnostics%'

and f.language = 'US'

and f.function_id = mf.function_id

and mf.menu_id = m.menu_id

and m.language = 'US'

order by 1,2



Here is the query that lists all users with assigned “'FND Diagnostics%” permission sets:


SELECT distinct m.user_menu_name, g.grantee_key, a.user_name

FROM fnd_form_functions_tl f,

fnd_compiled_menu_functions mf,

fnd_menus_tl m,

fnd_grants g,

wf_user_role_assignments a

where f.user_function_name like 'FND Diagnostics%'

and f.language = 'US'

and f.function_id = mf.function_id

and mf.menu_id = m.menu_id

and m.language = 'US'

and m.menu_id = g.menu_id

and sysdate between g.start_date and nvl(g.end_date, sysdate+1)

and g.grantee_key = a.role_name(+)

order by 1,2,3


There are permissions, for example FND Diagnostics Custom, FND Diagnostics Menu Examine, FND Diagnostics Personalize, FND Diagnostics Values, giving to the users unlimited read/write access to the Diagnostics menu items.


The following query can be used to get a list of roles having those permissions:


SELECT distinct g.grantee_key role

FROM fnd_menus_tl m,

fnd_grants g

where m.user_menu_name in (

'FND Diagnostics Support',

'FND Diagnostics Custom Menu',

'FND Diagnostics Examine Menu',

'FND Diagnostics Menu Developer',

'FND Diagnostics Menu Support',

'FND Diagnostics Personalizations Menu',

'FND Diagnostics Personalize',

'FND Diagnostics Properties Menu')

and m.language = 'US'

and m.menu_id = g.menu_id

and sysdate between g.start_date and nvl(g.end_date, sysdate+1)

order by 1


The following query can be used to get a list of users having those permissions assigned to them through roles:


SELECT distinct g.grantee_key role, a.user_name, a.start_date

FROM fnd_menus_tl m,

fnd_grants g,

wf_user_role_assignments a

where m.user_menu_name in (

'FND Diagnostics Support',

'FND Diagnostics Custom Menu',

'FND Diagnostics Examine Menu',

'FND Diagnostics Menu Developer',

'FND Diagnostics Menu Support',

'FND Diagnostics Personalizations Menu',

'FND Diagnostics Personalize',

'FND Diagnostics Properties Menu')

and m.language = 'US'

and m.menu_id = g.menu_id

and sysdate between g.start_date and nvl(g.end_date, sysdate+1)

and g.grantee_key = a.role_name(+)

and sysdate between a.start_date and nvl(a.end_date, sysdate+1)

order by 1,2


To revoke the “Diagnostics menu” read/write roles assigned to the users you can run the following PL/SQL block:


begin


for r in (SELECT distinct g.grantee_key role, a.user_name, a.start_date

FROM fnd_menus_tl m,

fnd_grants g,

wf_user_role_assignments a

where m.user_menu_name in (

'FND Diagnostics Support',

'FND Diagnostics Custom Menu',

'FND Diagnostics Examine Menu',

'FND Diagnostics Menu Developer',

'FND Diagnostics Menu Support',

'FND Diagnostics Personalizations Menu',

'FND Diagnostics Personalize',

'FND Diagnostics Properties Menu')

and m.language = 'US'

and m.menu_id = g.menu_id

and sysdate between g.start_date and nvl(g.end_date, sysdate+1)

and g.grantee_key = a.role_name(+)

and sysdate between a.start_date and nvl(a.end_date, sysdate+1))

loop

wf_local_synch.PropagateUserRole(

p_user_name => r.user_name,

p_role_name => r.role,

p_start_date => r.start_date,

p_expiration_date => sysdate);

end loop;


commit;


end;


It sets end date to the role assignments.


5. Recommended settings



2 views0 comments

Recent Posts

See All

After logon trigger - trace user sessions

-- Activate trace for sessions using after logon database trigger -- -- Tables: -- -- SYS.XX_LOGIN_TRACE_CONDITION - add here the conditions for activating trace -- SYS.XX_LOGIN_TRACED_SESSIONS - con

INCLUDE parameter of expdp

-- Export all public database links expdp user/password full=y \ include=db_link:"in (select db_link from dba_db_links where owner = 'PUBLIC')" -- Export one table expdp user/password include=table:"i

Multiplexing Redo Log and Control files

-- List redo log files select group#, member from v$logfile order by group#, member; -- Check redo log file status select group#, members, status from v$log; -- Adding redo log file to a group alter d