Łańcuchy własności i przełączanie kontekstu w SQL Server
Opublikowano: 25 kwietnia 2007
Zawartość strony
Wstęp | |
Scenariusz | |
Konfiguracja środowiska | |
Utworzenie procedury składowanej w celu dostępu do danych | |
Uzyskanie dostępu do danych poprzez procedurę składowaną | |
Ponowne uruchomienie środowiska | |
Kompletny przykład | |
Inne zasoby, pomoc i informacje |
Wstęp
W niniejszym przewodniku przy pomocy przykładowego scenariusza opisano zasady działania zabezpieczeń serwera SQL związane z łańcuchami własności i przełączaniem kontekstu użytkownika. Więcej informacji o łańcuchach własności można znaleźć w artykule Ownership Chains (j.ang.). Więcej informacji o przełączaniu kontekstu można znaleźć w artykule Context Switching (j.ang.).
Uwaga:
Aby uruchomić kod opisany w niniejszym przewodniku należy zarówno skonfigurować zabezpieczenie trybu mieszanego, jak i zainstalować bazę danych AdventureWorks. Więcej informacji o zabezpieczeniach trybu mieszanego można znaleźć w artykuleAuthentication Mode (j.ang.). Więcej informacji o instalowaniu bazy danych AdventureWorks można znaleźć w artykule Installing AdventureWorks Sample Databases and Samples (j.ang.).
Do początku strony
Scenariusz
W przedstawionym scenariuszu dwaj użytkownicy potrzebują kont, aby uzyskać dostęp do danych zamówienia przechowywanych w bazie danych AdventureWorks. Wymagania są następujące:
- Pierwsze konto (TestManagerUser) musi umożliwiać przejrzenie wszystkich szczegółów każdego zamówienia.
- Drugie konto (TestEmployeeUser) musi umożliwiać przejrzenie numeru zamówienia, daty zamówienia, daty dostarczenia, identyfikatorów liczbowych produktów oraz liczby zamówionych i dostarczonych towarów dla poszczególnych zamówień, w przypadkach, gdy zamówienia zrealizowano częściowo.
- Wszystkie pozostałe konta muszą zachować aktualne uprawnienia.
Aby spełnić wymagania niniejszego scenariusza, przykład podzielony został na cztery części demonstrujące zasady działania łańcuchów własności i przełączania kontekstu:
- Konfiguracja środowiska
- Utworzenie procedury składowanej w celu uzyskania dostępu do danych według numeru zamówienia.
- Uzyskanie dostępu do danych poprzez procedurę składowaną.
- Ponowne uruchomienie środowiska.
Każdy fragment kodu w tym przykładzie wyjaśniony jest w danym wierszu. Na końcu przewodnika znajduje się kompletny przykładowy kod.
Do początku strony
Konfiguracja środowiska
Należy użyć SQL Server Management Studio i poniższego kodu, aby otworzyć bazę danych AdventureWorks, oraz użyć instrukcji CURRENT_USER Transact-SQL do sprawdzenia, czy jako kontekst wyświetlony jest użytkownik dbo.
USE AdventureWorks;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
Więcej informacji o instrukcji CURRENT_USER można znaleźć w artykule CURRENT_USER (Transact-SQL) (j.ang.).
Poniższy kod należy uruchomić z konta użytkownika dbo, aby utworzyć dwa konta użytkowników na serwerze i w bazie danych AdventureWorks.
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx'
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy'
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
Więcej informacji o instrukcji CREATE USER można znaleźć w artykule CREATE USER (Transact-SQL) (j.ang.). Więcej informacji o instrukcji CREATE LOGIN można znaleźć w artykule CREATE LOGIN (Transact-SQL) (j.ang.).
Poniższy kod zmienia właściciela schematu Purchasing na konto TestManagerUser. Pozwoli to temu kontu na wykonywanie wszystkich instrukcji Data Manipulation Language (DML) (jak na przykład uprawnień SELECT i INSERT) na zawartym w nim obiekcie. Jako że nie dotyczy to zezwoleń Data Definition Language (DDL), konto TestManagerUser otrzymuje bezpośrednio uprawnienia dostępu do tabel PurchaseOrderHeader i PurchaseOrderDetail, a także możliwość tworzenia procedur składowanych.
/* Zmiana właściciela schematu Purchasing na TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
/* Przyznanie kontu TestManagerUser uprawnień do używania tych obiektów przy pomocy opcji GRANT */
GRANT ALL
ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
TO TestManagerUser
WITH GRANT OPTION;
GO
GRANT ALL
ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
TO TestManagerUser WITH GRANT OPTION;
GO
/* Uwaga: DML współdziała dobrze z właścicielem schematu, ale nie z DDL. */
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
Więcej informacji o instrukcji GRANT można znaleźć w artykule GRANT (Transact-SQL) (j.ang.). Więcej informacji o schematach bazy danych można znaleźć w artykule Schemas (j.ang.). Więcej informacji o procedurach składowanych można znaleźć w artykule Stored Procedures (Database Engine) (j.ang.).
Do początku strony
Utworzenie procedury składowanej w celu dostępu do danych
Istnieją dwa sposoby na umożliwienie użytkownikowi zmiany kontekstu w ramach bazy danych: SETUSER lub EXECUTE AS. Użycie instrukcji SETUSER wymaga od wywołującego członkostwa w stałej roli serwera sysadmin albo używania konta dbo. EXECUTE AS wymaga uprawnień IMPERSONATE. Więcej informacji o tych instrukcjach można znaleźć w artykule EXECUTE AS vs. SETUSER (j.ang.).
Instrukcja EXECUTE AS w poniższym kodzie zmienia kontekst na TestManagerUser i tworzy procedurę składowaną pokazującą jedynie dane wymagane przez TestEmployeeUser. Aby spełnić wymagania, procedura składowana zaakceptuje jedną zmienną dla numeru zamówienia i nie będzie wyświetlać informacji finansowej, a klauzula WHERE ograniczy wyniki do zamówień zrealizowanych częściowo .
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Uwaga: Użytkownik wywołujący instrukcję EXECUTE AS musi mieć uprawnienia
IMPERSONATE na docelowym podmiocie zabezpieczeń */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
, b.ProductID, b.OrderQty, b.ReceivedQty
FROM Purchasing.PurchaseOrderHeader a
INNER JOIN Purchasing.PurchaseOrderDetail b
ON a.PurchaseOrderID = b.PurchaseOrderID
WHERE b.OrderQty > b.ReceivedQty
AND @ProductID = b.ProductID
ORDER BY b.ProductID ASC
END
GO
Obecnie TestEmployeeUser nie ma dostępu do żadnych obiektów bazy danych. Poniższy kod (nadal w kontekście TestManagerUser) przyznaje kontu użytkownika możliwość wysyłania kwerend do tabeli bazy poprzez procedurę składowaną.
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
Procedura składowana jest częścią schematu Purchasing, mimo że żaden schemat nie został bezpośrednio określony, jako że konto TestManagerUser jest domyślnie przydzielone do schematu Purchasing. Aby zlokalizować obiekty, użyć można informacji z katalogu systemowego, jak widać w poniższym kodzie.
SELECT a.name AS 'Schema'
, b.name AS 'Object Name'
, b.type AS 'Object Type'
FROM sys.schemas a
INNER JOIN sys.objects b
ON a.schema_id = b.schema_id
WHERE b.name = 'usp_ShowWaitingItems';
GO
Więcej informacji o katalogach systemowych można znaleźć w artykule Querying the SQL Server System Catalog (j.ang.).
Po ukończeniu tej części przykładu, kod zmienia kontekst z powrotem na dbo przy pomocy instrukcji REVERT.
REVERT;
GO
Więcej informacji o instrukcji REVERT można znaleźć w artykule REVERT (Transact-SQL) (j. ang.).
Do początku strony
Uzyskanie dostępu do danych poprzez procedurę składowaną
TestEmployeeUser nie ma uprawnień do używania obiektów bazy danych AdventureWorks innych niż login i prawa przydzielone roli bazy danych public. Poniższy kod zwraca błąd gdy TestEmployeeUser próbuje uzyskać dostęp do tabel bazy.
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* To nie zadziała */
SELECT *
FROM Purchasing.PurchaseOrderHeader;
GO
SELECT *
FROM Purchasing.PurchaseOrderDetail;
GO
Jako że obiekty, do których odwołuje się procedura składowana utworzona w poprzedniej sekcjinależą do TestManagerUser ze względu na własność schematu Purchasing, TestEmployeeUser może uzyskać dostęp do tabeli bazy poprzez procedurę składowaną. Poniższy kod, wciąż korzystający z kontekstu TestEmployeeUser, używa jako parametru zamówienia 952.
EXEC Purchasing.usp_ShowWaitingItems 952
GO
Do początku strony
Ponowne uruchomienie środowiska
Następujący kod używa instrukcji REVERT, aby przywrócić kontekst aktualnego konta do dbo i ponownie uruchomić środowisko.
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Do początku strony
Kompletny przykład
Poniżej zamieszczono kompletny przykładowy kod.
Uwaga:
Poniższy kod nie obejmuje dwóch oczekiwanych błędów demonstrujących niemożliwość pobierania danych z tabeli bazy przezTestEmployeeUser.
/*
Skrypt: UserContextTutorial.sql
Autor: Microsoft
Ostatnia aktualizacja: Books Online
Warunki: Wykonać jako DBO lub sysadmin w bazie danych AdventureWorks
Sekcja 1: Konfiguracja środowiska
*/
USE AdventureWorks;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Utworzenie użytkowników serwera i bazy danych */
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx'
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy'
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
/* Zmiana właściciela schematu Purchasing na TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
/* Przyznanie kontu TestManagerUser uprawnień do używania tych obiektów przy pomocy opcji GRANT */
GRANT ALL
ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader
TO TestManagerUser
WITH GRANT OPTION;
GO
GRANT ALL
ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail
TO TestManagerUser WITH GRANT OPTION;
GO
/* Uwaga: DML współdziała dobrze z właścicielem schematu ale nie DDL */
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
/*
Sekcja 2: Przełączanie kontekstu i tworzenie obiektów
*/
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Uwaga: Użytkownik wywołujący instrukcję EXECUTE AS musi mieć uprawnienia
IMPERSONATE na docelowym podmiocie zabezpieczeń */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
, b.ProductID, b.OrderQty, b.ReceivedQty
FROM Purchasing.PurchaseOrderHeader a
INNER JOIN Purchasing.PurchaseOrderDetail b
ON a.PurchaseOrderID = b.PurchaseOrderID
WHERE b.OrderQty > b.ReceivedQty
AND @ProductID = b.ProductID
ORDER BY b.ProductID ASC
END
GO
/* Przyznanie pracownikowi możliwości uruchomienia procedury */
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
/* Zauważyć należy, że procedura składowana umieszczona jest w schemacie
Purchasing. Demonstruje to również katalogi systemowe */
SELECT a.name AS 'Schema'
, b.name AS 'Object Name'
, b.type AS 'Object Type'
FROM sys.schemas a
INNER JOIN sys.objects b
ON a.schema_id = b.schema_id
WHERE b.name = 'usp_ShowWaitingItems';
GO
/* Ponowne zalogowanie się jako użytkownik dbo */
REVERT;
GO
/*
Sekcja 3: Przełączanie kontekstu i obserwacja zabezpieczeń
*/
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
EXEC Purchasing.usp_ShowWaitingItems 952
GO
/*
Krok 4 – Oczyszczenie przykładu
*/
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Do początku strony
Inne zasoby, pomoc i informacje
- Security Considerations for Databases and Database Applications (j.ang.)
- Getting SQL Server 2005 Assistance (j.ang.)
Do początku strony