SQL Server objects ownership oraz Ownership Chain

SQLServer_OwnershipChain_000

Widoki bardzo często dają możliwość wyświetlenia odpowiednich danych określonym użytkownikom. Podobną rolę mogą spełniać procedury składowane czy też tabelaryczne funkcje użytkownika. Jak można przypuszczać obiekty te bardzo często są wykorzystywane jako mechanizm bezpieczeństwa co wydaje się naturalne i nie ma w tym nic złego. Czy jednak nadanie praw do odpytania widoku czy wykonania procedury gwarantuje, że zwrócone zostaną dane? Oczywiście, że nie! W grę wchodzi tutaj posiadanie uprawnień właściciela oraz mechanizm Ownership Chain o których chciałbym dziś napisać parę słów.

Na samym początku troszeczkę teorii jeśli chodzi o prawa właściciela oraz schematy ze względu na fakt, iż są one ze sobą bardzo mocno powiązane. Każdy z nas wie, że schematy to nic innego jak kontenery na obiekty, które z punktu widzenia bezpieczeństwa przede wszystkim mają za zadanie uprościć zarządzanie uprawnieniami. Bardzo często obiekty zawarte w określonym schemacie posiadają uprawnienia i właściciela, który jest dziedziczony ze schematu właśnie – z takimi przypadkami w moim odczuciu spotykamy się w przygniatającej większości przypadków. To zdecydowanie normalne działanie mechanizmu zabezpieczeń, że schemat posiada swojego właściciela, który z kolei jest dziedziczony do każdego obiektu wewnątrz schematu. Technicznie rzecz biorąc możliwe jest tworzenie obiektów z właścicielem innym niż właściciel schematu z kompletnie innymi uprawnieniami jednakże wiąże się to z dodatkowym poziomem skomplikowania związanym z utrzymaniem dostępów.

Do uprawnienia właściciela wrócimy w dalszej części artykułu – na ten moment przejdźmy dalej. Kolejnym tematem zawartym w tytule niniejszego posta jest ownership chain. Sposób działania tego mechanizmu najłatwiej rozpatrywać w kontekście widoków (demonstracje oprzemy o ten typ obiektów ale inne obiekty działają w analogiczny sposób) to schemat ich działania wygląda następująco:

Kolorem niebieskim oznaczone są obiekty, których właścicielem jest jest userA, kolorem żółtym oznaczone zostały obiekty których właścicielem jest userB. Omawiany mechanizm działa w taki sposób, że jeżeli ktokolwiek odpyta np. Widok A do którego posiada prawa do odczytu to uprawnienia do innych obiektów nie są sprawdzane chyba, że posiadają one innego właściciela. Dlatego też w naszym przykładzie jeśli:

  • ktoś odpyta Widok A to sprawdzone zostaną również dostępy do Tabel A, Tabela B, Tabela C
  • ktoś odpyta Widok B to sprawdzone zostaną dostępy do Tabela A oraz Tabela B oraz Tabela C
  • ktoś odpyta Widok C to sprawdzone zostaną dostępy do Tabela C

Te wszystkie dostępy zostaną sprawdzone tylko z tego powodu, że właścicielem obiektów źródłowych (tabel) jest inny użytkownik niż widoku na którym mamy uprawnienie do odczytu. Sprawdźmy to na przykładzie – na samym początku stworzymy obiekty, które będą służyć nam w demonstracji:

Wstawmy również po jednym wierszu do każdej z tabel:

Ostatnim obiektem jaki stworzymy są widoki opierające się na stworzonych przed chwilą tabelach.

Cała definicja jest zgodna z tym co zostało przedstawione na grafice. W tym miejscu możemy zadać sobie pytanie kto jest właścicielem tych obiektów?  Sprawdźmy to! W sieci możemy znaleźć informacji aby użyć procedury systemowej sp_table – zróbmy to zatem:

W kolumnie TABLE_OWNER mamy jasne wskazanie, że właścicielem jest dbo. Co jeżeli chcemy sprawdzić właścicieli każdej tabeli? Wywołajmy sp_tables bez żadnego parametru:

Sposób ten jest jednak problematyczny bo TABLE_OWNER w tym miejscu to tak naprawdę nazwa schematu, a nie ownera co nie zawsze jest tożsame! Na ten moment nic nie zmienialiśmy więc otrzymany rezultat jest prawidłowy ale w dalszej części postaram się Wam przedstawić dowód na potwierdzenie mojej tezy i nieco lepszy sposób na osiągnięcie zamierzonego efektu.

Jak możecie zauważyć oprócz naszych tabel użytkownika dostaliśmy w rezultacie dużą liczbę obiektów systemowych. Póki co jednak informacja, którą chcieliśmy dostać znaleźliśmy – właścicielem wszystkich stworzonych przez nas obiektów jest dbo. Dlaczego właśnie ten użytkownik? Zazwyczaj właścicielem obiektu jest ten użytkownik będący właścicielem schematu w którym dany obiekt się znajduje ale oczywiście bez większego problemu możemy to zmienić. Właściciel obiektu to najwyższe możliwe uprawnienie na poziomie obiektu – mamy do dyspozycji również uprawnienie CONTROL jednakże nie jest ono tożsame z byciem właścicielem, które jest prawem niezbywalnym i nie może być ograniczone w przeciwieństwie właśnie do CONTROL. Ponadto użytkownik czy tez inaczej “principal” (czyli obiekt któremu możemy nadać uprawnienie), który jest właścicielem nie może zostać usunięty dopóki wszystkie obiekty do których posiada prawa właściciela nie zostaną przeniesione na innego “principala”.

Stwórzmy sobie schemat użytkownika testUser, który będzie właścicielem schematu test – aby nadać właściciela używamy słówka kluczowego AUTHORIZATION:

Nowa tabela test.TestTable dostała automatycznie właściciela testUser:

Niektórym w tym miejscu powinna się zapalić zielona lampka bo zwrócone dane są nieprawidłowe ale o tym za chwilę. Zmieńmy zatem właściciela na dbo – dokonamy tego komendą ALTER AUTHORIZATION (możecie znaleźć wiele źródeł mówiących o użyciu procedury sp_changeobjectowner jednakże jest to bardzo stara składnia i zalecane jest używanie ALTER):

Teraz sprawdźmy czy zmiana właściciela rzeczywiście miała miejsce – najpierw użyjmy sp_tables:

Coś poszło nie tak? Czyż nie powinniśmy zobaczyć tam dbo? Podpowiem, że wszystko działa w porządku, a stara procedura sp_tables odzwierciedla nazwę schematu, która kiedyś mogła być postrzegana jako właściciel tabeli… Tak to jest ze starymi obiektami w SQL Server – jaką zatem mamy alternatywę żeby sprawdzić właściciela? Wystarczy użyć tabel systemowych:

Wszystko wygląda w porządku – mamy pełną informację kto jest właścicielem oraz to czy uprawnienie to zostało odziedziczone czy przypisane wprost.

Wiemy już mniej więcej jak działa posiadanie uprawnień właściciela na obiekcie to teraz zobaczmy jak działa w praktyce ownership chain. Nadajmy uprawnienie do odczytu widoku dbo.vViewA dla użytkownika testUser:

Przełączmy kontekst na tego właśnie użytkownika i spróbujmy odpytać widok:

Rezultat został zwrócony bez większego problemu:

Zmieńmy teraz właściciela jednej z tabel z których korzysta widok na innego użytkownika  i spróbujmy wykonać tą samą operację raz jeszcze:

W rezultacie otrzymaliśmy błąd:

Do problemu doszło właśnie ze względu na ownership chain

Rozwiązaniem tego problemu jest oczywiście nadanie odpowiednich uprawnień do tabeli z innym właścicielem:

Część z Was może również pomyśleć, że rozwiązaniem może być przeniesienie uprawnień właściciela na principala będącego właścicielem widoku jednakże w tym miejscu warto pamiętać, że przy takiej operacji znikają wszelkie uprawnienia na danym obiekcie (pisałem o tym tutaj)! Także nie jest to najlepsze wyjście.

Na ten moment to by było na tyle. Temat z całą pewnością nie został wyczerpany ale w zamierzeniu miał właśnie zaznaczyć temat ze względu na krążące po sieci legendy i stare wpisy na blogach i forach, które niekoniecznie są prawidłowe. Mam nadzieję, że zawarte tutaj treści okazały się dla Was przydatne.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *