Руководство. Подписывание хранимых процедур с помощью сертификатаTutorial: Signing Stored Procedures with a Certificate

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server нетБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

В этом учебнике демонстрируется подписание хранимых процедур с помощью сертификата, созданного SQL ServerSQL Server.This tutorial illustrates signing stored procedures using a certificate generated by SQL ServerSQL Server.

Примечание

Для запуска кода в этом учебнике необходимо, чтобы был настроен режим смешанной безопасности. Кроме того, необходимо наличие установленной базы данных AdventureWorks2017.To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2017 database installed.

Подписывание хранимой процедуры с помощью сертификата полезно в том случае, если для хранимой процедуры необходимо требовать разрешения, но явно предоставлять пользователям эти права нежелательно.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. Хотя эту задачу можно выполнить и другими способами, такими как инструкция EXECUTE AS, использование сертификата позволяет применить трассировку, чтобы найти участника, вызвавшего хранимую процедуру.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. Таким образом обеспечивается высокий уровень аудита, особенно во время выполнения операций безопасности или операций языка описания данных DDL.This provides a high level of auditing, especially during security or Data Definition Language (DDL) operations.

Можно создать сертификат в базе данных master (чтобы предоставлять разрешения уровня сервера) или в любой другой пользовательской базе данных (для предоставления разрешений уровня базы данных).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. В этом сценарии пользователь, не обладающий правами на базовые таблицы, должен получить доступ к хранимой процедуре в базе данных AdventureWorks2017, при этом необходимо проконтролировать след доступа к объекту.In this scenario, a user with no rights to base tables must access a stored procedure in the AdventureWorks2017 database, and you want to audit the object access trail. Вместо того чтобы использовать другие методы цепочки владения, будет создана учетная запись пользователя сервера и базы данных без прав на базовые объекты, а также учетная запись пользователя базы данных с правами на таблицы и хранимые процедуры.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. Безопасность хранимой процедуры и второй учетной записи пользователя базы данных будет обеспечена сертификатом.Both the stored procedure and the second database user account will be secured with a certificate. Вторая учетная запись пользователя будет обладать доступом ко всем объектам. Она предоставляет доступ к хранимой процедуре первой учетной записи пользователя.The second database account will have access to all objects, and grant access to the stored procedure to the first database user account.

В этом сценарии сначала создается сертификат базы данных, хранимая процедура и пользователь, затем весь процесс проверяется с помощью следующих шагов: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:

Каждый блок кода в этом примере объясняется по порядку.Each code block in this example is explained in line. Чтобы скопировать весь пример, см. раздел Пример целиком в конце этого учебника.To copy the complete example, see Complete Example at the end of this tutorial.

предварительные требованияPrerequisites

Для работы с этим учебником требуется среда SQL Server Management Studio, доступ к серверу SQL Server и база данных AdventureWorks.To complete this tutorial, you need SQL Server Management Studio, access to a server that's running SQL Server, and an AdventureWorks database.

Инструкции по восстановлению базы данных в SQL Server Management Studio см. в разделе Восстановление базы данных.For instructions on restoring a database in SQL Server Management Studio, see Restore a database.

1. Настройка среды1. Configure the Environment

Чтобы задать начальный контекст в этом примере, откройте в SQL Server Management StudioSQL Server Management Studio новый запрос и откройте базу данных Adventureworks2017 с помощью приведенного ниже кода.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 Adventureworks2017 database. Этот код изменяет контекст базы данных на AdventureWorks2012 , затем создает новое имя входа сервера и новую учетную запись пользователя базы данных (TestCreditRatingUser) с использованием пароля.This code changes the database context to AdventureWorks2012 and creates a new server login and database user account (TestCreditRatingUser), using a password.

USE AdventureWorks2017;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  

Дополнительные сведения об инструкции CREATE USER см. в разделе CREATE USER (Transact-SQL).For more information on the CREATE USER statement, see CREATE USER (Transact-SQL). Дополнительные сведения об инструкции CREATE LOGIN см. в разделе CREATE LOGIN (Transact-SQL).For more information on the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

2. Создание сертификата2. Create a Certificate

Можно создавать сертификаты на сервере, использующем в качестве контекста базу данных master, базу данных пользователя или обе базы одновременно.You can create certificates in the server using the master database as the context, using a user database, or both. Есть несколько вариантов обеспечения безопасности сертификата.There are multiple options for securing the certificate. Дополнительные сведения о сертификатах см. в разделе CREATE CERTIFICATE (Transact-SQL).For more information on certificates, see CREATE CERTIFICATE (Transact-SQL).

Запустите этот код, чтобы создать сертификат базы данных и защитить его паролем.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/2020';  -- Error 3701 will occur if this date is not in the future
GO  

3. Создание и подписывание хранимой процедуры с помощью сертификата3. Create and Sign a Stored Procedure Using the Certificate

Используйте следующий код, чтобы создать хранимую процедуру, которая выбирает данные из таблицы Vendor в схеме базы данных Purchasing, ограничивая доступ и предоставляя его только для компаний с уровнем кредитоспособности 1.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. В первом разделе хранимой процедуры в целях демонстрации основных принципов работы выводится контекст учетной записи пользователя, с которой работает процедура.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. Удовлетворять требованиям не обязательно.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  

Запустите этот код, чтобы подписывать хранимую процедуру сертификатом базы данных с использованием пароля.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  

Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (компонент Database Engine).For more information on stored procedures, see Stored Procedures (Database Engine).

Дополнительные сведения о подписывании хранимых процедур см. в разделе ADD SIGNATURE (Transact-SQL).For more information on signing stored procedures, see ADD SIGNATURE (Transact-SQL).

4. Создание учетной записи сертификата с помощью сертификата4. Create a Certificate Account Using the Certificate

Запустите этот код, чтобы создать пользователя базы данных (TestCreditRatingcertificateAccount) из сертификата.Run this code to create a database user (TestCreditRatingcertificateAccount) from the certificate. У этой учетной записи нет имени входа сервера. Она в конечном итоге предназначена для управления доступом к базовым таблицам.This account has no server login, and will ultimately control access to the underlying tables.

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

5. Предоставление учетной записи сертификата прав на базу данных5. Grant the Certificate Account Database Rights

Запустите этот код, чтобы предоставить учетной записи TestCreditRatingcertificateAccount права на базовую таблицу и хранимую процедуру.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  

Дополнительные сведения о предоставлении разрешений объектам см. в разделе GRANT (Transact-SQL).For more information on granting permissions to objects, see GRANT (Transact-SQL).

6. Отображение контекста доступа6. Display the Access Context

Для отображения прав, связанных с доступом хранимой процедуры, запустите следующий код, чтобы предоставить права на запуск хранимой процедуры пользователю TestCreditRatingUser.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  

После этого с помощью приведенного ниже кода запустите хранимую процедуру от имени входа dbo, которое было использовано на сервере.Next, run the following code to run the stored procedure as the dbo login you used on the server. Просмотрите вывод сведений о контексте пользователя.Observe the output of the user context information. Учетная запись dbo будет показана как контекст со своими собственными правами, а не через членство в группе.It will show the dbo account as the context with its own rights and not through a group membership.

EXECUTE TestCreditRatingSP;  
GO  

Запустите следующий код, чтобы с помощью инструкции EXECUTE AS от имени учетной записи TestCreditRatingUser выполнить хранимую процедуру.Run the following code to use the EXECUTE AS statement to become the TestCreditRatingUser account and run the stored procedure. На этот раз будет показано, что задан контекст пользователя USER MAPPED TO CERTIFICATE.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  

Это означает, что подписывание хранимой процедуры сделало доступным аудит.This shows you the auditing available because you signed the stored procedure.

Примечание

Использование EXECUTE AS для переключения контекстов в базе данных.Use EXECUTE AS to switch contexts within a database.

7. Сброс среды7. Reset the Environment

В приведенном ниже коде с помощью инструкции REVERT контекст текущей учетной записи изменяется на dbo. Затем выполняется сброс среды.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  

Дополнительные сведения об инструкции REVERT см. в разделе REVERT (Transact-SQL).For more information about the REVERT statement, see REVERT (Transact-SQL).

Пример целикомComplete Example

В этом разделе приведен полный код примера.This section displays the complete example code.

/* Step 1 - Open the AdventureWorks2017 database */  
USE AdventureWorks2017;  
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/2020';   -- 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 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  

См. также:See Also

Центр обеспечения безопасности для базы данных Azure SQL и SQL Server Database EngineSecurity Center for SQL Server Database Engine and Azure SQL Database