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

Here is an example of a query which uses org and geo filters at multiple levels of the query. You can see that the same filters are applied in each subquery in the association filters.

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)

We have received the following response for this RFE:

In our next platform release, we plan to implement an enhancement for geo/org redundant checks, though not exactly in the way that has been requested in the Idea. But these changes should help and we will further evaluate this as part of the performance enhancements we will continue to pursue.

We would like to understand what is meant by 'not exactly in the way that has been requested in the Idea' to ensure that it still meets our needs.

Thanks.

Hi Mark, You solution is great if you dont need to limit data access for your users...In this instance we do need to control that. Data Security is therefore a significant consideration. Some of our environments house data for multiple clients and provide access for both customer and service provider.
The primary issue here is that if a user is a member of multiple groups (CSR many customers) then the resulting SQL generated by the system for their queries is hugely complex within the WHERE clause and of course any sub-queries also contain their own complex WHERE clauses. As you know the security portion of the query is parsed as string literals whereas the rest of the query uses bind variables. Consequenty every user that has a differnt secuity configuration to another generates differnet SQL_ID making tuning in Oracle a challenge.
I'd be keen to explore any other idea you might have based on the above. This issue is really killing us at the moment

Hi all,

What we do is populate the top level Organization and Geography on the security group 'TRIRIGA Global Data Access Group' and have added this group to all of our Tririga Users. Think you may want to test this as a potential solution to your issue. All other security groups do not have Org or Geography hierarchies populated. Also if you ever migrate this group to Production you NEED to clear these two fields and manually repopulate them. Break a bunch of stuff if you don't. Don't forget.

Best, Mark

Any updates on the status of this RFE Lou?

Has this RFE been scheduled for a specific release? It has high impact on the clients.

Could you please provide an update as to what release this is targetted for?

Thank you for submitting your Idea. We think this is a good idea and will target this for inclusion in a future release.