Widok sys.database_role_members

sys.database_role_members zawiera przypisania użytkowników do ról bazy danych.

Dwie kolumny, które opisują całe członkostwo

Ten widok katalogowy jest wyjątkowo oszczędny: zawiera zasadniczo dwie kolumny liczbowe — role_principal_id wskazującą rolę oraz member_principal_id wskazującą jej członka. Obie są identyfikatorami z widoku sys.database_principals, więc sensowny odczyt wymaga podwójnego złączenia tej samej tabeli: raz po stronie roli, raz po stronie członka. Efektem jest lista par w postaci nazwa użytkownika i nazwa roli, czytelna dla człowieka.

Widok działa w zakresie jednej bazy danych. Uruchomiony po USE innej bazy zwraca zupełnie inny zestaw wierszy, co bywa źródłem błędnych wniosków przy audycie instancji z kilkunastoma bazami — trzeba obejść je wszystkie, na przykład procedurą sp_MSforeachdb albo pętlą po sys.databases. Odpowiednikiem dla ról serwerowych jest osobny widok sys.server_role_members.

Sprawdzanie uprawnień w praktyce

Do szybkiego pytania o pojedynczą osobę wygodniejsza bywa funkcja IS_ROLEMEMBER, zwracająca 1, 0 albo NULL, gdy rola lub użytkownik nie istnieje. Trzeba jednak pamiętać o dziedziczeniu: role bazodanowe mogą być zagnieżdżane, więc użytkownik należący do roli własnej, która sama jest członkiem db_datareader, w prostym zapytaniu do widoku nie pojawi się przy db_datareader. Pełny obraz daje dopiero zapytanie rekurencyjne z klauzulą WITH, schodzące po kolejnych poziomach zagnieżdżenia. Sama zmiana członkostwa od nowszych wersji odbywa się poleceniem ALTER ROLE nazwa ADD MEMBER uzytkownik; starsze sp_addrolemember jest oznaczone jako wycofywane i lepiej go nie używać w nowych skryptach.

Co widać w tym widoku, gdy coś jest nie tak

Pierwszy typowy obraz to lawina członkostw w db_owner. Wystarczy, że przy wdrożeniu ktoś nie chciał drążyć, które tabele są potrzebne, i nadał najszersze uprawnienia całemu działowi — z perspektywy widoku wygląda to niewinnie, a oznacza, że każda z tych osób może usunąć bazę. Drugi to użytkownicy osieroceni po przeniesieniu bazy na inny serwer: wpis o członkostwie istnieje, użytkownik bazy istnieje, ale nie ma odpowiadającego loginu na poziomie instancji, więc nikt się nie zaloguje. Rozwiązaniem jest ponowne powiązanie identyfikatorów przez ALTER USER z klauzulą WITH LOGIN. Trzeci to pusta rola publiczna z nadanymi uprawnieniami — należą do niej wszyscy i nie da się nikogo z niej usunąć, więc przypadkowe nadanie tam prawa odczytu otwiera bazę dla każdego konta.

Uporządkowanie dostępu do baz systemów sprzedaży

Wdrażamy i serwisujemy programy handlowe pracujące na SQL Serverze, a przegląd ról jest stałym elementem takiej pracy. Zamieniamy nadania bezpośrednie na role odpowiadające stanowiskom, odcinamy zbędny dostęp do bazy z komputerów, na których działa tylko klient aplikacji, i naprawiamy osierocone konta po migracji serwera. Pracujemy również nocą, gdy przełączenie bazy nie może kolidować ze sprzedażą.