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

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

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

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

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

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

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

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

Для работы с этим учебником требуется среда 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 откройте новый запрос и запустите следующий код, чтобы открыть AdventureWorks2025 базу данных. Этот код изменяет контекст базы данных на AdventureWorks2025 , затем создает новое имя входа сервера и новую учетную запись пользователя базы данных (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, сопоставленный с CERTIFICATE. Обратите внимание, что этот параметр не поддерживается в автономной базе данных или базе данных SQL Azure или Azure Synapse Analytics.

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

Это показывает доступные возможности аудита, поскольку вы подписали хранимую процедуру.

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