EXECUTE AS

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

Варианты следующие:

1. Выбираем нужные таблицы и даем пользователю право читать их. Неудобно то, что все нужно будет повторять для каждого отчета и пользователя. Введение специальных ролей тоже не особо поможет, т.к. для каждого отчета набор таблиц обычно разный, плюс со временем может меняться как сам набор таблиц, так и пользователи, которым разрешен запуск отчета.
2. Включаем пользователя в роль db_datareader. В этом случае проблем с администрированием почти никаких, но пользователь будет иметь возможность читать все таблицы базы данных. В большинстве случаев политика безопасности предприятия не позволяет делать так.
3. Дать пользователю только право запускать хранимую процедуру, а уже внутри процедуры переключить контекст с помощью конструкции EXECUTE AS. В этом случае пользователь не будет иметь никаких избыточных прав, а администрирование сводится только к выдаче прав на конкретную процедуру.

Пример процедуры, реализующей последний вариант:

  1: USE iScalaDB
  2: 
  3: -- создаем новый логин
  4: CREATE LOGIN ReportUserLogin WITH PASSWORD='ERFss$#45%$'
  5: GO
  6: 
  7: -- создаем пользователя, на которого будем переключать контекст
  8: CREATE USER ReportUser FOR LOGIN ReportUserLogin
  9: GO
 10: 
 11: -- даем вспомогательному пользователю право читать всю базу данных
 12: EXEC sp_addrolemember 'db_datareader','ReportUser'
 13: GO
 14: 
 15: -- создаем проверочную процедру
 16: CREATE PROCEDURE dbo.TestExecuteAS
 17: WITH EXECUTE AS 'ReportUser'
 18: AS
 19: BEGIN
 20:     SET NOCOUNT ON
 21: 
 22:     SELECT *
 23:     FROM GL060108
 24: END
 25: GO
 26: 

Конструкция EXECUTE AS появилась в MS SQL2005, в более ранних версиях была команда SETUSER, но она имела ряд ограничений, которые не позволяли использовать ее в нашем сценарии.