Lernprogramm: Signieren von gespeicherten Prozeduren mit einem Zertifikat

Artikel
08/11/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Tutorial wird erläutert, wie gespeicherte Prozeduren mit einem Zertifikat signiert werden können, das von SQL Server generiert wurde.

Hinweis

Damit Sie den Code ausführen können, der in diesem Lernprogramm enthalten ist, müssen Sie die Sicherheit für den gemischte Modus konfiguriert und die AdventureWorks2022 -Datenbank installiert haben.

Das Signieren einer gespeicherten Prozedur mit einem Zertifikat bietet sich an, wenn Berechtigungen für die gespeicherte Prozedur erforderlich sein sollen, Sie einem Benutzer diese Berechtigungen aber nicht explizit erteilen möchten. Zwar gibt es für das Ausführen dieser Aufgabe mehrere Möglichkeiten (z. B. können Sie die EXECUTE AS-Anweisung verwenden), das Verwenden eines Zertifikats ermöglicht aber das Verwenden einer Ablaufverfolgung, um die Person ausfindig zu machen, von der die gespeicherte Prozedur ursprünglich aufgerufen wurde. Auf diese Weise erreichen Sie ein hohes Maß an Überwachung, insbesondere bei der Sicherheit von DDL-Vorgängen (Data Definition Language, Datendefinitionssprache).

Sie können ein Zertifikat in der master-Datenbank erstellen, damit Berechtigungen auf Serverebene möglich sind. Sie können aber auch ein Zertifikat in einer beliebigen Benutzerdatenbank erstellen, damit Berechtigungen auf Datenbankebene möglich sind. In diesem Szenario muss ein Benutzer, der keine Berechtigungen für die Basistabellen hat, auf eine gespeicherte Prozedur in der AdventureWorks2022 -Datenbank zugreifen. Außerdem soll in diesem Szenario der Objektzugriffspfad überwacht werden. Dazu erstellen Sie ein Server- und ein Datenbank-Benutzerkonto ohne Berechtigungen für die Basisobjekte sowie ein Datenbank-Benutzerkonto, das Berechtigungen für eine Tabelle und eine gespeicherte Prozedur hat. Andere Methoden für Besitzketten werden nicht verwendet. Sowohl die gespeicherte Prozedur als auch das zweite Datenbank-Benutzerkonto werden mit einem Zertifikat gesichert. Das zweite Datenbank-Benutzerkonto hat Zugriff auf alle Objekte und erteilt dem ersten Datenbank-Benutzerkonto die Berechtigung zum Zugreifen auf die gespeicherte Prozedur.

In diesem Szenario erstellen Sie zunächst ein Datenbankzertifikat, eine gespeicherte Prozedur und einen Benutzer. Anschließend testen Sie den Prozess durch Ausführen der folgenden Schritte:

Jeder Codeblock dieses Beispiels wird jeweils sofort erläutert. Informationen, wie Sie das vollständige Beispiel kopieren können, finden Sie unter Vollständiges Beispiel am Ende dieses Lernprogramms.

Voraussetzungen

Zur Durchführung dieses Tutorials benötigen Sie SQL Server Management Studio, Zugriff auf einen Server, auf dem SQL-Server ausgeführt wird, und eine AdventureWorks-Datenbank.

Installieren Sie SQL Server Management Studio.
Installieren Sie die SQL Server 2017 Developer Edition.
Laden Sie eine AdventureWorks-Beispieldatenbank herunter.

Weitere Informationen zum Wiederherstellen einer Datenbank in SQL Server Management Studio finden Sie unter Wiederherstellen einer Datenbank.

1. Konfigurieren der Umgebung

Um den anfangskontext des Beispiels festzulegen, öffnen Sie in SQL Server Management Studio eine neue Abfrage, und führen Sie den folgenden Code aus, um die AdventureWorks2022 Datenbank zu öffnen. Dieser Code ändert den Datenbankkontext zu AdventureWorks2022 und erstellt eine neue Serveranmeldung sowie ein neues Datenbank-Benutzerkonto (TestCreditRatingUser), wobei ein Kennwort verwendet wird.

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

Weitere Informationen zur CREATE USER-Anweisung finden Sie unter CREATE USER (Transact-SQL).For more information on the CREATE USER statement, see CREATE USER (Transact-SQL). Weitere Informationen zur CREATE LOGIN-Anweisung finden Sie unter CREATE LOGIN (Transact-SQL).For more information on the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

2. Erstellen eines Zertifikats

Zertifikate können Sie auf dem Server erstellen, indem Sie die master-Datenbank, eine Benutzerdatenbank oder beide als Kontext verwenden. Für das Sichern eines Zertifikats gibt es mehrere Optionen. Weitere Informationen zu Zertifikaten finden Sie unter CREATE CERTIFICATE (Transact-SQL).

Führen Sie den folgenden Code aus, um ein Datenbankzertifikat zu erstellen und mit einem Kennwort zu sichern.

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. Erstellen einer gespeicherten Prozedur und Signieren der Prozedur mithilfe des Zertifikats

Erstellen Sie mit dem folgenden Code eine gespeicherte Prozedur, die Daten aus der Vendor -Tabelle Purchasing -Datenbankschema auswählt, wobei der Zugriff auf Unternehmen beschränkt wird, die die Bonität (CreditRating) 1 haben. Der erste Abschnitt der gespeicherten Prozedur zeigt den Kontext des Benutzerkontos an, unter dem die gespeicherte Prozedur ausgeführt wird. Hiermit sollen lediglich die Konzepte verdeutlicht werden. Es ist nicht erforderlich, die Anforderungen zu erfüllen.

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

Führen Sie den folgenden Code aus, um die gespeicherte Prozedur mit dem Datenbankzertifikat zu signieren und dazu ein Kennwort zu verwenden.

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

Weitere Informationen zu gespeicherten Prozeduren finden Sie unter Gespeicherte Prozeduren (Datenbankmodul).

Weitere Informationen zum Signieren gespeicherter Prozeduren finden Sie unter ADD SIGNATURE (Transact-SQL).

4. Erstellen eines Zertifikatkontos mithilfe des Zertifikats

Führen Sie den folgenden Code aus, um über das Zertifikat einen Datenbankbenutzer (TestCreditRatingcertificateAccount) zu erstellen. Das Konto hat keine Serveranmeldung und steuert ausschließlich den Zugriff auf die zugrunde liegenden Tabellen.

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

5. Erteilen der Datenbankberechtigungen für das Zertifikatkonto

Führen Sie den folgenden Code aus, damit TestCreditRatingcertificateAccount die Berechtigungen für die Basistabellen und die gespeicherte Prozedur erteilt werden.

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

Weitere Informationen zum Erteilen von Berechtigungen für Objekte finden Sie unter GRANT (Transact-SQL).For more information on grant permissions to objects, see GRANT (Transact-SQL).

6. Anzeigen des Zugriffskontexts

Damit die Berechtigungen angezeigt werden können, die mit dem Zugriff über die gespeicherte Prozedur verknüpft sind, führen Sie den folgenden Code aus. Der Code erteilt dem Benutzer TestCreditRatingUser die Berechtigung, die gespeicherte Prozedur auszuführen.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Führen Sie nun den folgenden Code aus, um die gespeicherte Prozedur mit der dbo-Anmeldung auszuführen, die Sie auf dem Server verwendet haben. Sehen Sie sich die Informationen an, die für den Benutzerkontext ausgegeben wurden. Die Informationen zeigen, dass das Konto dbo der Kontext mit seinen eigenen Berechtigungen ist, die Berechtigungen also nicht über eine Gruppenmitgliedschaft erteilt wurden.

EXECUTE TestCreditRatingSP;  
GO

Führen Sie den folgenden Code aus. In dem Code wird die EXECUTE AS -Anweisung dazu verwendet, die gespeicherte Prozedur unter dem Konto TestCreditRatingUser auszuführen. Diesmal ist zu sehen, dass der Kontext auf den USER MAPPED TO CERTIFICATE-Kontext (Einem Zertifikat zugeordneter Datenbankbenutzer) festgelegt ist. Beachten Sie, dass diese Option in eigenständigen Datenbanken, Azure SQL-Datenbank und Azure Synapse Analytics nicht unterstützt wird.

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

Auf diese Weise wird die Überwachung demonstriert, die verfügbar ist, weil Sie die gespeicherte Prozedur signiert haben.

Hinweis

Verwenden Sie EXECUTE AS zum Wechseln des Kontexts in einer Datenbank.

7. Zurücksetzen der Umgebung

Im folgenden Code wird die REVERT-Anweisung verwendet, um den Kontext des aktuellen Kontos auf dbo zurückzusetzen, und dann die Umgebung zurückgesetzt.

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

Weitere Informationen zur REVERT-Anweisung finden Sie unter REVERT (Transact-SQL).For more information about the REVERT statement, see REVERT (Transact-SQL).

Vollständiges Beispiel

In diesem Abschnitt wird der vollständige Beispielcode angezeigt.

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

Weitere Informationen

Sicherheitscenter für SQL Server-Datenbank-Engine und Azure SQL-Datenbank