Вопрос: Вызов 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?


7
2018-06-25 21:28


Источник


Решаемые. Для решения было три части: цепочка прав собственности должна быть включена на сервере; пользователь, используемый в инструкции EXECUTE AS, должен быть sa или пользователем с аналогичными разрешениями для запуска заданий xp_sqlagent_ *; и работа должна принадлежать тому же пользователю, который указан в инструкции EXECUTE AS. - Ed Leighton-Dick
Еще немного экспериментов показало одну вариацию этого решения. Если вы хотите использовать прокси-пользователя, не являющегося SA, для выполнения задания, вы можете предоставить разрешения пользователя EXECUTE для прокси-сервера для процедур xp_sqlagent_ * в основной базе данных. (Остальные два требования - совместное использование базы данных и владение собственностью) по-прежнему применяются.) - Ed Leighton-Dick


Ответы:


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

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


4
2018-06-25 23:07



Да. Я начал с добавления его в роль SQLAgentOperator, а затем попробовал прямые разрешения EXECUTE непосредственно на sp_start_job. Ничего не помогло. Кажется, что эта ошибка возникает независимо от разрешений прокси - даже сбой учетной записи уровня sysadmin. - Ed Leighton-Dick
Используйте SQL Profiler и посмотрите, какая учетная запись фактически выполняет вызов. Теперь, когда я думаю об этом больше, Execute As использует пользователя базы данных, который, вероятно, неправильно переводится в другую базу данных. Попробуйте включить цепочку привязки базы данных и посмотрите, работает ли это. - mrdenny
Собственность цепочки была большой частью решения, поэтому я награждаю пункты здесь. Оказывается, что есть еще две части; Я отмечу выше. - Ed Leighton-Dick


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

ВЫПОЛНИТЬ КАК ОБЪЕКТ

Предложения 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):

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


9
2017-07-19 19:56





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

http://www.stackoverflow.com


0
2018-06-25 22:25



Я думаю, что это, вероятно, проблема безопасности базы данных, но я попробую StackOverflow, если мы не сможем найти ответ здесь. - Ed Leighton-Dick


Проверка случайного 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 и дважды проверьте, не унаследовал ли я какой-либо явный доступ к запрету.

НТН.


0
2017-07-09 23:59



Пользователь явно является членом SQLAgentOperatorRole и SQLAgentUserRole. - Ed Leighton-Dick


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

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


0
2017-09-29 13:46