До этого момента мы уже затрагивали несколько особенностей SQL Azure, связанных с безопасностью.
Во-первых, все соединения с SQL Azure в обязательном порядке шифруются на основе TLS (SSL), что соответствует добавлению в строку соединения свойств Encrypt=True;TrustServerCertificate=true. Их можно не прописывать в явном виде - при установлении соединения с сервером SQL Azure оно будет зашифровано по его инициативе. Делается это по аналогии с шифрованием пользовательских соединений в обычном SQL Server, которое мы рассматривали здесь как-то по весне. Таким образом обеспечивается защита от злобного хакера, который перехватывает на полдороге трафик между клиентом и Облаком и пытается его прочитать.
Во-вторых, по умолчанию, клиентские соединения с сервером SQL Azure запрещены. Чтобы их разрешить, надо добавить IP-адрес клиента в правила брандмауэра SQL Azure. Мы это проделывали в посте Создание сервера SQL Azure - см.тамошние Рис.9, 12. Программное управление правилами осуществляется хранимыми процедурами sp_set_firewall_rule N'Название правила','Начальный IP','Конечный IP', при помощи которой создается новое правило брандмауэра, и sp_delete_firewall_rule [@name =] N"Название правила", при помощи которой оно удаляется, а также системного представления sys.firewall_rules, при помощи которого просматриваются существующие правила брандмауэра.
В SQL Azure поддерживается стандартная модель безопасности SQL Server, то есть логин, входящий на уровень сервера при помощи пароля, и соответствующий ему пользователь внутри той или иной базы данных. При создании нового сервиса в виде сервера SQL Azure (см. Рис.6, 7) вместе с БД master создается административный логин (Рис.8), который выполняет ту же роль, что и небезызвестный sa в обычном SQL Server. В обычном SQL Server существуют системные представления sys.server_principals, показывающее логины и роли уровня сервера, sys.server_role_members, показывающее членство логинов в серверных ролях, и sys.server_permissions, показывающее, какой принципал какими правами на уровне сервера обладает. Список поддерживаемых в SQL Azure системных представлений можно посмотреть в BOL. Ни одного из трех перечисленных в нем нет. Существует аналог представления sql_logins, которое показывает все логины для данного сервера SQL Azure, потому что интегрированная с Windows аутентификация не поддерживается. Создание нового логина осуществляется при помощи команды CREATE LOGIN. В отличие от обычного SQL Server нельзя указывать политики сложности и срока действия пароля; то, что человек должен сменить его при первом заходе; базу данных по умолчанию, куда будет устанавливаться соединение и т.д.
create login Гудвин with password = 'p@$$w0rd'
Грантование прав на доступ к базе, как обычно, осуществляется заведением в этой БД пользователя, соответствующего данному логину. Сейчас я нахожусь в контексте БД master и выдаю права на доступ к этой базе свежесозданному логину:
create user Гудвин_user from login Гудвин
Чтобы логин мог создавать других логинов, в SQL Azure нужно включить соответствующего ему пользователя в БД master в роль loginmanager в этой базе. В обычном SQL Server ее аналогом выступает серверная роль securityadmin.
exec sp_addrolemember @rolename = 'loginmanager', @membername = N'Гудвин_user'
Рис.1
select name, principal_id, type_desc, default_schema_name, owning_principal_id, is_fixed_role, authentication_type_desc from sys.database_principals
Рис.2
Теперь логин Гудвин может зайти на данный сервер SQL Azure в контексте БД master:
sqlcmd -S tcp:fxv4koqar4.database.windows.net,1433 -U Гудвин@fxv4koqar4 -P p@$$w0rd -N -C
Рис.3
На всякий случай напомню, что при коннекте sqlcmd к SQL Azure логин задается в виде <логин>@<сервер SQL Azure> - см. Рис.13 предыдущего поста.
Человек, которому мы выдадим этот логин, может не иметь эккаунта Windows Azure, следовательно, портал управления windows.azure.com -см., напр., Создание базы данных\Рис.6 - будет ему не доступен. Однако он может попасть на портал администрирования своей базы по адресу <имя сервера, где лежит его база>.database.windows.net. Я сейчас специально выбираю LiveID, не имеющий прав на Azure, захожу на https://fxv4koqar4.database.windows.net/ и на открывшемся голубом экране, как при Создании базы данных\Рис.7, ввожу в поле User Гудвин и в поле Password пароль этого логина.
Рис.4
Открывается портал управления БД - см. Работа с базой данных из портала\Рис.1. Нажимаем New Query и удостоверяемся, что это действительно тов.Гудвин:
Рис.5
Членство в роли loginmanager дает право создавать новые логины, но не дает право создавать новые базы:
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)
Рис.6
Нужно зайти на https://fxv4koqar4.database.windows.net/#$entity=NewQuery&id=1&workspace=Query под глобальным администратором сервера fxv4koqar4 (тем, который заводился при создании сервера) и дать право на создание новых баз логину Гудвин. Сказать grant create any database to Гудвин мы не можем, потому что Msg 40520, Level 16, State 1. Securable class 'server' not supported in this version of SQL Server. Можно включить пользователя Гудвин_user, соответствующего логину Гудвин в БД master в датабазную роль dbmanager БД master аналогично включению в роль loginmanager:
exec sp_addrolemember @rolename = 'dbmanager', @membername = N'Гудвин_user'
Рис.7
Теперь оператор создания новой базы на гудвиновском соединении отрабатывает нормально:
Рис.8
Чтобы дать логинам Страшила и Железный дровосек право на доступ к свежесозданной базе, надо создать в ней пользователей, соответствующих этим логинам. Гудвин сейчас находится в контексте БД master. Команды USE, как мы помним, в SQL Azure нет. Поэтому он должен открыть еще одно соединение аналогично Рис.4 и вместо master указать название БД Изумрудный город.
Рис.9
Для разведения интересов пользователей каждому внутри базы будет создана схема под его объекты и выданы некоторые права на копошение внутри своей схемы:
create user Страшила_user from login Страшила
go
create user [Ж/д_user] from login [Железный дровосек]
go
create schema Sch_Страшила authorization Страшила_user
grant create table to Страшила_user
go
create schema [Sch_ж/д] authorization [Ж/д_user]
grant create table to [Ж/д_user]
Рис.10
Выполнять эти операторы лучше не торопясь, вдумчиво, один за другим, чтобы к моменту создания схемы SQL Azure успела сообразить, что такой пользователь в базе уже создан.
Теперь по образцу Рис.9 открываем еще одно соединение от лица логина Страшила к БД Изумрудный город и выполняем скрипт
create table Sch_Страшила.[Мои документы] (ID int identity(1, 1) primary key clustered, Название nvarchar(150), Содержание nvarchar(max))
insert Sch_Страшила.[Мои документы] (Название) values ('Мозги на основе отрубей и булавок. Диссертация на соискание ученой степени любителя наук (любительская)')
grant select on schema::Sch_Страшила to [Ж/д_user]
create table [Sch_ж/д].[Мои документы] (ID int identity(1, 1) primary key clustered, Название nvarchar(150), Содержание nvarchar(max))
Рис.11
Мы видим что пользователь Страшила_user обладает у себя в схеме правами, розданными ему создателем базы Изумрудный город на уровне базы. На чужие схемы у него таких прав нет, как и наоборот - у Железного дровосека на схему Страшилы. Чтобы Железный дровосек мог читать из таблиц схемы Страшилы, тот грантует ему права на чтение схемы. Открываем еще одно соединение, на этот раз от имени Железного дровосека, и проверяем:
select * from Sch_Страшила.[Мои документы]
update Sch_Страшила.[Мои документы] set Название = 'Мозги на основе отрубей и булавок',
Содержание = 'Диссертация на соискание ученой степени любителя наук (любительская)'
where id = 1
Рис.12
Таким образом, разводка пользователей на уровне схем работает нормально. Под разводкой пользователей понимается не то, что сейчас подумали циничные читатели, а разделение пользовательских данных и гибкое управление правами на доступ к ним. Однако не для всех сценариев схемы являются оптимальным вариантом. Допустим, при помощи своей облачной базы Гудвин предоставляет сервисы по хранению и обработке пользовательских снимков. Или проверке орфографии в документах. Или еще что-нибудь. Пользователей в этом случае ожидается гораздо больше. Заводить под каждого схему нереально. Проще создать одну табличку на всех, где на каждую фотку отводится запись с полем varbinary(max), далее описание, выдержка, диафрагма, вспышка, размер кадра и т.д. Мы с вами упражнялись перекладыванием фоток в базу еще во времена SQL Server 2008 на тему FILESTREAM. См., например, посты из серии SQL Server File System. Проблема состоит в том, что каждый пользователь должен в этом случае изначально получать доступ только к своим записям в общей таблице, т.е. в row-level security, точнее, в ее отсутствии. Ее, кстати сказать, очень сильно недостает и в обычном SQL Server. Зато в 2012 появилась функция iif(). Невозможно себе представить, как мы жили без нее до сих пор с одним немудреным case ... when ... else. Нет, я ничего не имею против функции iif(). Не исключено, что это очередной тектонический сдвиг, который позволил резко укрепить конкурентные преимущества и т.д. Просто мне казалось, что появление, наконец, безопасности уровня записи осчастливило бы гораздо больше людей, которые ждали ее и в 2005-м, и в 2008-м, и снова мимо. Стало быть, я заблуждался. На сегодня в колбасе потребности нет. Придется снова прибегнуть к костылям и подпоркам - см. SQL Server Label Security Toolkit на CodePlexe. Там имеется ссылка на техническую статью "Implementing Row- and Cell-Level Security in Classified Databases", которая, как неожиданно выяснилось, не работает. Давайте на этом месте ненадолго прервемся, а мультитенантность в пределах одной таблицы разберем в следующем посте.