Oracle Analytics and AI

Floor Jansen

Description

make it easier to create an overview of all data roles, the job roles and (person) security in it

Use Case and Business Need

We have hunderds of data roles in use, because of the complexity of our (HR) organisation. Creating so much data roles is sensitive to errors. It would be very useful to have a report available (or being able to create a reports) that shows at least the following information of date roles:

• name of the data role
• name of the job role attached
• name of the security (in our case person security) attached to it
• names of people to who this role is assigned

 

Original Idea Number: df8627c4c8

Micha Gruler-21543

Hi Floor,

you can already do this by writing a query and report in BI Publisher.
I don't have one that shows all you asked for in one single query, but here are a few that you can combine:

 

All data roles with their security profiles and their job roles:

-----------------------------------------

SELECT /*csv*/
    gen.data_role_name as DATA_ROLE_NAME,
    gen.DATA_ROLE_DISPLAY_NAME,
    gen.DATA_ROLE_DESCRIPTION,
    gen.DELEGATION_ALLOWED,
       DECODE(gen.base_role_id
              , -1 , 'Job/Abstract/Custom Enterprise Role'
              ,'Data Role'
              ) "Role Type",
       (select '"'||ROLE_DISTINGUISHED_NAME||'"' from fusion.per_roles_dn rol where rol.role_id = gen.data_role_id) Distinguished_Name_in_LDAP,
       (SELECT role_name
        FROM   fusion.per_roles_dn_vl
        WHERE role_id = gen.base_role_id
       ) base_role_name,
       (select count(*)
        from   fusion.per_gen_data_role_profiles gdrp
        where  gdrp.generated_data_role_id = gen.generated_data_role_id
       ) "Number of Security Profiles",
       (select NVL(sec.name,'Missing Person Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_person_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'PERSON'
          and  gdrp_per.security_profile_id = sec.person_security_profile_id(+)
        ) "Person Security Profile",       
       (select NVL(sec.name,'Missing Public Person Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_person_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'PUBLIC_PERSON'
          and  gdrp_per.security_profile_id = sec.person_security_profile_id(+)
        ) "Public Person Security Profile",
       (select NVL(sec.name,'Missing Organization Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_org_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'ORGANIZATION'
          and  gdrp_per.security_profile_id = sec.org_security_profile_id(+)
        ) "Organization Security Profile",    
       (select NVL(sec.name,'Missing Position Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_position_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'POSITION'
          and  gdrp_per.security_profile_id = sec.position_security_profile_id(+)
        ) "Position Security Profile",
       (select NVL(sec.name,'Missing Country Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_country_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'COUNTRY'
          and  gdrp_per.security_profile_id = sec.country_security_profile_id(+)
        ) "Country Security Profile",
       (select NVL(sec.name,'Missing LDG Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_ldg_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'LDG'
          and  gdrp_per.security_profile_id = sec.ldg_security_profile_id(+)
        ) "LDG Security Profile",
       (select NVL(sec.name,'Missing Payroll Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.pay_pay_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'PAYROLL'
          and  gdrp_per.security_profile_id = sec.pay_security_profile_id(+)
        ) "Payroll Security Profile",
       -- (select NVL(sec.name,'Missing WLM Process Security Profile: '||TO_CHAR(security_profile_id))
        -- from   fusion.per_gen_data_role_profiles gdrp_per    
              -- ,fusion.wlm_process_security_profiles sec
        -- where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          -- and  gdrp_per.hr_securing_object = 'WLM'
          -- and  gdrp_per.security_profile_id = sec.process_security_profile_id(+)
        -- ) "WLM Process Security Profile",
       (select NVL(sec.name,'Missing Payroll Flow Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.pay_flw_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'FLOWPATTERN'
          and  gdrp_per.security_profile_id = sec.flw_security_profile_id(+)
        ) "Payroll Flow Security Profile",
       (select NVL(sec.name,'Missing Doc Type Security Profile: '||TO_CHAR(security_profile_id))
        from   fusion.per_gen_data_role_profiles gdrp_per    
              ,fusion.per_doc_type_security_profiles sec
        where  gdrp_per.generated_data_role_id = gen.generated_data_role_id
          and  gdrp_per.hr_securing_object = 'DOR'
          and  gdrp_per.security_profile_id = sec.doc_type_security_profile_id(+)
        ) "Document Type Security Profile"    
FROM  fusion.per_generated_data_roles gen

-----------------------------------------

 

All security profiles and their SQL predicates:

-----------------------------------------

SELECT pro.type,
    pro.NAME,
       FO.OBJ_NAME,
       FOISV.PREDICATE
FROM
 (select 'Organization Profile' type, name, ORG_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_ORG_SECURITY_PROFILES
  union
  select 'Country Profile' type, name, COUNTRY_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_COUNTRY_SECURITY_PROFILES
  union
  select 'Document Type Profile' type, name, DOC_TYPE_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_DOC_TYPE_SECURITY_PROFILES
  union
  select 'Grade Profile' type, name, GRADE_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_GRADE_SECURITY_PROFILES
  union
  select 'Job Profile' type, name, JOB_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_JOB_SECURITY_PROFILES
  union
  select 'LDG Profile' type, name, LDG_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_LDG_SECURITY_PROFILES
  union
  select 'Location Profile' type, name, LOCATION_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_LOCATION_SECURITY_PROFILES
  union
  select 'Person Profile' type, name, PERSON_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_PERSON_SECURITY_PROFILES
  union
  select 'Position Profile' type, name, POSITION_SECURITY_PROFILE_ID as SECURITY_PROFILE_ID
  from FUSION.PER_POSITION_SECURITY_PROFILES 
 ) pro,
 
  FUSION.PER_INSTANCE_SET_MAPPINGS PISM,
     FUSION.FND_OBJECT_INSTANCE_SETS_VL FOISV,
     FUSION.FND_OBJECTS_VL FO
WHERE pro.SECURITY_PROFILE_ID=PISM.SECURITY_PROFILE_ID
  AND PISM.INSTANCE_SET_ID=FOISV.INSTANCE_SET_ID
  AND FO.OBJECT_ID=FOISV.OBJECT_ID
  and pro.NAME like nvl(:name, pro.NAME)
  and pro.type like nvl(:type, pro.type)
  and FO.OBJ_NAME like nvl(:OBJ_NAME, FO.OBJ_NAME)
ORDER BY pro.NAME,
         FO.OBJ_NAME

-----------------------------------------

 

All Users and their roles:

-----------------------------------------

SELECT pu.person_id,
  'Person' as Type,
  ppn.display_name,
  paf.person_number,
  pu.username,
  DECODE(pu.suspended, 'Y', 'Yes', 'No') as Suspended,
  prdv.role_name,
  pu.user_guid,
  prdv.description,
  prdv.abstract_role,
  prdv.job_role,
  prdv.data_role
FROM fusion.per_users pu,
  fusion.per_user_roles pur,
  fusion.per_roles_dn_vl prdv,
  fusion.per_person_names_f ppn,
  fusion.per_all_people_f paf
WHERE pu.user_id      = pur.user_id(+)
AND prdv.role_guid(+) = pur.role_guid
and ppn.name_type (+) = 'GLOBAL'
AND pu.person_id = ppn.person_id (+)
and trunc(sysdate) between ppn.effective_start_date (+) and ppn.effective_end_date (+)
and pu.active_flag = 'Y'
and pu.user_guid = nvl(:user_guid, pu.user_guid)
and upper(pu.username) like upper(nvl(:username, pu.username))
and pu.person_id = paf.person_id (+)
and trunc(sysdate) between paf.effective_start_date (+) and paf.effective_end_date (+)
order by pur.last_update_date desc

 

-----------------------------------------

 

Hope this makes sense.

 

Regards

Micha