Мультитенантные таблицы в 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
go
drop login Страшила
go
drop login [Железный дровосек]
go
drop login Гудвин
go
drop database [Изумрудный город]
go
Скрипт 1
Соответственно, если считать, что от прошлого занятия на сервере SQL Azure ничего не осталось, нужно зайти в БД master под его владельцем и выполнить скрипт:
create login Гудвин with password = 'p@$$w0rd'
go
create user Гудвин_user from login Гудвин
go
exec sp_addrolemember @rolename = 'loginmanager', @membername = N'Гудвин_user'
exec sp_addrolemember @rolename = 'dbmanager', @membername = N'Гудвин_user'
Скрипт 2
От имени пользователя Гудвин в контексте БД master:
create login Страшила with password = 'p@$$w0rd'
go
create login [Железный дровосек] with password = 'p@$$w0rd'
go
create database [Изумрудный город] collate cyrillic_general_100_ci_as (maxsize = 1 GB)
Скрипт 3
От имени пользователя Гудвин в контексте БД Изумрудный город:
create user Страшила_user from login Страшила
go
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.Документы
go
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.Документы_друзей
go
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.Собственные_документы
go
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
---------
<Users><aaa /><bbb /><ccc /></Users>
Скрипт 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.Дать_доступ_пользователю_к_документу
go
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
go
grant execute on Дать_доступ_пользователю_к_документу to public
Рис.6
Функция dbo.Есть_ли_такой_пользователь_в_базе также создается на соединении Гудвина:
if object_id('dbo.Есть_ли_такой_пользователь_в_базе', 'FN') is not null drop function dbo.Есть_ли_такой_пользователь_в_базе
go
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
go
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.Отобрать_у_пользователя_доступ_к_документу
go
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_документа
go
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)
(1 row(s) affected)
В упомянутой выше статье рассматривается также сценарий безопасности уровня ячейки, при котором факт наличия записи пользователя не скрывается от других пользователей. В нем считается, что нет криминала в том, что Железный дровосек будет знать, что у Страшилы в таблице имеется столько-то записей. Он даже может знать названия лежащих там документов. Защищаются только отдельные ее поля, например, содержание. Применимость подобного подхода зависит от ситуации. В некоторых случаях не только названия документов, но даже их количество у конкретного пользователя может являться чувствительной информацией. В то же время, если это приложение обработки и хранения изображений, и в таблице лежат фотки, которые Страшила сделал во время своего недавнего отпуска, нет, к примеру, большого греха в том, что Железный дровосек будет знать, что в этой записи у Страшилы хранится "Утро в сосновом бору", а в этой - "Последний кабак у заставы". Главное, что сами изображения, т.е. поле Содержание, он сможет увидеть не раньше, чем Страшила добавит его в друзья. Подобный сценарий реализуется за счет индивидуального шифрования ячеек стандартными функциями T-SQL EncryptByKey, которая производит шифрование на основе симметричного ключа, EncryptByAsymKey и EncryptByCert, которые понятно, при помощи чего производят шифрование. К сожалению, они не поддерживаются в SQL Azure.
В SQL Azure также не поддерживается TDE - прозрачное шифрование БД, при котором она шифруется целиком. Это понятно. TDE не защищает данные от администратора, потому что они автоматически расшифровываются при подтягивании страниц базы с диска в память, а поскольку администратор сервера SQL Azure имеет доступ к любой расположенной на нем базе, то при желании он их легко увидит. Прозрачное шифрование выступает, скорее, как защита от ворья, случайно получившего доступ к файлам БД. Незашифрованные файлы можно унести, приаттачить и прочитать на другом сервере. На худой конец, бинарным редактором. С зашифрованной базой такие номера не пройдут. Предполагается, что датацентр хорошо защищен по периметру и случайные воры туда не проникнут.
Алексей Шуленин