Clarity

Hi team,

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:

select

r.ID as 'USER ID',

from

niku.INV_INVESTMENTS i

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

where

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.
 

Thanks,

Ryan

ryan.erlandsen:

Hi team,

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:

select

r.ID as 'USER ID',

from

niku.INV_INVESTMENTS i

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

where

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.
 

Thanks,

Ryan

 

 

Hi All,

Any thoughts here for Ryan?

Thanks!

Chris

Thanks Chris.

However, I think I have found the answer. I have found that some groups have Project - View Documents rights for certain OBS (something that was added before me and not documented anywhere . This would account for why these users are being added to the ODFSEC_PROJECT_V2 view with the adjustment to the OBS.

Now, it doesn't explain why these global users don't have access to this data in the report, however I have a theory why that I'd like to run past the forum users. We ran a SQL trace on the portlet in Clarity that gives correct results and it contained references to the ODFSEC_PROJECT_V view in addition to the ODFSEC_PROJECT_V2 view.

So, with my powers of deduction, it is now my understanding that the view ODFSEC_PROJECT_V2 contains all instance level access rights (including OBS rights) between users and the object instances (in this case project instances) and the ODFSEC_PROJECT_V view contains all users with global access rights for the particular object (view project). So our reports need to check both views for security access.

Can anyone confirm if my thoughts are correct?

Thanks!