Microsoft SQL Server 2008

Łańcuchy własności i przełączanie kontekstu w SQL Server

Opublikowano: 25 kwietnia 2007

Zawartość strony
Wstęp  Wstęp
Scenariusz  Scenariusz
Konfiguracja środowiska  Konfiguracja środowiska
Utworzenie procedury składowanej w celu dostępu do danych  Utworzenie procedury składowanej w celu dostępu do danych
Uzyskanie dostępu do danych poprzez procedurę składowaną  Uzyskanie dostępu do danych poprzez procedurę składowaną
Ponowne uruchomienie środowiska  Ponowne uruchomienie środowiska
Kompletny przykład  Kompletny przykład
Inne zasoby, pomoc i informacje  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 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:

  1. Konfiguracja środowiska
  2. Utworzenie procedury składowanej w celu uzyskania dostępu do danych według numeru zamówienia.
  3. Uzyskanie dostępu do danych poprzez procedurę składowaną.
  4. 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 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 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 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 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 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 Do początku strony

Inne zasoby, pomoc i informacje

Do początku strony Do początku strony

 

Microsoft SQL Server 2008