Назад | Перейти на главную страницу

Вызов sp_start_job из хранимой процедуры

Наши разработчики должны иметь возможность запускать задание агента SQL Server из своего кода .Net. Я знаю, что могу позвонить msdb..sp_start_job для этого, но я не хочу давать обычным учетным записям пользователей прямой доступ для выполнения заданий.

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

CREATE PROCEDURE dbo.StartAgentJob 
    WITH EXECUTE AS 'agentProxy'
AS
BEGIN
    EXEC msdb.dbo.sp_start_job N'RunThisJob';
END

Однако когда мы запускаем это, мы получаем следующее сообщение:

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Любые идеи? Это лучший способ сделать это в SQL2005?

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

EXECUTE AS область олицетворения

Предложения EXECUTE AS бывают двух видов: EXECUTE AS LOGIN и EXECUTE AS USER. EXECUTE AS LOGIN аутентифицируется сервером и является контекстом олицетворения, которому доверяет весь экземпляр SQL (в области сервера):

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

EXECUTE AS USER аутентифицируется базой данных и является контекстом олицетворения, которому доверяет только эта база данных (в области базы данных):

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

Хранимая процедура, имеющая предложение EXECUTE AS, создаст контекст олицетворения в области базы данных и, как таковая, не сможет ссылаться на объекты вне базы данных, в данном случае вы не сможете ссылаться msdb.dbo.sp_start_job потому что в msdb. Доступно множество других примеров, таких как попытка доступа к DMV области сервера, попытка использовать связанный сервер или попытка доставить сообщение Service Broker в другую базу данных.

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

  • Включив свойство TRUSTWORTHY в базе данных, которая аутентифицировала контекст олицетворения (т. Е. В базе данных, в которой было выдано предложение EXECUTE AS).
  • Используя подписи кода.

Эти подробности описаны в MSDN: Расширение олицетворения базы данных с помощью EXECUTE AS.

Когда вы решили проблему с помощью перекрестной цепочки владения базой данных, вы включили перекрестную цепочку баз данных на уровне всего сервера, что считается угрозой безопасности. Наиболее контролируемый и детализированный способ достижения желаемого результата - использовать подпись кода:

  • В базе данных приложения создайте самоподписанный сертификат
  • подписать dbo.StartAgentJob с этим сертификатом
  • сбросить закрытый ключ сертификата
  • экспортировать сертификат на диск
  • импортировать сертификат в msdb
  • создать производного пользователя из импортированного сертификата в msdb
  • предоставить разрешение AUTHENTICATE производному пользователю в msdb

Эти шаги гарантируют, что контекст EXECUTE AS dbo.StartAgentJob процедура теперь доверена msdb, потому что контекст подписан участником, имеющим разрешение AUTHENTICATE в msdb. Это решает половину головоломки. Другая половина - фактически предоставить разрешение EXECUTE на msdb.dbo.sp_start_job в теперь надежный контекст олицетворения. Есть несколько способов сделать это:

  1. сопоставить выданного пользователя agentProxy пользователь в msdb и дать ему разрешение на исполнение msdb.dbo.sp_start_job
  2. предоставить разрешение на выполнение msdb пользователь, полученный из сертификата аутентификатора
  3. добавить в процедуру новую подпись, получить для нее пользователя в msdb и предоставить разрешение на выполнение этому производному пользователю

Вариант 1. прост, но имеет большой недостаток: agentProxy теперь пользователь может выполнить msdb.dbo.sp_start_job по собственному желанию, ему действительно предоставляется доступ к msdb и имеет разрешение на выполнение.

Вариант 3 безусловно правильный, но я считаю, что это излишний перебор.

Поэтому я предпочитаю вариант 2: предоставить разрешение EXECUTE на msdb.dbo.sp_start_job полученному от сертификата пользователю, созданному в msdb.

Вот соответствующий SQL:

use [<appdb>];
go

create certificate agentProxy 
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
    with subject = 'agentProxy'
   , start_date='01/01/2009';
go

ADD SIGNATURE TO OBJECT::[StartAgentJob]
      BY CERTIFICATE [agentProxy]
        WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
go

alter certificate [agentProxy] 
  remove private key;
go

backup certificate [agentProxy] 
 to file='c:\temp\agentProxy.cer';
go

use msdb
go

create certificate [agentProxy] 
  from file='c:\temp\agentProxy.cer';
go

create user [agentProxyAuthenticator] 
 from certificate [agentProxy];
go

grant authenticate to [agentProxyAuthenticator];
grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator];
go

use [<appdb>];
go

exec dbo.StartAgentJob;
go

В моем блоге есть несколько статей по этой теме, написанных в контексте процедур активации Service Broker (поскольку для них требуется условие EXECUTE AS):

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

Вы поместили логин agentProxy в базу данных msdb и дали ему права на запуск sp_start_job? Если нет, вам нужно включить цепочку разрешений базы данных для базы данных msdb и вашей пользовательской базы данных.

Вероятно, вам лучше поместить логин в базу данных msdb и предоставить ему правильные права.

Поскольку вы пытаетесь запустить агент SQL Server из кода .NET, это может быть лучший вопрос для StackOverflow?

http://www.stackoverflow.com

Проверка случайного экземпляра SQL в сети SQLAgentOperatorRole не дает вам напрямую привилегий sp_start_job, он наследует их от SQLAgentUserRole.

Дважды проверьте это, используя:

select dp.NAME AS principal_name,
                 dp.type_desc AS principal_type_desc,
                 o.NAME AS object_name,
                 p.permission_name,
                 p.state_desc AS permission_state_desc 
    from    sys.database_permissions p
    left    OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID
    inner   JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
    where o.name = 'sp_start_job'

Запустите это в MSDB и дважды проверьте, не унаследовали ли вы явный отказ в доступе.

hth.

Один из способов добиться этого без предоставления дополнительных разрешений: не позволять хранимой процедуре запускать задание напрямую, а просто разрешить сохраненной процедуре немного перевернуть бит в таблице (в базе данных приложения); затем позвольте заданию выполняться примерно каждую минуту, проверьте, перевернут ли бит, и если да, выполните работу и снова переверните бит. Если задание видит, что бит не перевернут, задание просто завершится.

Работает как шарм, если вы не возражаете против задержки (а работа выполняется очень часто).