Usuwanie uprawnień za pomocą bazy gdy ze strony SharePoint się nie da + krótka historia o ograniczeniach

SharePoint mimo wielu (no kilku) swoich zalet, ma też dużo wad. Jedną z nich napewno są developerzy, którzy zabierają się do pisania kodu nie czytając dostępnych technicznych dokumentów na temat tworzenia rozwiązań na SharePoint.

Jednym z klasycznych błędów jest założenie, że lista może zawierać N elementów i wciąż będzie działała jak należy. Nie ma nic bardziej mylnego! SharePoint pozwala trzymać w jednym kontenerze do 2 tys. elementów – nie jest to stricte ograniczenie, bo do listy możecie dodać nawet 20-30 tysięcy elementów, ale jest to ograniczenie, po którym MS nie gwarantuje, że lista będzie działać wydajnie. Ba, nawet jest udukomentowany test, który pokazuje że wraz ze wzrostem elementów na liście wydajność takiej listy maksymalnie spada. Zachęcam więc wszystkich do lektury, bo warto o tym wiedzieć. Także warto wiedzieć, że w takich przypadkach nie wykożystuje się iteratora foreach na wszystkich elementach listy, tylko np.: przez SPQuery lub PortalSiteMapProvider.

Jednakże wystarczy tylko zrozumieć co to jest kontener, i automatycznie na liście można przechowywać do 5 milionów elementów bez wpłuwu na jej wydajność. A więc, co to takiego jest kontener? Kontener na liście to nic innego jak folder. Czyli, nasza lista może składać się z 2K kontenerów, które zawierają 2K kontenerów, które zawierają 2K elementów itd. Dzięki takiemy podejściu strona z listą i elementami na liście powinna w miarę szybko i sprawnie się renederować. Także dostęp poprzez SPList.Items będzie szybszy i wydajniejszy.

Ale dlaczego piszę o tym wszystkim w kontekście uprawnień? Bo z nimi jest dosłownie tak samo, z tą różnicą, że jeżeli przekroczycie magiczne 2K na Site to SharePoint daje wam krzyżyk na drogę. Kontenerem w tym wypadku jest grupa SharePoint, która może zawierać zarówno użytkowników jak i grupy AD. W obydwu przypadkach liczba grup i użytkowników w danym kontenerze nie powinna przekraczać 2K. Jak tylko przekroczy ona tą magicznę liczbę, to jakakolwiek próba usunięcia, dodania czy nawet modyfikacji uprawnienia dla użytkownika na danej stronie w zależności od maszyny (zwykły 8 corowy Xeon i 9 GB ramu należy w tym przypadku do słabego sprzętu) na której stoi SharePoint może zakończyć się timeoutem, ba może też spowodować deadlocki na bazie danych.

Dlaczego tak się dzieje? Jak tylko użytkownik zaloguje się na stronę SharePoint po raz pierwszy, jego wpis trafia do bazy danych UserInfo, która to zawiera takie dane jak login, nazwę, ID itp. Jest to tak naprawdę podstawa, to tam są trzymane wszystkie dane dot. użytkownika. Następnie SharePoint dodaje użytkownika do tabeli RoleAssgiments w której przypisuje użytkownika do odpowiedniej roli na odpowiednim site i web. Jak już to wszystko zostało dodane, SharePoint aktualizuje tabelę Perms i jej Acl dla odpowiedniego Scope. Do póki bawimy się uprawnieniamy na SharePoint w Site/Web Scope to wszystko jest wporządku i raczej nie przekroczy się liczby 2K – skoro działa się tak ogólnie to można potworzyć grupy i nadać uprawnienia grupom. Problem zaczyna się gdy na pewnej liście potrzebujemy unikatowych uprawnień per użytkownik.

Dodanie unikatowe uprawnienia dla użytkownika dla kontrektego elementu, powoduje, że SharePoint propaguje to uprawnienie do listy i następnie do Web. Będzie ono figurowało jako Limited Access, ale wpisy się dokonają. Tym razem zostanie dodany wpis w Perms dla elementu, zostanie zaktualizowany perms dla listy i dla webu, dodatkowo nowy wpis pojawi się w RoleAssigment by zaznaczyć że dla danego Scope użytkownik ma inne uprawnienia. Teraz jeżeli trochę sobie policzymy, to wartości w Perms rosną proporcjonalnie do elmentów do których przypisujemy uprawnienia. Jeżeli teraz nadamy uprawnienia 2K osobom, to nasze tabele RoleAssigment i Perms rozrosną się o łączną wartość okolo 4K elmentów, a jeżeli mamy włączone dziedziczenie, to z propaguje to się także na inne listy. Czyli 4K jest minimalnym zwrotem liczby elementów w bazie danych. Jeżeli teraz to pociągniemy dalej to liczby bardzo szybko mogą sięgać milionów rekordów. Mimo, że SQL Server poradzi sobie z taką liczbą elementów to SharePoint już nie, a tym bardziej nie poradzą sobie procedury wbudowane od SharePoint. Spowodowane jest to tym, że SharePoint w procedurze proc_SecRemovePrincipalFromOneScope wywołuje za pomocą kursora proc_SecUpdateAclForScope, która za pomocą kursora aktualizuje wszystkie RoleAssgiments spełniające warunki SiteId, WebId i ScopeId. Zaś proc_SecRemovePrincipalFromOneScope jest wywoływana w kursorze w proc_SecRemovePrincipalFromScope, która robi iteracje po wszystkich Perms. I teraz przy dużej liczbie elementów w tabeli RoleAssgiments dla danego Site i Web, takie proste usunięcie jednego użytkownika z listy uprawnień może trwać ponad godzinę jak nie więcej (ja w ramach testów rezygnowałem po 40 minutach czekania). Nie wspominając, że wykonanie tego ze strony po 4-5 minutach spowoduje timeout. Następnie, przeważnie klikamy by usunać użytkownika jeszcze raz itd. aż baza danych się nam wykrzaczy :)

Wyjściem z sytuacji jest usuwanie ręczne użytkowników z bazy danych, a dokładnie mówiąc, usunięcie użytkowników z RoleAssigments dla odpowiednich scope – scope ID można popbrać poprzez np.: zapytanie:

SELECT
      ScopeId,
      ScopeUrl
FROM
      Perms
WHERE
      SiteId = @SiteId AND
      WebId = @WebId

Lub porpzez:

SELECT
      ScopeId,
      ScopeUrl
FROM
      Perms
WHERE
      ScopeUrl = 'strona/itp/itd'

Dzięki temu, dowiemy się jakie istnieją ScopeId, z których użytkownicy powinni zostać usunięci. Następnie znajać już ScopeID wykonujemy operacje DELETE na RoleAssigments.

Możemy także zapytanie połaczyć i stworzyć coś takiego:

SELECT
      ScopeId INTO #tempPermsGutek
FROM
      Perms
WHERE
    SiteId = '79419D7E-C83E-4520-8D3F-825DB3C28790' AND
    WebId = '1588250E-4FEB-4369-B831-5C011B871CAF' AND
    Perms.ScopeUrl IN
      (
            SELECT
                  ScopeUrl
            FROM
                  Perms
            WHERE
                  SiteId = '79419D7E-C83E-4520-8D3F-825DB3C28790'AND
                  (
                        ScopeUrl LIKE 'rp/Lists/Submi%'
                        OR
                        ScopeUrl LIKE 'rp/Lists/Task%'
                  )
      )
 
DELETE
      RoleAssignment
FROM
    RoleAssignment
      INNER JOIN #tempPermsGutek ON RoleAssignment.ScopeId = #tempPermsGutek.ScopeId
WHERE
    RoleAssignment.SiteId = '79419D7E-C83E-4520-8D3F-825DB3C28790'
    AND
      RoleAssignment.PrincipalId NOT IN
      (
            -- NUMERY ID OSOB, KTORYCH NIE CHCEMY KASOWAÆ
      )

Kiedy już mamy pewność, że tabela RoleAssigments została oczyszczona – to znaczy zawiera odpowiednio mało rekordów (9 milionów w moim przypadku było mało), wykonujemy następujący kod:

DECLARE @scopeId uniqueidentifier
DECLARE cur CURSOR
FOR
      SELECT
            ScopeId
      FROM
            #tempPermsGutek
 
OPEN cur
 
FETCH NEXT FROM cur INTO @scopeId
WHILE (@@FETCH_STATUS = 0)
BEGIN
      EXEC proc_SecUpdateAclForScope '79419D7E-C83E-4520-8D3F-825DB3C28790' , @scopeId
      FETCH NEXT FROM cur INTO @scopeId
END
 
CLOSE cur
DEALLOCATE cur

Teraz by mieć pewność, że nie zostawiliśmy żadnego smordu w bazie danych, a użytkoników, których kasowaliśmy kasowaliśmy z poziomu Web, upewniamy się, że nie widnieją oni w tabelach:

• EventSubsMatches
• ImmedSubscriptions
• SchedSubscriptions
• Personalization
• WebParts
• WebMembers

Jeżeli widnieją, to wykonujemy następujące zapytanie:

DECLARE @SiteId uniqueidentifier,
DECLARE @WebId uniqueidentifier,
DECLARE @WebScopeId uniqueidentifier,
DECLARE @ScopeId uniqueidentifier,
DECLARE @PrincipalId int
 
DELETE FROM
    EventSubsMatches
WHERE
    SubId
IN (
    SELECT ImmedSubscriptions.Id FROM
        Webs, ImmedSubscriptions
    WHERE
        ImmedSubscriptions.SiteId = Webs.SiteId AND
        ImmedSubscriptions.WebId = Webs.Id AND
        ImmedSubscriptions.UserId = @PrincipalId AND
        Webs.SiteId = @SiteId AND
        Webs.FirstUniqueAncestorWebId = @WebId
    UNION
    SELECT SchedSubscriptions.Id FROM
        Webs, SchedSubscriptions
    WHERE
        SchedSubscriptions.SiteId = Webs.SiteId AND
        SchedSubscriptions.WebId = Webs.Id AND
        SchedSubscriptions.UserId = @PrincipalId AND
        Webs.SiteId = @SiteId AND
        Webs.FirstUniqueAncestorWebId = @WebId
)
DELETE
    ImmedSubscriptions
FROM
    Webs, ImmedSubscriptions
WHERE
    ImmedSubscriptions.SiteId = Webs.SiteId AND
    ImmedSubscriptions.WebId = Webs.Id AND
    ImmedSubscriptions.UserId = @PrincipalId AND
    Webs.SiteId = @SiteId AND
    Webs.FirstUniqueAncestorWebId = @WebId
DELETE
    SchedSubscriptions
FROM
    Webs, SchedSubscriptions
WHERE
    SchedSubscriptions.SiteId = Webs.SiteId AND
    SchedSubscriptions.WebId = Webs.Id AND
    SchedSubscriptions.UserId = @PrincipalId AND
    Webs.SiteId = @SiteId AND
    Webs.FirstUniqueAncestorWebId = @WebId
DELETE
    Personalization
FROM
    Personalization
INNER JOIN
    Docs
ON
    Personalization.tp_PageUrlID = Docs.Id
WHERE
    Personalization.tp_SiteId = @SiteId AND
    Personalization.tp_UserID = @PrincipalId AND
    Docs.SiteId = @SiteId AND
    Docs.WebId = @WebId
DELETE
    WebParts
FROM
    WebParts INNER JOIN Docs ON WebParts.tp_PageUrlID = Docs.Id
WHERE
    WebParts.tp_SiteId = @SiteId AND
    WebParts.tp_UserID = @PrincipalId AND
    Docs.SiteId = @SiteId AND
    Docs.WebId = @WebId
DELETE FROM
    WebMembers
WHERE
    WebId = @WebId AND
    UserId = @PrincipalId

I już na samym końcu wywołujemy:

EXEC proc_SecUpdateSiteLevelSecurityMetadata @SiteId , 1, 0
EXEC proc_GetWebIdAuditMask @SiteId, @WebId

Teraz możemy ponownie rozkosztować się stroną SharePoint, aż do następnego razu :)

Cały kod, który tutaj wkleiłem postaram się ubrać w odpowiednie procedury wbudowane, dzięki czemu będzie z niego łatwiej skożystać.