Руководство по подписи хранимых процедур с помощью сертификата

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

Замечание

Чтобы запустить код в этом руководстве, необходимо настроить как безопасность смешанного режима, так и базу данных AdventureWorks2012 . Сценарий

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

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

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

Настройка среды.
Создание сертификата.
Создайте и зарегистрируйте хранимую процедуру с помощью сертификата.
Создайте учетную запись сертификата с помощью сертификата.
Предоставьте учетной записи сертификата права на базу данных.
Отображение контекста доступа.
Сброс среды.

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

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

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

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

Дополнительные сведения об инструкции 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/05/2014';  
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 AdventureWorks2012;  
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.

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

См. также

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

Last updated on 2017-06-14