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

Откуда: Москва
Сообщений: 24
Добрый день.
Возникла необходимость представить пользователю родней на базу данных, но не выдавать ему права на посмотр или изменение данных в таблицах. Роль сисадмина предоставить не является возможными.
Необходим пользователь с максимально урезанными правами, который может создавать новых пользователей и предоставлять им роли на чтение и на запись.
Db_sDb_securityadmin и db_adb_accessadmin не работают.
заранее спасибо.
11 фев 15, 18:05    [17251889]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
За ошибки приношу извинения, пишу с телефона в метро.
11 фев 15, 18:09    [17251899]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
Glory
Member

Откуда:
Сообщений: 104760
paag5
Db_sDb_securityadmin и db_adb_accessadmin не работают.

и почему же они не работают вдруг ?
11 фев 15, 18:42    [17252007]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

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

Не знаю, сам удивлен. На сервере представляю пользователюdb_securityю роль securityadmin, на Необходимую базу даю db_accessadmin и db_securityadmin. На msdb даю райтера.

В итоге пользователь может создать профайл на сервере и даже добавить этот профайл в необходимую базу с пабликом, но ппри попытке предоставить любую роль помимо стандартного public говорит, что нет прав (ннот пермишен).

Пробовал на 5 разных серверах разных версий (2005, 2008 r2)
11 фев 15, 18:57    [17252071]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
Glory
Member

Откуда:
Сообщений: 104760
paag5
В итоге пользователь может создать профайл на сервере и даже добавить этот профайл в необходимую базу с пабликом, но ппри попытке предоставить любую роль помимо стандартного public говорит, что нет прав (ннот пермишен).

Значит вы что-то неправильно сделали
11 фев 15, 19:07    [17252107]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

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

Ну, я описал всю последовательность своих действий. Ошибку не нашел, потому и интересуюсь, как сделать правильно и какая правильная последовательность действий. Сам понимаю, что в теории должно работать, но как факт- банально db_datareader не могу представить с того профайла.
И sql профайл пробовал, и доменный.
11 фев 15, 19:11    [17252121]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
Glory
Member

Откуда:
Сообщений: 104760
paag5
Ну, я описал всю последовательность своих действий.

Код не описывают. Его предоставляют. Чтобы все могли его проверить у себя

paag5
И sql профайл пробовал, и доменный.

Что такое "профайл" ?
11 фев 15, 19:43    [17252187]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
Я тоже с телефона, ссылки не будет, пардон, но откройте БОЛ, sp_addrolemember, permissions:
Adding members to fixed database role requires membership in the db_owner fixed database role
11 фев 15, 20:19    [17252265]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
Glory,
Добрался до компа, вот примерно скрипт, которым предоставлял доступ (давал через интерфейс):
USE [master]
GO
CREATE LOGIN [test1] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [test1]
GO
USE [test_db]
GO
CREATE USER [test1] FOR LOGIN [test1]
GO
USE [test_db]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [test1]
GO
USE [test_db]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [test1]
GO


o-o
Я тоже с телефона, ссылки не будет, пардон, но откройте БОЛ, sp_addrolemember, permissions:
Adding members to fixed database role requires membership in the db_owner fixed database role

В том-то и дело, что на одном серваке именно после предоставления описанным выше образом прав необходимый эффект достигнут. Овнера ни на схемы, ни на базы нет, даже нет доступа на чтение с таблиц, а предоставление работает. Различий в правах не нашел.
11 фев 15, 21:47    [17252587]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
И вот такая ошибка после предоставления доступа подобным образом.
(дома поставил для теста сейчас sql server 11.0.2100)

К сообщению приложен файл. Размер - 48Kb
11 фев 15, 21:50    [17252602]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
Однако прав на следующие действия хватает:
USE [computer]
GO
CREATE USER [test2] FOR LOGIN [test2]
GO

Добавить логин в базу прав хватает, но вот предоставить этому логину какую-то роль - нет.
11 фев 15, 21:53    [17252618]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
o-o
Я тоже с телефона, ссылки не будет, пардон, но откройте БОЛ, sp_addrolemember, permissions:
Adding members to fixed database role requires membership in the db_owner fixed database role


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

С английским беда. Если не прав - поправьте, пожалуйста.
11 фев 15, 22:11    [17252687]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
paag5,

на русском это будет так:
чтобы добавлять пользователей в фиксированные датабэйсные роли, надо самому быть членом роли db_owner.
т.е. для этого недостаточно быть членом роли db_securityadmin
(этого бы хватило в случае добавления в пользовательскую роль)
11 фев 15, 22:25    [17252730]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
o-o
paag5,

на русском это будет так:
чтобы добавлять пользователей в фиксированные датабэйсные роли, надо самому быть членом роли db_owner.
т.е. для этого недостаточно быть членом роли db_securityadmin
(этого бы хватило в случае добавления в пользовательскую роль)


Да, уже проверил опытным путем, все так и есть. Созданную роль db_read предоставить получается, стандартную db_datareader выдавать отказывается.
Такой вариант не подходит, к сожалению.

О-О, Glory , спасибо за помощь.
11 фев 15, 22:32    [17252752]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
paag5,

O-O -- это я что-ли под лупой? прикольно, спасибо

можно решение вашей задачи реализовать через процедуру с EXECUTE AS.
создаете в требуемой базе юзера без логина
(то, к-ое в вашей терминологии "профайлер", оно вообще-то логин),
делаете его db_owner-ом:
create user owner_user without login;
go

exec sp_addrolemember 'db_owner', 'owner_user'
go


затем пишете вашу процедуру по добавлению юзера в роль
с использованием EXECUTE AS:
create procedure addrolemember_sp @user sysname, @role sysname
with execute as 'owner_user' 
as
...


и выдаете права на процедуру тому юзеру, к-ый должен в роли добавлять пользователей.
все.
он будет добавлять, но только через эту процедуру.
11 фев 15, 23:12    [17252891]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
o-o
paag5,

O-O -- это я что-ли под лупой? прикольно, спасибо

можно решение вашей задачи реализовать через процедуру с EXECUTE AS.
создаете в требуемой базе юзера без логина
(то, к-ое в вашей терминологии "профайлер", оно вообще-то логин),
делаете его db_owner-ом:
create user owner_user without login;
go

exec sp_addrolemember 'db_owner', 'owner_user'
go


затем пишете вашу процедуру по добавлению юзера в роль
с использованием EXECUTE AS:
create procedure addrolemember_sp @user sysname, @role sysname
with execute as 'owner_user' 
as
...


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

Интересный вариант. Нужно покумекать насколько он мне подойдет и что можно сделать еще через подобного юзера и процедурку. Аудит, к сожалению, не дремлет, а политики безопасности компании достаточно жесткие.
P.S. Профайл имеется в виду AD-шный, виндовый. Использование sql-логинов запрещено, так как обезличинный. Все доступы в компании только через ad-профайл.
12 фев 15, 08:24    [17253529]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
aleks2
Guest
o-o
paag5,

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


Хе-хе. Оне думают - "мелкософт глюпый".
Чего толку в таком зарывании головы в песок?
Этой процедурой можно себя в овнеры зачислить.
И в чем цимес?
12 фев 15, 08:33    [17253560]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
aleks2, именно это и хочу протестить на тестовой машинке.
12 фев 15, 09:19    [17253712]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
aleks2
o-o
paag5,

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


Хе-хе. Оне думают - "мелкософт глюпый".
Чего толку в таком зарывании головы в песок?
Этой процедурой можно себя в овнеры зачислить.
И в чем цимес?


оне думають, что уже в который раз aleks2 заявляет, что в подобную процедуру
можно передать левый код на исполнение,
но упорно не приводит репро,
наверное, потому что никакого репро просто у него нет?
так КТО ЖЕ ИСТИННЫЙ СТРАУС?

create procedure addrolemember_sp 
  @role sysname,
  @user sysname

with execute as 'dbo'
as

set nocount on;

declare @t table (dbrole sysname);
insert into  @t values
('db_accessadmin'),('db_backupoperator'),('db_datareader'),
('db_datawriter'),('db_ddladmin'),('db_denydatareader'),
('db_denydatawriter'),('db_securityadmin');

declare @dbrole sysname = (select dbrole from @t where dbrole = @role);
if @dbrole is null
raiserror ('Invalide fixed database role name', 16, 1);

declare @sql varchar(1000)='sp_addrolemember '+ quotename(@dbrole, '''') + ',' + quotename(@user,'''');
exec(@sql);
12 фев 15, 12:21    [17254929]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
paag5
что можно сделать еще через подобного юзера и процедурку.

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

paag5
Аудит, к сожалению, не дремлет, а политики безопасности компании достаточно жесткие.

при имперсонэйте да, код будет выполняться от имени owner_user.
но original_login() будет показывать истинного исполнителя.
если же ваш аудит опирается на что-то типа user,
то можно сделать все то же самое через сертификат. оно морочнее,
но юзер будет сохранен тот самый, и к нему лишь добавятся права

paag5
P.S. Профайл имеется в виду AD-шный, виндовый.
Использование sql-логинов запрещено, так как обезличинный. Все доступы в компании только через ad-профайл.

виндовый логин на сервере так и зовется Windows login.
у меня перестало вообще открываться msdn, полный интернет-привет.
но поищите, допустим, статью sys.server_principals (Transact-SQL).
там в колонке type_desc расписаны все возможные принципалы уровня сервера:

SQL_LOGIN

WINDOWS_LOGIN

WINDOWS_GROUP


SERVER_ROLE

CERTIFICATE_MAPPED_LOGIN

ASYMMETRIC_KEY_MAPPED_LOGIN
12 фев 15, 12:48    [17255156]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
P.S. можно добавить проверку @user-a на самого того юзера,
чтоб сам себя этой процедурой куда-то не зачислил, куда не надо.
12 фев 15, 12:57    [17255237]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
aleks2
Guest
o-o
P.S. можно добавить проверку @user-a на самого того юзера,
чтоб сам себя этой процедурой куда-то не зачислил, куда не надо.

Дык зачисли другого.

ЗЫ. Борьба за "администраторов без административных прав" сродни борьбе с ветряными мельницами.
12 фев 15, 13:08    [17255364]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
aleks2,

здрасьте.
если процедуру пишут для зачисления,
то она не должна зачислять?
и овнера в списке у меня нет
12 фев 15, 13:20    [17255475]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
paag5
Member

Откуда: Москва
Сообщений: 24
aleks2
o-o
P.S. можно добавить проверку @user-a на самого того юзера,
чтоб сам себя этой процедурой куда-то не зачислил, куда не надо.

Дык зачисли другого.

ЗЫ. Борьба за "администраторов без административных прав" сродни борьбе с ветряными мельницами.

В том то и дело, что у администраторов БД есть права администраторов. Только нужен не администратор, а человек на доступах, что бы акромя создания логинов, добавления ролей ридера и райтера, а так же инсерта и делита в некоторых таблицах, больше прав не имел.
Такая политика оправдана существенным ущербом в связав с потерей данных или простоем её на время восстановления бэкапов.
12 фев 15, 13:55    [17255826]     Ответить | Цитировать Сообщить модератору
 Re: Предоставление прав на выдачу ролей.  [new]
o-o
Guest
paag5
В том то и дело, что у администраторов БД есть права администраторов. Только нужен не администратор, а человек на доступах, что бы акромя создания логинов, добавления ролей ридера и райтера, а так же инсерта и делита в некоторых таблицах, больше прав не имел.
Такая политика оправдана существенным ущербом в связав с потерей данных или простоем её на время восстановления бэкапов.

в вашей терминологии черт ногу сломит.
но вроде вы хотите сказать, что у вас db_owner-ы баз это и есть админы сервера.
и что вам нужен такой логин/юзер, чтобы он не был админом сервера,
не был db_owner-ом, но мог добавлять в db_datareader/db_datawriter?

в моей процедуре оставьте в ролях только 'db_datareader', 'db_datawriter',
добавьте проверку того самого юзера на несовпадение с @user,
он не сможет себя зачислить в db_datareader/db_datawriter, а других да.
но только в эти роли и в пользовательские
12 фев 15, 14:20    [17256025]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить