Учебник. Подписывание хранимых процедур с помощью сертификата

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этом руководстве демонстрируется подписание хранимых процедур с помощью сертификата, созданного SQL Server.

Примечание.

Для запуска кода в этом учебнике необходимо, чтобы был настроен режим смешанной безопасности. Кроме того, необходимо установить базу данных AdventureWorks2022 .

Подписывание хранимой процедуры с помощью сертификата полезно в том случае, если для хранимой процедуры необходимо требовать разрешения, но явно предоставлять пользователям эти права нежелательно. Хотя эту задачу можно выполнить и другими способами, такими как инструкция EXECUTE AS, использование сертификата позволяет применить трассировку, чтобы найти участника, вызвавшего хранимую процедуру. Таким образом обеспечивается высокий уровень аудита, особенно во время выполнения операций безопасности или операций языка описания данных DDL.

Можно создать сертификат в базе данных master (чтобы предоставлять разрешения уровня сервера) или в любой другой пользовательской базе данных (для предоставления разрешений уровня базы данных). В этом сценарии пользователь, не обладающий правами на базовые таблицы, должен получить доступ к хранимой процедуре в базе данных AdventureWorks2022 , при этом необходимо отследить путь доступа к объекту. Вместо того чтобы использовать другие методы цепочки владения, будет создана учетная запись пользователя сервера и базы данных без прав на базовые объекты, а также учетная запись пользователя базы данных с правами на таблицы и хранимые процедуры. Безопасность хранимой процедуры и второй учетной записи пользователя базы данных будет обеспечена сертификатом. Вторая учетная запись пользователя будет обладать доступом ко всем объектам. Она предоставляет доступ к хранимой процедуре первой учетной записи пользователя.

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

Каждый блок кода в этом примере объясняется по порядку. Чтобы скопировать весь пример, см. раздел Пример целиком в конце этого учебника.

Необходимые компоненты

Для работы с этим учебником требуется среда SQL Server Management Studio, доступ к серверу SQL Server и база данных AdventureWorks.

• Установите SQL Server Management Studio.
• Установите выпуск SQL Server 2017 Developer Edition.
• Скачайте примеры баз данных AdventureWorks.

Инструкции по восстановлению базы данных в SQL Server Management Studio см. в разделе Восстановление базы данных.

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

Чтобы задать начальный контекст примера, в SQL Server Management Studio откройте новый запрос и запустите следующий код, чтобы открыть AdventureWorks2022 базу данных. Этот код изменяет контекст базы данных на AdventureWorks2022 , затем создает новое имя входа сервера и новую учетную запись пользователя базы данных (TestCreditRatingUser) с использованием пароля.

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  

Дополнительные сведения об инструкции CREATE USER см. в статье CREATE USER (Transact-SQL). Дополнительные сведения об инструкции CREATE LOGIN см. в статье CREATE LOGIN (Transact-SQL).

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

Можно создавать сертификаты на сервере, использующем в качестве контекста базу данных master, базу данных пользователя или обе базы одновременно. Есть несколько вариантов обеспечения безопасности сертификата. Дополнительные сведения о сертификатах см. в статье CREATE CERTIFICATE (Transact-SQL).

Запустите этот код, чтобы создать сертификат базы данных и защитить его паролем.

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. Создание и подписывание хранимой процедуры с помощью сертификата

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

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  

Запустите этот код, чтобы подписывать хранимую процедуру сертификатом базы данных с использованием пароля.

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

Дополнительные сведения о хранимых процедурах см. в разделе "Хранимые процедуры" (ядро СУБД).

Дополнительные сведения о подписи хранимых процедур см. в разделе ADD SIGNATURE (Transact-SQL).

4. Создание учетной записи сертификата с помощью сертификата

Запустите этот код, чтобы создать пользователя базы данных (TestCreditRatingcertificateAccount) из сертификата. У этой учетной записи нет имени входа сервера. Она в конечном итоге предназначена для управления доступом к базовым таблицам.

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

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

Запустите этот код, чтобы предоставить учетной записи TestCreditRatingcertificateAccount права на базовую таблицу и хранимую процедуру.

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

Дополнительные сведения о предоставлении разрешений объектам см. в разделе GRANT (Transact-SQL).

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

Для отображения прав, связанных с доступом хранимой процедуры, запустите следующий код, чтобы предоставить права на запуск хранимой процедуры пользователю TestCreditRatingUser .

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  

После этого с помощью приведенного ниже кода запустите хранимую процедуру от имени входа dbo, которое было использовано на сервере. Просмотрите вывод сведений о контексте пользователя. Учетная запись dbo будет показана как контекст со своими собственными правами, а не через членство в группе.

EXECUTE TestCreditRatingSP;  
GO  

Запустите следующий код, чтобы с помощью инструкции EXECUTE AS от имени учетной записи TestCreditRatingUser выполнить хранимую процедуру. На этот раз будет показано, что задан контекст пользователя USER MAPPED TO CERTIFICATE. Обратите внимание, что этот параметр не поддерживается в автономной базе данных или базе данных SQL Azure или Azure Synapse Analytics.

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

Это означает, что подписывание хранимой процедуры сделало доступным аудит.

Примечание.

Использование EXECUTE AS для переключения контекстов в базе данных.

7. Сброс среды

В приведенном ниже коде с помощью инструкции REVERT контекст текущей учетной записи изменяется на dbo. Затем выполняется сброс среды.

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

Полный пример

В этом разделе приведен полный код примера.

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

См. также

Центр безопасности для ядра СУБД SQL Server и Базы данных Azure SQL