Clarity

Expand all | Collapse all

NSQL Security CONSTRUCT not working?

  • 1.  NSQL Security CONSTRUCT not working?

    Posted 01-31-2019 01:44 PM

    Hi Everyone,

     

    I have written the following NSQL code for a Milestone Reporting portlet, however, the security construct is not working, can I get some help on what I might be coding incorrectly?

     

    CODE:

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:MST:ROWNUM:uq_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."ID":id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project Name":Project_Name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Task Name":Task_Name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery.externalid:externalid@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project ID":project_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Audit ID":audit_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."CSOC ID":csoc_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Tech Risk ID":tech_risk_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Systems Pillar Sponsor":systems_pillar_sponsor@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Project Manager":project_manager@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Task Owner":task_owner@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."OM_ID":OM_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Business Lead":business_lead@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."BL_ID":BL_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Business Owner":business_owner@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."BO_ID":BO_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Start":start_Date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Finish":finish_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Baseline Start":baseline_start@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Baseline Finish":baseline_finish@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."RAG Status":rag_status@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status Report Date":status_report_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."ST_ID":ST_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status":status@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Status Comment":status_comment@,
    @SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.prismilestone:milestone@,
    @SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.priskey:key_task@,
    @SELECT:DIM_PROP:USER_DEF:BOOLEAN:MST:hgquery.is_active:is_active@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Product":product@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Sub Product":sub_product@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery."Strategic Investment Category":strategic_investment_category@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:MST:hgquery.hg_has_children:hg_has_children@
    FROM(
    SELECT        P.UNIQUE_NAME "Project ID",
                   P.ID "ID",
                   CP.AUDIT_ID "Audit ID",
                   CP.CSOC_ID "CSOC ID",
                   CP.TECHNOLOGY_RISK_ID "Tech Risk ID",
                   P.NAME "Project Name",
       NULL "Task Name",
                   P.ID HG_HAS_CHILDREN,
                   SPS.NAME "Systems Pillar Sponsor",
                   RPM.FULL_NAME "Project Manager",
           NULL "Task Owner",
       RPM.ID "OM_ID",
                   RBL.FULL_NAME "Business Lead",
       RBL.ID "BL_ID",
                   RBO.FULL_NAME "Business Owner",
       RBO.ID "BO_ID",
                   PR.PRSTART "Start",
       PR.PRFINISH "Finish",
                   PR.PRBASESTART "Baseline Start",
                   PR.PRBASEFINISH "Baseline Finish",
                   CASE WHEN ST.OVERALL > 0 THEN ST.OVERALL ELSE 0 END "RAG Status",
                   TO_CHAR(ST.COP_REPORT_DATE, 'MM/DD/YYYY') "Status Report Date",
       PR.PROGRESS "ST_ID",
       DECODE(PR.PROGRESS,
                          0,
                          'Not Started',
                          1,
                          'Started',
                          2,
                          'Completed') "Status",
                   ST.COP_REPORT_UPDATE "Status Comment",
                   0 PRISMILESTONE,
                   0 PRISKEY,
                   cast(P.IS_Active as Integer) is_active,
                   PRO.NAME "Product",
                   SUBPR.NAME "Sub Product",
                   CP.BBH_STRATEGICINVCATE "Strategic Investment Category",
                   P.UNIQUE_NAME EXTERNALID
            
              FROM NIKU.PRJ_PROJECTS   PR,
                   NIKU.SRM_PROJECTS   P,
                   NIKU.ODF_CA_PROJECT CP,
                   NIKU.SRM_RESOURCES  RBO,
                   NIKU.SRM_RESOURCES  RPM,
                   NIKU.SRM_RESOURCES  RBL,
                   NIKU.ODF_CA_INV     SP,
                   
                   (SELECT ODF.ODF_PARENT_ID,
                           ODF.COP_REPORT_DATE,
                           ODF.COP_REPORT_UPDATE,
                           (ODF.COP_SCHEDULE_STATUS + ODF.COP_SCOPE_STATUS +
                                  ODF.COP_COST_EFT_STATUS) OVERALL
                      FROM NIKU.ODF_CA_COP_PRJ_STATUSRPT ODF,
                           (SELECT OD.ODF_PARENT_ID, MAX(OD.ID) "ID"
                              FROM NIKU.ODF_CA_COP_PRJ_STATUSRPT OD
                             WHERE OD.COP_REPORT_STATUS = 'FINAL'
                               AND OD.COP_REPORT_DATE >=
                                   ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)
                             GROUP BY OD.ODF_PARENT_ID) LU
                     WHERE LU.ID = ODF.ID) ST,
                   
                   (SELECT CMN.LOOKUP_CODE, CMN.NAME
                      FROM NIKU.CMN_LOOKUPS_V CMN
                     WHERE CMN.LANGUAGE_CODE = 'en'
                       AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY4') SPS,
                   (SELECT CMN.LOOKUP_CODE, CMN.NAME
                      FROM NIKU.CMN_LOOKUPS_V CMN
                     WHERE CMN.LANGUAGE_CODE = 'en'
                       AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY1') PRO,
                   (SELECT CMN.LOOKUP_CODE, CMN.NAME
                      FROM NIKU.CMN_LOOKUPS_V CMN
                     WHERE CMN.LANGUAGE_CODE = 'en'
                       AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY2') SUBPR
            
             WHERE P.ID = PR.PRID
               AND CP.ID = P.ID(+)
               AND CP.OBJ_STAKEHOLDER1 = RBO.ID(+)
               AND RPM.USER_ID = PR.MANAGER_ID(+)
               AND CP.BBH_BUSINESSLEAD = RBL.ID(+)
               AND P.ID = SP.ID(+)
               AND SP.OBJ_PFL_CATEGORY4 = SPS.LOOKUP_CODE(+)
               AND SP.OBJ_PFL_CATEGORY1 = PRO.LOOKUP_CODE(+)
               AND SP.OBJ_PFL_CATEGORY2 = SUBPR.LOOKUP_CODE(+)
               AND P.ID = ST.ODF_PARENT_ID(+)
       AND (PR.PRCLOSEDTIME IS NULL OR PR.PRCLOSEDTIME >= '01-JAN-17')
               AND P.IS_TEMPLATE = 0
               AND P.IS_PROGRAM = 0
               AND PR.PRUSERTEXT1 NOT LIKE '%HARD%'
               AND PR.PRUSERTEXT1 NOT LIKE '%ROUTE%'
       AND @WHERE:PARAM:user_def:STRING:hg_row_id@ IS NULL

     

    UNION

     

    SELECT P.UNIQUE_NAME "Project ID",
                   T.PRID "ID",
                   CP.AUDIT_ID "Audit ID",
                   CP.CSOC_ID "CSOC ID",
                   CP.TECHNOLOGY_RISK_ID "Tech Risk ID",
    P.NAME "Project Name",
                   T.PRNAME "Task Name",
                   NULL HG_HAS_CHILDREN,
                   NULL "Systems Pillar Sponsor",
       NULL "Project Manager",
                   RTO.FULL_NAME "Task Owner",
       RTO.ID "OM_ID",
                   NULL "Business Lead",
       NULL "BL_ID",
                   NULL "Business Owner",
       NULL "BO_ID",
                   T.PRSTART "Start",
                   T.PRFINISH "Finish",
                   BA.START_DATE "Baseline Start",
                   BA.FINISH_DATE "Baseline Finish",
                   CAST(CT.MILESTONE_STATUS AS INTEGER) "RAG Status",
                   TO_CHAR(SYSDATE, 'MM/DD/YYYY') "Status Report Date",
                   T.PRSTATUS "ST_ID",
       DECODE(T.PRSTATUS,
                          0,
                          'Not Started',
                          1,
                          'Started',
                          2,
                          'Completed') "Status",
                   T.PRUSERTEXT1 "Status Comment",
                   T.PRISMILESTONE,
                   T.PRISKEY,
                   cast(P.IS_ACTIVE as Integer) is_active,
                   PRO.NAME "Product",
                   SUBPR.NAME "Sub Product",
                   CP.BBH_STRATEGICINVCATE "Strategic Investment Category",
                   T.PREXTERNALID EXTERNALID
            
              FROM NIKU.PRTASK         T,
                   NIKU.ODF_CA_TASK    CT,
                   NIKU.SRM_RESOURCES  RTO,
                   NIKU.SRM_PROJECTS   P,
       NIKU.PRJ_PROJECTS   PR,
                   NIKU.ODF_CA_PROJECT CP,
                   NIKU.ODF_CA_INV     SP,
                   NIKU.SRM_RESOURCES  RBO,
                   NIKU.SRM_RESOURCES  RBL,
                   
                   (SELECT CMN.LOOKUP_CODE, CMN.NAME
                      FROM NIKU.CMN_LOOKUPS_V CMN
                     WHERE CMN.LANGUAGE_CODE = 'en'
                       AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY4') SPS,
                   (SELECT CMN.LOOKUP_CODE, CMN.NAME
                      FROM NIKU.CMN_LOOKUPS_V CMN
                     WHERE CMN.LANGUAGE_CODE = 'en'
                       AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY1') PRO,
                   (SELECT CMN.LOOKUP_CODE, CMN.NAME
                      FROM NIKU.CMN_LOOKUPS_V CMN
                     WHERE CMN.LANGUAGE_CODE = 'en'
                       AND CMN.LOOKUP_TYPE = 'OBJ_INVESTMENT_PFL_CATEGORY2') SUBPR,
                   
                   (SELECT B.OBJECT_ID, B.START_DATE, B.FINISH_DATE
                      FROM NIKU.PRJ_BASELINE_DETAILS B
                     WHERE B.OBJECT_TYPE = 'TASK') BA
            
             WHERE T.PRID = CT.ID
               AND T.TASK_OWNER = RTO.ID(+)
               AND T.PRISTASK = 1
               AND (T.PRISKEY = 1 OR T.PRISMILESTONE = 1)
               AND P.ID = T.PRPROJECTID
               AND CP.ID = P.ID(+)
       AND P.ID = PR.PRID
               AND CP.BBH_BUSINESSLEAD = RBL.ID(+)
               AND CP.OBJ_STAKEHOLDER1 = RBO.ID(+)
               AND SP.ID = P.ID(+)
               AND SP.OBJ_PFL_CATEGORY4 = SPS.LOOKUP_CODE(+)
               AND SP.OBJ_PFL_CATEGORY1 = PRO.LOOKUP_CODE(+)
               AND SP.OBJ_PFL_CATEGORY2 = SUBPR.LOOKUP_CODE(+)
               AND T.PRID = BA.OBJECT_ID(+)
       AND (PR.PRCLOSEDTIME IS NULL OR PR.PRCLOSEDTIME >= '01-JAN-17')
               AND P.IS_TEMPLATE = 0
               AND P.IS_PROGRAM = 0
               AND PR.PRUSERTEXT1 NOT LIKE '%HARD%'
               AND PR.PRUSERTEXT1 NOT LIKE '%ROUTE%'
       AND (t.PRPROJECTID = @WHERE:PARAM:user_def:STRING:hg_row_id@ OR
    @WHERE:PARAM:USER_DEF:STRING:hg_all_rows@ = 1)
    ) hgquery
       
       where (
     
         @WHERE:PARAM:USER_DEF:INTEGER:OBS@ IS NULL
         OR
         (
              SELECT DISTINCT pou.id AS prj_unit_id
                FROM prj_obs_associations poa
                JOIN prj_obs_units pou ON pou.ID = poa.unit_id
               WHERE poa.table_name = 'SRM_PROJECTS'
                 AND poa.record_id = hgquery."ID"
                 AND pou.type_id = (SELECT DISTINCT P.type_id
                                           FROM prj_obs_units P
                                          WHERE P.id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@ )
         )
         IN
         (
         SELECT DISTINCT F.unit_id
           FROM prj_obs_units_flat F
          WHERE branch_unit_id = @WHERE:PARAM:USER_DEF:INTEGER:OBS@
         )
    )
    AND hgquery."ID" = @NVL@(@WHERE:PARAM:XML:INTEGER:/data/id/@value@,hgquery."ID")
    AND @WHERE:SECURITY:PROJECT:SRM_PROJECTS.ID@
    AND @filter@



  • 2.  Re: NSQL Security CONSTRUCT not working?

    Posted 01-31-2019 04:03 PM

    I do not know if it is this, but the documentations has it

    @WHERE:SECURITY:PROJECT:SRM.PROJECTS.ID@

     

    Preview seems to accept both. Did not test that further than that.



  • 3.  Re: NSQL Security CONSTRUCT not working?

    Posted 02-01-2019 03:34 AM

    The "thing" that you are trying to apply security to needs to be "in scope" at the point your use the security construct - what I am saying is that SRM_PROJECTS.ID is not in scope when you reference it, since SRM_PROJECTS is not part of your outer query.

     

    2 ways to approach this; either put the security construct lower down in the query (i.e. in multiple places where you reference SRM_PROJECTS) or return the SRM_PROJECTS.ID to the outer query (currently you return it as ID for some of the UNION'd statements, but sometimes ID is a task id as well I think).

     

    I think that the former option would be clearer though.