О двух непонятностях в аудите
Я опубликовал эту заметку еще в мае, но она вскорости незаметно и бесследно исчезла. На мой недоуменный вопрос последовали клятвенные заверения, что это происходит миграция блога на новый движок и что не пройдет неделя, как все всенепременно восстановится. С тех пор миновал почти месяц – и тишина вдоль дорог. Я тем не менее рискну еще разок повольнодумствовать на тему аудита в SQL Server, и, если опять выкосят, по крайней мере будет понятно, что за миграцию блоговского движка у нас теперь взялись основательно.
Данный пост служит продолжением январского примера, в котором разбиралось, как средствами аудита отлавливать различные действа на уровне SQL Server, так и внутри отдельной базы вплоть до конкретных операторов SQL против заданных объектов. Однако ситуация, когда в глухую полночь на базу проникает злоумышленник, который начинает слать по ней разные вредительские ad-hoc запросы, является не то, чтобы совсем несбыточной, но достаточно экзотичной при условии нормального администрирования. Гораздо более часто на практике калечить базу принимается кто-то из своих по причине криворучия. Естественно, он не будет открывать SSMS и выполнять оттуда в явном виде DELETE, если только не окончательно съехал с крыши от регулярного сверхурочного бдения. Вред произойдет из недр бизнес-логики приложения, погребенный под спудом хранимых процедур, функций и триггеров, и его будет очень трудно отловить как раз по той причине, что его никто не хотел причинять специально. Просто в бизнес-логике кто-то что-то не додумал и там в один прекрасный момент это что-то не срослось. Наша задача – при помощи аудита определить кривой фрагмент кода, по нему – автора и выразить ему свое ай-яй-яй.
Пусть имеем таблицу
usetempdb
if object_id('tbl', 'U') is not null drop tabletbl
create table tbl (id int identity, fld nvarchar(50) default 'aaa')
insert tbl values (default), (default), (default)
Скрипт 1
и криворукую процедуру, которая при недодуманном стечении параметров начинает удалять записи из этой таблицы
if object_id('prc', 'P') is not null drop procprc
go
create proc prc @prm int = 0 as delete tbl where @prm =1
go
Скрипт 2
Мы хотим понять, кто повадился удалять записи из таблицы tbl, поэтому аналогично предыдущему постусоздаем и запускаем аудит:
use master
if exists (select 1 from sys.server_audits where name = 'Test') begin
alter server audit Test with (state = off);
drop serveraudit Test
end
create serveraudit Test
to file (filepath = 'c:\Temp', maxsize = 2MB, max_rollover_files = 3, reserve_disk_space = off)
with (queue_delay = 1000, on_failure = continue)
usetempdb
if exists (select * from sys.database_audit_specifications where name = N'Test') begin
alter database audit specification Test with (state = off)
drop databaseaudit specification Test
end
create database audit specification Test for serveraudit Test
add (DELETE on OBJECT::dbo.tbl by public)
with (state = on)
use master
alter server audit Test with (state = on)
Скрипт 3
Производим крамольные действия:
use tempdb
execprc
exec prc @prm = 1
Скрипт 4
И смотрим результаты аудита:
select top 2 * from sys.fn_get_audit_file('c:\Temp\Test_*', default, default) order by event_time desc
Скрипт 5
В результатах аудита мы видим ту самую таблицу tbl (object_name) и тот самый оператор delete tbl where @prm = 1 (statement), мы видим, что он succeeded, мы вообще много чего видим: и session_id, и event_time, и database_principal_id вместе с server_principal_id и т.д. Мы не видим главного: 1) значения параметра, т.е. в каком из двух случаев выполнение оператора реально повлекло за собой удаление записей, и 2) из какой процедуры был вызван этот оператор. А если у меня в базе их тысячи (процедур)? Что теперь, шариться по исходникам и смотреть, в код какой процедуры входит этот оператор? Он может присутствовать не в одной. Надо понять, откуда конкретно он выстрелил в данный момент. Хорошо, давайте в определение БД-аудита (Скрипт 3) добавим отлов вызова любой процедуры.
alter database audit specification Test with (state = off)
alter database audit specification Test for serveraudit Test
add (EXECUTE on SCHEMA::dbo by public)
alter database audit specification Test with (state = on)
Скрипт 6
Повторяем Скрипты 4, 5.
Мы видим, что за каждым оператором DELETE следует вызов хранимой процедуры, откуда он выполнялся. Но, во-первых, где гарантия того, что так будет всегда, и, во-вторых, чтобы определить, из какой процедуры был выполнен тот или иной оператор, в спецификацию аудита требуется включать вызовы всех хранимых процедур, что на нагруженных системах может быть тяжко. А как быть, если эта процедура вообще из другой базы? Вопрос: не знает ли кто более простого способа, как определить в аудите, из какого модуля (хранимая процедура, функция, триггер) был выполнен данный оператор? Второй вопрос – как определить набор значений параметров, с которыми состоялся конкретный вызов процедуры? В графе statement тупо отражается оператор exec, который ее позвал. А если в нем нет явного указания значений параметров? Например, вызов происходит со значениями по умолчанию. По-хорошему, в результатах аудита по-любому должны присутствовать фактические значения параметров, имевшие место в данном вызове.
Алексей Шуленин