Kurz: Řetězy vlastnictví a přepínání kontextu

platí pro:SQL Server azure SQL Managed Instance

Tento kurz používá scénář k ilustraci konceptů zabezpečení SQL Serveru zahrnujících řetězy vlastnictví a přepínání kontextu uživatele.

Poznámka:

Pokud chcete spustit kód v tomto kurzu, musíte mít nakonfigurované zabezpečení smíšeného režimu i nainstalovanou AdventureWorks2025 databázi. Další informace o zabezpečení smíšeného režimu naleznete v tématu Volba režimu ověřování.

Scenario

V tomto scénáři potřebují dva uživatelé účty pro přístup k datům nákupní objednávky uloženým AdventureWorks2025 v databázi. Požadavky jsou následující:

První účet (TestManagerUser) musí být schopen zobrazit všechny podrobnosti v každé nákupní objednávce.
Druhý účet (TestEmployeeUser) musí být schopen zobrazit číslo nákupní objednávky, datum objednávky, datum expedice, čísla ID produktu a objednané a přijaté položky na nákupní objednávku podle čísla nákupní objednávky pro položky, kde byly doručeny částečné zásilky.
Všechny ostatní účty si musí zachovat aktuální oprávnění.
Aby bylo možné splnit požadavky tohoto scénáře, je příklad rozdělen na čtyři části, které demonstrují koncepty řetězců vlastnictví a přepínání kontextu:

Konfigurace prostředí
Vytvoření uložené procedury pro přístup k datům podle nákupní objednávky
Přístup k datům prostřednictvím uložené procedury.
Resetování prostředí

Každý blok kódu v tomto příkladu je vysvětlený na řádku. Pokud chcete zkopírovat kompletní příklad, podívejte se na část Kompletní příklad na konci tohoto kurzu.

Požadavky

K dokončení tohoto kurzu potřebujete SQL Server Management Studio, přístup k serveru, na kterém běží SQL Server, a k databázi AdventureWorks2025.

Nainstalujte SQL Server Management Studio.
Nainstalujte SQL Server 2017 Developer Edition.
Stáhněte si ukázkové databáze AdventureWorks.

Pokyny k obnovení databáze v aplikaci SQL Server Management Studio najdete v tématu Obnovení databáze.

1. Konfigurace prostředí

K otevření AdventureWorks2025 databáze použijte SQL Server Management Studio a následující kód a pomocí CURRENT_USER příkazu Transact-SQL zkontrolujte, jestli se uživatel dbo zobrazí jako kontext.

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

Další informace o příkazu CURRENT_USER naleznete v tématu CURRENT_USER (Transact-SQL).

Tento kód použijte jako uživatel dbo k vytvoření dvou uživatelů na serveru a v AdventureWorks2025 databázi.

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

Další informace o příkazu CREATE USER naleznete v tématu CREATE USER (Transact-SQL). Další informace o příkazu CREATE LOGIN naleznete v tématu CREATE LOGIN (Transact-SQL).

Pomocí následujícího kódu můžete změnit vlastnictví schématu Purchasing na TestManagerUser účet. To umožňuje, aby účet používal všechny příkazy jazyka pro manipulaci s daty (Data Manipulation Language, DML), jako je například přístup s oprávněním SELECT a INSERT, k objektům, které obsahuje. TestManagerUser má také možnost vytvářet uložené procedury.

/* Change owner of the Purchasing Schema to TestManagerUser */  
ALTER AUTHORIZATION   
   ON SCHEMA::Purchasing   
   TO TestManagerUser;  
GO  
  
GRANT CREATE PROCEDURE   
   TO TestManagerUser   
   WITH GRANT OPTION;  
GO

Další informace o příkazu GRANT naleznete v tématu GRANT (Transact-SQL). Další informace o uložených procedurách naleznete v tématu Uložené procedury (databázový stroj). Pro podrobnosti o všech oprávněních databázového stroje se podívejte na https://aka.ms/sql-permissions-poster.

2. Vytvoření uložené procedury pro přístup k datům

Pokud chcete přepnout kontext v databázi, použijte příkaz EXECUTE AS. FUNKCE EXECUTE AS vyžaduje oprávnění IMPERSONATE.

Použijte příkaz EXECUTE AS v následujícím kódu pro změnu kontextu na TestManagerUser a vytvoření uložené procedury, která zobrazuje pouze data požadovaná TestEmployeeUser. Pro splnění požadavků přijímá uložená procedura jednu proměnnou pro číslo nákupní objednávky a nezobrazuje finanční informace a klauzule WHERE omezuje výsledky na částečné zásilky.

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

V současné době TestEmployeeUser nemá přístup k žádným databázovým objektům. Následující kód (stále v TestManagerUser kontextu) uděluje uživatelskému účtu možnost dotazovat se na informace základní tabulky prostřednictvím uložené procedury.

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

Uložená procedura je součástí schématu Purchasing , i když nebyla explicitně zadána žádná schémata, protože TestManagerUser je přiřazena schématu Purchasing ve výchozím nastavení. Informace o systémovém katalogu můžete použít k vyhledání objektů, jak je znázorněno v následujícím kódu.

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

Když je tato část příkladu dokončena, kód přepne kontext zpět na dbo pomocí REVERT příkazu.

REVERT;  
GO

Další informace o příkazu REVERT naleznete v tématu REVERT (Transact-SQL).

3. Přístup k datům prostřednictvím uložené procedury

TestEmployeeUser nemá žádná oprávnění k AdventureWorks2025 jiným databázovým objektům než přihlášení a práva přiřazená k roli veřejné databáze. Následující kód vrátí chybu při TestEmployeeUser pokusu o přístup k základním tabulkám.

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

Vrácená chyba:

Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.

Vzhledem k tomu, že objekty, na které odkazuje uložená procedura vytvořená v poslední části, vlastní TestManagerUser vlastnictví schématu Purchasing , TestEmployeeUser mají přístup k základním tabulkám prostřednictvím uložené procedury. Následující kód, který stále používá TestEmployeeUser kontext, předává nákupní objednávku 952 jako parametr.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4. Resetování prostředí

Následující kód použije příkaz REVERT k obnovení kontextu aktuálního účtu do dbo a pak resetuje prostředí.

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

Kompletní příklad

V této části se zobrazí kompletní ukázkový kód.

Poznámka:

Tento kód neobsahuje dvě očekávané chyby, které demonstrují nemožnost TestEmployeeUser výběru ze základních tabulek.

/*   
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 AdventureWorks2022;  
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 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 AS a  
      INNER JOIN Purchasing.PurchaseOrderDetail AS 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 AS a  
   INNER JOIN sys.objects AS 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

Viz také

Security Center pro databázový stroj SQL Serveru a Azure SQL databáze

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-11-19