Remedy - Server - Performance issue with Dynamic Permissions introduced by Row Level Security - ARS 9.1.03 / 9.1.04

Version 3
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    Remedy AR System Server v.9.1.03 / 9.1.04



    PROBLEM:

    Our application uses Dynamic Permissions and after upgrade to 9.1.03 the SQL query to the database multiplies RLS access by the queries for every Field which has dynamic group access, not only for RequestID of the entry but for all fields they are in the query . Some of the queries in the tablefield has this part multiplied 40 times.

    In the SQL-log the following is observed as an example:
    ...................................
    WITH AR_SQL_Alias$1 AS (SELECT T1011.C2, T1011.C1, ROW_NUMBER() OVER (ORDER BY T1011.C1 ASC) AS 'AR_RowNumber_Alias$1' FROM T1011 
    WHERE ((('insingh1' = T1011.C8) OR (T1011.C7 = 0)) 

    AND ((T1011.C112 LIKE '%;'insingh1';%') OR (T1011.C112 LIKE '%;0;%') OR (T1011.C112 LIKE '%;2837;%')) 
    AND ((T1011.C112 LIKE '%;'insingh1';%') OR (T1011.C112 LIKE '%;0;%') OR (T1011.C112 LIKE '%;2837;%')) 
    AND ((T1011.C112 LIKE '%;'insingh1';%') OR (T1011.C112 LIKE '%;0;%') OR (T1011.C112 LIKE '%;2837;%')))) 

    SELECT AR_SQL_Alias$1.C2, AR_SQL_Alias$1.C1 FROM AR_SQL_Alias$1 WHERE (AR_SQL_Alias$1.AR_RowNumber_Alias$1 BETWEEN 0 AND 1001)
    ...................................


    The row level qualification should not be repetitive.
    ....................................
    WITH AR_SQL_Alias$1 AS (SELECT T1011.C2, T1011.C1, ROW_NUMBER() OVER (ORDER BY T1011.C1 ASC) AS 'AR_RowNumber_Alias$1' FROM T1011 
    WHERE ((('insingh1' = T1011.C8) OR (T1011.C7 = 0)) 

    AND ((T1011.C112 LIKE '%;'insingh1';%') OR (T1011.C112 LIKE '%;0;%') OR (T1011.C112 LIKE '%;2837;%')))) 
    SELECT AR_SQL_Alias$1.C2, AR_SQL_Alias$1.C1 FROM AR_SQL_Alias$1 WHERE (AR_SQL_Alias$1.AR_RowNumber_Alias$1 BETWEEN 0 AND 1001)
    ....................................


    CAUSE:

    Defect: SW00543351


    SOLUTION:

    This behavior is reported in the defect:
    ........................
    SW00543351 - Performance issue with Dynamic Permissions introduced by Row Level Security
    ........................


    There is a Hot Fix available for version 18.05.
    Please contact BMC Support to provide this Hot Fix to you.
    Note: You will need to be currently on version 18.05 in order to apply this Hot Fix.


    Article Number:

    000151073


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles