Lernprogramm: Signieren von gespeicherten Prozeduren mit einem Zertifikat

In diesem Lernprogramm 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 AdventureWorks-Datenbank installiert haben. Szenario

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 AdventureWorks-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:

1. Konfigurieren der Umgebung.

2. Erstellen eines Zertifikats.

3. Erstellen einer gespeicherten Prozedur und Signieren der Prozedur mithilfe des Zertifikats.

4. Erstellen eines Zertifikatkontos mithilfe des Zertifikats.

5. Erteilen der Datenbankberechtigungen für das Zertifikatkonto.

6. Anzeigen des Zugriffskontextes.

7. Zurücksetzen der Umgebung.

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.

1. Konfigurieren der Umgebung

Zum Festlegen des Anfangskontextes für das Beispiel öffnen Sie in SQL Server Management Studio eine neue Abfrage und führen den folgenden Code aus, um die AdventureWorks-Datenbank zu öffnen. Dieser Code ändert den Datenbankkontext zu AdventureWorks und erstellt eine neue Serveranmeldung sowie ein neues Datenbank-Benutzerkonto (TestCreditRatingUser), wobei ein Kennwort verwendet wird.

USE AdventureWorks;
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). Weitere Informationen zur CREATE LOGIN-Anweisung finden Sie unter 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/05/2010';
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 im 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 zu Datenbankschemas finden Sie unter Schemas (Datenbankmodul).

Weitere Informationen zum Signieren von gespeicherten 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 AdventureWorks;
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).

6. Anzeigen des Zugriffskontextes

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.

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
Sie haben zwei Möglichkeiten, einem Benutzer ein Wechseln des Kontextes in einer Datenbank zu ermöglichen: SETUSER oder EXECUTE AS. Weitere Informationen zum Kontextwechsel finden Sie unter EXECUTE AS im Vergleich zu SETUSER.

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).

Vollständiges Beispiel

In diesem Abschnitt wird der vollständige Beispielcode angezeigt.

/* Step 1 - Open the AdventureWorks database */
USE AdventureWorks;
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 AdventureWorks database */
CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
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 AdventureWorks;
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

Siehe auch

Konzepte

Sicherheit und Schutz (Datenbankmodul)

Sichere Entwicklung (Datenbankmodul)