Oracle Cloud ERP: Access Management Queries

User Listing Query

Purpose: The following query generates a comprehensive user role assignment report for Oracle Fusion ERP applications. It provides detailed information about user accounts, their associated roles, and assignment status including both current and historical data with proper effective dates support.

SELECT pu.user_guid
     , pu.username
     , papf.person_number employee_number
     , ppnf.full_name employee_name
     , pu.active_flag user_active_flag
     , CASE 
         WHEN pu.party_id IS NULL 
           THEN 'INTERNAL'
         ELSE 'EXTERNAL'
       END user_type     
     , pu.start_date user_start_date
     , pu.end_date user_end_date
     , pr.role_guid
     , pr.role_name
     , pur.active_flag assignment_active_flag
     , pur.start_date assignment_start_date
     , pur.end_date assignment_end_date
  FROM per_roles_dn_vl pr
     , per_user_roles pur
     , per_person_names_f ppnf
     , per_all_people_f papf
     , per_users pu
 WHERE pur.role_id = pr.role_id
   AND pu.user_id = pur.user_id
   AND SYSDATE BETWEEN ppnf.effective_start_date (+) 
                   AND ppnf.effective_end_date (+)
   AND ppnf.name_type(+)  =  'GLOBAL'
   AND ppnf.person_id (+) = papf.person_id
   AND SYSDATE BETWEEN papf.effective_start_date (+) 
                   AND papf.effective_end_date (+)
   AND pu.person_id = papf.person_id (+)
 ORDER BY pu.username
        , pr.role_name;

Role Listing Query

Purpose: This query generates a comprehensive role catalog report that provides detailed metadata about all available roles in Oracle Fusion ERP along with current assignment statistics. It serves as a master inventory of roles with usage analytics for role governance and optimization.

SELECT pr.role_guid
     , pr.role_common_name
     , pr.role_name
     , pr.description
     , pr.abstract_role
     , pr.job_role
     , pr.data_role
     , pr.active_flag
     , (SELECT COUNT(1)
          FROM per_user_roles pur
         WHERE SYSDATE BETWEEN NVL(pur.start_date, SYSDATE) 
                           AND NVL(pur.end_date, SYSDATE + 1)
           AND pur.active_flag = 'Y'
           AND pur.role_id = pr.role_id) assignment_count
  FROM per_roles_dn_vl pr
 ORDER BY pr.role_name;

User Role & Security Context Listing Query

Purpose: The following query generates a comprehensive user security context report that combines both role assignments and data security assignments into a single detailed view. It provides complete visibility into user access patterns across all Oracle Fusion ERP modules with specific security context details.

SELECT pu.username
     , pu.user_guid
     , pu.active_flag user_active_flag
     , CASE 
         WHEN pu.party_id IS NULL 
           THEN 'INTERNAL'
         ELSE 'EXTERNAL'
       END user_type     
     , pu.start_date user_start_date
     , pu.end_date user_end_date
     , pr.role_name
     , pr.role_guid
     , pur.active_flag role_assignment_active_flag
     , pur.start_date role_assignment_start_date
     , pur.end_date role_assignment_end_date
     , fur.active_flag security_assignment_active_flag
     , fur.start_date_active security_assignment_start_date
     , fur.end_date_active security_assignment_end_date
     , (SELECT bu.bu_name
          FROM fun_all_business_units_v bu
         WHERE fur.org_id = bu.bu_id) business_unit
     , (SELECT gas.name
          FROM gl_access_sets gas
         WHERE fur.access_set_id = gas.access_set_id) data_access_set
     , (SELECT gl.name
          FROM gl_ledgers gl
         WHERE fur.ledger_id = gl.ledger_id) ledger
     , (SELECT fbc.book_type_name
          FROM fa_book_controls fbc
         WHERE fur.book_id = fbc.book_control_id) asset_book
     , (SELECT fss.set_name
          FROM fnd_setid_sets_vl fss
         WHERE fur.set_id = fss.set_id) reference_data_set
     , (SELECT iop.organization_code
          FROM inv_org_parameters iop
         WHERE fur.inv_organization_id = iop.organization_id) inventory_organization
     , (SELECT cco.cost_org_name
          FROM cst_cost_orgs_v cco
         WHERE fur.cst_organization_id = cco.cost_org_id) cost_organization
     , (SELECT xcb.name
          FROM xcc_control_budgets xcb
         WHERE fur.control_budget_id = xcb.control_budget_id) control_budget
     , (SELECT fio.interco_org_name
          FROM fun_interco_organizations fio
         WHERE fur.interco_org_id = fio.interco_org_id) intercompany_organization
     , (SELECT rmp.def_supply_subinv
          FROM rcs_mfg_parameters rmp
         WHERE fur.mfg_organization_id = rmp.organization_id) manufacturing_plant
  FROM fun_user_role_data_asgnmnts fur
     , per_roles_dn_vl pr
     , per_user_roles pur
     , per_users pu
 WHERE pr.role_common_name = fur.role_name
   AND pu.user_guid = fur.user_guid
  -- AND SYSDATE BETWEEN NVL(fur.start_date_active, SYSDATE) AND NVL(fur.end_date_active, SYSDATE + 1)
  -- AND fur.active_flag = 'Y'
   AND pur.role_id = pr.role_id
   AND pu.user_id = pur.user_id
   AND pu.username = <User Name>
 ORDER BY pu.username
        , pr.role_name;