Обработка ошибок и транзакций в sql server. часть 1. обработка ошибок

Введение

Один из самых важных критериев надежности информационной системы — безопасность СУБД. Атаки, направленные на нее, в большинстве случаев критические, потому что могут частично либо полностью нарушить работоспособность системы. Поскольку крупные организации формировали свою инфраструктуру давным-давно и обновление на новые версии ПО вызывает у них «большие» проблемы, самыми распространенными версиями до сих пор остаются MS SQL Server 2005 и MS SQL Server 2008. Но это всего лишь статистика, и далее мы будем рассматривать общие для всех версий векторы и техники. Для удобства условно разобьем весь процесс пентеста на несколько этапов.

Отключение эскалации блокировки

Хотя можно отключить эскалацию блокировки в SQL Server, мы не рекомендуем ее. Вместо этого используйте стратегии предотвращения, описанные в разделе Предотвращение эскалации блокировки.

• Уровень таблицы: Можно отключить эскалацию блокировки на уровне таблицы. См. ALTER TABLE . SET (LOCK_ESCALATION = DISABLE) . Чтобы определить, какую таблицу нацелить, изучите запросы T-SQL. Если это невозможно, используйте расширенные события,в lock_escalation событие и изучите столбец object_id. Кроме того, используйте событие Lock:Escalation и изучите столбец с ObjectID2 помощью SQL Profiler.
• Уровень экземпляра: Вы можете отключить эскалацию блокировки, включив флаг трассировки 1211 для экземпляра. Однако этот флаг трассировки отключает всю эскалацию блокировки во всем мире в экземпляре SQL Server. Эскалация блокировки служит полезной цели в SQL Server за счет максимальной эффективности запросов, которые в противном случае замедляются из-за накладных расходов на приобретение и освобождение нескольких тысяч замков. Эскалация блокировки также помогает свести к минимуму требуемую память, чтобы отслеживать блокировки. Память, которую SQL Server динамически распределить для структур блокировки, является конечной. Поэтому, если отключить эскалацию блокировки и объем памяти блокировки будет достаточно большим, любая попытка выделить дополнительные блокировки для любого запроса может привести к сбойу и создать следующую запись ошибки:

При ошибке 1204 она останавливает обработку текущего заявления и вызывает откат активной транзакции. Откат сам по себе может заблокировать пользователей или вызвать длительное время восстановления базы данных, если перезапустить SQL Server службу.

Этот флаг трассировки (-T1211) можно добавить с помощью диспетчер конфигурации SQL Server. Необходимо перезапустить службу SQL Server, чтобы новый параметр запуска вступил в силу. При запуске DBCC TRACEON (1211, -1) запроса флаг трассировки вступает в силу немедленно. Однако если не добавить параметр запуска -T1211, при перезапуске службы SQL Server теряется DBCC TRACEON эффект команды. Включение флага трассировки предотвращает любые будущие эскалации блокировки, но не отменяет эскалации блокировки, которые уже произошли в активной транзакции.

Если используется подсказка блокировки, например ROWLOCK, это изменяет только первоначальный план блокировки. Подсказки блокировки не препятствуют эскалации блокировки.

Повышение привилегий. Хранимые процедуры, подписанные сертификатом

Для описания данного вектора создадим уязвимую хранимую процедуру, подписанную сертификатом. В отличие от предыдущих примеров, для эскалации привилегий необязательны:

свойство TRUSTWORTHY = On;

привилегии IMPERSONATE и функция EXECUTE AS;

конфигурация хранимой процедуры с классом WITH EXECUTE AS для ее выполнения от имени другой учетной записи.

Создадим учетную запись с минимальными правами:

Выключим свойство : . И создадим простую хранимую процедуру , которая будет выводить столбец из базы , а также из базы, которую ввел пользователь:

После этого создадим ключ шифрования для базы :

И сертификат:

Следующим шагом создаем логин из сертификата :

И подпишем процедуру созданным сертификатом:

Присвоим логину привилегии :

Даем привилегии членам группы PUBLIC исполнять процедуру:

В итоге мы создали пользователя с минимальными правами, хранимую процедуру , которая выводит имя введенной базы, создали сертификат и подписали им хранимую процедуру, а также создали логин из сертификата и дали ему привилегии . На этом подготовительная часть закончена. Логинимся учетной записью и вызываем хранимую процедуру:

Как и положено, она вернет нам имя указанной нами БД — и (см. рис. 3).

Рис. 3. Результат выполнения запроса EXEC MASTER.dbo.sp_xxx ‘master’

Запрос вида вернет уже только (см. рис. 4).

Рис .4. Результат выполнения запроса EXEC MASTER.dbo.xxx ‘master»—‘

Отлично. Это означает, что хранимая процедура подвержена SQL-инъекции. Проверим наши привилегии с помощью следующего запроса:

Рис. 5. Проверяем наши привилегии через уязвимую хранимую процедуру

(см. рис. 5) означает, что мы имеем привилегии sysadmin. Выполнить команду не получится, потому что у учетной записи минимальные права, но если этот запрос внедрить в SQL-инъекцию, то все сработает (рис. 6).

Рис.6. Проверяем свои привилегии в системе

Что самое интересное, такой трюк будет работать в версиях 2005–2014.

Настройка блокировок

Настройку блокировок можно осуществлять, используя подсказки блокировок (locking hints) или параметр LOCK_TIMEOUT инструкции SET. Эти возможности описываются в следующих разделах.

Подсказки блокировок (locking hints)

Подсказки блокировок задают тип блокировки, используемой компонентом Database Engine для блокировки табличных данных. Подсказки блокировки уровня таблиц применяются, когда требуется более точное управление типами блокировок, накладываемых на ресурс. (Подсказки блокировок перекрывают текущий уровень изоляции для сеанса.)

Все подсказки блокировок указываются в предложении FROM инструкции SELECT. Далее приводится список и краткое описание доступных подсказок блокировок:

UPDLOCK

Устанавливается блокировка обновления для каждой строки таблицы при операции чтения. Все блокировки обновления удерживаются до окончания транзакции.

TABLOCK

Устанавливается разделяемая (или монопольная) блокировка для таблицы. Все блокировки удерживаются до окончания транзакции.

ROWLOCK

Существующая разделяемая блокировка таблицы заменяется разделяемой блокировкой строк для каждой отвечающей требованиям строки таблицы.

PAGLOCK

Разделяемая блокировка таблицы заменяется разделяемой блокировкой страницы для каждой страницы, содержащей указанные строки.

NOLOCK

Синоним для READUNCOMMITTED, который мы рассмотрим при обсуждении уровней изоляции.

HOLDLOCK

Синоним для REPEATABLEREAD.

XLOCK

Устанавливается монопольная блокировка, удерживаемая до завершения транзакции. Если подсказка xlock указывается с подсказкой rowlock, paglock или tablock, монопольные блокировки устанавливаются на соответствующем уровне гранулярности.

READPAST

Указывает, что компонент Database Engine не должен считывать строки, заблокированные другими транзакциями.

Все эти параметры можно объединять вместе в любом имеющем смысл порядке. Например, комбинация подсказок TABLOCK с PAGLOCK не имеет смысла, поскольку каждая из них применяется для разных ресурсов.

Параметр LOCK_TIMEOUT

Чтобы процесс не ожидал освобождения блокируемого объекта до бесконечности, можно в инструкции SET использовать параметр LOCK_TIMEOUT. Этот параметр задает период в миллисекундах, в течение которого транзакция будет ожидать снятия блокировки с объекта. Например, если вы хотите чтобы период ожидания был равен восемь секунд, то это следует указать следующим образом:

Если данный ресурс не может быть предоставлен процессу в течение этого периода времени, инструкция завершается аварийно и выдается соответствующее сообщение об ошибке. Значение LOCK_TIMEOUT равное -1 (значение по умолчанию) указывает отсутствие периода ожидания, т.е. транзакция не будет ожидать освобождения ресурса совсем. (Подсказка блокировки READPAST предоставляет альтернативу параметру LOCK_TIMEOUT.)

Основные способы избегания взаимоблокировок

1. Убедитесь, что процессы обращаются ко всем общим объектам в одном и том же порядке.

Рассмотрим процесс: 

Плохой пример, если оба процесса обираются выполнить шаг 3. Каждый из них может оказаться заблокированным другим, потому что им обоим нужен доступ к объекту, заблокированному на шаге 2 другим процессом и который не должен был освободиться до конца транзакции.

Изменить  порядок операторов,  в котором объекты и ресурсы базы данных должны быть доступны процессам – это хороший пример

2. Сделайте транзакции короткими и простыми

В примере выше  у процессов  есть два оператора в транзакции, используйте в каждой транзакции один оператор, тогда транзакции будут очень короткие, и здесь вообще не будет тупиков.

3. Убедитесь, что процессы используют минимально необходимый уровень изоляции транзакций.

Чем ниже уровень изоляции, тем меньше вероятность возникновения взаимоблокировок (хотя и выше вероятность нарушения целостности данных).

Самую высокую скорость выполнения и самую низкую согласованность имеет уровень read uncommitted (взаимоблокировки отсутствуют). На этом уровне каждая транзакция видит незафиксированные изменения другой транзакции (феномен грязного чтения). В SQL Server вы можете минимизировать конкуренцию за блокировку, одновременно защищая транзакции от грязного чтения или незафиксированных изменений данных, используя уровень read commited. Самую низкую скорость выполнения и самую высокую согласованность — serializable.

Установить уровень транзакции:

Проверить уровень транзакции :

Если вы не можете избежать взаимоблокировок, есть возможность указать, какой процесс должен умереть при возникновении взаимоблокировки. 

SQL Server выбирает жертву взаимоблокировки на основе двух факторов: DEADLOCK_PRIORITY, установленного для каждого сеанса, и объема работы, которую SQL Server должен выполнить для отката транзакции.

Параметр DEADLOCK_PRIORITY может быть установлен пользователем на HIGH, NORMAL, LOW или на целочисленное значение от -10 до 10. По умолчанию DEADLOCK_PRIORITY установлено на NORMAL (0)

Чтобы проверить приоритет тупика сеанса, вы можете использовать следующий запрос:

Подробнее об

Резюме

Взаимные блокировки возникают, когда два (или более) сеанса не могут быть продолжены, поскольку они содержат взаимно несовместимые блокировки для одних и тех же ресурсов. SQL Server внутренне обрабатывает взаимоблокировки и устраняет их, убивая транзакцию с наименьшим приоритетом взаимоблокировки или ту, которая выполнила наименьший объем работы. Пишите запросы разумно и используйте правильные индексы, чтобы минимизировать ненужные блокировки.

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

Длинная версия

При изменении таблицы в некоторых случаях среда SSMS генерирует скрипт, который пересоздает всю таблицу и в некоторых простых случаях (например, добавление или удаление столбца) скрипт не’т пересоздать таблицу.

Позвольте’s сделать это образец таблицы в качестве примера:

Каждая таблица имеет параметр lock_escalation` параметр, который установлен для стола по умолчанию.
Позвольте’s поменять это здесь:

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

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

В данном примере это действительно необходимо для создания оператора установить параметр lock_escalation, потому что таблица создается с нуля, и его значение должно быть сохранено.

Если я пытаюсь сделать простое изменение в таблицу с помощью SSMS конструктора таблиц, такие как добавление нового столбца, затем среда SSMS создает сценарий, который не’т пересоздать таблицу:

Как видите, он еще добавляет заявление на альтер настольный набор параметр lock_escalation не’т изменение текущего параметра. Я думаю, разработчики среде SSMS решил, что это не стоит усилий, чтобы попытаться определить, в каких случаях этот оператор Alter настольный набор параметр lock_escalation` является избыточным и генерировать его всегда, на всякий случай. Нет никакого вреда в добавлении это заявление в любое время.

Опять же, стол-широкий параметр не имеет значения, а таблица изменений схемы с помощью оператора Alter таблицыПараметр lock_escalation` настройка влияет только на блокировку поведение операторов DML, такие как «обновить».

Наконец, цитата из альтер TABLE`подчеркивают мое:

Отображение информации о блокировках

Наиболее важным средством для отображения информации о блокировках является динамическое административное представление sys.dm_tran_locks. Это представление возвращает информацию о текущих активных ресурсах диспетчера блокировок. Каждая строка представления отображает активный в настоящий момент запрос на блокировку, которая была предоставлена или предоставление которой ожидается. Столбцы представления соответствуют двум группам: ресурсам и запросам. Группа ресурсов описывает ресурсы, на блокировку которых делается запрос, а группа запросов описывает запрос блокировки. Наиболее важными столбцами этого представления являются следующие:

• resource_type — указывает тип ресурса;

• resource_database_id — задает идентификатор базы данных, к которой принадлежит данный ресурс;

• request_mode — задает режим запроса;

• request_status — задает текущее состояние запроса.

В примере ниже показан запрос, использующий представление sys.dm_tran_locks для отображения блокировок в состоянии ожидания:

Блокирующие замки

Блокирующий замок (blocking lock) возникает, когда блокировка, установленная на объект пользователем, предотвращает или блокирует другим пользователям доступ к тому же объекту или объектам. Таблица удобна для получения этой информации — она сообщает, какие сеансы в данный момент удерживают блокировки на объектах, доступа к которым ждет какой-то другой объект. Информацию из таблицы можно комбинировать с информацией из таблицы , что позволит узнать, кому принадлежит блокирующий сеанс. Ниже показан соответствующий оператор SQL: 

Далее приведен простой пример блокирующего сеанса: пользователь Nick Alapati издает следующий оператор DML, но не фиксирует его:

Пользователь Nina Alapati, между тем, выдает аналогичный оператор, который при выполнении зависает:

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

С помощью представления можно узнать, какие сеансы блокируют другие сеансы. Вот простой запрос, использующий это представление, который показывает блокирующий замок, вызванный предыдущими двумя операторами SQL: 

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

Явное блокирование таблицы

Каждый раз, когда вы добавляете столбец к таблице, база данных должна установить монопольную DML-блокировку на этой таблице. Можно указать, что команда DDL должна ожидать определенный период времени перед отказом, когда не удается установить блокировку DML. Оператор позволяет специфицировать максимальный период времени, который оператор DDL может ожидать возможности захвата DML-блокировки таблицы. Применяйте это средство при добавлении столбца, часто обновляемого пользователями.

Ниже приведен синтаксис оператора : 

В операторе значения и параметр означают следующее.

• Если вы хотите, чтобы база данных вернула управления немедленно, обнаружив,что требуемая таблица уже заблокирована другим пользователем, укажите опцию .
• С помощью параметра можно задать количество секунд, в течение которых оператор может ожидать возможности установки DML-блокировки. Значение этого параметра является целочисленным и на него не накладывается никаких ограничений.
• Если не указано ни , ни , база данных будет ожидать до тех пор, пока заблокированная таблица не станет доступной, и затем заблокирует ее перед возвратом управления.

Использование интерфейса Database Control для управления блокировками сеансов

Наиболее эффективный способ увидеть, какие блокировки в данный момент существуют в экземпляре, заключается в использовании инструмента Oracle Enterprise Manager (OEM) Database Control (или Grid Control). Попасть на эту страницу можно через Database Control Home Page -> Performance -> Additional Monitoring Links -> Instance Locks (Домашняя страница Database Control -> Производительность -> Дополнительные ссылки мониторинга -> Блокировки экземпляра). На странице Instance Locks (Блокировки экземпляра) отображаются все замки — как блокирующие, так и не блокирующие.Большинство замков, которые вы увидите, безвредны; это стандартные неблокирующие замки, которые Oracle использует для поддержки параллелизма.

Чтобы увидеть замки, которые вызывают соперничество между сеансами в системе, выберите элемент Blocking Sessions (Заблокированные сеансы) из раскрывающегося списка на странице Instance Locks. На странице Blocking Sessions (Заблокированные сеансы) отображаются все сеансы, которые в данный момент блокируют другие сеансы. Перейти непосредственно на страницу Blocking Sessions можно также по маршруту Database Control Home Page -> Performance -> Additional Monitoring Links -> Blocking Sessions (Домашняя страница Database Control -> Производительность -> Дополнительные ссылки мониторинга -> Заблокированные сеансы).

На странице Blocking Sessions отображаются идентификаторы блокирующих и блокируемых сеансов (рис. 1). Блокирующий сеанс можно прервать, выбрав его и щелкнув на кнопке Kill Session (Уничтожит сеанс).

На рис. ниже видно, что пользователь удерживает монопольную блокировку (на определенной строке таблицы , которую можно видеть на рисунке),тем самым блокируя попытки пользователя получить монопольную блокировку той же строки. Блокирующий сеанс идентифицируется значением 1 или выше в столбце (Заблокированные сеансы) на странице Blocking Sessions (см. рис. 1.). Блокируемый сеанс обозначается значением 0.

Узнать в точности состояние ожидания блокирующих и ожидающих сеансов можно на OEM-странице Hang Analysis (Анализ зависаний), которая доступна по маршруту Database Control Home Page -> Performance -> Additional Monitoring Links -> Hang Analysis (Домашняя страница Database Control — Производительность — Дополнительные ссылки мониторинга — Анализ зависаний). На странице Hang Analysis отображается следующая информация:

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

Когда в экземпляре происходит жесткая конкуренция за блокировки, страница Hang Analysis помогает быстрее идентифицировать проблемы, чем запуск сценария SQL, который сам по себе может еще более ухудшить ситуацию.

На заметку!

Взаимоблокировки — не зло

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

Некоторые люди склонны паниковать, когда видят взаимоблокировки. Случалось, что клиенты поднимали проблемы с P1, но после расследования было обнаружено, что через несколько часов у них было несколько взаимоблокировок для хранимой процедуры, которая вызывалась тысячи раз в минуту.

Как объяснялось ранее в этом блоге, SQL Server внутренне обрабатывает и устраняет взаимоблокировки, которые в противном случае привели бы к бесконечной блокировке между двумя или более сеансами.

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

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

Защелки, внутренние и распределенные блокировки

Защелки (latch) — это внутренние механизмы, которые защищают разделяемые структуры данных в SGA. Например, вхождения словаря данных доступны в буфере для многих целей, и защелки контролируют процессы доступа к этим структурам памяти. Структуры данных, которые перечисляют блоки, находящиеся в данный момент в памяти, также часто читаются во время работы экземпляра Oracle, и серверные и фоновые процессы, которые нуждаются в изменении или чтении критичных структур данных вроде этих, должны устанавливать на них очень кратковременные блокировки (именуемые защелками). Реализация защелок, включая спецификацию длительности ожидания их, обычно специфична для операционной системы.

Блокировки словаря данных используются Oracle при каждой модификации объектов словаря. Распределенные защелки представляют собой специализированные механизмы блокировки, используемые в распределенной системе базы данных или среде Oracle Real Application Clusters (RAC). Внутренние блокировки используются Oracle для защиты доступа к таким структурам, как файлы данных, табличные пространства и сегменты отката.

Как найти MS SQL

Первое, что начинает делать пентестер, — это собирать информацию о сервисах, расположенных на сервере жертвы. Самое главное, что нужно знать для поиска Microsoft SQL Server, — номера портов, которые он слушает. А слушает он порты 1433 (TCP) и 1434 (UDP). Чтобы проверить, имеется ли MS SQL на сервере жертвы, необходимо его просканировать. Для этого можно использовать Nmap cо скриптом . Запускаться сканирование будет примерно так:

Ну а результат его выполнения представлен на рис. 1.

Рис. 1.Сканирование MS SQL при помощи Nmap
Другие статьи в выпуске:

Хакер #195. Атаки на Oracle DB

• Содержание выпуска
• Подписка на «Хакер»-60%

Помимо Nmap, есть отличный сканирующий модуль для Метасплоита , позволяющий также определять наличие MS SQL на атакуемом сервере:

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

Рис. 2. Сканирование MS SQL при помощи mssql_ping 

Получение shell’а

В случае если у нас получилось сбрутить учетку , мы можем залогиниться в БД. Далее сценарий прост — включаем хранимую процедуру, позволяющую выполнять команды на уровне операционной системы, и заливаем на сервер Meterpreter shell. Крутые ребята написали для Метасплоита отличный модуль , который автоматизирует этот процесс:

Сессия Meterpreter’a создана, теперь ты имеешь полный доступ. Можешь дампить хеш админа, делать скриншоты, создавать/удалять файлы, включать/выключать мышь или клавиатуру и многое другое. Пожалуй, это самый популярный шелл, который используется при тестах на проникновение. Полный список команд Meterpreter’a можно подсмотреть здесь.

Выводы

1. Если есть возможность использовать Центр Управления Производительностью, лучше использовать его, т.к. он позволит вам сэкономить существенное время при расследовании ожиданий на блокировках.
2. Стоит помнить, что сбор необходимой для анализа информации в нагруженной системе не является бесплатным (с точки зрения затрат памяти, процессорного времени, дисков). Поэтому нужно рассматривать каждый сбор информации как серьезное вмешательство в работу информационной системы, которое может существенно сказаться на качестве работы пользователей. Число таких вмешательств нужно стараться минимизировать. Для этого требуется заранее готовиться и стараться понимать, какие данные должны быть собраны на следующем шаге расследования.
3. Ожидания на блокировках всегда являются проблемой. Если ожидания происходят при работе с одними и теми же данными, что-то не так с построенными бизнес-процессами. Если ожидания происходят, когда пользователи (со своей точки зрения) работают с разными данными, то это всегда ошибки программирования, которые необходимо устранять. Необходимо учитывать, что проблемой являются не только ошибки, но и избыточные впустую потраченные ресурсы в результате отката транзакции с ошибкой и возможного повтора транзакции с самого начала.
4. Длительное выполнение операции с точки зрения пользователя не всегда является следствием ожидания на блокировках, но совершенно точно нужно уметь выявлять и исключать эту составляющую путем исправления найденных проблем.
5. При выполнении тестов по методикам нагрузочного тестирования скорость выполнения операций одним пользователем и в нагрузочном тесте отличается в первую очередь в результате ожиданий. При этом производительность под нагрузкой никогда не будет выше, чем производительность одного пользователя. Ожидания на блокировках (управляемых и транзакционных на уровне СУБД) могут быть только частью всех имеющихся ожиданий.