search cancel

The SMP console takes a long time to load for users of non Administrative roles when selecting saved searches

book

Article ID: 164116

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

Scenario: 

The SMP was recently upgraded to 8.0 HF4. 

When users who are members of the security role “Symantec Level 1 Workers” open the console and go to “Manage > Computers “ and select one of the saved searches, under “Favorites” such as “Installed Agent” or “New Computers” the right pane panel take more than one minute to load.

If the user is added to Symantec Administrators the right pane loads very quickly.

The problem was not noticed, or did not seem to exist prior to upgrading to HF4.

Sometimes the process entirely times out and throws an error.

Environment

Platform 8.0 HF4

Cause

When rendering the results from save searches the stored procedure “spAC_FilterAndGetResourcesDataWithTrustee” is invoked and passed in the list of computer candidates as well as the trustees that need to be evaluated for permissions.

Example:

DECLARE @hasMixedResources__auto AS bit;

EXECUTE spAC_FilterAndGetResourcesDataWithTrustee @resourceGuidList=N'383ec855-4c40-4723-bec5-088de073d481_8d4d6d5-7f79-4bae-9795-08afcfa7d578_bcab663-0876-44ed-a5c6-1e762ac119bd_bd3b650-0051-4105-9f48-1f55ce32d142_22ddbc5-cada-47e2-a553-25c57862df89_2ea33a0-10fa-42c0-9a1e-2c51edab6c54_3345c72-cd6e-447e-afba-366cf85f2a00_af56484-b7a7-4f5a-9e13-64ebf53016e3_eb995eb-6a82-4574-8bae-67bd207bbf2b_36cf6e9-66d0-409c-aa77-777f35fa1891_5150f6b-8ae6-4f32-b2ea-7d35f5edf4b5_8e362f2-6810-4ed0-aabc-9358420b7931_ad426f0-d023-4340-a171-93d708840450_cdf613c-cac3-46d6-9c46-9c886bf1a372_509f150-18dd-440f-a5a1-beca7ddbb1f9_40f0338-0cbe-4c91-a3d3-c21028d5e798_8c7af7b-27d2-486e-800c-cb4ae85e304b_cc5b221-6e1e-4402-b4fc-e7d2b880279f_76353e1-309d-4139-938f-e94b2b5e0349_3fc7a9b-5e89-407a-913a-f0605efa5455_24dd688-178c-4418-b3ca-f658302ae116_cd79219-f9a1-4c1b-9d39-fa66e051ceea', @typeGuidList=N'493435f7-3b17-4c4c-b07f-c23e7ab7781f_c3cb3bb-fee9-48df-804f-90856198b600_b5442be-9f23-4f2b-8fe6-e65fe07ea341', @scopingResourceType='493435f7-3b17-4c4c-b07f-c23e7ab7781f', @trusteeList=N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{7454724D-3900-4FD7-A5E5-8A1772D6F89E},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}', @sortOrder=1, @isAdmin=1, @[email protected]__auto OUTPUT

There is a section within the stored procedure (line 35-46) where, if the user is not a member of an administrative role, all of the candidates are inserted into a table variable and passed into the function “fnGetTrusteeScopedResourcesByType” to vet the user’s security permissions to each of the items being evaluated.

The table variable does not use an index and therefore the contents are being compared much like a flat file would be.  If there are enough items in the table (hundreds of thousands, or millions) it will become much slower.

Resolution

WORKAROUND:  When  fnGetTrusteeScopedResourcesByType, is modified (attached to this article) to comment out the check for Symantec Administrators membership, and the ELSE statement that drops non Symantec Administrators into—then the saved search results loaded very quickly in the right pane regardless of what security role is used.  But it also disables security scoping in that area of the console too (which is minor).


ALTER FUNCTION [dbo].[fnGetTrusteeScopedResourcesByType]
(
    @resourceTypeGuid   uniqueidentifier,
    @trustees           nvarchar(max),
    @includeDerived     bit = 1
)
RETURNS @resources TABLE ( ResourceGuid uniqueidentifier PRIMARY KEY )

AS

BEGIN

    DECLARE       @resourceTypeTable TABLE (ResourceTypeGuid UNIQUEIDENTIFIER)
    INSERT  INTO  @resourceTypeTable
        SELECT    DISTINCT rth.ResourceTypeGuid
            FROM  ResourceTypeHierarchy rth
            WHERE rth.ResourceTypeGuid = @resourceTypeGuid
            OR  ( @includeDerived = 1 AND rth.BaseResourceTypeGuid = @resourceTypeGuid )

    DECLARE  @TrusteeGuids GuidTableType
    INSERT
        INTO @TrusteeGuids
        SELECT   DISTINCT st.TrusteeGuid
            FROM dbo.fnListToTable( @trustees, DEFAULT ) fn
            JOIN sec_Trustee st WITH (nolock) ON st.Trustee = fn.nstr

   /* IF EXISTS ( SELECT TOP 1 1 FROM @TrusteeGuids WHERE Guid = '2E1F478A-4986-4223-9D1E-B5920A63AB41' )  */

        INSERT  INTO  @resources
            SELECT    DISTINCT sm.ResourceGuid
                FROM  ScopeMembership          sm
                JOIN  ItemResourceType         rt ON rt.[Guid] = sm.ResourceGuid
                JOIN  @resourceTypeTable       tt ON tt.ResourceTypeGuid = rt.ResourceTypeGuid
                JOIN  sec_Entity               se ON se.EntityGuid = sm.ScopeCollectionGuid

/*
  ELSE
        INSERT  INTO  @resources
            SELECT    DISTINCT sm.ResourceGuid
                FROM  ScopeMembership          sm
                JOIN  ItemResourceType         rt ON rt.Guid = sm.ResourceGuid
                JOIN  @resourceTypeTable       tt ON tt.ResourceTypeGuid = rt.ResourceTypeGuid
                JOIN  sec_EntitySource         ss ON ss.EntityGuid = sm.ScopeCollectionGuid
                JOIN  sec_EntityTrustee        st ON st.EntityGuid = ss.SourceGuid
                JOIN  @TrusteeGuids            tg ON tg.Guid = st.TrusteeGuid
                WHERE st.Permission >=  0x2000000000000000
*/

    RETURN

END

Attachments

fnGetTrusteeScopedResourcesByType_No_Scoping.sql get_app