Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выполнить sp_send_dbmail "из под" роли приложения.  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
- Клиент соединяется с сервером в качестве пользователя.
- Затем выполняется sp_setapprole

Собственно вопрос как теперь выполнить sp_send_dbmail.

sp_send_dbmail под первоначально залогиненным юзером (без прав роли) выполняется успешно.

"Под ролью" при выполнении sp_send_dbmail получаю ошибку "Запрещено разрешение Execute на обьект sp_send_dbmail..."

Добавление guest-a (msdb) в роль DatabaseMailUserRole не приводит к его "появлению" в интерфейсе списка доступных пользователей в "Личных профилях" Database Mail.

Как заставить работать?
20 окт 16, 17:31    [19805725]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Альтернативный вариант с изоляцией доступа к системной базе.

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

Ну или брокера прикрутить, хотя с джобом мне кажется решение более наглядным, да и брокера не надо запускать.
20 окт 16, 18:12    [19805826]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
PaulWist
Как заставить работать?
1. В msdb создать сертификат и пользователя на его основе.
2. Пользователя из п.1. добавить в роль DatabaseMailUserRole
3. В свою БД импортировать сертификат из п.1.
4. В своей БД создать процедуру-обертку для sp_send_dbmail, подписать ее импортированным сертификатом и дать разрешение на выполнение роли приложения.
20 окт 16, 18:57    [19805937]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
2invm

Гранд мерси за направление.

Итак результат (что делаю):

invm
1. В msdb создать сертификат и пользователя на его основе.


use MSDB
go
-- Создаём сертификат
CREATE CERTIFICATE MyMailSendCertificate 
   ENCRYPTION BY PASSWORD = 'ThisCertificateCreateByMail'
   WITH SUBJECT = 'Сертификат для отправки почты из под роли приложения', 
   EXPIRY_DATE = '01/01/2099'

-- Создаём юзера на основе сертификата
CREATE USER MyMailUserMSDB 
FOR CERTIFICATE MyMailSendCertificate
go

go




invm
2. Пользователя из п.1. добавить в роль DatabaseMailUserRole
3. В свою БД импортировать сертификат из п.1.


-- Добавляем юзера в роль DatabaseMailUserRole
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = 'MyMailUserMSDB '
go 


invm
3. В свою БД импортировать сертификат из п.1.


-- выгружаем сертификат на диск
BACKUP CERTIFICATE MyMailSendCertificate 
TO FILE = 'c:\MyMailSendCertificate.cer'
WITH PRIVATE KEY (  DECRYPTION BY PASSWORD = 'ThisCertificateCreateByMail' ,
FILE = 'c:\MyMailSendCertificateKey.key' , 
ENCRYPTION BY PASSWORD = 'ThisCertificateKeyCreateByMail' )
GO


-- Переходим на  БД
use MyDB 
go

-- Создаём сертификат путем ИПОРТА из выгруженного сертификата из БД MSDB
CREATE CERTIFICATE MyMailSendCertificate 
   FROM FILE = 'c:\MyMailSendCertificate.cer'
   WITH PRIVATE KEY (FILE = 'c:\MyMailSendCertificateKey.key', 
   DECRYPTION BY PASSWORD = 'ThisCertificateKeyCreateByMail',
   ENCRYPTION BY PASSWORD = 'ThisCertificateKeyCreateByMail')
go


invm
4. В своей БД создать процедуру-обертку для sp_send_dbmail, подписать ее импортированным сертификатом и дать разрешение на выполнение роли приложения.


ALTER PROCEDURE [MySh].[MailSend]

AS
BEGIN
	SET NOCOUNT ON

	BEGIN TRY

		exec @OutID = msdb.dbo.sp_send_dbmail
				 @profile_name = 'MSDBMail Profile',
				 @recipients ='user@firm.com' ,
				 @body = ' TEST BODY ',
				 @subject =  ' TEST SUBJ '
	
	END TRY
	BEGIN CATCH
		-- Возвращаем сообщение об ошибке
 	END CATCH

END

-- Подписываем ХП обертку над msdb.dbo.sp_send_dbmail сертификатом импортированным из MSDB
ADD SIGNATURE TO MySh.MailSend 
    BY CERTIFICATE MyMailSendCertificate
    WITH PASSWORD = 'ThisCertificateKeyCreateByMail' 
go     


Всё проходит без ошибок.

2. Теперь из приложения выполняю

exec MySh.MailSend 


Получаю первоначальную ошибку:

автор
"Запрещено разрешение Execute на обьект sp_send_dbmail..."


Где я накосячил?

PS Да, забыл:

автор
Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64) Sep 3 2014 04:11:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
21 окт 16, 11:45    [19808181]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Добавочек.

Просто под юзером, если дать ему

GRANT EXECUTE ON MySc.MailSend TO UserDB


отправка почты уходит (используя сертификат)

Тоже самое:

GRANT EXECUTE ON MySc.MailSend TO AppRole


говорит, что нет разрешения на Execute msdb.dbo.sp_send_dbmail
21 окт 16, 12:38    [19808614]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
PaulWist,

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

?
21 окт 16, 13:37    [19809036]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
PaulWist,

Сделайте так - https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/

Или, если без заморочек, но не очень безопасно то:
1. Смените у вашей БД владельца на sa и включите trustworthy.
2. Процедуру-обертку создайте с указанием контекста выполнения - with execute as 'dbo'
21 окт 16, 13:41    [19809057]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
2Владислав Колосов

Владислав Колосов
PaulWist,

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

?


Влад, в первом посте я написал:
PaulWist
Добавление guest-a (msdb) в роль DatabaseMailUserRole не приводит к его "появлению" в интерфейсе списка доступных пользователей в "Личных профилях" Database Mail.


Гость в msdb не отключен.

2invm

invm
PaulWist,

Сделайте так - https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/


Те же яйца только в профиль :( (для роли не работает, во всяком случае у меня не получилось)

invm
Или, если без заморочек, но не очень безопасно то:
1. Смените у вашей БД владельца на sa и включите trustworthy.
2. Процедуру-обертку создайте с указанием контекста выполнения - with execute as 'dbo'


Мдя, ... я понимаю, что это предложение от "безисходности", но в таком случае можно было с сертификатами не заморачиваться :)

Спасибо за участие, ещё поковыряюсь ...
21 окт 16, 14:53    [19809496]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить sp_send_dbmail "из под" роли приложения.  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
invm
PaulWist,

Сделайте так - https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/



2invm

С меня пиво!!!

PS Изначально ошибся в коде, ... пример работает из-под роли приложения!
21 окт 16, 15:59    [19809900]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить