Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] Z galerii mitów: Ownership Chains

W niedzielę, 31 maja, miałem przyjemność poprowadzić prezentację pt. "Błogosławieństwa i przekleństwa procedur składowanych" na konferencji CodeCamp w Krakowie. W czasie prezentacji okazało się, że nadal w środowisku programistów i ludzi pracujących z SQL Serverem żyje mit, który mówi: "uprawnienie do wykonywania procedury składowanej umożliwia dostęp do tabeli używanej w procedurze bez nadawania uprawnienia bezpośrednio na samej tabeli wtedy, gdy tabela i procedura znajdują się w tym samym schemacie". Mit mówi o szczególnym przypadku tego, co nazywa się po angielsku ownership chains. Sama nazwa mechanizmu sugeruje, jakie pojęcie jest kluczem działania. Pojęciem tym jest "właściciel" (ang. owner).

W SQL Server 2005 Microsoft wprowadził na dobre pojęcie schematu (ang. schema) jako logicznego pojemnika na obiekty w bazach danych. Co prawda pojęcie to istniało w SQL Server 2000, ale w tamtej wersji w zasadzie pozostawało bezużyteczne i użytkownicy operowali bezpośrednio pojęciem "właściciela obiektu". W SQL Server 2005 i 2008 pojęcie właściciela obiektu zostało niejako zniesione. Pojawił się za to "właściciel schematu". I tu jest pies pogrzebany. Zobaczmy to na przykładzie (przykład z mojej prezentacji na CodeCamp):

USE AdvdentureWorks;
GO

-- Tworzymy nowego usera w bazie
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Jasio')
DROP USER Jasio;
GO
CREATE USER Jasio WITHOUT LOGIN;
GO

-- Próba wykonania zapytania przez usera kończy się błędem:
-- Msg 229, Level 14, State 5, Line 1
-- The SELECT permission was denied on the object 'SalesOrderDetail',
-- database 'AdventureWorks', schema 'Sales'.
EXECUTE AS USER='Jasio';
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = 710;
GO
REVERT;

-- Tworzymy procedurę składowaną wykonującą zapytanie z parametrem
-- WAŻNE: procedura jest w schemacie dbo, a tabela w schemacie Sales
IF OBJECT_ID('dbo.usp_GetOrderDetailsByProduct', 'P') IS NOT NULL
DROP PROC dbo.usp_GetOrderDetailsByProduct;
GO
CREATE PROC dbo.usp_GetOrderDetailsByProduct
@ProductID int
WITH RECOMPILE
AS
SET NOCOUNT ON;
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;
GO

-- Dajemy userowi uprawnienia do wykonywania procedury
GRANT EXECUTE ON OBJECT::dbo.usp_GetOrderDetailsByProduct TO Jasio;
GO

-- User może dzięki procedurze dostać się do tabeli
-- mimo, że tabela i procedura są w innych schematach!
EXECUTE AS USER='Jasio';
GO
EXEC dbo.usp_GetOrderDetailsByProduct 710;
GO
REVERT;

-- Tworzymy nowego usera o nazwie Stasio
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Stasio')
DROP USER Stasio;
GO
CREATE USER Stasio WITHOUT LOGIN;
GO

-- User Stasio nowym właścicielem schematu Sales
ALTER AUTHORIZATION ON SCHEMA::Sales TO Stasio;
GO

-- Jasio nie ma dostępu do tabeli Sales.SalesOrderDetail
-- przez procedurę dbo.usp_GetOrderDetailsByProduct,
-- ponieważ schematy dbo i Sales mają innych właścicieli
EXECUTE AS USER='Jasio';
GO
EXEC dbo.usp_GetOrderDetailsByProduct 710;
GO
REVERT;

-- Sprzątamy
ALTER AUTHORIZATION ON SCHEMA::Sales TO dbo;
DROP USER Stasio;
DROP USER Jasio;
DROP PROC dbo.usp_GetOrderDetailsByProduct;
GO


A zatem, by obalić mit, podsumujmy: uprawnienie do wykonywania procedury składowanej umożliwia dostęp do tabeli używanej w procedurze bez nadawania uprawnienia bezpośrednio na samej tabeli wtedy, gdy tabela i procedura znajdują się w schematach, których właścicielem jest ten sam user. Taka sama zasada dotyczy oczywiście widoków i tabel w nich wykorzystywanych.

Jeżeli macie propozycję jakiegoś mitu, chętnie pochylę się nad każdą obiegową opinią czy stwierdzeniem. A może za jakiś czas Maciek Pilecki zrobi sesję Mity w SQL Server cz. II ;-)

[EDIT] Tak się skupiłem na technikaliach, że zapomniałem napisać o CodeCamp :D W dwóch słowach: super konferencja! Świetna organizacja i znakomicie prowadzący imprezę Szymon Kobalczyk odbierający każdemu prelegentowi slajd "About me" ;-) Za rok też tam pewnie pojadę, tym razem może na nieco dłużej. [/EDIT]

Opublikowane 1 czerwca 2009 09:46 przez brejk

Komentarze:

# re: [PL] Z galerii mitów: Ownership Chains

1 czerwca 2009 13:26 by Marcin Z

Hej, swietny wpis. Wkradla sie jednak mala literowka, najprawdoposobniej za przyczyna copy/paste`a:

-- Tworzymy nowego usera o nazwie StasioIF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Jasio')  DROP USER Stasio;

w select, chyba chodzi o usera Stasio, nie Jasio.

Pzdr.

# re: [PL] Z galerii mitów: Ownership Chains

1 czerwca 2009 13:45 by brejk

@Marcin Z: Dzięki. Copy paste odstawiłem w tym miejscu i został Jasiek ;-)

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek