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

Статья
12/15/2008

Добавления: 12 декабря 2006 г.

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

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

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

Сценарий

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

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

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

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

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

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

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

USE AdventureWorks;
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/2010';
GO

3. Создание и подписывание хранимой процедуры с помощью сертификата

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

Дополнительные сведения о хранимых процедурах см. в разделе Хранимые процедуры (компонент Database Engine).

Дополнительные сведения о схемах баз данных см. в разделе Схемы.

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

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

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

USE AdventureWorks;
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

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

Примечание.
Есть два способа разрешить пользователю переключение контекстов в базе данных: SETUSER или EXECUTE AS. Дополнительные сведения о переключении контекста см. в разделе EXECUTE AS и SETUSER.

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 AdventureWorks database */
USE AdventureWorks;
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 AdventureWorks database */
CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
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 AdventureWorks;
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
Вопросы безопасности баз данных и приложений для работы с базами данных

Справка и поддержка

Получение помощи по SQL Server 2005

Поделиться через