How to Identify all the groups who have Report Run rights and on which Reports
This SQL can help
"SELECT DISTINCT RI.GROUP_NAME GROUP_NAME,G.GROUP_NAME ""Access Right Name"", qu.name,G.DESCRIPTION DESCRIPTION,G.RIGHT_TYPE GRANTED_THROUGH_TYPE,O.NAME GRANTED_THROUGH,
--(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =UG.CREATED_BY) ""Granted By"",--TO_CHAR(UG.CREATED_DATE,'DD-Mon-YYYY') ""Granted Date"",'Group Access Rights: Instance' ""Access Right Type""FROM CMN_SEC_ASSGND_OBJ_PERM Ainner join (select distinct job_def.id as id, captions.name name, captions.description description, job_def.executable filename, job_def.is_active as active, job_def.is_system as is_system, type_lookups.name jobtype, job_code from cmn_sch_job_definitions job_def join cmn_captions_nls captions on job_def.id = captions.pk_id join cmn_lookups_v type_lookups on job_def.job_type = type_lookups.id left outer join cmn_sch_job_def_categories job_def_categories on job_def.id = job_def_categories.job_id left join cmn_lookups_v category_lookups on job_def_categories.category_id = category_lookups.id and category_lookups.language_code = 'en' where job_def.is_admin_visible = 1 and captions.table_name = 'CMN_SCH_JOB_DEFINITIONS' and job_def.id = captions.pk_id and captions.language_code = 'en' and job_def.job_type = type_lookups.id and type_lookups.language_code='en' and not exists ( select 1 from cmn_sch_job_definitions filter_job_def, cmn_sch_job_def_categories filter_assoc where filter_job_def.id = filter_assoc.job_id and filter_assoc.category_id=4 and job_def.id = filter_job_def.id) and job_def.is_system_visible=1 and type_lookups.lookup_code = 'REPORT' and job_def.IS_SYSTEM=0and (job_def.EXECUTABLE not like '%CSP%'ANDjob_def.EXECUTABLE not like '%CTS%'ANDjob_def.EXECUTABLE not like '%com.niku%')and job_def.source='customer' order by name) qu on qu.id=A.OBJECT_INSTANCE_ID, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O, CMN_SEC_USER_GROUPS UG,CMN_SEC_GROUPS_V RI, SRM_RESOURCES SRM
WHERE SRM.USER_ID = UG.USER_ID AND A.PRINCIPAL_ID =UG.GROUP_ID AND A.PRINCIPAL_TYPE = 'GROUP' AND A.RIGHT_ID = G.ID AND G.RIGHT_TYPE = O.RIGHT_CODE AND G.ID=50245 AND RI.ID = UG.GROUP_ID AND G.LANGUAGE_CODE = 'en' AND O.LANGUAGE_CODE = 'en' AND G.IS_ACTIVE=1"
Unfortunately there is not an easy way to do this through the PPM UI. You can click on an individual group and look at what access rights it has, but you cant go to an access right and see what groups its assigned to. You would have to write some type of a custom report or query to gather that information. There may be some folks out here that have done something like this and are willing to share their info with you. For the part about which reports they have access to, that is controlled by way of folders in Jasper. You can grant groups rights to certain folders, and then folks in those groups can access those folders.
Hope this helps a bit at least.
@ksarpotd - does this help you? If so, please mark Suman's reply as correct
Thank you so much Suman
There is one more same question
How to Identify all the resources who have Report Run rights over which Reports at instance level
Individual report access is controlled by folder access in Jasper. I do not believe there is a way with in ppm to set permissions on individual reports.
Retrieving data ...