Алексей Князев a.knyazev@t-sql.ru XEvents и Audit - презентация

1 Алексей Князев XEvents и Audit

2 Содержание Средства мониторинга XEvents Audit Заключение Вопросы? 2

3 SQL Server 2012 Средства аудита в SQL Server 2000 and UP

4 Немного истории 4

5 «А где бабуля…?» Зачем убирать SQL Trace? 5

6 SQL Server 2012 XEvents – расширенные события

7 Extended Events Engine 7

8 Генерация событий 8

9 SSMS ADDIN (SQL Server 2008) 9

10 SQL Server 2012 UI 10

11 Основные команды DDL 11

12 Демонстрация XEvents (Расширенные события)

13 SQL Server 2012 Audit

14 Про аудит… Что нового в SQL Server 2012 в сравнении с SQL Server 2008? Влияние аудита на производительность? Можно ли защитить журнал аудита от DBA? Что произойдет, если аудит не сможет писать события? Что делать, если SQL Server не запускается из-за аудита? Что еще я должен знать про аудит? 14

15 Что изменилось по сравнению с SQL Server 2008? 15

16 Много чего. Аудит в SQL Server стал более гибким и надежным. 16

17 17 Подсистема аудита SQL Server Улучшения Аудит поддерживается во всех редакциях SQL ServerПовышенная устойчивость аудитаПользовательские события аудитаФильтрация событий аудитаT-SQL Стек выполнения

18 18 Аудит поддерживается во всех редакциях Основные возможности аудита – Аудит сервера – Аудит БД только в Enterprise Больше не нужен SQLTrace (!) – В будущих версиях будет удалён Основные преимущества аудита – Производительность – Несколько аудитов для нескольких целей – Управляемость и изоляция – Несколько сценариев реагирования на сбой аудита

19 19 Улучшение устойчивости Было: – В случаи сбоя записи событий аудита они могли потеряться – Использовалось ON_FAILURE = SHUTDOWN Теперь: – Автоматическое восстановление при большинстве сбоев – Добавлено ON_FAILURE = FAIL_OPERATION – Добавлено MAX_FILES option Select… Rollback

20 20 T-SQL Стек выполнения (дополнительная информация в аудите) Audit Log dbo.Get_Speaker dbo.Speaker24PASS exec dbo.Get_Speaker Select Speaker, City from dbo.Speaker24PASS

21 Демонстрация T-SQL Стек выполнения

22 Пользовательские события аудита 22 exec sp_audit_write 1234, 1, @user_defined_info Audit Log

23 Демонстрация Аудит пользовательских событий

24 Запись с фильтрацией 24 CREATE SERVER AUDIT audit_name TO { [ FILE ( [,...n ]) ] | APPLICATION_LOG | SECURITY_LOG } [ WITH ( [,...n ] ) ] [ FILTER = ] } … ::= { [ NOT ] | {( ) } [ { AND | OR } [ NOT ] { | ( ) } ] [,...n ] } Запись в журнал аудита только ключевых событий –Остальные события аудита генерируются, но не записываются Используется XEvents (расширенные события) фильтрация

25 Демонстрация Аудит с фильтром

26 26 Создаёт ли аудит дополнительную нагрузку на сервер БД?

27 27 Да...

28 Производительность Аудита 28 Зависит от: – Рабочей нагрузки – Того, что отслеживаем Сравнение аудита SQL Server с SQL Trace для 5 разных типичных рабочих нагрузок у клиентов... Workload 1Workload 2Workload 3Workload 4Workload 5 11 dbs, ranging from 1.94 MB to MB. 755 tables with average of 2761 rows 1,219,234 stmts executed. 2 dbs ranging from 64 MB to MB 35 tables with average of 49,141 rows 1,633,557 stmts executed 3 dbs ranging from 1.94 MB to MB 154 tables with average of 586 rows, Here is the activity 585,400 stmts executed 1 db at MB 84 tables with average of 144,245 rows 3,435,303 stmts executed. 1 db at MB 152 tables with average of 4,108 rows 296,642 stmts executed.

29 SQL Server Audit vs SQL Trace 29

30 30 Можно ли защитить журнал аудита от DBA? Вечная борьба с SA

31 31 Да!

32 Защита данных аудита 32 Windows Security Log Tamper-proof log (защищённый журнал) DBA не может очистить журнал (если он не Administrator на сервере) System Center Operations Manager пакет Audit Collection Service Копирование журналов аудита в безопасное место Папка или «шара» может быть закрыта от DBA Журналы аудита доступны только на чтение, когда аудит активен Можно обеспечить дополнительную защиту от несанкционированного доступа путем шифрования папки Комбинируйте решения Фиксируйте все попытки изменить аудит в Windows Security Log Все остальные события записывайте в файл

33 Audit Write Failure (Shutdown) 33 Остановка службы События аудита из буфера теряются

34 Audit Write Failure (Continue) 34 Размер буфера аудита Размер буфера аудита варьируется, но составляет около 4 Мб (эквивалент не менее 170 событий, в зависимости от текста инструкции) Сервер блокирует новые события аудита Не оказывает влияние на другие Аудиты Блокировка продолжается пока нет возможности записать в буфер или Аудит не доступен Аудит сессии не происходит Производится запись в журнал ошибок SQL Server Все последующие события вновь пытаются записаться в лог Аудита При каждом новом события Аудит пытается перезапуститься Buffer filled System error

35 Audit Write Failure (Fail Operation) 35 Размер буфера аудита Размер буфера аудита варьируется, но составляет около 4 Мб (эквивалент не менее 170 событий, в зависимости от текста инструкции) Сервер откатывает все события, которые должны попасть в Аудит Не влияет на другие Аудиты Все повторные события откатываются пока Аудит не доступен Все последующие события продолжают осуществлять попытку записи в журнал Аудита Buffer filled

36 36 Что делать, если SQL Server не запускается из-за аудита?

37 37 Потребуется запуск сервера в однопользовательском режиме

38 Запуск после сбоя 38 Шаг 1 Устранить причину сбоя Например очистить диск Шаг 2 Запуск SQL Server в однопользовательском режиме, -m Аудит работает, но режим shutdown-on-failure не активен Администратор аудита изменяет настройки Аудита Шаг 3 Запуск в минимальной конфигурации, -f Аудит отключен, но события DDL продолжают логироваться. Бонус Если используется Fail Operation и AUDIT_ CHANGE_GROUP, используйте DAC подключение События аудита генерируются но не вызывают сбой

39 Демонстрация Использование Аудита совместно с политиками (Policy- Based Management)

40 40 Что еще я должен знать про аудит?

41 41 Всего несколько ключевых вещей.

42 Что ещё вы должны знать про аудит 42 Параметризованные запросы Журналы аудита не сжаты и не зашифрованы Запись в файл быстрее чем в журнал событий Нет аудита выходных рекордсетов

43 Заключение

44 44 Расширенные события и Аудит в SQL Server 2012 претерпели ряд существенных изменений Повысилась управляемость и отказоустойчивость Появились новые возможности (пользовательские события, фильтры, T-SQL стек, политики) Высокая производительность Мониторинг активности администратора и предотвращение фальсификации журналов Аудита SQL Trace уходит в прошлое и уже сейчас необходимо полностью от него отказаться (!!!)

45 Q&A Ваши вопросы

46 Ресурсы Books Online: – Security Enhancements (Database Engine), – SQL Server Audit (Database Engine), Whitepaper: – Auditing in SQL Server 2008, SQL Server Security Forum: – SQL Security Blog: – Наш сайт – Мой блог –