Tutorial: Ownership Chains and Context Switching

Ten samouczek użyto do zilustrowania scenariusza SQL Server pojęcia dotyczące zabezpieczeń związanych z łańcucha własności i przełączania kontekstu użytkownika. Aby uzyskać więcej informacji na temat łańcucha własności zobacz Ownership Chains. Aby uzyskać więcej informacji na temat przełączania kontekstu zobacz Context Switching (Database Engine).

Uwaga

Aby uruchomić kod w tej instrukcji, musisz mieć zabezpieczeń zarówno trybu mieszanego, skonfigurowany i AdventureWorks baza danych zainstalowana.Aby uzyskać więcej informacji na temat zabezpieczeń w trybie mieszanym zobacz Choosing an Authentication Mode.

Scenariusz

W tym scenariuszu konieczne jest kont dostępu do danych zamówienia zakupu przechowywane w dwóch użytkowników AdventureWorks bazy danych.Dostępne są następujące wymagania:

• Pierwszy (kontaTestManagerUser) musi być w stanie wyświetlić wszystkie szczegóły w każdym zamówieniu zakupu.

• Druga (kontaTestEmployeeUser) musi być w stanie wyświetlić numer zamówienia zakupu, daty zamówienia, data dostawy, numery identyfikatorów produktów i towarów zamówionych i odebrane w każdym zamówieniu zakupu, numer zamówienia zakupu dla dostaw częściowych zostały odebrane elementów.

• Wszystkie konta musi zachować bieżące uprawnienia.

By mogła spełnić najostrzejsze wymagania związane z tego scenariusza, w przykładzie jest podzielony na cztery części, które wskazują pojęć związanych z łańcucha własności i przełączania kontekstu:

1. Konfigurowanie środowiska.

2. Tworzenie procedura przechowywana w celu dostępu do danych przez zamówienia zakupu.

3. Dostęp do danych za pomocą procedura przechowywana.

4. Resetowanie środowiska.

Każdy blok kodu w tym przykładzie jest omówiona w wierszu.Aby skopiować pełny przykład, zobacz Przykład pełnej na końcu tego samouczka.

1.Configure the Environment

Użycie SQL Server Management Studio a następujący kod w celu otwarcia AdventureWorks bazy danych oraz stosowanie CURRENT_USER Transact-SQL instrukcję, aby sprawdzić, czy dbo użytkownika są wyświetlane w kontekście.

USE AdventureWorks;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

Aby uzyskać więcej informacji na temat instrukcja CURRENT_USER zobacz CURRENT_USER (języka Transact-SQL).

Użyj tego kodu jako dbo użytkownikowi tworzenie dwóch użytkowników na serwerze i w AdventureWorks 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 

Aby uzyskać więcej informacji na temat instrukcja CREATE USER zobacz CREATE USER (języka Transact-SQL). Aby uzyskać więcej informacji na temat instrukcja CREATE LOGIN zobacz CREATE LOGIN (Transact-SQL).

Użyj następującego kodu, aby zmienić własność Purchasing schemat TestManagerUser konto. Dzięki temu tego konta (takie jak używać dostęp instrukcja język edycji danych (DML) SELECT i INSERT uprawnienia) na niej obiektów. Ponieważ nie obejmuje uprawnienia definicja danych Language (DDL) TestManagerUser jest jawnie udzielany praw PurchaseOrderHeader i PurchaseOrderDetail tabele, a także możliwość tworzenia procedur przechowywanych.

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
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
/* Note: DML works fine with Schema owner, but not DDL. */
GRANT CREATE PROCEDURE 
   TO TestManagerUser 
   WITH GRANT OPTION;
GO

Aby uzyskać więcej informacji na temat instrukcja GRANT zobacz GRANT (Transact-SQL). Aby uzyskać więcej informacji na temat schematów bazy danych zobacz Schemas (Database Engine). Aby uzyskać więcej informacji na temat procedur przechowywanych zobacz Stored Procedures (Database Engine).

2.Create a Stored Procedure to Access Data

Istnieją dwa sposoby umożliwiające użytkownikowi przełączanie kontekstów w bazie danych: SETUSER lub AS. wykonać Za pomocą instrukcja SETUSER wymaga obiekt wywołujący mają członkostwo sysadmin być lub ustalić roli serweradbo konto.wykonać AS musi mieć uprawnienia PERSONIFIKACJI.Aby uzyskać więcej informacji dotyczących tych pojęć zobacz EXECUTE AS vs. SETUSER.

Użycie EXECUTE AS Instrukcja poniższy kod, aby zmienić kontekst do TestManagerUser i Tworzenie procedura przechowywana, pokazujący tylko dane wymagane przez TestEmployeeUser. Spełnia wymagania, procedura przechowywana akceptuje jednej zmiennej dla numeru zamówienia zakupu i nie wyświetla informacji finansowych i klauzula WHERE ograniczające wyniki tylko do tych dostaw częściowych.

EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
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. W poniższym kodzie (nadal w TestManagerUser kontekst) udziela konta użytkownika, możliwości kwerendy tabela bazy informacji za pomocą procedura przechowywana.

GRANT EXECUTE
   ON OBJECT::Purchasing.usp_ShowWaitingItems
   TO TestEmployeeUser;
GO

Procedura przechowywana jest częścią Purchasing schemat, nawet jeżeli schemat nie został jawnie określony, ponieważ TestManagerUser przypisany przez ustawienie domyślne to Purchasing schemat. Za pomocą informacji o systemie wykazu może lokalizowanie obiektów, jak to pokazano 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

Aby uzyskać więcej informacji o katalogach systemu Zobacz Badanie katalogu systemu SQL Server.

W tej sekcji w przykładzie zakończone, przełączniki kod kontekstu z powrotem do dbo za pomocą instrukcja operacji PRZYWRACANIA.

REVERT;
GO

Aby uzyskać więcej informacji na temat instrukcja operacji PRZYWRACANIA zobacz REVERT (języka Transact-SQL).

3.Access Data Through the Stored Procedure

TestEmployeeUser nie ma uprawnień AdventureWorks obiekty inne niż identyfikator logowania i praw przypisanych do publiczne rola bazy danych.W poniższym kodzie zwróci błąd, gdy TestEmployeeUser próbuje uzyskać dostęp do tabel bazowych.

EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* This won't work */
SELECT *
FROM Purchasing.PurchaseOrderHeader;
GO
SELECT *
FROM Purchasing.PurchaseOrderDetail;
GO

Ponieważ obiekty odwołuje się procedura przechowywana utworzonego w ostatniej sekcji są własnością TestManagerUser na podstawie Purchasing własność schematu TestEmployeeUser dostępne tabele bazowe za pomocą procedury przechowywanej. Następujący kod w dalszym ciągu używają TestEmployeeUser kontekst, przekazuje zamówienia zakupu 952 jako parametr.

EXEC Purchasing.usp_ShowWaitingItems 952
GO

4.Reset the Environment

Następujący kod używa REVERT polecenie, aby zwrócić kontekście bieżącego konta do dbo, a następnie przywraca środowiska.

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

Przykład pełnej

W tej sekcji jest wyświetlana pełna przykładowy kod źródłowy.

Uwaga

Kod ten nie obejmuje dwa oczekiwanego błędów, które wykazują niezdolność z TestEmployeeUser Aby wybrać z tabel podstawowych.

/* 
Script:       UserContextTutorial.sql
Author:       Microsoft
Last Updated: Books Online
Conditions:   Execute as DBO or sysadmin in the AdventureWorks database
Section 1:    Configure the Environment 
*/

USE AdventureWorks;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

/* Create server and database users */
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 

/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION 
   ON SCHEMA::Purchasing 
   TO TestManagerUser;
GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */
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
/* Note: DML works fine with Schema owner, but not DDL */
GRANT CREATE PROCEDURE 
   TO TestManagerUser 
   WITH GRANT OPTION;
GO

/* 
Section 2: Switch Context and Create Objects
*/
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
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

/* Give the employee the ability to run the procedure */
GRANT EXECUTE 
   ON OBJECT::Purchasing.usp_ShowWaitingItems
   TO TestEmployeeUser;
GO 

/* Notice that the stored procedure is located in the Purchasing 
schema. This also demonstrates system catalogs */
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

/* Go back to being the dbo user */
REVERT;
GO

/* 
Section 3: Switch Context and Observe Security 
*/
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
EXEC Purchasing.usp_ShowWaitingItems 952
GO

/* 
Section 4: Clean Up Example
*/
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

See Also

Concepts

Ownership Chains

Security and Protection (Database Engine)

Other Resources

Context Switching (Database Engine)