Samouczek: Łańcuchy własności i przełączania kontekstu
Ten scenariusz samouczku do zilustrowania SQL Server pojęć związanych z zabezpieczeniami, obejmujące łańcucha własności i przełączania kontekstu użytkownika.Aby uzyskać więcej informacji na temat łańcucha własności, zobacz Łańcuchy własności.Aby uzyskać więcej informacji na temat przełączania kontekstu, zobacz Kontekst przełączania (aparat bazy danych).
Ostrzeżenie
Aby uruchomić kod w tym samouczku musi mieć oba mieszany tryb zabezpieczeń skonfigurowane i AdventureWorks2008R2 Baza danych zainstalowana.Aby uzyskać więcej informacji na temat mieszany tryb zabezpieczeń, zobacz Wybieranie trybu uwierzytelniania.
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:
Konfigurowanie środowiska.
Tworzenie procedura składowana dostępu do danych przez zamówienia zakupu.
Dostęp do danych za pomocą procedura składowana.
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.
Ostrzeżenie
Kod ten nie obejmuje dwa oczekiwane błędy, które wykazują niezdolność TestEmployeeUser 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 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