The SMP console takes a long time to load for users of non Administrative roles when selecting saved searches
Article ID: 164116
Updated On:
Products
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
Feedback
