Delen via

Zelfstudie: Opgeslagen procedures ondertekenen met een certificaat

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Deze zelfstudie illustreert het ondertekenen van opgeslagen procedures met behulp van een certificaat dat is gegenereerd door SQL Server.

Opmerking

Als u de code in deze zelfstudie wilt uitvoeren, moet zowel de beveiliging in de gemengde modus als de AdventureWorks2025 database zijn geïnstalleerd.

Het ondertekenen van opgeslagen procedures met behulp van een certificaat is handig wanneer u machtigingen wilt vereisen voor de opgeslagen procedure, maar u niet expliciet een gebruiker die rechten wilt verlenen. Hoewel u deze taak op andere manieren kunt uitvoeren, zoals het gebruik van de EXECUTE AS-instructie, kunt u met behulp van een certificaat een tracering gebruiken om de oorspronkelijke aanroeper van de opgeslagen procedure te vinden. Dit biedt een hoog controleniveau, met name tijdens DDL-bewerkingen (Security or Data Definition Language).

U kunt een certificaat in de hoofddatabase maken om machtigingen op serverniveau toe te staan of u kunt een certificaat maken in gebruikersdatabases om machtigingen op databaseniveau toe te staan. In dit scenario moet een gebruiker zonder rechten voor basistabellen toegang krijgen tot een opgeslagen procedure in de AdventureWorks2025 database en wilt u het toegangspad voor het object controleren. In plaats van andere methoden voor eigendomsketens te gebruiken, maakt u een server- en databasegebruikersaccount zonder rechten voor de basisobjecten en een databasegebruikersaccount met rechten voor een tabel en een opgeslagen procedure. Zowel de opgeslagen procedure als het tweede databasegebruikersaccount wordt beveiligd met een certificaat. Het tweede databaseaccount heeft toegang tot alle objecten en verleent toegang tot de opgeslagen procedure aan het eerste databasegebruikersaccount.

In dit scenario maakt u eerst een databasecertificaat, een opgeslagen procedure en een gebruiker. Vervolgens test u het proces met de volgende stappen:

Elk codeblok in dit voorbeeld wordt regel voor regel uitgelegd. Zie Volledig voorbeeld aan het einde van deze zelfstudie als u het volledige voorbeeld wilt kopiëren.

Vereiste voorwaarden

Voor het voltooien van deze zelfstudie hebt u SQL Server Management Studio nodig, toegang tot een server waarop SQL Server wordt uitgevoerd en een AdventureWorks-database.

Zie Een database herstellen voor instructies over het herstellen van een database in SQL Server Management Studio.

1. De omgeving configureren

Als u de eerste context van het voorbeeld wilt instellen, opent u in SQL Server Management Studio een nieuwe query en voert u de volgende code uit om de AdventureWorks2025 database te openen. Deze code wijzigt de databasecontext in AdventureWorks2025 en maakt een nieuw serveraanmeldings- en databasegebruikersaccount (TestCreditRatingUser), met behulp van een wachtwoord.

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

Zie CREATE USER (Transact-SQL) voor meer informatie over de instructie CREATE USER. Zie CREATE LOGIN (Transact-SQL) voor meer informatie over de instructie CREATE LOGIN.

2. Een certificaat maken

U kunt certificaten op de server maken met behulp van de hoofddatabase als context, met behulp van een gebruikersdatabase of beide. Er zijn meerdere opties voor het beveiligen van het certificaat. Zie CREATE CERTIFICATE (Transact-SQL)voor meer informatie over certificaten.

Voer deze code uit om een databasecertificaat te maken en te beveiligen met behulp van een wachtwoord.

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. Een opgeslagen procedure maken en ondertekenen met behulp van het certificaat

Gebruik de volgende code om een opgeslagen procedure te maken waarmee gegevens uit de Vendor tabel in het Purchasing databaseschema worden geselecteerd, waardoor de toegang wordt beperkt tot alleen de bedrijven met een kredietclassificatie van 1. In het eerste gedeelte van de opgeslagen procedure wordt de context weergegeven van het gebruikersaccount waarop de opgeslagen procedure wordt uitgevoerd. Dit is om alleen de concepten te demonstreren. Het is niet vereist om te voldoen aan de vereisten.

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

Voer deze code uit om de opgeslagen procedure met het databasecertificaat te ondertekenen met behulp van een wachtwoord.

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

Zie Opgeslagen procedures (Database Engine) voor meer informatie over opgeslagen procedures.

Zie ADD SIGNATURE (Transact-SQL) voor meer informatie over het ondertekenen van opgeslagen procedures.

4. Maak een certificaataccount met behulp van het certificaat

Voer deze code uit om een databasegebruiker (TestCreditRatingcertificateAccount) te maken op basis van het certificaat. Dit account heeft geen serveraanmelding en bepaalt uiteindelijk de toegang tot de onderliggende tabellen.

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

5. De databaserechten van het certificaataccount verlenen

Voer deze code uit om rechten te verlenen TestCreditRatingcertificateAccount aan de basistabel en de opgeslagen procedure.

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

Zie GRANT (Transact-SQL)voor meer informatie over het verlenen van machtigingen aan objecten.

6. De Access-context weergeven

Als u de rechten wilt weergeven die zijn gekoppeld aan de toegang tot de opgeslagen procedure, voert u de volgende code uit om de opgeslagen procedure aan de TestCreditRatingUser gebruiker toe te kennen.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Voer vervolgens de volgende code uit om de opgeslagen procedure uit te voeren als de dbo-aanmelding die u op de server hebt gebruikt. Observeer de uitvoer van de gebruikerscontextinformatie. Het dbo-account wordt weergegeven als context met eigen rechten en niet via een groepslidmaatschap.

EXECUTE TestCreditRatingSP;  
GO

Voer de volgende code uit om de EXECUTE AS-instructie te gebruiken, overschakelen naar het TestCreditRatingUser-account en de opgeslagen procedure uit te voeren. Deze keer ziet u dat de gebruikerscontext is ingesteld op de context USER MAPPED TO CERTIFICATE. Houd er rekening mee dat deze optie niet wordt ondersteund in een ingesloten database of Azure SQL Database of Azure Synapse Analytics.

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

Hier ziet u de controle die beschikbaar is omdat u de opgeslagen procedure hebt ondertekend.

Opmerking

Gebruik EXECUTE AS om te schakelen tussen contexten binnen een database.

7. De omgeving opnieuw instellen

De volgende code gebruikt de REVERT instructie om de context van het huidige account te retourneren naar dbo en de omgeving opnieuw in te stellen.

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

Zie REVERT (Transact-SQL)voor meer informatie over de revert-instructie.

Volledig voorbeeld

In deze sectie wordt de volledige voorbeeldcode weergegeven.

/* 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

Zie ook

Security Center voor SQL Server Database Engine en Azure SQL Database

  • Last updated on