Lernprogramm: Signieren von gespeicherten Prozeduren mit einem ZertifikatTutorial: Signing Stored Procedures with a Certificate

In diesem Lernprogramm wird erläutert, wie gespeicherte Prozeduren mit einem Zertifikat signiert werden können, das von SQL ServerSQL Servergeneriert wurde.This tutorial illustrates signing stored procedures using a certificate generated by SQL ServerSQL Server.

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 AdventureWorks2012AdventureWorks2012 -Datenbank installiert haben.To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2012AdventureWorks2012 database installed. SzenarioScenario

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.Signing stored procedures using a certificate is useful when you want to require permissions on the stored procedure but you do not want to explicitly grant a user those rights. 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.Although you can accomplish this task in other ways, such as using the EXECUTE AS statement, using a certificate allows you to use a trace to find the original caller of the stored procedure. Auf diese Weise erreichen Sie ein hohes Maß an Überwachung, insbesondere bei der Sicherheit von DDL-Vorgängen (Data Definition Language, Datendefinitionssprache).This provides a high level of auditing, especially during security or Data Definition Language (DDL) operations.

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.You can create a certificate in the master database to allow server-level permissions, or you can create a certificate in any user databases to allow database-level permissions. In diesem Szenario muss ein Benutzer, der keine Berechtigungen für die Basistabellen hat, auf eine gespeicherte Prozedur in der AdventureWorks2012AdventureWorks2012 -Datenbank zugreifen. Außerdem soll in diesem Szenario der Objektzugriffspfad überwacht werden.In this scenario, a user with no rights to base tables must access a stored procedure in the AdventureWorks2012AdventureWorks2012 database, and you want to audit the object access trail. 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.Rather than using other ownership chain methods, you will create a server and database user account with no rights to the base objects, and a database user account with rights to a table and a stored procedure. Sowohl die gespeicherte Prozedur als auch das zweite Datenbank-Benutzerkonto werden mit einem Zertifikat gesichert.Both the stored procedure and the second database user account will be secured with a certificate. Das zweite Datenbank-Benutzerkonto hat Zugriff auf alle Objekte und erteilt dem ersten Datenbank-Benutzerkonto die Berechtigung zum Zugreifen auf die gespeicherte Prozedur.The second database account will have access to all objects, and grant access to the stored procedure to the first database user account.

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:In this scenario you will first create a database certificate, a stored procedure, and a user, and then you will test the process following these steps:

  1. Konfigurieren der Umgebung.Configure the environment.

  2. Erstellen eines Zertifikats.Create a certificate.

  3. Erstellen einer gespeicherten Prozedur und Signieren der Prozedur mithilfe des Zertifikats.Create and sign a stored procedure using the certificate.

  4. Erstellen eines Zertifikatkontos mithilfe des Zertifikats.Create a certificate account using the certificate.

  5. Erteilen der Datenbankberechtigungen für das Zertifikatkonto.Grant the certificate account database rights.

  6. Anzeigen des Zugriffskontexts.Display the access context.

  7. Zurücksetzen der Umgebung.Reset the environment.

Jeder Codeblock dieses Beispiels wird jeweils sofort erläutert.Each code block in this example is explained in line. Informationen, wie Sie das vollständige Beispiel kopieren können, finden Sie unter Vollständiges Beispiel am Ende dieses Lernprogramms.To copy the complete example, see Complete Example at the end of this tutorial.

1. Konfigurieren der Umgebung1. Configure the Environment

Zum Festlegen des Anfangskontexts für das Beispiel öffnen Sie in SQL Server Management StudioSQL Server Management Studio eine neue Abfrage und führen den folgenden Code aus, um die AdventureWorks2012AdventureWorks2012 -Datenbank zu öffnen.To set the initial context of the example, in SQL Server Management StudioSQL Server Management Studio open a new Query and run the following code to open the AdventureWorks2012AdventureWorks2012 database. Dieser Code ändert den Datenbankkontext zu AdventureWorks2012 und erstellt eine neue Serveranmeldung sowie ein neues Datenbank-Benutzerkonto (TestCreditRatingUser), wobei ein Kennwort verwendet wird.This code changes the database context to AdventureWorks2012 and creates a new server login and database user account (TestCreditRatingUser), using a password.

USE AdventureWorks2012;  
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 Zertifikats2. Create a Certificate

Zertifikate können Sie auf dem Server erstellen, indem Sie die master-Datenbank, eine Benutzerdatenbank oder beide als Kontext verwenden.You can create certificates in the server using the master database as the context, using a user database, or both. Für das Sichern eines Zertifikats gibt es mehrere Optionen.There are multiple options for securing the certificate. Weitere Informationen zum Erstellen eines Zertifikats finden Sie unter CREATE CERTIFICATE (Transact-SQL).For more information on certificates, see CREATE CERTIFICATE (Transact-SQL).

Führen Sie den folgenden Code aus, um ein Datenbankzertifikat zu erstellen und mit einem Kennwort zu sichern.Run this code to create a database certificate and secure it using a password.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
GO  

3. Erstellen einer gespeicherten Prozedur und Signieren der Prozedur mithilfe des Zertifikats3. Create and Sign a Stored Procedure Using the Certificate

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.Use the following code to create a stored procedure that selects data from the Vendor table in the Purchasing database schema, restricting access to only the companies with a credit rating of 1. 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.Note that the first section of the stored procedure displays the context of the user account running the stored procedure, which is to demonstrate the concepts only. Es ist nicht erforderlich, die Anforderungen zu erfüllen.It is not required to satisfy the requirements.

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.Run this code to sign the stored procedure with the database certificate, using a password.

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

Weitere Informationen zum Aufrufen von gespeicherten Prozeduren finden Sie unter Gespeicherte Prozeduren (Datenbankmodul).For more information on stored procedures, see Stored Procedures (Database Engine).

Weitere Informationen zum Signieren von gespeicherten Prozeduren finden Sie unter ADD SIGNATURE (Transact-SQL).For more information on signing stored procedures, see ADD SIGNATURE (Transact-SQL).

4. Erstellen eines Zertifikatkontos mithilfe des Zertifikats4. Create a Certificate Account Using the Certificate

Führen Sie den folgenden Code aus, um über das Zertifikat einen Datenbankbenutzer (TestCreditRatingcertificateAccount) zu erstellen.Run this code to create a database user (TestCreditRatingcertificateAccount) from the certificate. Das Konto hat keine Serveranmeldung und steuert ausschließlich den Zugriff auf die zugrunde liegenden Tabellen.This account has no server login, and will ultimately control access to the underlying tables.

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

5. Erteilen der Datenbankberechtigungen für das Zertifikatkonto5. Grant the Certificate Account Database Rights

Führen Sie den folgenden Code aus, damit TestCreditRatingcertificateAccount die Berechtigungen für die Basistabellen und die gespeicherte Prozedur erteilt werden.Run this code to grant TestCreditRatingcertificateAccount rights to the base table and the stored procedure.

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 granting permissions to objects, see GRANT (Transact-SQL).

6. Anzeigen des Zugriffskontexts6. Display the Access Context

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.To display the rights associated with the stored procedure access, run the following code to grant the rights to run the stored procedure to the TestCreditRatingUser user.

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.Next, run the following code to run the stored procedure as the dbo login you used on the server. Sehen Sie sich die Informationen an, die für den Benutzerkontext ausgegeben wurden.Observe the output of the user context information. Die Informationen zeigen, dass das Konto dbo der Kontext mit seinen eigenen Berechtigungen ist, die Berechtigungen also nicht über eine Gruppenmitgliedschaft erteilt wurden.It will show the dbo account as the context with its own rights and not through a group membership.

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.Run the following code to use the EXECUTE AS statement to become the TestCreditRatingUser account and run the stored procedure. Diesmal ist zu sehen, dass der Kontext auf den USER MAPPED TO CERTIFICATE-Kontext (Einem Zertifikat zugeordneter Datenbankbenutzer) festgelegt ist.This time you will see the user context is set to the USER MAPPED TO CERTIFICATE context.

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.This shows you the auditing available because you signed the stored procedure.

Hinweis

Verwenden Sie EXECUTE AS zum Wechseln des Kontexts in einer Datenbank.Use EXECUTE AS to switch contexts within a database.

7. Zurücksetzen der Umgebung7. Reset the Environment

Im folgenden Code wird die REVERT-Anweisung verwendet, um den Kontext des aktuellen Kontos auf dbo zurückzusetzen, und dann die Umgebung zurückgesetzt.The following code uses the REVERT statement to return the context of the current account to dbo, and resets the environment.

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 BeispielComplete Example

In diesem Abschnitt wird der vollständige Beispielcode angezeigt.This section displays the complete example code.

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

Sicherheitscenter für SQL Server-Datenbankmodul und Azure SQL-DatenbankSecurity Center for SQL Server Database Engine and Azure SQL Database