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

Откуда:
Сообщений: 2028
Подскажите как разрулить ситуацию лучше.

Есть процедура, внутри которой идет обращение к системной таблице базы msdb (лог почты).

Есть пользователь, у которого нет прав на эту базу, но есть права на запуск процедуры.
Как сделать так, чтобы процедура выполнялась от имени учетки сисадмина всегда, неважно кто её запустил.

нашел пример http://stackoverflow.com/questions/2819367/execute-stored-procedure-as-another-user-premission

EXECUTE AS user = 'special_user'

EXECUTE YourProcerdure

REVERT



Но оно мне не подходит, так как в разных копиях системы, разные "special user".
20 ноя 14, 07:24    [16875851]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Giving Permissions through Stored Procedures. Оно?
20 ноя 14, 10:26    [16876525]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
andrey odegov,

Да оно. Сделал через EXECUTE AS OWNER

Теперь другой вопрос - как .dbo позволить рассылать почту через sp_send_dbmail

 Запрещено разрешение "EXECUTE" на объект "sp_send_dbmail" базы данных "msdb", схемы "dbo"


вы случайно не в курсе? процедура запускается на базе отличной от msdb но на том же сервере
20 ноя 14, 11:49    [16877228]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
еперь другой вопрос - как .dbo позволить рассылать почту через sp_send_dbmail

Еще один нечитатель

Permissions
Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.
20 ноя 14, 11:50    [16877249]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
еперь другой вопрос - как .dbo позволить рассылать почту через sp_send_dbmail

Еще один нечитатель

Permissions
Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.



EXEC sp_addrolemember
    @rolename = 'DatabaseMailUserRole'
  , @membername = 'dbo';
Go


Msg 15405, Level 16, State 1, Line 28
Невозможно использовать специального участника "dbo".


Сделал.
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'defaultProfile'
  , @principal_name = 'dbo'
  , @is_default = 1;
Go


Ошибка не пропала.

Я далек от администрирования SQL сервера.
И похоже именно ошибка имена в доступе к профилю почты а именно к доступе процедуры отправки сообщений из рабочей базы приложения к msdb.

т.к.
Запрещено разрешение "EXECUTE" на объект "sp_send_dbmail" базы данных "msdb", схемы "dbo"
имхо не равно
 sp_send_dbmail returns an error and does not send the message
20 ноя 14, 12:09    [16877409]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Может быть я вообще не тем путем иду?

Опишу сценарий - есть БД, в которой активно добавляются/удаляются пользователи (более 1000)
Прописывать всем пользователям доступ к DB msdb..sp_send_dbmail в данной случае невозможен практически.


Идея была сделать обертку-процедуру. Которая выполнялась бы к примеру OWNER (в данном случае dbo)
т.е. EXECUTE AS OWNER.

Но что-то пошло не так (см) выше.
Может идти другим путем? Как лучше сделать?
20 ноя 14, 12:30    [16877562]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
Прописывать всем пользователям доступ к DB msdb..sp_send_dbmail в данной случае невозможен практически.

Вы в курсе, что есть роли ?
20 ноя 14, 13:19    [16878037]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

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

У пользователей разные роли. Но все они должны иметь доступ к sp_send_dbmail + список ролей постоянно меняется.
Отмечу что пользователей 1000+ и они постоянно меняются, в том числе у них меняется и членство в группах.


Другими словами, все те пользователи, независимо от их ролей, кто имеет доступ к процедуре обертке SP_QueueMessage (которая содержит в своем теле sp_send_dbmail ) в DataBase1 должны иметь возможность отправлять сообщения через msdb..sp_send_dbmail.

Такое вообще реализуемо средствами Database Mail?
20 ноя 14, 13:27    [16878117]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
У пользователей разные роли. Но все они должны иметь доступ к sp_send_dbmail + список ролей постоянно меняется.

Фейспалм.
А сделать одну роль "типа для иметь доступ к sp_send_dbmail" что мешает ?
20 ноя 14, 13:28    [16878132]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
Другими словами, все те пользователи, независимо от их ролей, кто имеет доступ к процедуре обертке SP_QueueMessage (которая содержит в своем теле sp_send_dbmail ) в DataBase1 должны иметь возможность отправлять сообщения через msdb..sp_send_dbmail.

Такое вообще реализуемо средствами Database Mail?

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.
20 ноя 14, 13:29    [16878139]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
У пользователей разные роли. Но все они должны иметь доступ к sp_send_dbmail + список ролей постоянно меняется.

Фейспалм.
А сделать одну роль "типа для иметь доступ к sp_send_dbmail" что мешает ?


Делать такую отдельную роль и назначить 1000+ пользователям неудобно.
20 ноя 14, 13:53    [16878396]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
Делать такую отдельную роль и назначить 1000+ пользователям неудобно.

Неудобно штатны через голову одевать. Или на потолке спать.

А для того, чтобы не делать 1000+ логинов существует возможность добавлять в логины сетевые группы.
Вы об этом тоже не слышали ?
20 ноя 14, 13:56    [16878428]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
Другими словами, все те пользователи, независимо от их ролей, кто имеет доступ к процедуре обертке SP_QueueMessage (которая содержит в своем теле sp_send_dbmail ) в DataBase1 должны иметь возможность отправлять сообщения через msdb..sp_send_dbmail.

Такое вообще реализуемо средствами Database Mail?

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.



Решается
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public',
@profile_name = 'someDBMailProfileName',
@is_default = 1 ;


Пока сделано через WITH EXECUTE AS 'Domain\MailLogin'.
чем не нравится решение - не на всех копях системы (в разных фирмах) стоит доменная идентификация пользователей.
с WITH EXECUTE AS 'sa' не дает сделать.
20 ноя 14, 13:56    [16878432]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
Делать такую отдельную роль и назначить 1000+ пользователям неудобно.

Неудобно штатны через голову одевать. Или на потолке спать.

А для того, чтобы не делать 1000+ логинов существует возможность добавлять в логины сетевые группы.
Вы об этом тоже не слышали ?


Если вы имеете в виду доменные сетевые группы - не ко всем подойдет. см сообщение выше.
20 ноя 14, 13:57    [16878441]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
Делать такую отдельную роль и назначить 1000+ пользователям неудобно.

Неудобно штатны через голову одевать. Или на потолке спать.

А для того, чтобы не делать 1000+ логинов существует возможность добавлять в логины сетевые группы.
Вы об этом тоже не слышали ?


Если бы сервак был один. Система стоит в более 15+ фирмах.
Где доменная аутентификация, где SQL, полный бардак.

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

Меня сейчас не интересует вопрос применимости такого решения, а вообще возможно такое сделать или нет.
20 ноя 14, 14:00    [16878472]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
Если вы имеете в виду доменные сетевые группы - не ко всем подойдет. см сообщение выше.

Если нет желание кликнуть один раз мышкой при добавлении логина/пользователя, то есть триггера
20 ноя 14, 14:01    [16878483]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
Если вы имеете в виду доменные сетевые группы - не ко всем подойдет. см сообщение выше.

Если нет желание кликнуть один раз мышкой при добавлении логина/пользователя, то есть триггера


Я ведь вопрос не задаю о том как сделать группу и раскидать её всем пользователям.

Просто если не реализуемо то что я хочу - дайте знать.
Я понимаю, что вы хотите наставить на путь истинный и сделать все по фен-шую.

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

Еще раз повторюсь. Любой пользователь, у которого есть GRAN PERMISSION на EXECUTE определенной процедуры, в контексте выполнения этой определенной процедуры отправить почту через msdb sp_send_dbmail. Вот чего хочется добиться в идеале.
Если это технически не реализуемо - просто дайте знать.
20 ноя 14, 14:07    [16878552]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
Просто если не реализуемо то что я хочу - дайте знать.

Что нереализуемо ?
Добавление dbo в какие то роли ?
Разумеется это нереализуемо. Потому, что dbo и так уже имеет все возможные права

LameUser
Еще раз повторюсь. Любой пользователь, у которого есть GRAN PERMISSION на EXECUTE определенной процедуры, в контексте выполнения этой определенной процедуры отправить почту через msdb sp_send_dbmail. Вот чего хочется добиться в идеале.
Если это технически не реализуемо - просто дайте знать.

Добавить логин этого пользователя в базу msdb, в роль DatabaseMailUser и дать права на профиль
20 ноя 14, 14:12    [16878619]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
Просто если не реализуемо то что я хочу - дайте знать.

Что нереализуемо ?
Добавление dbo в какие то роли ?
Разумеется это нереализуемо. Потому, что dbo и так уже имеет все возможные права

LameUser
Еще раз повторюсь. Любой пользователь, у которого есть GRAN PERMISSION на EXECUTE определенной процедуры, в контексте выполнения этой определенной процедуры отправить почту через msdb sp_send_dbmail. Вот чего хочется добиться в идеале.
Если это технически не реализуемо - просто дайте знать.

Добавить логин этого пользователя в базу msdb, в роль DatabaseMailUser и дать права на профиль



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

В общем вопрос решился для компаний, у которых доменная политика.
Добавляется один! логин в роль DatabaseMailUse в базу msdb,
процедура запускается от его имени (WITH EXECUTE AS 'Domain\MailUser').

Завтра буду разбираться с теми у кого SQL авторизация.
20 ноя 14, 15:17    [16879211]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Glory
Member

Откуда:
Сообщений: 104751
LameUser
Добавляется один! логин в роль DatabaseMailUse в базу msdb,

В роли базы данных можно добавить только пользователя базы данных, а никак не логин

LameUser
Завтра буду разбираться с теми у кого SQL авторизация.

Разберитесь лучше с Tutorial: Ownership Chains and Context Switching
20 ноя 14, 15:21    [16879252]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8818
Аллес капут, зачем каждому из 100500 юзеров дергать процедуру отсылки и лезть в msdb, не пойму? Пусть это сервер делает, кладите письма в таблицу и отправляйте агентом раз в 5 минут.
20 ноя 14, 16:40    [16880021]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
SERG1257
Member

Откуда:
Сообщений: 2877
https://www.sql.ru/blogs/decolores/950
20 ноя 14, 19:29    [16881256]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Владислав Колосов
Аллес капут, зачем каждому из 100500 юзеров дергать процедуру отсылки и лезть в msdb, не пойму? Пусть это сервер делает, кладите письма в таблицу и отправляйте агентом раз в 5 минут.


Так было сделано ранее. Заказчик хочет моментального отправления писем (отсылка в очередь на почтовый сервер через Database Mail Account)
5 дек 14, 07:24    [16953198]     Ответить | Цитировать Сообщить модератору
 Re: Дать процедуре исключительные права  [new]
Klemzig
Guest
LameUser,

Service Broker. В пределах одного инстанса будет летать со свистом.
5 дек 14, 08:39    [16953295]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить