Мультитенантные таблицы в SQL Azure. Безопасность уровня записи.

Содержание предыдущей серии

Наверно, все нашли и прочитали статью Implementing Row- and Cell-Level Security in Classified Databases. Это очень полезный концептуальный материал, в котором рассматриваются различные сценарии управления правами: уровни секретности (доступ к совершенно секретным документам дает доступ вниз по иерархии к секретным и конфиденциальным документам), ведомственное разделение прав (доступ к совершенно секретным документам подразделения А не означает доступ к секретным документам подразделения Б) и т.д. Чтобы не усложнять изложение, обойдемся без вспомогательных таблиц меток. В нашем случае будет иметь место только доменный атрибут безопасности, где членами домена выступают пользователи базы данных, с правилом сравнения "любой из". Права пользователей на запись (в смысле, строки, а не операции) будут находиться в дополнительном XML-поле этой записи - своего рода ACL. Также я ставлю входным условием, что над записью возможны только два типа действий: чтение и модификация (обновление, удаление). Правом на модификацию обладает только ее владелец - тот, кто ее создал, т.е. сделал insert этой записи. Он может раздавать остальным права на ее чтение и отбирать их. Имена пользователей перечисляются как элементы в XML-поле этой записи. Первым элементом всегда идет ее собственник, последуюшими - те, кому он дал право видеть эту запись. Получается достаточно простой, но иллюстративный пример. Заходим на сервер SQL Azure в базу Изумрудный город тремя соединениями под логинами Гудвин, Страшила и Железный дровосек. Это все было создано в прошлой серии. Единственно, нужно убрать недопустимые для имени XML-элемента символы из пользователя [Ж/д_user], соответствующего логину Железный дровосек (см. прошлый пост\Рис.10). Пусть будет ЖД_user. Созданные на прошлом занятии схемы [Sch_ж/д] и Sch_Страшила, а также таблица Sch_Страшила.[Мои документы] больше не понадобятся. Желающие могут вообще пересоздать БД Изумрудный город и начать жизнь с нуля. В зависимости от радикальности очистки нужно выполнить тот или иной фрагмент уборочного скрипта:

--От имени Гудвина или владельца сервера SQL Azure в контексте БД Изумрудный город:

drop table Sch_Страшила.[Мои документы]

drop schema [Sch_ж/д]

drop schema Sch_Страшила

drop user Страшила_user

drop user [Ж/д_user]

--От имени владельца сервера SQL Azure в контексте БД master:

drop user Гудвин_user

drop login Страшила

drop login [Железный дровосек]

drop login Гудвин

drop database [Изумрудный город]

Скрипт 1

Соответственно, если считать, что от прошлого занятия на сервере SQL Azure ничего не осталось, нужно зайти в БД master под его владельцем и выполнить скрипт:

create login Гудвин with password = 'p@$$w0rd'

create user Гудвин_user from login Гудвин

exec sp_addrolemember @rolename = 'loginmanager', @membername = N'Гудвин_user'

exec sp_addrolemember @rolename = 'dbmanager', @membername = N'Гудвин_user'

Скрипт 2

От имени пользователя Гудвин в контексте БД master:

create login Страшила with password = 'p@$$w0rd'

create login [Железный дровосек] with password = 'p@$$w0rd'

create database [Изумрудный город] collate cyrillic_general_100_ci_as (maxsize = 1 GB)

Скрипт 3

От имени пользователя Гудвин в контексте БД Изумрудный город:

create user Страшила_user from login Страшила

create user [ЖД_user] from login [Железный дровосек]

Скрипт 4

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

Для разнообразия будем соединяться с сервером SQL Azure из SSMS, а не из SQL Azure Management Portal. Про соединение с облачной базой из SSMS рассказывалось в позапрошлой серии. Гудвин как dbo создает в базе таблицу, в которой будут хранить свои документы остальные пользователи.

if object_id('dbo.Документы', 'U') is not null drop table dbo.Документы

create table dbo.Документы (ID int identity(1, 1) primary key clustered,

Название nvarchar(150), Содержание nvarchar(max),

Доступ xml default '<Users><' + CURRENT_USER + '/></Users>')

insert dbo.Документы (Название, Содержание) values ('ааа', 'аааааааааа')

select * from dbo.Документы

Рис.1

От Рис.11 предыдущей серии она отличается тем, что, во-первых, предполагается общей и лежит в схеме dbo, а не Sch_Страшила или [Sch_ж/д]. Во-вторых, в ней добавилось XML-поле Доступ. Каждый пользователь будет иметь доступ на чтение к тем записям, в поле Доступ которых он значится.

select * from dbo.Документы where Доступ.exist('Users/dbo') = 1

Скрипт 5

Требуется параметризовать в этом запросе имя пользователя. Не имеющие опыта работы с XQuery обычно не заморачиваются:

declare @user sysname = 'dbo'

select * from dbo.Документы where Доступ.exist('Users/' + @user) = 1

Msg 8172, Level 16, State 1, Line 2

The argument 1 of the XML data type method "exist" must be a string literal.

Скрипт 6

На форумах находятся "гуру", которые на полном серьезе советуют переписать его как динамический запрос. Те из читателей, что потратили летом немного времени на прочтение постов Как импортировать конфигурацию Windows Firewall в SQL Server, знают, что все делается гораздо проще:

declare @user varchar(100) = 'dbo'

select * from dbo.Документы where Доступ.exist('Users/*[local-name()=sql:variable("@user")]') = 1

Рис.2

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

if object_id('dbo.Документы_друзей', 'V') is not null drop view dbo.Документы_друзей

create view dbo.Документы_друзей as

select ID, Название, Содержание from (select *, CURRENT_USER as CurrentUser from dbo.Документы) t

where Доступ.exist('Users/*[local-name()=sql:column("CurrentUser") and position()>1]') = 1

Скрипт 7

К сожалению, в качестве аргумента sql:variable() должна быть действительно @переменная. Функция не прокатывает. Даже такая встроенная, как CURRENT_USER (она же USER_NAME()), возвращающая текущего пользователя. Пришлось сделать представление на основе запроса с вычисляемой колонкой, в которую засунуть вызов этой функции и вместо sql:variable использовать sql:column в в качестве параметра XQuery-запроса.

В концептуальной статье предполагается, что когда пользователь получает доступ к записи, он с ней волен делать все: и читать, и модифицировать. Если права на действия разделяются, авторы задействуют instead of-триггеры. Можно вообще вынести весь процесс управления правами в прикладной слой. Это прагматично и неинтересно. Лучше, конечно, помучиться (с) Сухов. Я создам еще одно представление для операций модификации. В него войдут те записи, для которых текущий пользователь является владельцем, т.е. первым (.../*[1]) элементом в XML-поле:

if object_id('dbo.Собственные_документы', 'V') is not null drop view dbo.Собственные_документы

create view dbo.Собственные_документы as

select ID, Название, Содержание from dbo.Документы where Доступ.value('local-name(Users[1]/*[1])', 'sysname') = CURRENT_USER

Скрипт 8

На первое представление всем пользователям базы будут выданы права на чтение, на второе - на чтение и модификацию:

grant select on dbo.Документы_друзей to public

grant select, insert, update, delete on dbo.Собственные_документы to public

Рис.3

Страшила не видит добавленную Гудвином (Рис.1) запись, поскольку у него нет к ней доступа, а права на непосредственное чтение таблицы dbo.Документы у него тоже нет. Он может добавить в представление Собственные_документы записи, владельцом которых станет

insert dbo.Собственные_документы (��азвание, Содержание) values ('ббб', 'бббббббббб')

insert dbo.Собственные_документы (Название, Содержание) values ('ввв', 'вввввввввв')

Рис.4

но другие пользователи их тоже не увидят

select * from dbo.Документы

insert dbo.Документы (Название, Содержание) values ('ггг', 'гггггггггг')

select * from dbo.Собственные_документы

insert dbo.Собственные_документы (Название, Содержание) values ('ддд', 'дддддддддд')

insert dbo.Собственные_документы (Название, Содержание) values ('еее', 'ееееееееее')

select * from dbo.Собственные_документы

select * from dbo.Документы_друзей

Рис.5

пока он им не выдаст права на чтение. Осталось научить пользователей давать друг другу или отбирать гостевой доступ, т.е. добавлять имя другого пользователя в виде XML-элемента в поле Доступ. Не имеющие опыта работы с XQuery делают обычно так:

declare @x xml = '<Users><aaa/><bbb/></Users>', @user sysname = '<ccc/>'

set @x.modify('insert sql:variable("@user") as last into (/Users)[1]')

Msg 2207, Level 16, State 1, Line 2

XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string ?".

Скрипт 9

Но после упражнений с XQuery в серии постов Как импортировать конфигурацию Windows Firewall в SQL Server мы знаем, что вставлять надо не строку, а XML:

declare @x xml = '<Users><aaa/><bbb/></Users>', @user xml = '<ccc />'

set @x.modify('insert sql:variable("@user") as last into (/Users)[1]')

select @x

---------

Скрипт 10

Теперь не составляет труда написать процедуру, которая, принимая в качестве параметров идентификатор строки и имя пользователя, добавляет его в XML-поле данной строки. Это поле мы не выносили в представления, следовательно, обновлять придется базовую таблицу, следовательно, необходимо убедиться, что вызывающий процедуру пользователь является собственником записи с id = @ID_документа. в представлениях. Также необходимо убедиться, что добавляемый пользователь является действительным пользователем базы, т.е. содержится в sys.database_principals. В соответствии с Metadata Visibility Configuration по умолчанию Страшила не увидит в списке пользователей Железного дровосека и наоборот. В связи с этим была написана функция dbo.Есть_ли_такой_пользователь_в_базе, вызываемая с правами EXECUTE AS OWNER. Еще я включил в процедуру проверку, что пользователя с таким именем в XML-поле Доступ еще нет (update ... where ... Доступ.exist ... = 0), чтобы оно не распухало от дубликатов. Право на выполнение этой процедуры дается всем пользователям базы:

if object_id('dbo.Дать_доступ_пользователю_к_документу', 'P') is not null drop proc dbo.Дать_доступ_пользователю_к_документу

create proc dbo.Дать_доступ_пользователю_к_документу @ID_документа int, @пользователь sysname as

--Если записи с таким id нет в таблице или вызывающий эту процедуру не имеет на нее прав, завершиться с ошибкой

if not exists (select 1 from dbo.Собственные_документы where id = @ID_документа)

begin

raiserror ('Нет документа с id = %i', 16, 1, @ID_документа)

return

end

--Проверка на валидность имени пользователя

if dbo.Есть_ли_такой_пользователь_в_базе(@пользователь) = 0

begin

raiserror('Нет такого пользователя - %s :(', 16, 1, @пользователь)

return

end

--Добавить пользователя как XML-элемент в конец поля Доступ для записи с заданным id

--Предварительно проверяется, что такого пользователя нет в поле Доступ (where ... Доступ.exist(...) = 0), чтобы избежать дубликатов

declare @пользователь_в_виде_xml xml = '<' + @пользователь + '/>'

update dbo.Документы set Доступ.modify('insert sql:variable("@пользователь_в_виде_xml") as last into (/Users)[1]')

where id = @ID_документа and Доступ.exist('Users/*[local-name()=sql:variable("@пользователь")]') = 0

grant execute on Дать_доступ_пользователю_к_документу to public

Рис.6

Функция dbo.Есть_ли_такой_пользователь_в_базе также создается на соединении Гудвина:

if object_id('dbo.Есть_ли_такой_пользователь_в_базе', 'FN') is not null drop function dbo.Есть_ли_такой_пользователь_в_базе

create function dbo.Есть_ли_такой_пользователь_в_базе(@пользователь sysname) returns bit with execute as owner as

begin

return case when exists (select 1 from sys.database_principals where type = 'S' and isnull(sid, 0) <> 0 and name = @пользователь) then 1 else 0 end

end

grant execute on dbo.Есть_ли_такой_пользователь_в_базе to public

Скрипт 8

При помощи процедуры dbo.Дать_доступ_пользователю_к_документу Страшила дает право на просмотр одного из своих документов Железному дровосеку:

exec dbo.Дать_доступ_пользователю_к_документу @ID_документа = 3, @пользователь = 'ЖД_user'

Рис.7

После чего тот начинает ее видеть (ср. с Рис.3):

select * from dbo.Документы_друзей

Рис.8

потому что пользователь ЖД_user добавлен к списку доступа данной записи:

Рис.9

Процедура не позволяет Страшиле добавить пользователя в не принадлежащие ему записи, а также осуществляет валидацию имени пользователя перед его добавлением:

exec dbo.Дать_доступ_пользователю_к_документу @ID_документа = 1, @пользователь = 'ЖД_user'

exec dbo.Дать_доступ_пользователю_к_документу @ID_документа = 2, @пользователь = 'aaa'

--------------------------------------------------------------------

Msg 50000, Level 16, State 1, Procedure Дать_доступ_пользователю_к_документу, Line 5

Нет документа с id = 1

Msg 50000, Level 16, State 1, Procedure Дать_доступ_пользователю_к_документу, Line 11

Нет такого пользователя - aaa :(

Скрипт 11

Обратное действие - удаление пользователя из ACL - выполняет процедура dbo.Отобрать_у_пользователя_доступ_к_документу:

if object_id('dbo.Отобрать_у_пользователя_доступ_к_документу', 'P') is not null drop proc dbo.Отобрать_у_пользователя_доступ_к_документу

create proc dbo.Отобрать_у_пользователя_доступ_к_документу @ID_документа int, @пользователь sysname as

--Вызывающий должен быть владельцем записи с @ID_документа в таблице dbo.Документы, что проверяется ее джойном с представлением dbo.Собственные_документы

--Не разрешается удалять из списка себя, т.е. владельца: position() > 1

update dbo.Документы set Доступ.modify('delete Users/*[local-name()=sql:variable("@пользователь") and position() > 1]')

from Документы d inner join dbo.Собственные_документы sd on d.id = sd.id where d.id = @ID_документа

grant execute on Отобрать_у_пользователя_доступ_к_документу to public

Скрипт 12

которая, как и dbo.Дать_доступ_пользователю_к_документу, создается от лица Гудвина. Процедура также осуществляет проверку на то, что вызывающий является владельцем записи с id = @ID_документа, кроме того, он не может удалить из списка себя, т.е. первый элемент поля Доступ. Проверка на порядковый номер элемента осуществляется так же, как и в Скрипте 7.

Пример: Страшила отбирает у Железного дровосека гостевой доступ на запись с id = 3 и заодно пытается лишить доступа себя:

Рис.10

Теперь Железный дровосек действительно не видит больше запись Страшилы:

Рис.11

вместе с тем, совершить над собой аутодафе Страшиле не удалось – он остался в списке пользователей:

Рис.12

В завершение покажем на примере Страшилы, что пользователь может обновлять и удалять собственные записи, т.е. те, которые он добавил:

update dbo.Собственные_документы set Название = 'в' where id = 3

delete dbo.Собственные_документы where id = 3

--------------------------------------------------------

(1 row(s) affected)

В упомянутой выше статье рассматривается также сценарий безопасности уровня ячейки, при котором факт наличия записи пользователя не скрывается от других пользователей. В нем считается, что нет криминала в том, что Железный дровосек будет знать, что у Страшилы в таблице имеется столько-то записей. Он даже может знать названия лежащих там документов. Защищаются только отдельные ее поля, например, содержание. Применимость подобного подхода зависит от ситуации. В некоторых случаях не только названия документов, но даже их количество у конкретного пользователя может являться чувствительной информацией. В то же время, если это приложение обработки и хранения изображений, и в таблице лежат фотки, которые Страшила сделал во время своего недавнего отпуска, нет, к примеру, большого греха в том, что Железный дровосек будет знать, что в этой записи у Страшилы хранится "Утро в сосновом бору", а в этой - "Последний кабак у заставы". Главное, что сами изображения, т.е. поле Содержание, он сможет увидеть не раньше, чем Страшила добавит его в друзья. Подобный сценарий реализуется за счет индивидуального шифрования ячеек стандартными функциями T-SQL EncryptByKey, которая производит шифрование на основе симметричного ключа, EncryptByAsymKey и EncryptByCert, которые понятно, при помощи чего производят шифрование. К сожалению, они не поддерживаются в SQL Azure.

В SQL Azure также не поддерживается TDE - прозрачное шифрование БД, при котором она шифруется целиком. Это понятно. TDE не защищает данные от администратора, потому что они автоматически расшифровываются при подтягивании страниц базы с диска в память, а поскольку администратор сервера SQL Azure имеет доступ к любой расположенной на нем базе, то при желании он их легко увидит. Прозрачное шифрование выступает, скорее, как защита от ворья, случайно получившего доступ к файлам БД. Незашифрованные файлы можно унести, приаттачить и прочитать на другом сервере. На худой конец, бинарным редактором. С зашифрованной базой такие номера не пройдут. Предполагается, что датацентр хорошо защищен по периметру и случайные воры туда не проникнут.

Продолжение следует.

Алексей Шуленин

Last updated on 2012-02-17

Freigeben über

Мультитенантные таблицы в SQL Azure. Безопасность уровня записи.

Zusätzliche Ressourcen