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
Comments