Dela via

Självstudie: Signera lagrade procedurer med ett certifikat

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Den här självstudien visar hur du signerar lagrade procedurer med hjälp av ett certifikat som genereras av SQL Server.

Anmärkning

Om du vill köra koden i den här självstudien måste du ha både säkerhet i blandat läge konfigurerat och AdventureWorks2025 databasen installerad.

Att signera lagrade procedurer med hjälp av ett certifikat är användbart när du vill kräva behörigheter för den lagrade proceduren, men du inte uttryckligen vill bevilja en användare dessa rättigheter. Även om du kan utföra den här uppgiften på andra sätt, till exempel genom att använda EXECUTE AS-instruktionen, kan du använda ett certifikat för att använda en spårning för att hitta den ursprungliga anroparen för den lagrade proceduren. Detta ger en hög granskningsnivå, särskilt under åtgärder med säkerhets- eller datadefinitionsspråk (DDL).

Du kan skapa ett certifikat i huvuddatabasen för att tillåta behörigheter på servernivå, eller så kan du skapa ett certifikat i alla användardatabaser för att tillåta behörigheter på databasnivå. I det här scenariot måste en användare utan behörighet till bastabeller komma åt en lagrad procedur i AdventureWorks2025 databasen och du vill granska åtkomstspårningen för objekt. I stället för att använda andra metoder för ägarskapskedja skapar du ett server- och databasanvändarkonto utan behörighet till basobjekten och ett databasanvändarkonto med rättigheter till en tabell och en lagrad procedur. Både den lagrade proceduren och det andra databasanvändarkontot skyddas med ett certifikat. Det andra databaskontot har åtkomst till alla objekt och ger åtkomst till den lagrade proceduren till det första databasanvändarkontot.

I det här scenariot skapar du först ett databascertifikat, en lagrad procedur och en användare, och sedan testar du processen enligt följande steg:

Varje kodblock i det här exemplet förklaras på rad. Om du vill kopiera det fullständiga exemplet läser du Fullständigt exempel i slutet av den här handledningen.

Förutsättningar

För att slutföra den här självstudien behöver du SQL Server Management Studio, åtkomst till en server som kör SQL Server och en AdventureWorks-databas.

Anvisningar om hur du återställer en databas i SQL Server Management Studio finns i Återställa en databas.

1. Konfigurera miljön

Om du vill ange den inledande kontexten för exemplet öppnar du en ny fråga i SQL Server Management Studio och kör följande kod för att öppna AdventureWorks2025 databasen. Den här koden ändrar databaskontexten till AdventureWorks2025 och skapar ett nytt serverinloggnings- och databasanvändarkonto (TestCreditRatingUser) med hjälp av ett lösenord.

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

Mer information om instruktionen CREATE USER finns i CREATE USER (Transact-SQL). Mer information om CREATE LOGIN-instruktionen finns i CREATE LOGIN (Transact-SQL).

2. Skapa ett certifikat

Du kan skapa certifikat på servern med huvuddatabasen som kontext, med hjälp av en användardatabas eller både och. Det finns flera alternativ för att skydda certifikatet. Mer information om certifikat finns i SKAPA CERTIFIKAT (Transact-SQL).

Kör den här koden för att skapa ett databascertifikat och skydda det med ett lösenord.

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. Skapa och signera en lagrad procedur med hjälp av certifikatet

Använd följande kod för att skapa en lagrad procedur som väljer data från Vendor tabellen i Purchasing databasschemat, vilket begränsar åtkomsten till endast företag med kreditvärdighet 1. Observera att det första avsnittet i den lagrade proceduren visar kontexten för användarkontot som kör den lagrade proceduren, vilket är att endast demonstrera begreppen. Det är inte nödvändigt att uppfylla kraven.

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

Kör den här koden för att signera den lagrade proceduren med databascertifikatet med hjälp av ett lösenord.

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

Mer information om lagrade procedurer finns i Lagrade procedurer (databasmotor).

Mer information om hur du signerar lagrade procedurer finns i ADD SIGNATURE (Transact-SQL).

4. Skapa ett certifikatkonto med hjälp av certifikatet

Kör den här koden för att skapa en databasanvändare (TestCreditRatingcertificateAccount) från certifikatet. Det här kontot har ingen serverinloggning och styr slutligen åtkomsten till de underliggande tabellerna.

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

5. Bevilja certifikatkontots databasrättigheter

Kör den här koden för att bevilja TestCreditRatingcertificateAccount rättigheter till bastabellen och den lagrade proceduren.

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

Mer information om att bevilja behörigheter till objekt finns i BEVILJA (Transact-SQL).

6. Visa åtkomstkontexten

Om du vill visa de rättigheter som är associerade med åtkomsten till den lagrade proceduren kör du följande kod för att ge användaren behörighet att köra den lagrade proceduren TestCreditRatingUser .

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Kör sedan följande kod för att köra den lagrade proceduren som den dbo-inloggning som du använde på servern. Observera resultatet av användarkontextinformationen. Den kommer att visa dbo-kontot i kontexten med dess egna rättigheter och inte genom ett gruppmedlemskaps rättigheter.

EXECUTE TestCreditRatingSP;  
GO

Kör följande kod för att använda instruktionen EXECUTE AS för att bli TestCreditRatingUser-kontot och köra den lagrade proceduren. Den här gången ser du att användarkontexten är inställd på KONTEXTEN ANVÄNDARE MAPPAD TILL CERTIFIKAT. Observera att det här alternativet inte stöds i en innesluten databas, Azure SQL Database eller Azure Synapse Analytics.

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

Den här visar vilka granskningar som är tillgängliga eftersom du har signerat den lagrade proceduren.

Anmärkning

Använd EXECUTE AS för att växla kontexter i en databas.

7. Återställ miljön

Följande kod använder -instruktionen REVERT för att returnera kontexten för det aktuella kontot till dbo och återställer miljön.

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

Mer information om REVERT-instruktionen finns i REVERT (Transact-SQL).

Fullständigt exempel

Det här avsnittet visar den fullständiga exempelkoden.

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

Se även

Säkerhetscenter för SQL Server Database Engine och Azure SQL Database

  • Last updated on