As it happened to us, we went live on Oracle Fusion Finance and eventually storm passed. Till one day we got a love letter from Oracle telling us that we have granted module access to more users than we have license for. So wasted many man hours in trying to identify the user and their access to modules. Eventually we stumbled upon an Oracle document having the query to identify it. Unfortunately now i have the document number but may be the query itself will save many hours to developers. Of course, credit goes to Oracle. Below is the query
SELECT distinct vl2.description service
, u.user_login user_login
, pn.last_name
, pn.first_name
FROM fusion.ase_user_role_mbr ur
, FND_VS_VALUE_SETS vs
, FND_VS_VALUES_B v
, FND_VS_VALUES_TL vl
, FND_VS_VALUE_SETS vs2
, FND_VS_VALUES_B v2
, FND_VS_VALUES_TL vl2, fusion.ase_user_b u
, fusion.ase_role_b r
, fusion.PER_USERS pu
, fusion.PER_PERSON_NAMES_F_V pn
, ( —
— Derived Table to construct flattened role hierarchy
—
SELECT CONNECT_BY_ROOT parent_role_code top_role_code
, CONNECT_BY_ROOT parent_role_name top_role_name
, CONNECT_BY_ROOT parent_role_id top_role_id
, CONNECT_BY_ROOT parent_role_guid top_role_guid
, child_role_code role_code
, child_role_name role_name
, child_role_id role_id
, child_role_guid role_guid
FROM ( —
— Derived Table to resolve surrogate keys in building parent-child role relationships before flattening
—
SELECT pr.code parent_role_code
, pr.role_name parent_role_name
, pr.role_id parent_role_id
, pr.guid parent_role_guid
, cr.code child_role_code
, cr.role_name child_role_name
, cr.role_id child_role_id
, cr.guid child_role_guid
FROM fusion.ase_role_vl pr
, fusion.ase_role_vl cr
, fusion.ase_role_role_mbr rr
WHERE rr.effective_end_date IS NULL
AND cr.role_id = rr.parent_role_id
AND pr.role_id = rr.child_role_id ) role_hierarchy
CONNECT BY NOCYCLE PRIOR child_role_id = parent_role_id
UNION ALL
—
— UNION to include the top role in the flattened list
—
SELECT r.code
, r.role_name
, r.role_id
, r.guid
, r.code
, r.role_name
, r.role_id
, r.guid
FROM fusion.ase_role_vl r) fr
, ( SELECT privrole.role_id
, priv.code
, priv.name
, privrole.effective_start_date policy_start_date
, privrole.effective_end_date policy_end_date
FROM fusion.ase_priv_role_mbr privrole
, fusion.ase_privilege_vl priv
WHERE priv.privilege_id = privrole.privilege_id
UNION ALL
—
— Role List to avoid outer join, also to provide a separate row for role changes
—
SELECT r.role_id
, NULL
, NULL
, r.effective_start_date
, NULL
FROM fusion.ase_role_b r
) priv
, fnd_vs_value_sets vsets
, fnd_vs_values_b valb
, fnd_vs_values_tl vtl
WHERE ur.user_id = u.user_id
AND r.role_id = ur.role_id
AND fr.top_role_id = r.role_id
AND priv.role_id = fr.role_id
AND NVL( ur.effective_end_date , SYSDATE ) >= SYSDATE
AND priv.policy_start_date <= NVL( ur.effective_end_date , SYSDATE )
AND NVL( priv.policy_end_date , SYSDATE ) >= SYSDATE
AND priv.code = vtl.translated_value
AND vsets.value_set_id = valb.value_set_id
AND vsets.value_set_code = vl2.translated_value
AND vtl.value_id = valb.value_id
AND valb.ENABLED_FLAG=’Y’ and sysdate between nvl(V.START_DATE_ACTIVE,sysdate) and nvl(V.END_DATE_ACTIVE,sysdate)
AND vtl.language = ‘US’
AND v.value_set_id=vs.value_set_id and v.value_id=vl.value_id and vl.language=’US’
and vs.value_set_code = ‘ORA_PER_METRICS_SETS’
and V.ENABLED_FLAG=’Y’ and sysdate between nvl(V.START_DATE_ACTIVE,sysdate) and nvl(V.END_DATE_ACTIVE,sysdate)
and v2.value_set_id=vs2.value_set_id and v2.value_id=vl2.value_id and vl2.language=’US’
and vl.translated_value=vs2.value_set_code
and V2.ENABLED_FLAG=’Y’ and sysdate between nvl(V2.START_DATE_ACTIVE,sysdate) and nvl(V2.END_DATE_ACTIVE,sysdate)
and v2.flex_value_attribute17 is null
and u.USER_GUID = pu.USER_GUID
and pu.person_id = pn.person_id(+)
and sysdate between pn.effective_start_date(+) and pn.effective_end_date(+)
and pu.active_flag = ‘Y’
and (pu.suspended is null or pu.suspended = ‘N’)
and user_login not in (‘orcladmin’,’weblogic_read_only’,’xelsysadm’,’oamSoftwareUser’,’IDROUser’,’PolicyROUser’,’PolicyRWUser’,’oimAdminUser’,’IDMPolicyROUser’,’IDMPolicyRWUser’,’faoperator’,’saas_readonly’,’em_monitoring’,’oamAdminUser’,’weblogic_idm’,’IDRWUser’,’PUBLIC’,’FAAdmin’,’weblogic’,’XELOPERATOR’,’OblixAnonymous’,’OCLOUD9_osn_APPID’)
and user_login not like ‘FUSION_APPS_%_APPID’
order by service
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Related Posts
Report Security
Report Security Query – Data Access On Specific Entities
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com