Samouczek: Łańcuchy własności i przełączania kontekstu

Scenariusz

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

• Pierwsze konto (TestManagerUser) musi być widoczne wszystkie szczegóły w każdym zamówieniu zakupu.

• Drugi rachunek (TestEmployeeUser) musi być w stanie wyświetlić numer zamówienia zakupu, Data zamówienia, datę wysyłki, numery ID produktu i Zamówione i odebranych elementów na zamówienie zakupu, numer zamówienia zakupu dla towarów, których otrzymano dostaw częściowych.

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

Aby spełnić wymagania w tym scenariuszu, przykład jest dzielony na cztery części, które wykazują pojęcia łańcucha własności i przełączania kontekstu:

1. Konfigurowanie środowiska.

2. Tworzenie procedura składowana dostępu do danych przez zamówienia zakupu.

3. Dostęp do danych za pomocą procedura składowana.

4. Resetowanie środowiska.

Każdy blok kodu w tym przykładzie jest wyjaśnione w wierszu.Aby skopiować pełny przykład, zobacz Pełny przykład na końcu tego samouczka.

1.Configure the Environment

Use SQL Server Management Studio and the following code to open the AdventureWorks2008R2 database, and use the CURRENT_USER Transact-SQL statement to check that the dbo user is displayed as the context.

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

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

Użyj tego kodu jako dbo użytkownikowi tworzenie dwóch użytkowników na serwerze i AdventureWorks2008R2 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 Utwórz użytkownika (Transact-SQL).Aby uzyskać więcej informacji na temat instrukcja tworzenia logowania, zobacz Utwórz logowania (Transact-SQL).

Użyć poniższego kodu zmienić własność Purchasing schematu do TestManagerUser konta.Umożliwia to konto umożliwia dostęp do instrukcja Data Manipulation Language (DML) (takie jak SELECT i INSERT uprawnienia) na niej obiektów.Ponieważ nie obejmuje uprawnienia Data Definition Language (DDL), TestManagerUser jawnie udzielono praw na PurchaseOrderHeader i PurchaseOrderDetail tabel, jak również 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 UDZIEL (Transact-SQL).Aby uzyskać więcej informacji dotyczących schematów bazy danych, zobacz Schematy (aparat bazy danych).Aby uzyskać więcej informacji na temat procedur przechowywanych, zobacz Procedury przechowywane (aparat bazy danych).

2.Create a Stored Procedure to Access Data

Istnieją dwa sposoby użytkownika, aby przełączyć kontekstów w bazie danych: SETUSER lub jako EXECUTEZa pomocą instrukcja SETUSER wymaga obiekt wywołujący mają członkostwo sysadmin stała rola serwera lub dbo konta.JAK wykonywanie wymaga uprawnienia PERSONIFIKUJ.Aby uzyskać więcej informacji dotyczących tych pojęć, zobacz WYKONYWANIE jako vs. SETUSER.

Użyj EXECUTE AS instrukcja poniższy kod, aby zmienić kontekst TestManagerUser i Utwórz procedura składowana, pokazujący tylko dane wymagane przez TestEmployeeUser.Spełniać wymagania, procedura składowana akceptuje jedną zmienną dla numeru zamówienia zakupu i nie są wyświetlane informacje finansowe i klauzula WHERE zawęża wyniki do 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.Poniższy kod (nadal w TestManagerUser kontekstu) udziela konta użytkownika do kwerendy base -tabela informacji za pomocą procedura składowana.

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

procedura składowana jest częścią Purchasing schematu, nawet jeżeli schematu nie został jawnie określony, ponieważ TestManagerUser przypisane domyślnie Purchasing schematu.Informacje o systemie wykazu służy do lokalizowania obiektów, jak 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 systemie wykazów, zobacz Badanie wykazu systemu SQL Server.

Z tej sekcji przykład ukończona, przełączniki kodu, w kontekście z powrotem do dbo za pomocą REVERT instrukcja.

REVERT;
GO

Aby uzyskać więcej informacji na temat instrukcja PRZYWRACANIA Zobacz PRZYWRÓĆ (Transact-SQL).

3.Access Data Through the Stored Procedure

TestEmployeeUsernie ma uprawnień AdventureWorks2008R2 obiektów innych niż identyfikatora logowania i praw przypisanych do bazy danych public rola bazy danych.Poniższy kod zwraca błąd podczas TestEmployeeUser próbuje uzyskać dostęp do tabel podstawowych.

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 składowana, utworzone w ostatniej sekcji są własnością TestManagerUser z Purchasing własność schematu TestEmployeeUser dostępu do tabel bazowych za pomocą procedura składowana.Następujący kod w dalszym ciągu z TestEmployeeUser kontekstu, 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 zwraca kontekstu 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

Pełny przykład

Ta sekcja zawiera kod pełny przykład.

/* 
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 AdventureWorks2008R2;
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::AdventureWorks2008R2.Purchasing.PurchaseOrderHeader
    TO TestManagerUser 
   WITH GRANT OPTION;
GO
GRANT ALL 
   ON OBJECT::AdventureWorks2008R2.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

Zobacz także

Koncepcje

Inne zasoby