Sdílet prostřednictvím

Kurz: Podepisování uložených procedur pomocí certifikátu

platí pro: SQL Server Azure SQL DatabaseAzure SQL Managed Instance

Tento kurz ukazuje podepisování uložených procedur pomocí certifikátu vygenerovaného SQL Serverem.

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.

Podepisování uložených procedur pomocí certifikátu je užitečné, pokud chcete vyžadovat oprávnění k uložené proceduře, ale nechcete explicitně udělit uživateli tato práva. I když můžete tuto úlohu provést jinými způsoby, například pomocí příkazu EXECUTE AS, pomocí certifikátu můžete pomocí trasování najít původního volajícího uložené procedury. To poskytuje vysokou úroveň auditování, zejména během operací zabezpečení nebo DDL (Data Definition Language).

V hlavní databázi můžete vytvořit certifikát, který povolí oprávnění na úrovni serveru, nebo můžete vytvořit certifikát v jakékoli uživatelské databázi a povolit tak oprávnění na úrovni databáze. V tomto scénáři musí uživatel bez oprávnění k základním tabulkám přistupovat k uložené procedurě v AdventureWorks2025 databázi a chcete auditovat přístupový záznam objektu. Místo použití jiných metod řetězu vlastnictví vytvoříte serverový a databázový uživatelský účet bez oprávnění k základním objektům a uživatelský účet databáze s právy na tabulku a uloženou proceduru. Uložená procedura i druhý databázový uživatelský účet budou zabezpečeny certifikátem. Druhý databázový účet bude mít přístup ke všem objektům a udělí přístup k uložené proceduře prvnímu databázovému uživatelskému účtu.

V tomto scénáři nejprve vytvoříte databázový certifikát, uloženou proceduru a uživatele a pak otestujete proces následujícím postupem:

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 databázi AdventureWorks.

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

1. Konfigurace prostředí

Pokud chcete nastavit počáteční kontext příkladu, otevřete v aplikaci SQL Server Management Studio nový dotaz a spuštěním následujícího kódu otevřete AdventureWorks2025 databázi. Tento kód změní kontext databáze na AdventureWorks2025 a vytvoří nový přihlašovací účet serveru a uživatelský účet databáze (TestCreditRatingUser) pomocí hesla.

USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
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).

2. Vytvoření certifikátu

Certifikáty můžete na serveru vytvořit pomocí hlavní databáze jako kontextu, pomocí uživatelské databáze nebo obojího. Pro zabezpečení certifikátu existuje několik možností. Další informace o certifikátech naleznete v tématu CREATE CERTIFICATE (Transact-SQL).

Spuštěním tohoto kódu vytvořte databázový certifikát a zabezpečte ho pomocí hesla.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2022';  -- Error 3701 will occur if this date is not in the future
GO

3. Vytvoření a podepsání uložené procedury pomocí certifikátu

Pomocí následujícího kódu vytvořte uloženou proceduru, která vybere data z Vendor tabulky ve Purchasing schématu databáze a omezí přístup pouze na společnosti s ratingem 1. Všimněte si, že první část uložené procedury zobrazuje kontext uživatelského účtu, na kterém běží uložená procedura, což je pouze předvedení konceptů. Není nutné splnit požadavky.

CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Show who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1  
END  
GO

Spuštěním tohoto kódu podepište uloženou proceduru pomocí databázového certifikátu pomocí hesla.

ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO

Další informace o uložených procedurách naleznete v tématu Uložené procedury (databázový stroj).

Další informace o podepisování uložených procedur naleznete v tématu ADD SIGNATURE (Transact-SQL).

4. Vytvoření účtu certifikátu pomocí certifikátu

Spuštěním tohoto kódu vytvořte uživatele databáze (TestCreditRatingcertificateAccount) z certifikátu. Tento účet nemá žádné přihlášení k serveru a nakonec bude řídit přístup k podkladovým tabulkám.

USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO

5. Udělení práv k databázi certifikátových účtů

Spuštěním tohoto kódu přiřaďte TestCreditRatingcertificateAccount práva základní tabulce a uložené proceduře.

GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO

Další informace o udělení oprávnění k objektům naleznete v tématu GRANT (Transact-SQL).

6. Zobrazení kontextu přístupu

Pokud chcete zobrazit práva přidružená k přístupu k uložené proceduře, spuštěním následujícího kódu udělte oprávnění ke spuštění uložené procedury TestCreditRatingUser uživateli.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Dále spusťte následující kód, který spustí uloženou proceduru jako přihlašovací jméno dbo, které jste použili na serveru. Prohlédněte si výstup informací o kontextu uživatele. Zobrazí se účet dbo jako kontext s vlastními právy, a ne prostřednictvím členství ve skupině.

EXECUTE TestCreditRatingSP;  
GO

Spuštěním následujícího kódu použijte EXECUTE AS příkaz, kterým se stanete účtem TestCreditRatingUser a spusťte uložený postup. Tentokrát uvidíte, že kontext uživatele je nastavený na kontext UŽIVATELE MAPOVANÉHO NA CERTIFIKÁT. Upozorňujeme, že tato možnost není podporována v obsažené databázi nebo Azure SQL Database nebo Azure Synapse Analytics.

EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXECUTE TestCreditRatingSP;  
GO

Zobrazené auditování je dostupné díky tomu, že jste podepsali uloženou proceduru.

Poznámka:

Pomocí příkazu EXECUTE AS můžete přepínat kontexty v databázi.

7. Resetování prostředí

Následující kód pomocí REVERT příkazu vrátí kontext aktuálního účtu do dbo a resetuje prostředí.

REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

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

Kompletní příklad

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

/* Step 1 - Open the AdventureWorks2022 database */  
USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  
  
/* Step 2 - Create a certificate in the AdventureWorks2022 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2021';   -- Error 3701 will occur if this date is not in the future
GO  
  
/* Step 3 - Create a stored procedure and  
sign it using the certificate */  
CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Shows who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token;     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1;  
END  
GO  
  
ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
  
/* Step 4 - Create a database user for the certificate.   
This user has the ownership chain associated with it. */  
USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO  
  
/* Step 5 - Grant the user database rights */  
GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE  
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  
  
/* Step 6 - Test, using the EXECUTE AS statement */  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  
  
-- Run the procedure as the dbo user, notice the output for the type  
EXEC TestCreditRatingSP;  
GO  
  
EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXEC TestCreditRatingSP;  
GO  
  
/* Step 7 - Clean up the example */  
REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

Viz také

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

  • Last updated on