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.
Platform 8.0 HF4
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.
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.
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 )
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 */
I opened ETrack 4052264 to report this behavior but it was closed again stating that it (the function) could not be optimized any more.
Subscribing will provide email updates when this Article is updated. Login is required.