Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 решение, отличное от sysadmin  [new]
o-o
Guest
ситуация:
админы сервера вне доступности, считаем, что их нет.
в базу отмаплены несколько виндовых групп,
одна из к-ых db_owner (ну т.е. имеем КУЧУ овнеров).
зачем-то отмаплены индивидуально ВСЕ юзеры этих групп.

в базе творились беспорядки, типа изменяют мои процедуры
(может, и не только мои, но на других мне фиолетово),
и пойди найди КТО.
мной был навешен на базу DDL trigger (with encryption), чтоб отслеживать, кто гадит в процедуры.
триггер пишет лог в таблицy ddl_hist в моей личной схеме a.
по идее, все кто попало db_owner-ы, заходи и удаляй мою таблицу.
и тут меня осенило, правда не могу пока оценить последствия.
итак, создаю роль super_role.
в нее курсором запихиваю всех пользователей, кроме себя, dbo, ролей и виндовых групп.
роли делаю:
deny alter on schema::a to super_role
deny select, insert, delete, update on schema::a to super_role
deny alter on role::super_role to super_role
deny alter any user to super_role
deny alter any database ddl trigger to super_role


теперь ни у кого нет доступа к моей схеме и никто себя из роли выкинуть не может.
делаю тесты от соседской учетки (типа db_owner): нет, ничего сделать не может.
т.е. вообще весь DDL обломался.
на любой DDL срабатывает триггер:
Msg 229, Level 14, State 5, Procedure tg_ddl_hist, Line 10
The INSERT permission was denied on the object 'ddl_hist', database 'BASEDATI_BI', schema 'a'.


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

т.е. вот если вы оказались на месте db_owner-а, к-ый получает такую каку при исполнении DDL-я,
ваши действия? (про код спрашиваю, "задать мне за все хорошее" не предлагать)
26 ноя 13, 20:08    [15194175]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
o-o,

автор
нет, ничего сделать не может.


В том числе и необходимые GRANT?
26 ноя 13, 20:17    [15194221]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
o-o
Guest
pkarklin,

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

на самом деле это была месть презентация user-defined database roles начальнице-ораклоидке.
она заявила, что в SQL Server роли какие-то тупые, если db_datareader, то читает ВСЕ.
вот, говорит, то-ли дело оракл :)
говорю, а чего в нашей базе пользовательскую роль создать слабО?
где только на нужное выдать разрешения?
(у нас не все овнеры. странно, но факт.
имеются даже ридеры, но читают даже то, что им не положено. начальство, тоже овнер, этим удручено)

так не верит, что такое возможно!
говорю, набираем CREATE ROLE и жмем F1, и жму F1, на ее компе.
начинается установка ХЭЛПА, к-ую она успешно прерывает!
ну, пусть теперь отгадывают, как такое возможно, овнеры хреновы
(пардоньте. крик души, можно сказать)
26 ноя 13, 23:31    [15195090]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
use master;
create database DBTestSecurity;
go

use DBTestSecurity;
go

create schema a;
go

create table a.Test (id int);
go

create user TestUser without login;
go

exec sp_addrolemember 'db_owner', 'TestUser';
go

create role super_role;
go

deny alter on schema::a to super_role
deny select, insert, delete, update on schema::a to super_role
deny alter on role::super_role to super_role
deny alter any user to super_role
deny alter any database ddl trigger to super_role
go

execute as user = 'TestUser';
go
select * from a.Test; --success
go
revert;
go

exec sp_addrolemember 'super_role', 'TestUser';
go

execute as user = 'TestUser';
go
select * from a.Test; --fail
go
revert;
go

execute as user = 'TestUser';
go
exec sp_droprolemember 'super_role', 'TestUser';
go
select * from a.Test; --success
go
revert;
go

use master;
alter database DBTestSecurity set single_user with rollback immediate;
drop database DBTestSecurity;
go
27 ноя 13, 00:01    [15195189]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
o-o
Guest
ну так для начала надо допереть, что тебя "засунули" в какую-то роль,
а товарищи не подозревают даже о самой возможности существования чего-то,
отличного от db_owner и db_datareader

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

таблицу-лог перенесу в другую схему и переименую позауряднее, триггер перепишу.
нафиг нужно им "выдавать", куда что-то интересное пишется, даже в виде ошибки.
а на свою схему эту "защиту от дурака" оставлю, в первом приближении сойдет.
27 ноя 13, 02:07    [15195723]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
o-o
Guest
минуточку-минуточку.
это я совсем уже засыпаю.
как раз из соображений "сделать так, чтоб из роли себя не выкинули"
и было сделано:
deny alter on role::super_role to super_role

т.к. именно у sp_droprolemember в БОЛ Permissions: Requires ALTER permission on the role

почему же позволяет выкинуть себя из роли???
не работает DENY ALTER на самого себя?
а почему тогда ошибку не выдает?

вот проверить, сработало или нет, времени на работе не хватило.
а зря. вот, invm сразу нашел.
27 ноя 13, 02:17    [15195757]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
o-o
Guest
просто издевательство какое-то.
делаю глобально:
deny alter any role to super_role


смотрю
execute as user = 'TestUser';
go
select *
from sys.fn_my_permissions(null, 'database')

там НЕТУ alter any role, значит, запретилось. но не канает же,
можно себя из роли выкидывать, ПОЧЕМУ?

---------------------------------------------------------
завтра курсором вообще все разрешения (61 штука) супер_роли позапрещаю.
вернее, 60 (except CONTROL!!!!).
потому что после глобального
deny control to super_role

у меня вообще перестало исполняться
execute as user = 'TestUser';

выдает
Msg 916, Level 14, State 1, Line 1
The server principal "S-1-9-3-1797990921-1180562145-203943612-2095304448." is not able to access the database "DBTestSecurity" under the current security context.


завтра разберусь, сейчас уже не понимаю вообще ничего
27 ноя 13, 02:46    [15195802]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3348
o-o,

Похоже, все дело в волшебной роли db_owner - ее членам можно ограничить права, но нельзя запретить эти права себе менять:
use master;
go
create database [RT];
go
use RT;
go
create user Test without login with default_schema = dbo;
go
alter role db_owner add member Test;
--grant control on database::RT to Test;
go
create role rst authorization dbo;
go
deny alter to rst;
go
alter role rst add member Test;
go

execute as user = 'Test';
go
select * from sys.fn_my_permissions(null, 'database') where permission_name = 'ALTER ANY ROLE';
go
alter role rst drop member Test;
go
select * from sys.fn_my_permissions(null, 'database') where permission_name = 'ALTER ANY ROLE';
go
revert;
go

use master;
go
drop database RT;
go
Если заменить членство в роли владельцев на GRANT CONTROL, что по сути то же самое, то магия исчезает - человек больше не может поменять свои права.

Короче, "бардак не автоматизируем".
27 ноя 13, 06:52    [15195971]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
o-o
т.к. именно у sp_droprolemember в БОЛ Permissions: Requires ALTER permission on the role
Документация не всегда соответствует действительности. А действительность видна во фрагменте sp_droprolemember:
    -- CHECK PERMISSIONS --
	-- Only member of db_owner can drop members from db-fixed roles --
	if (not is_member('db_owner') = 1) and
       (not ( (@roluid >=16384 and @roluid <  16400) and is_member('db_owner') = 1))         and
       (not ( (@roluid < 16384 or  @roluid >= 16400) and is_member('db_securityadmin') = 1)) and
       (not ( (@roluid < 16384 or  @roluid >= 16400) and 
			((is_member(user_name(@owner)) = 1) or (has_perms_by_name(@rolename, 'role', 'alter') = 1))
			))
    begin
		ROLLBACK TRANSACTION
		EXEC %%System().AuditEvent(ID = 1296321618, Success = 0, TargetLoginName = NULL, TargetUserName = @membername, Role = @rolename, Object = NULL, Provider = NULL, Server = NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

ЗЫ: Нельзя db_owner'ов чего-либо лишить - они всегда могут себе это вернуть.
27 ноя 13, 10:08    [15196507]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
o-o
Guest
invm
ЗЫ: Нельзя db_owner'ов чего-либо лишить - они всегда могут себе это вернуть.


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

спасибо за разъяснение про sp_droprolemember

+
вот я, где в продакшене не овнер, на те базы у меня отсутствует даже коннект.
зато имеем linked server, к-ый смотрит на server3, а тот смотрит обратно на server1 (тоже linked),
и все это удовольствие не using current security context, а через 2ух SQL Server-ных юзеров.
и угадайте, что.
тот второй юзер, что с прилинкованного server3 попадает на server1, он на первом(server1) db_owner, во ВСЕХ базах.
т.е. даже куда у меня напрямую нету CONNECT-а.
круто, да?
только там не настроено RPC OUT, поэтому все овнерство свелось к DML-ю.
но в теории могу поголовно все таблички почистить DELETE-ом,
во всех базах.
и виноват будет кто? некий юзер GCSQLW0H3linked

начальница как узнала, 2 дня не спала, написала админам и кому-то повыше и мне говорит:
все, сейчас лазейку прикроют, я всем сообщила.
думала, ей медаль выдадут за обнаруживание дыры.
ага, ЩАС.
не для того они эти линки создавали
воз и ныне там, а та тетка "повыше" вообще не поверила, что мы попадаем в те формально не доступные нам базы
(в к-ые мы со своих виндовых групп не попадаем, если напрямую)
а начальница сперва хотела той назло демонстративно что-нибудь удалить,
а потом сама себе: ой, это же продакшн!
ой, обещай мне, что и ты не полезешь.

я вообще туда даже больше не логинюсь. потом известно, на кого все свалят. нафиг-нафиг.

P.S. если бы был отдельный топик "а не похвастаться ли нам, у кого SECURITY круче",
может, мне бы первое место присудили.
или еще можно открыть конкурс на лучшее высказывание ораклоидов о SQL Server-е.
мне есть, что туда запостить,
каждый божий день пополняю коллекцию перлов
27 ноя 13, 12:12    [15197431]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
o-o
я вообще туда даже больше не логинюсь. потом известно, на кого все свалят. нафиг-нафиг.
Свалят в любом случае, вне зависимости от логинюсь/не логинюсь. Бежать надо из таких контор. И чем скорее, - тем лучше.
27 ноя 13, 12:40    [15197675]     Ответить | Цитировать Сообщить модератору
 Re: решение, отличное от sysadmin  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а смысл? не дать или запротоколировать? первое в адрес админа - нереально. второе я бы делал через штатный аудит с записью в журналы операционки - оттуда выкосить записи не так просто будет
27 ноя 13, 14:00    [15198559]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить