Кому это может быть интересно?
Вопросы безопасности всегда относились к категории “продвинутых” тем и, одновременно, тем неблагодарных. Компонент безопасности любого законченного решения лишь снижает его производительность и затрудняет поддержку и развитие. При этом представить рабочую систему масштаба хотя бы “средняя” без такового компонента решительно невозможно.
Видимо в силу изложенной специфики авторы независимые не могут не писать о безопасной “стороне” той или иной платформы, но делают это не очень охотно, а если и пишут, то через абзац вставляют нечто вроде “за нюансами проблемы обратитесь к SQL Server Books Online (BOL)”. Ну, конечно, если речь идет о безопасности именно этого сервера. В BOL, разумеется, есть вся информация по столь замечательному продукту как SQL Server, вот только именно подраздел «Security and Protection (Database Engine)» изложен столь сухим и формальным языком, что сосредоточенного внимания хватает хорошо если на полчаса такого чтения. А кроме того, авторы каждой отдельной статьи этого подраздела, похоже исходили из того что мы уже прочли и разобрались во всех прочих составных компонентах безопасности SQL Server и поэтому тему можно излагать максимально сжато и не отвлекаясь на объяснение терминов и сущностей упоминаемых чуть не в каждом втором предложении. Все это крайне негативно сказывается на целостности восприятия: каждая отдельная глава статьи вроде и понятна, а зачем все это сведено в один материал — нет. Так же существенным минусом является или полное отсутствие “запускабельных” примеров освещающих ту или иную концепцию, или, в лучшем случае, изрядная скудность таковых.
Все это привело автора строк, которые вы читаете, к желанию в паре-другой собственноручно набранных заметок поделится собственноручными же изысканиями в сфере безопасности Microsoft SQL Server, а так же изложить свое видение и понимание тонких нюансов, коими сфера эта нашпигована зело богато.
Итак, как вы уже понимаете, речь у нас пойдет о безопасности платформы Microsoft SQL Server, а более конкретно ее версии 2008 R2. Подавляющее большинство излагаемых концепций и принципов будут работать совершенно идентично и в версии 2008, и даже 2005. А вот пользователям версий 2000 и более ранних данная статья может быть полезна разве что с академической точки зрения: подавляющее большинство сопровождающих изложение примеров и скриптов (а их будет с избытком) на этой версии или работать не будут вовсе, или будут возвращать не тот результат который мы вправе ожидать от более новых версий. И, раз уж речь зашла о примерах, давайте сразу и определимся что нам потребуется для их успешного запуска. На самом деле — не так много: на нашей машине (физической или виртуальной) должен быть развернут Microsoft SQL Server любой редакции и версии (но, как уже было указано, не ниже 2005-й). Причем нам нужны будут 2 экземпляра (instance) такого сервера: один по умолчанию и один именованный. На машине автора последний имеет имя MSSQL2, а сама машина называется WINR2 но, конечно же, это не принципиально, если вы не против в паре скриптов подправить указанные значения на соответствующие вашему окружению. Каждый приводимый в статье скрипт является самодостаточным в том плане что создает все необходимые для демонстрации объекты (от баз и имен входов до строк таблицы), а в конце работы все эти объекты удаляет. Тем не менее автор настойчиво рекомендует запускать все тестовые скрипты на тестовых же машинах и не использовать для изучения излагаемых концепций рабочий сервер вашего предприятия. :) Если не указано иное, то скрипт можно запускать на любом из двух экземпляров; если же пара скриптов предназначена для совместной работы на разных экземплярах, то это будет явно отмечено в тексте. В любом случае мы (т.е. “запускатели” скриптов) должны быть членами фиксированной серверной роли sysadmin на обоих экземплярах. Наконец автор некоторое время размышлял над вопросом формата публикации самих скриптов: выложить ли их как готовое решение (solution) для Management Studio и ссылаться на них из текста статьи или же включить их полный код непосредственно в статью? По зрелому размышлению победил последний подход. Да, это значительно “раздувает” объем материала и читателям только на PC этот подход будет доставлять определенные неудобства. НО! Немалое количество читателей статей подобного толка (и сам автор из их числа) любят их печатать или копировать на свой PDA/КПК дабы неспешно изучить по дороге на работу/домой. В этом случае подобный подход позволяет “запускать скрипт глазами” оставаясь постоянно “на волне” излагаемого материала. Скрипты которые при подобном чтении заинтересовали особо или показались спорными, неясными можно как-то пометить и потом прогнать на реальном сервере.
Так же будет уместно предупреждение о том, что цикл статей ни разу не является ни учебником по серверу как таковому, ни по языку программирования T-SQL тем более. Он рассчитан на аудиторию имеющую хотя бы небольшой, но несомненно отличный от нулевого практический опыт работы с платформой. В частности, для полного понимания материалов излагаемых в этой и следующих частях потребуется хотя бы поверхностное (но лучше уверенное) знание таких концепций как:
- имя входа (login)
- пользователь базы данных (user) и, в особенности, его взаимосвязь с предыдущей сущностью
- разрешения (permissions)
- схема (schema)
- контекст выполнения (execution context) вообще и токен безопасности (security token) в частности
- цепочка владения (ownership chaining); справедливости ради замечу, что вот этот механизм будет описан весьма (даже можно сказать исчерпывающе) подробно в этой и следующей части цикла, однако некоторый исходный набор базовых знаний по нему так же не повредит
И, разумеется, предполагается знание основных конструкций языка T-SQL таких как операторы, хранимые процедуры, функции, триггеры, блоки обработки ошибок try/catch, пакеты (batches) и т.п.
Одним словом цикл нацелен на IT профессионалов плотно работающих с SQL Server 2005/8/8R2, имеющих определенный опыт общения с ним и желающим упорядочить и/или расширить свои знания в области безопасности указанной платформы. Кстати, даже такой казалось бы вполне конкретный термин как “безопасность” примененный даже к конкретному продукту (SQL Server) все еще можно трактовать достаточно широко. Поэтому уточню, что нас будет прежде всего интересовать контролируемый и управляемый доступ пользователей тех решений что мы разрабатываем и/или администрируем, к данным в этих самых решениях содержащихся. А так же способы и варианты достижения такого доступа и “грабли” (чаще всего нежданные) коими эти варианты щедро усыпаны. При этом вариант самый очевидный — завести нового пользователя и дать ему соответствующие разрешения (permissions) — мы рассматривать не будем, предполагается что этот подход нам хорошо известен. Мы рассмотрим ситуации неявной раздачи прав, когда пользователь приобретает их лишь на ограниченном участке T-SQL кода.
Очертив круг интересов и определив исходные позиции давайте переходить непосредственно к сути повествования.
О чем поговорим
Центральной темой этого цикла автор выбрал такую конструкцию как EXECUTE AS. Считаете что с ней и так все ясно? Не торопитесь, или, по крайней мере, проверьте свою уверенность прочитав все заметки цикла до конца. С большой долей вероятности вы узнаете что-то новое… Как бы то ни было мы постараемся разобрать указанный элемент языка T-SQL а так же концепции платформы вокруг которых он вращается практически “по косточкам”.
Цепочки владения
Как уже пояснялось во вступительной части, автор исходит из предположения достаточной квалификации читающей данные строки аудитории и поэтому обзор и описание базовых концепций подсистемы безопасности SQL Server не входят в его цели. Тем не менее, одна из таких концепций, а именно та что вынесена в заголовок текущего раздела (или она же на языке оригинала — ownership chain) настолько важна для понимания дальнейшего материала, и настолько связана с конструкцией EXECUTE AS, что автор рискнет описать ее довольно детально и дополнительно проиллюстрирует описание примерами. И более того мы начнем именно с этого механизма, а потом перейдем к теме центральной и посмотрим где и в чем их связь проявляется.
Итак… В базе DB1 есть пользователи A, B и TEST. Пользователь A владеет (owned) хранимой процедурой usp1, которая состоит из одной строки
select * from tbl1и при этом владельцем целевой таблицы tbl1 снова является A. В этом случае пользователю TEST для успешного извлечения данных из указанной таблицы посредством usp1 достаточно иметь единственное разрешение (permission) — на выполнение (т.е. EXECUTE) самой хранимой процедуры. Ему не требуется разрешение на выборку данных из таблицы. Это происходит потому, что usp1 (объект ссылающийся) и tbl1 (объект ссылаемый) принадлежат одному и тому же пользователю — A. В результате образуется та самая ownership chain, приводящая к пропуску движком сервера (и это не фигура речи! это действительно случается в буквальном смысле) проверки разрешений на ссылаемом объекте. Если сменить владельца usp1 или tbl1 (но не обоих вместе!) на пользователя B мы будем иметь разорванную цепочку владения (broken ownership chain) которая характеризуется различными владельцами ссылающегося и ссылаемого объектов и при которой права пользователя TEST проверяются самым обычным образом, то бишь по полной программе. И, конечно же, этот механизм действует не только в случае хранимая процедура->таблица, а в любом месте где у нас один объект ссылается на другой объект. Скажем у нас может выстроиться такая цепочка: триггер T1 вызывает функцию F1, которая обращается к представлению (view) V1, которое выбирает данные из представления V2, которое работает с базовой таблицей TBL1. Тогда в каждом из звеньев такой цепочки T1->F1->V1->V2->TBL1 ownership chain будет или цела, или разорвана и, следовательно, проверка движком сервера прав пользователя инициировавшего цепочку на исполнение может приостанавливаться и возобновляться несколько раз!
Проверим все вышеизложенное практикой, скрипт Test01, но сначала небольшое предупреждение. Дело в том, что одним из центральных мест этого скрипта является как раз исполнение инструкций EXECUTE AS / REVERT, речь о которых у нас пока далеко впереди. Для читателей совсем с ними дело не имевших (а дело имевшие могут данный абзац пропустить и обратиться непосредственно к коду скрипта) поясню на текущий момент очень кратко: весь набор T-SQL инструкций и операторов заключенных между двумя указанными выполняется не от нашего лица (т.е. администратора сервера / пользователя dbo любой базы), а от лица пользователя указанного в первой из инструкций (EXECUTE AS). Вообще-то, если бы нам было не лень, то можно было бы остановить выполнение тест-скрипта перед EXECUTE AS, открыть второе окно редактора студии, подключится в нем как особый (не dbo) пользователь и скопировать в него весь набор инструкций между EXECUTE AS / REVERT (уже, конечно, не включая их самих). Результат теста был бы тот же самый, но нам лень :) и проще воспользоваться указанным приемом, который по научному называется переключением контекста исполнения (switch of the execution context) и который, по сути, и является фокусом внимания всего этого цикла статей. Еще раз повторю, что обе инструкции в свое время будут разобраны очень подробно, а пока достаточно знать что они просто избавляют нас от необходимости открывать вторую «не-администраторскую» сессию работы с сервером.
Итак — Test01:
USE master
go
-- Создаем тестовую базу
CREATE DATABASE [~DB~]
go
-- Переключаемся в нее
USE [~DB~]
go
-- создаем пользователя под которым будем запускать процедуру
CREATE USER TEST WITHOUT LOGIN
go
-- создаем пару пользователей что бы назначить их владельцами объектов
CREATE USER A WITHOUT LOGIN
CREATE USER B WITHOUT LOGIN
go
-- Создаем тестовую таблицу. Поскольку мы будем тестировать разрешения
-- нам не требуются строки в ней; возврат пустого набора уже достаточен для
-- подтверждения наличия прав на выборку данных
CREATE TABLE tbl1 (t int NOT NULL)
go
-- хранимая процедура
CREATE PROCEDURE usp1 AS SELECT * FROM tbl1
go
-- делаем A владельцем обоих объектов
ALTER AUTHORIZATION ON tbl1 TO A
go
ALTER AUTHORIZATION ON usp1 TO A
go
-- даем TEST разрешение на выполнение проц-ры usp1
-- НЕ ДАЕМ ему никаких прав на таблицу
GRANT EXECUTE ON usp1 TO TEST
-- выполняем usp1 от лица TEST
-- в силу ownership chain выполнение будет успешным
EXECUTE AS USER = 'TEST'
go
EXEC usp1
go
REVERT
go
-- меняем владельца одного из двух объектов
ALTER AUTHORIZATION ON usp1 TO B
go
-- поскольку изменился владелец про-ры, а разрешение для TEST "выписывалось" от лица A
-- надо "перевыписать" разрешение на исполнение, на этот раз от имени B
GRANT EXECUTE ON usp1 TO TEST
-- снова выполняем usp1 от лица TEST
-- однако на этот раз в силу BROKEN ownership chain выполнение закончится неудачей
-- сообщение: The SELECT permission was denied on the object 'tbl1', ...
EXECUTE AS USER = 'TEST'
go
EXEC usp1
go
REVERT
go
-- очистка
USE master
go
DROP DATABASE [~DB~]
go
Собственно, после сказанного выше комментировать особенно нечего. Все работает (или не работает) именно так и именно в тех местах где и ожидается.
Теперь — хороший вопрос! — а что, ownership chain отменяет проверку прямо-таки для любого запрошенного действия? Иными словами, если в сценарии чуть выше владельцем usp1 и tbl1 является dbo и процедура включает строчку типа
DROP TABLE tbl1то TEST обладая только правом исполнения процедуры сможет и это с помощью нее делать? Выясняется что нет, по меткому замечанию классика “всему же есть границы”. Граница «послаблений» цепочки владения проходит ровно по командам группы DML операторов (SELECT, INSERT, DELETE, UPDATE) и плюс EXECUTE. Только эти 5 команд пропускаются (разумеется, если у нас имеет место быть целая цепочка владения) движком сервера от сопоставления с набором прав того пользователя, который пытается их выполнить. Справедливости ради заметим, что это, пожалуй 95 (а может и 99) процентов команд нужных рядовому пользователю системы. Команды категорий CREATE / ALTER / DROP явно администраторские, ну может за исключением создания временных таблиц. Как бы то ни было, а даже такая команда как TRUNCATE TABLE, формально относящаяся к группе DML (она же данные и только данные меняет!), не будет пропущена движком сервера как результат работы обсуждаемого механизма. Оставив в стороне споры о легитимности присутствия этой команды в языке T-SQL вообще (что тоже интересно, но лежит явно в стороне сферы безопасности), заметим, что если мы в своей архитектуре решили полагаться на цепочки владения, то нам есть все резоны предпочесть данной “проблемной” команде классический DELETE. Вот он с обсуждаемым механизмом работает совершенно без вопросов, в чем мы сейчас и убедимся, скрипт Test02:
USE master
go
-- Создаем тестовую базу
CREATE DATABASE [~DB~]
go
-- Переключаемся в нее
USE [~DB~]
go
-- создаем пользователя под которым будем запускать процедуру
CREATE USER TEST WITHOUT LOGIN
go
-- Создаем тестовую таблицу. Поскольку мы будем пытаться удалять строки
-- вставим парочку для экспериментов
CREATE TABLE tbl1 (t int NOT NULL)
go
insert tbl1 values (11)
insert tbl1 values (22)
go
-- хранимая процедура; пробуем удалять строки через DELETE
CREATE PROCEDURE usp1 AS
SELECT 'usp1-старт',* FROM tbl1
DELETE tbl1 WHERE t=11
SELECT 'usp1-финиш',* FROM tbl1
go
-- хранимая процедура; пробуем удалять строки через TRUNCATE
CREATE PROCEDURE usp2 AS
SELECT 'usp2-старт',* FROM tbl1
BEGIN TRY
TRUNCATE TABLE tbl1
print 'Таблица tbl1 успешно усечена'
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
print 'Не удалось усечь таблицу tbl1'
END CATCH
SELECT 'usp2-финиш',* FROM tbl1
go
-- хранимая процедура вызывающая две предыдущие; нужна для тестирования пропуска
-- проверки разрешений на команду EXECUTE
CREATE PROCEDURE usp3 AS
EXECUTE usp1
EXECUTE usp2
go
-- даем TEST разрешение на выполнение проц-ры usp3
-- НЕ ДАЕМ ему никаких прав на таблицу или две других процедуры
-- владельцами всех четырех объектов остаемся мы, т.е. 'dbo'
GRANT EXECUTE ON usp3 TO TEST
-- выполняем usp3 от лица TEST, выводы:
-- 1) usp1/usp2 запускаются успешно -> разрешение EXECUTE НЕ ПРОВЕРЯЕТСЯ
-- 2) DELETE сработал -> проверка снова не производилась
-- 3) TRUNCATE завершился сообщениями:
---- 'Cannot find the object "tbl1" because it does not exist or you do not have permissions' и
---- 'Не удалось усечь таблицу tbl1'
---- и строка со значением 22 остается в таблице;
---- т.е. цепочка владения НЕ ПРИВОДИТ к пропуску проверки для этой команды
EXECUTE AS USER = 'TEST'
go
EXEC usp3
go
REVERT
go
-- очистка
USE master
go
DROP DATABASE [~DB~]
go
Все комментарии приведены непосредственно в тексте скрипта.
На этом первая часть цикла завершена, автор ждет отзывов, а в части второй мы продолжим изучение цепочек и посмотрим — как они работают (и работают ли вообще?) если два объекта ее составляющие находятся в разных базах, а так же в разных экземплярах сервера.