Security clause in Risk Issues and Change Request portlet is not displaying data anymore

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Security clause in RIC portlet is not displaying data anymore

Environment

Release : 15.9.3 and above

Component : Clarity Studio

Cause

STEPS TO REPRODUCE
1. Navigate to Administration --> Queries
2. Create a new query as below

SELECT  @SELECT:DIM:USER_DEF:IMPLIED:theDIMENSION:SQ.issue_int_id:dim_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.issue_int_id:issue_int_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.Product_id:Product_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.Product_Name:Product_Name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.proj_int_id:proj_int_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.proj_id:proj_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.proj_name:proj_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.investment_code_upper:investment_code_upper@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.proj_mngr_id:proj_mngr_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.issue_id:rim_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.issue_name:rim_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.description:description@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.resolution:resolution@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.category:category@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:CatType.name:CatType_Name@,    
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.priority_sl:priority_sl@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.priority:priority@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.status:status@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.assigned_to:assigned_to@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.assigned_email:assigned_email@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.created_by:created_by@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.created_date:created_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.target_resolve_date:target_resolve_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.resolved_date:resolved_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.last_updated_by:last_updated_by@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.last_updated_date:last_updated_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.schedule_sl:schedule_sl@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.category_type:category_type@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.status_code:status_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.assigned_to_code:assigned_to_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:SQ.type_code:type_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:CASE SQ.type_code WHEN 'RISK' THEN 1 ELSE 0 END:is_risk@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:CASE SQ.type_code WHEN 'ISSUE' THEN 1 ELSE 0 END:is_issue@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:theDIMENSION:CASE SQ.type_code WHEN 'CHANGE' THEN 1 ELSE 0 END:is_change@    
FROM (
    SELECT DISTINCT 
       Prod.id Product_id, 
       Prod.Name Product_Name,
       i.odf_pk issue_int_id,
       i.pk_id proj_int_id,
       i.pk_unique_name proj_id,
       i.pk_name proj_name,
       @UPPER@(Prod.odf_object_code) investment_code_upper,
       Prod.manager_id proj_mngr_id,
       i.rim_risk_issue_code issue_id,
       i.name issue_name,
       i.description description,
       i.resolution resolution,
       category.name category,
       i.priority_code priority_sl,
       priority.name priority,
       status.name status,
       r.full_name assigned_to,
       r.email assigned_email,
       i.created_by created_by,
       i.created_date created_date,
       i.target_resolve_date target_resolve_date,
       i.resolved_date resolved_date,
       i.updated_by last_updated_by,
       i.last_updated_date last_updated_date,
       @[email protected]_DATEDIFF_FCT(@[email protected]_DATE_TRUNC_FCT(@SYSDATE@),@[email protected]_DATE_TRUNC_FCT(i.target_resolve_date)) schedule_sl,
       i.category_type,
       i.status_code,
       i.assigned_to assigned_to_code,
       i.type_code
       FROM inv_investments Prod
       
       INNER JOIN odf_ca_project CP ON CP.id = Prod.id
       INNER JOIN ODF_ISSUE_V2 I ON I.pk_id = Prod.id
       LEFT   OUTER JOIN SRM_RESOURCES r ON  i.assigned_to = r.user_id 
       LEFT   OUTER JOIN CMN_LOOKUPS_V status ON  status.lookup_code = i.status_code
        AND status.lookup_type = 'RIM_STATUS' 
       AND status.language_code =  @WHERE:PARAM:LANGUAGE@ 
       LEFT   OUTER JOIN CMN_LOOKUPS_V category ON  category.lookup_code = i.category_type
                                         AND category.lookup_type = 'RIM_CATEGORY_TYPE' 
                                         AND category.language_code =  @WHERE:PARAM:LANGUAGE@ 
       LEFT   OUTER JOIN CMN_LOOKUPS_V priority ON  priority.lookup_code = i.priority_code
                                         AND priority.lookup_type = 'RIM_PRIORITY' 
                                         AND priority.language_code =  @WHERE:PARAM:LANGUAGE@  
       WHERE  5=5
       AND Prod.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
       AND    @WHERE:SECURITY:ISSUE:i.odf_pk@ 
       AND    @WHERE:SECURITY:PROJECT:CP.id@ 
       AND    1=1
     UNION ALL
       SELECT DISTINCT 
       Prod.id, 
       Prod.Name,
       i.odf_pk issue_int_id,
       i.pk_id proj_int_id,
       i.pk_unique_name proj_id,
       i.pk_name proj_name,
       @UPPER@(Prod.odf_object_code) investment_code_upper,
       Prod.manager_id proj_mngr_id,
       i.rim_risk_issue_code issue_id,
       i.name issue_name,
       i.description description,
       i.resolution resolution,
       category.name category,
       i.priority_code priority_sl,
       priority.name priority,
       status.name status,
       r.full_name assigned_to,
       r.email assigned_email,
       i.created_by created_by,
       i.created_date created_date,
       i.target_resolve_date target_resolve_date,
       i.resolved_date resolved_date,
       i.updated_by last_updated_by,
       i.last_updated_date last_updated_date,
       @[email protected]_DATEDIFF_FCT(@[email protected]_DATE_TRUNC_FCT(@SYSDATE@),@[email protected]_DATE_TRUNC_FCT(i.target_resolve_date)) schedule_sl,
       i.category_type,
       i.status_code,
       i.assigned_to assigned_to_code,
       i.type_code
       FROM inv_investments Prod
 
       INNER JOIN odf_ca_project CP ON CP.id = Prod.id
       INNER JOIN ODF_RISK_V2 I ON I.pk_id = Prod.id and I.tu_status = 'open' and i.tu_psr = 1
       LEFT   OUTER JOIN SRM_RESOURCES r ON  i.assigned_to = r.user_id 
       LEFT   OUTER JOIN CMN_LOOKUPS_V status ON  status.lookup_code = i.status_code
                                       AND status.lookup_type = 'RIM_STATUS' 
                                       AND status.language_code =  @WHERE:PARAM:LANGUAGE@ 
       LEFT   OUTER JOIN CMN_LOOKUPS_V category ON  category.lookup_code = i.category_type
                                         AND category.lookup_type = 'RIM_CATEGORY_TYPE' 
                                         AND category.language_code =  @WHERE:PARAM:LANGUAGE@ 
       LEFT   OUTER JOIN CMN_LOOKUPS_V priority ON  priority.lookup_code = i.priority_code
                                         AND priority.lookup_type = 'RIM_PRIORITY' 
                                         AND priority.language_code =  @WHERE:PARAM:LANGUAGE@  
       WHERE  5=5
   AND Prod.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
       AND    @WHERE:SECURITY:RISK:i.odf_pk@ 
       AND    @WHERE:SECURITY:PROJECT:cp.id@ 
 
     UNION ALL
       SELECT DISTINCT 
       Prod.id, 
       Prod.Name,
       i.odf_pk issue_int_id,
       i.pk_id proj_int_id,
       i.pk_unique_name proj_id,
       i.pk_name proj_name,
       @UPPER@(prod.odf_object_code) investment_code_upper,
       prod.manager_id proj_mngr_id,
       i.rim_risk_issue_code issue_id,
       i.name issue_name,
       i.description description,
       NULL resolution,
       category.name category,
       i.priority_code priority_sl,
       priority.name priority,
       status.name status,
       r.full_name assigned_to,
       r.email assigned_email,
       i.created_by created_by,
       i.created_date created_date,
       i.exp_closure_date target_resolve_date,
       i.closure_date resolved_date,
       i.updated_by last_updated_by,
       i.last_updated_date last_updated_date,
       @[email protected]_DATEDIFF_FCT(@[email protected]_DATE_TRUNC_FCT(@SYSDATE@),@[email protected]_DATE_TRUNC_FCT(i.exp_closure_date)) schedule_sl,
       i.category_type,
       i.status_code,
       i.assigned_to assigned_to_code,
       i.type_code
       FROM inv_investments Prod
 
       INNER JOIN odf_ca_project CP ON CP.id = prod.id
       INNER JOIN ODF_CHANGE_V2 I ON I.pk_id = prod.id
       LEFT   OUTER JOIN SRM_RESOURCES r ON  i.assigned_to = r.user_id 
       LEFT   OUTER JOIN CMN_LOOKUPS_V status ON  status.lookup_code = i.status_code
                                       AND status.lookup_type = 'RIM_STATUS' 
                                       AND status.language_code =  @WHERE:PARAM:LANGUAGE@ 
       LEFT   OUTER JOIN CMN_LOOKUPS_V category ON  category.lookup_code = i.category_type
                                         AND category.lookup_type = 'RIM_CATEGORY_TYPE' 
                                         AND category.language_code =  @WHERE:PARAM:LANGUAGE@ 
       LEFT   OUTER JOIN CMN_LOOKUPS_V priority ON  priority.lookup_code = i.priority_code
                                         AND priority.lookup_type = 'RIM_PRIORITY' 
                                         AND priority.language_code =  @WHERE:PARAM:LANGUAGE@  
       WHERE  5=5
   AND Prod.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
    
       AND    @WHERE:SECURITY:CHANGE:i.odf_pk@ 
       AND    @WHERE:SECURITY:PROJECT:cp.id@ 
 
 )SQ
LEFT   OUTER JOIN CMN_LOOKUPS_V CatType ON  CatType.lookup_code = SQ.type_code
               AND CatType.lookup_type = 'MAP_RIM_TYPE' 
               AND CatType.language_code =  @WHERE:PARAM:LANGUAGE@  
WHERE 4=4
       AND    ((SQ.status_code IS NULL)   OR (@WHERE:PARAM:USER_DEF:STRING:SQ.status_code:STATUS@))
       AND    ((SQ.assigned_to_code IS NULL)   OR (@WHERE:PARAM:USER_DEF:INTEGER:SQ.assigned_to_code:ASSIGNED_TO@))
AND @FILTER@

3. Create a Portlet using the above query 
4. NAvigate to Projects and open any project which has a Dashboard tab
5. Add the above portlet in the dashboard tab
6. Logout and login as a resource who has access to the project and the RIC in this project (it can also be a superuser)

Expected: In the Project Dashboard, the portlet shows the Risks Issues and CRs in the project
Actual: No data is displayed in the portlet

 

Resolution

The (custom) NSQL query mentioned here is a UNION ALL of issue, risk and change objects.

The problem is exactly same in all 3 individual parts (of the UNION ALL)

In 15.9.2, RICs were moved from being project's sub object to being investment's sub object.

With this change, the way NSQL's security clause was constructed was changed for RIC.
As a result now its driven by concrete parent instance id (pk_id) instead of its own odf_pk.

Bottomline, @WHERE:SECURITY:ISSUE:i.odf_pk@ should be changed to @WHERE:SECURITY:ISSUE:i.pk_id@

If we take a closer look at this custom NSQL (lets take the first part which is for 'issue' object. The Rationale for 'risk' and 'change' are exactly same. So we can just try to understand any one),

We will find below security clauses:

AND    @WHERE:SECURITY:ISSUE:i.odf_pk@ 
AND    @WHERE:SECURITY:PROJECT:CP.id@

So clearly, post 15.9.2 this won't work and it should be changed to 

AND    @WHERE:SECURITY:ISSUE:i.pk_id@ 
AND    @WHERE:SECURITY:PROJECT:CP.id@

Now the same query also has a join that says:
INNER JOIN ODF_ISSUE_V2 I ON I.pk_id = Prod.id

Which essentially makes the 2 security clauses identical.Hence we can get rid of the @WHERE:SECURITY:ISSUE:i.pk_id@   [ originally @WHERE:SECURITY:ISSUE:i.odf_pk@ ]  altogether