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

Откуда:
Сообщений: 2873
Доброго времени суток коллеги.
Есть задача: регулярно внешняя контора присылает бакап своей базы. Надо этот бакап восстановить, добавить своих юзеров, раздать им права. Задача элементарно решается через SQL Server Agent.
Однако требуется ее решить через левого IBM агента под названием ROBOT.
решение типа
sqlcmd -S myServer -d master -Q "restore database myDatabase"
sqlcmd -S myServer -d myDatabase -Q "create user someuser for login somelogin; exec sp_addrolemember role,user;"
требует дать пользователю ROBOT права sysadmin

Начальство озадачило меня на предмет создать процедуру например в master базе чтобы проделать эти действия, и дать пользователю ROBOT права только на эту процедуру.

С restore database проблем не возникло

Но как создать пользователя в свежевосстановленной базе?
я попробовал динамический sql

use master
create PROCEDURE [dbo].[addSomeUser]
with exec as owner
AS
BEGIN
exec ('use myDatabase ; if DATABASE_PRINCIPAL_ID (''someuser'') is null CREATE USER someuser FOR LOGIN somelogin;')
end


установил свойство TRUSTWORTHY у свежевосстановленной базы, обе базы master и myDatabase под владельцем sa

ALTER DATABASE myDatabase SET TRUSTWORTHY ON;

и все равно получаю
Msg 916, Level 14, State 1, Line 1
The server principal "sa" is not able to access the database "myDatabase" under the current security context.

Что я делаю не так?
12 фев 14, 18:48    [15558814]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
Добавил

ALTER DATABASE myDatabase SET DB_CHAINING ON

Не помогло
12 фев 14, 19:12    [15558917]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Даем возможность пользователю создавать логины в SQL Server, не давая прав администратора.
https://www.sql.ru/blogs/decolores/950
12 фев 14, 19:15    [15558932]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
чет не наблюдай create login перед create user
12 фев 14, 19:17    [15558940]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
Логины уже созданы. Надо создать юзеров в свежевосстановленой базе.
12 фев 14, 19:33    [15558986]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
o-o
Guest
SERG1257
Msg 916, Level 14, State 1, Line 1
The server principal "sa" is not able to access the database "myDatabase" under the current security context.

конкретно эта ошибка потому, что когда EXECUTE AS это не statement,
а clause в CREATE PROC, то имперсонировать можно только юзера, а не логин:

Functions (except inline table-valued functions), Stored Procedures, and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

DDL Triggers with Database Scope
{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }

DDL Triggers with Server Scope and logon triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }

Queues
{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }

EXECUTE AS Clause
12 фев 14, 19:37    [15558993]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
o-o
конкретно эта ошибка потому, что когда EXECUTE AS это не statement,
а clause в CREATE PROC, то имперсонировать можно только юзера, а не логин:
Хорошо, а как мне создать юзера в левой базе через процедуру. Кроме динамического SQL у меня нет других вариантов.

locky
Даем возможность пользователю создавать логины в SQL Server, не давая прав администратора.
https://www.sql.ru/blogs/decolores/950
Попробовал, работает.
Я дал VirtualSecurityOfficer роль sysadmin и поместил
exec ('use myDatabase; if DATABASE_PRINCIPAL_ID (''someuser'') is null CREATE USER someuser FOR LOGIN somelogin;')
в тело процедуры.
А какие Server Permissions (взамен sysadmin) требуются чтобы создавать пользователей и раздавать им разрешения?
Рассказал бы кто языком ЖЭКа что это за сертификаты такие и как оно работает. Читал BOL и с первого раза ничего не понял.
12 фев 14, 21:31    [15559454]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
o-o
Guest
самый правильный вариант через сертификат и есть.

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

еще отличие от EXECUTE AS:
если исполнителю процедуры выдан явный DENY на что-то, сертификат этот DENY не перешибет.
а процедуру со всемогущим EXECUTE AS пользователь с DENY запустит (при наличии права запуска процедуры, конечно),
т.е. при исполнении процедуры в случае сертификата будут проверены запреты исполнителя, а в случае EXECUTE AS запреты исполнителя никого не интересуют, лишь бы было все ок с правами товарища, указанного в EXECUTE AS
12 фев 14, 23:49    [15559994]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
o-o
Guest
и верните назад TRUSTWORTHY OFF восстановленной базы.
TRUSTWORTHY ON позволяет любому db_owner-у этой базы заделаться сисадмином, раз овнер восстановленной базы -- sa.
в любом случае, чтобы "выпустить юзера из базы", TRUSTWORTHY надо устанавливать как раз на ту базу, из к-ой собрались выпускать, т.е. в Вашем примере это была бы база master, а не база, в к-ую собрались "впускать".
13 фев 14, 00:06    [15560059]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
o-o
при любом изменении процедуры эти дополнительные права отвалятся, если только вновь не подписать процедуру сертификатом.
Это я уже заметил. А что случится после даты протухания сертификата? В BOL как то невнятно написано, то брокер следит за временем, то дата не проверяется. Или проставить дату следующим веком?
o-o
и верните назад TRUSTWORTHY OFF восстановленной базы.
Это легко сделать, ибо база все время новая.
13 фев 14, 01:14    [15560197]     Ответить | Цитировать Сообщить модератору
 Re: Создать юзера из процедуры  [new]
o-o
Guest
SERG1257
А что случится после даты протухания сертификата?

тоже права отвалятся, так что да, лучше лет на 100 вперед дату указать.
и чего это датa не проверяется, все проверяется :)
если Вы про фразу however, expiration is not enforced when the certificate is used for encryption, тo у нас же не encryption
13 фев 14, 01:50    [15560236]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить