TRIRIGA - Redundant security Geog/Org SQL for associated subqueries for Non-admin users - BIND variables

Hi Lou,

I have emailed you separately with three files which show examples of SQL changes, as I am unable to upload to this comment.

File 1: original sample SQL (note: existing binds have been replaced with literals)

File 2: Add bind variables as shown in the file which replaces the organisation and geography hierarchy path with a bind variable

File 3: Whilst doing this it would make sense to simplify the SQL so when there is more than 1 org/geo, it doesn't involve multiple access to the org/geo tables, but accesses once and retrieves all orgs/geos at once.

Thanks.

Hi David, Thank you for taking the time to provide your ideas to IBM. We need a little more information to understand your idea. Can you provide more information and sample SQL showing what you want parameterized?

I look forward to your response. Thank you again for your feedback.

This is an example of a query which shows the org and geo conditions applied, without using bind variables. For every variation of org and geo (potentially millions of combinations), this results in different SQL and SQL ID.

SELECT T1.triUserMessageFlagTX AS T1_1180,
     T1.triFormLabelSY AS T1_1191,
     T1.triIdTX AS T1_1181,
     T1.triDescriptionTX AS T1_1211,
     COALESCE(T3.triNameTX,
     T1.triNameTX) AS T3_1178,
     T1.triMatrixRequestClas AS T1_1540,
     T1.triMatrixRequestClasObjId AS T1_1540_OBJID,
     T1.triPriorityClassCL AS T1_1516,
     T1.triPriorityClassCLObjId AS T1_1516_OBJID,
     T1.triRequestedByTX AS T1_1488,
     T1.triRequestedByTXObjId AS T1_1488_OBJID,
     T1.triRequestedForTX AS T1_1493,
     COALESCE(T4.triParentBuildingTX,
     T2.triParentBuildingTX) AS T4_1075,
     COALESCE(T4.triParentFloorTX,
     T2.triParentFloorTX) AS T4_1080,
     COALESCE(T4.triNameTX,
     T2.triNameTX) AS T4_1045,
     T1.triPlannedStartDT AS T1_1441,
     T1.triPlannedEndDT AS T1_1442,
     T1.triActualTotalCostNU AS T1_1447,
     T1.triStatusCL AS T1_1183,
     T1.triStatusCLObjId AS T1_1183_OBJID,
     T1.triResourceAssignmen AS T1_1559,
     T1.triResourceAssignmenObjId AS T1_1559_OBJID,
     T1.SYS_TYPE1 AS T1_SYS_TYPE1,
     T1.SYS_GUIID AS T1_SYS_GUIID,
     T1.SPEC_ID AS T1_SPEC_ID,
     T1.triCurrencyUO AS T1_1458
FROM M_TRITASK T1 LEFT
OUTER JOIN IBS_SPEC_ASSIGNMENTS T5 ON T1.SPEC_ID = T5.SPEC_ID
    AND T5.ASS_SPEC_CLASS_TYPE = ?
    AND T5.ASS_TYPE = ? LEFT
OUTER JOIN M_LOCATION T2 ON T5.ASS_SPEC_ID = T2.SPEC_ID
    AND T2.SYS_OBJECTID > 0
    AND T2.SYS_TYPE1 IN (?, ?, ?) LEFT
OUTER JOIN ML_TRITASK T3 ON T1.SPEC_ID = T3.SPEC_ID
    AND T3.LANGUAGE_ID = ? LEFT
OUTER JOIN ML_LOCATION T4 ON T2.SPEC_ID = T4.SPEC_ID
    AND T4.LANGUAGE_ID = ?
WHERE T1.SYS_TYPE1 IN (?, ?, ?)
    AND ( T1.triStatusCL IN ('Closed',
    'Completed',
    'Cancelled') )
    AND T1.SPEC_ID IN (SELECT ASS_SPEC_ID
FROM IBS_SPEC_ASSIGNMENTS
WHERE SPEC_ID IN (SELECT T1.SPEC_ID AS T1_SPEC_ID
FROM T_TRIRESOURCE T1
WHERE T1.SYS_GUIID = ?
    AND T1.SPEC_ID IN (SELECT ASS_SPEC_ID
FROM IBS_SPEC_ASSIGNMENTS
WHERE SPEC_ID = ?
    AND ASS_TYPE = ?)
    AND T1.SYS_OBJECTID > ?
    AND (T1.SYS_ORGNAMEOBJID IN (select SPEC_ID
FROM T_ORGANIZATION
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Organizations\Customers\ABC' or TRIPATHSY like '\Organizations\Customers\ABC\%')) OR T1.SYS_ORGNAMEOBJID IN (select SPEC_ID
FROM T_ORGANIZATION
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Organizations\Customers\DEF' or TRIPATHSY like '\Organizations\Customers\DEF\%')) OR T1.SYS_ORGNAMEOBJID IN (select SPEC_ID
FROM T_ORGANIZATION
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Organizations\Providers\ABC' or TRIPATHSY like '\Organizations\Providers\ABC\%')) OR T1.SYS_ORGNAMEOBJID IS NULL OR T1.SYS_ORGNAMEOBJID = ?)
    AND (T1.SYS_GEOGRAPHYNAMEOBJID IN (select SPEC_ID
FROM M_GEOGRAPHY
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Geography\EMEA\Northern Europe\Norway' or TRIPATHSY like '\Geography\EMEA\Northern Europe\Norway\%')) OR T1.SYS_GEOGRAPHYNAMEOBJID IS NULL OR T1.SYS_GEOGRAPHYNAMEOBJID = ?))
    AND ASS_TYPE = ?)
    AND T1.SYS_OBJECTID > ?
    AND (T1.SYS_ORGNAMEOBJID IN (select SPEC_ID
FROM T_ORGANIZATION
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Organizations\Customers\ABC or TRIPATHSY like '\Organizations\Customers\ABC\%')) OR T1.SYS_ORGNAMEOBJID IN (select SPEC_ID
FROM T_ORGANIZATION
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Organizations\Customers\DEF' or TRIPATHSY like '\Organizations\Customers\DEF\%')) OR T1.SYS_ORGNAMEOBJID IN (select SPEC_ID
FROM T_ORGANIZATION
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Organizations\Providers\ABC' or TRIPATHSY like '\Organizations\Providers\ABC\%')) OR T1.SYS_ORGNAMEOBJID IS NULL OR T1.SYS_ORGNAMEOBJID = ?)
    AND (T1.SYS_GEOGRAPHYNAMEOBJID IN (select SPEC_ID
FROM M_GEOGRAPHY
WHERE SYS_OBJECTID > 0
    AND (TRIPATHSY = '\Geography\EMEA\Northern Europe\Norway' or TRIPATHSY like '\Geography\EMEA\Northern Europe\Norway\%')) OR T1.SYS_GEOGRAPHYNAMEOBJID IS NULL OR T1.SYS_GEOGRAPHYNAMEOBJID = ?)
ORDER BY UPPER(T1.triIdTX)