SQL Server objects ownership oraz Ownership Chain

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:

use master
go

DROP DATABASE IF EXISTS OwnershipChain 
GO

CREATE DATABASE OwnershipChain
GO

USE OwnershipChain
GO

CREATE TABLE dbo.TableA
(ID INT)
GO

CREATE TABLE dbo.TableB
(ID INT)
GO

CREATE TABLE dbo.TableC
(ID INT)
GO

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

INSERT INTO dbo.TableA SELECT 1
GO
INSERT INTO dbo.TAbleB SELECT 2
GO
INSERT INTO dbo.TableC SELECT 3
GO

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

CREATE VIEW dbo.vViewC
AS
SELECT ID FROM TableC
GO

CREATE VIEW dbo.vViewB
AS
SELECT ID FROM dbo.vViewC
UNION ALL
SELECT ID FROM dbo.TableB
UNION ALL
SELECT ID FROM dbo.TableA
GO

CREATE VIEW dbo.vViewA
AS
SELECT ID FROM dbo.vViewB

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:

EXEC sp_tables @table_name='TableA'

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:

EXEC sp_tables

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:

CREATE USER testUser WITHOUT LOGIN

CREATE SCHEMA TEST AUTHORIZATION testUSer

CREATE TABLE test.TestTable
(id int)

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

EXEC sp_tables @table_name='TestTable'

Niektórym w tym miejscu powinna się zapalić czerwona 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):

ALTER AUTHORIZATION ON test.TestTable TO dbo;

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

EXEC sp_tables @table_name='TestTable'

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:

SELECT ss.NAME,
       st.NAME,
       Isnull(dp.NAME, dp2.NAME)  AS OwnerName,
       Iif(dp.NAME IS NULL, 1, 0) AS InheritedFromSchema
FROM   sys.tables AS st
       JOIN sys.schemas AS ss
         ON ss.schema_id = st.schema_id
       LEFT JOIN sys.database_principals AS dp
              ON dp.principal_id = st.principal_id
       LEFT JOIN sys.database_principals AS dp2
              ON dp2.principal_id = ss.principal_id

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:

GRANT SELECT ON dbo.vViewA TO testUser
GO

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

EXECUTE AS USER='testUser'

SELECT * FROM dbo.vViewA

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:

REVERT

ALTER AUTHORIZATION ON OBJECT::[dbo].[TableC] TO testUser2;

EXECUTE AS USER='testUser'

SELECT * FROM dbo.vViewA

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:

REVERT

GRANT SELECT ON dbo.TableC TO testUser
GO
EXECUTE AS USER='testUser'

SELECT * FROM dbo.vViewA

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.