I'm having some troubles when replicating Clarity's instance rights for a project for building custom reports. For custom reports, I use the clause:
where inv.id in (select object_instance_id from ODFSEC_PROJECT_V2 where user_id = param_user_id)
This seems to work for most projects, but not all. I have a number of executive users that have global view rights, but can only see data from some, not all projects.
I ran a quick query to see which users had access rights to a particular project:
r.ID as 'USER ID',
inner join niku.ODF_CA_PROJECT p on p.id = i.ID
inner join niku.ODFSEC_PROJECT_V2 sec on i.ID = sec.object_instance_id
inner join niku.SRM_RESOURCES r on r.USER_ID = sec.user_id
LEFT OUTER JOIN niku.PRJ_OBS_ASSOCIATIONS assoc ON assoc.record_id= p.id AND assoc.table_name='SRM_PROJECTS'
LEFT OUTER JOIN niku.PRJ_OBS_UNITS unit ON unit.id=assoc.unit_id
i.CODE = 'Pr-0028'
Initially, when i ran it, it only returned 6 users, which corresponded to the 6 participants of the project, but did not include the 50+ executive or administrative users with global rights.
I then adjusted the OBS of the project, ran the query again and it returned 61 users as expected. The OBS was adjusted from Enterprise/Project Portfolio to Enterprise/Project Portfolio/*** Program, and we don't have any OBS level security rights, so I can't understand why this would alter the contents of the table.
So I'm wondering if anyone else has encountered this unusual behaviour with the security table and what others use for accurate security rights in custom reports. I found the thread Instance level Security in Reports where a user had experienced a similar issue in 2010, but I couldn't find a resolution to the actual issue. Any thoughts or comments welcome.