Настройка разрешений с олицетворением в SQL Server (ADO.NET)

Статья
05/20/2011

Во многих приложениях используются хранимые процедуры для получения доступа к данным, что позволяет ограничивать доступ к базовым таблицам на основе формирования цепочки владения. При этом можно предоставлять разрешения EXECUTE для хранимых процедур, отзывая или отменяя разрешения по отношению к базовым таблицам. В СУБД SQL Server если хранимая процедура и таблицы имеют одного владельца, то разрешения вызывающего объекта не проверяются. Но формирование цепочки владения перестает действовать, если объекты имеют разных владельцев, а также в случае применения динамического кода SQL.

Начиная с версии SQL Server 2005, появилась возможность использовать предложение EXECUTE AS в хранимой процедуре, если вызывающий объект не имеет разрешений на указанные в ссылках объекты базы данных. Результат действия предложения EXECUTE AS состоит в том, что контекст выполнения переключается на пользователя-посредника. Весь код, а также все вызовы вложенных хранимых процедур или триггеров выполняются в контексте безопасности пользователя-посредника. Контекст выполнения переходит к вызывающему объекту только после выполнения процедуры или при выполнении инструкции REVERT.

Переключение контекста с помощью инструкции EXECUTE AS

Инструкция EXECUTE AS языка Transact-SQL позволяет переключать контекст выполнения инструкции путем олицетворения другого имени входа или пользователя базы данных. Это удобный метод проверки запросов и процедур от имени другого пользователя.

EXECUTE AS LOGIN = 'loginName';
EXECUTE AS USER = 'userName';

В данном случае необходимо иметь разрешения IMPERSONATE по отношению к олицетворяемому имени входа или пользователю. Это разрешение подразумевается для sysadmin во всех базах данных, а также для членов роли db_owner в базах данных, которыми они владеют.

Предоставление разрешений с помощью предложения EXECUTE AS

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

Примечание
Некоторые действия, такие как TRUNCATE TABLE, не имеют предоставляемых разрешений.Включение инструкции в процедуру и определение пользователя-посредника, имеющего разрешения ALTER TABLE, позволяет распространить разрешения на усечение таблицы на те вызывающие объекты, которые имеют только разрешения EXECUTE на процедуру.

Некоторые действия, такие как TRUNCATE TABLE, не имеют предоставляемых разрешений.Включение инструкции в процедуру и определение пользователя-посредника, имеющего разрешения ALTER TABLE, позволяет распространить разрешения на усечение таблицы на те вызывающие объекты, которые имеют только разрешения EXECUTE на процедуру.

Контекст, заданный в предложении EXECUTE AS, действует только на время выполнения процедуры, включая вложенные процедуры и триггеры. Контекст снова становится контекстом вызывающего объекта после завершения выполнения или после выдачи инструкции REVERT.

Чтобы использовать предложение EXECUTE AS в процедуре, необходимо выполнить следующие три действия.

Создать в базе данных пользователя-посредника, который не сопоставляется с именем входа. Этот шаг не является обязательным, но позволяет упростить управление разрешениями.

CREATE USER proxyUser WITHOUT LOGIN

Предоставить пользователю-посреднику необходимые разрешения.
Добавить предложение EXECUTE AS в хранимую процедуру или определяемую пользователем функцию.

CREATE PROCEDURE [procName] WITH EXECUTE AS 'proxyUser' AS ...

Примечание
Работа приложений, требующих аудита, может быть нарушена, поскольку первоначальный контекст безопасности вызывающего объекта не сохраняется.Встроенные функции, которые возвращают идентификатор текущего пользователя, такие как SESSION_USER, USER или USER_NAME, возвращают данные о пользователе, связанном с предложением EXECUTE AS, а не данные первоначального вызывающего объекта.

Работа приложений, требующих аудита, может быть нарушена, поскольку первоначальный контекст безопасности вызывающего объекта не сохраняется.Встроенные функции, которые возвращают идентификатор текущего пользователя, такие как SESSION_USER, USER или USER_NAME, возвращают данные о пользователе, связанном с предложением EXECUTE AS, а не данные первоначального вызывающего объекта.

Использование предложения EXECUTE AS с инструкцией REVERT

Инструкцию REVERT языка Transact-SQL можно использовать для возврата к первоначальному контексту выполнения.

Необязательное предложение WITH NO REVERT COOKIE = @variableName позволяет переключать контекст выполнения обратно к контексту вызывающего объекта, если переменная @variableName содержит правильное значение. Это позволяет переключать контекст выполнения обратно к контексту вызывающего объекта в тех средах, где используются пулы соединений. Поскольку значение @variableName известно только в объекте, вызывающем инструкцию EXECUTE AS, вызывающий объект может гарантировать, что контекст выполнения не может изменяться конечным пользователем, который вызывает данное приложение. Соединение после закрытия возвращается в пул. Дополнительные сведения об использовании пулов соединений в ADO.NET см. в разделе Организация пулов соединений SQL Server (ADO.NET).

Определение контекста выполнения

Предложение EXECUTE AS можно не только использовать для указания пользователя, но и указывать в нем любое из следующих ключевых слов.

CALLER. По умолчанию происходит выполнение с ключевым словом CALLER. Если не указан другой параметр, то процедура выполняется в контексте безопасности вызывающего объекта.
OWNER. Выполнение с ключевым словом OWNER приводит к выполнению процедуры в контексте владельца процедуры. Если процедура создана в схеме, принадлежащей dbo или владельцу базы данных, то процедура выполняется с неограниченными разрешениями.
SELF. Выполнение с ключевым словом SELF приводит к выполнению в контексте безопасности создателя хранимой процедуры. Это эквивалентно вызову на выполнение от имени указанного пользователя, где указанным пользователем является лицо, создавшее или изменившее процедуру.

Внешние ресурсы

Дополнительные сведения см. в следующих ресурсах.

Ресурс	Описание
Переключение контекста в электронной документации по SQL Server 2008	Содержит ссылки на разделы с описаниями способов использования предложения EXECUTE AS.
Использование инструкции EXECUTE AS для создания пользовательских наборов разрешений и раздел Использование предложения EXECUTE AS в модулях электронной документации по SQL Server 2005	Разделы содержат описание способов использования предложения EXECUTE AS.