audit trail triggers

[Sabina]

Вопрос к тем, кто имеет опыт написания триггеров для создания системы аудита. То есть, грубо говоря, пишутся триггеры для почти всех юзерских таблиц базы данных, которые в свою очередь заполняют таблицы данных для аудита. И на основании audit tables можно печатать всякие отчеты - кто что изменил и когда и проч.

Не знаю насколько это нормально, но когда дизайнилась база под наше приложение, аудит во внимание явно не брался.
В результате теперь для каждого типа отчета нужно писать user function на пару страниц. При этом каждая user function представляет собой union 2-3-4 select-ов, каждый содержащий до 3-4 joint-ов.

Пару раз заходили в тупик и единственным решением было поменять db schema.

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

Спасибо,
Сабина

[Dmitry67]

Sabina, а если такое 'отложенное' решение:

Для каждой таблицы A делаем таблицу A_log, где есть еще колонка
datetime default getdate(),
oper char(1) -- ('I', 'U', 'D')
spid default @@spid -- отдельная тема как определяются пользователи
Заполняется напрямую от триггера который будет очень простым

Сам же аудит лог заполняется косвенно из этой таблицы, и уже там все joins и делаются чуть позже

[Sabina]

Sabina, а если такое 'отложенное' решение:

"Отложенное" в смысле "общепринятое", "как делается в большинстве случаев"?

Для каждой таблицы A делаем таблицу A_log, где есть еще колонка
datetime default getdate(),
oper char(1) -- ('I', 'U', 'D')
spid default @@spid -- отдельная тема как определяются пользователи
Заполняется напрямую от триггера который будет очень простым

Сам же аудит лог заполняется косвенно из этой таблицы, и уже там все joins и делаются чуть позже

Я попыталась представить такой расклад и, честно говоря, пока преимущества не вижу. Но может я просто не все учла...

Получается, что вместо заполнения двух общих таблиц aud-tracking и aud_tracking_details я буду заполнять A-Z_log таблицы. Сами триггеры в обоих случаях достаточно просты, ну по крайней мере одинаковой сложности.

Затем для составления query для веб сервиса, мне нужно будет создавать user functions, которые вместо опроса таблиц aud-tracking и aud_tracking_details будут содержать селекты для A-Z_log таблиц.

Joint-ами приходится пользоваться, чтобы вытащить нехватающие в aud-tracking и aud_tracking_details данные(скажем полное имя вместо id, или work order number по id проекта и проч.).
Но это и в случае A-Z_log таблиц так будет.

Union select-ов приходится делать, потому что каждый лог включает в себя до 3-4 разных типов данных и соответственно селекты для них немного отличаются.
То есть я это все к тому, что пока я не вижу преимущества такого подхода.

Если вы по-прежнему считаете, что A-Z_log подход признан наиболее правильным для большинства ситуаций, я попробую его реализовать на кусочке своей test db, чтобы посмотреть как это работает. Ведь зачастую многое проясняется по ходу ...

Сабина

[strvt]

Не знаю насколько это нормально, но когда дизайнилась база под наше приложение, аудит во внимание явно не брался.

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

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

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

[Sabina]

Системы аудита как правило являются компромисом между:
- лёгкостью и прозрачностью ведения аудитной информации
- лёгкостью и прозрачностью обработки и презентации аудитной информации

У нас получается так
1)ведение аудитной информации - относительно легко, триггеров много, но они однотипные, короче не 2х2, но достаточно просто.
2) обработка информации - тройной геморрой, такие uf пишу, что самой на них потом смотреть страшно, но зато
3) презентация информации - легче легкого, мы пользуемся JFreeReport и с помощью него, я получаю то, что мне product manager на бумажке рисует почти один в один.

Сложность пункта 2 пугает меня еще и тем, что при наличии больших объемов данных, сервер просто откажется это переваривать. А может я преувеличиваю? Но мне сравнивать не с чем к сожалению. Потому и спрашиваю.

Приведу для примера хотя бы одну из user functions:

Code: Select all

IF EXISTS (SELECT * 
     FROM   sysobjects 
     WHERE  name = N'uf_audEqpData')
  DROP FUNCTION uf_audEqpData
GO

CREATE FUNCTION dbo.uf_audEqpData 
  (@equipmentID    INTEGER)
RETURNS TABLE 
AS
  RETURN 
  (SELECT ('Chassis,'+ t.tablename) AS 'level', atd.fullFieldName, atd.oldValue, atd.newValue, 
   u.loginID AS userID, t.workOrderNum, 
  CONVERT(NCHAR(19), t.updatedTs, 120) AS updatedTs
  FROM AUD_tracking t (nolock)
        INNER JOIN AUD_trackingDetail atd (nolock)
  ON t.trackingID = atd.trackingID
        INNER JOIN User u (nolock)
  ON t.userID = u.userID
        WHERE t.objectID = @equipmentID AND t.trackingID IS NOT NULL
   AND t.tableName IN('EqpType1','EquipmentGeneral','MaintenanceAgreement')) 
         
UNION

(SELECT ('Slot ' + es.slotNum + ',' + t.tableName) AS 'level', atd.fullFieldName, atd.oldValue, 
   atd.newValue, u.loginID AS userID, t.workOrderNum, 
CONVERT(NCHAR(19), t.updatedTs, 120) AS updatedTs
  FROM AUD_tracking t (nolock)
        INNER JOIN AUD_trackingDetail atd (nolock)
  ON t.trackingID = atd.trackingID
        INNER JOIN EqpSlot es (nolock)
  ON es.slotID = t.objectID
        INNER JOIN User u (nolock)
  ON t.userID = u.userID
        WHERE t.objectID IN (SELECT slotID FROM EqpSlot WHERE eqpID=@equipmentID)
        AND t.tableName='EqpSlot') 

UNION

  (SELECT ('Slot ' + es.slotNum + ',Port ' + ep.portNum + ',' + t.tableName) AS 'level', atd.fullFieldName, atd.oldValue, 
        atd.newValue, u.loginID AS userID, t.workOrderNum, 
CONVERT(NCHAR(19), t.updatedTs, 120) AS updatedTs 
  FROM AUD_tracking t (nolock)
        INNER JOIN AUD_trackingDetail atd (nolock)
  ON t.trackingID = atd.trackingID
        INNER JOIN EqpPort rp (nolock)
  ON ep.portID = t.objectID
        INNER JOIN EqpSlot rs (nolock)
  ON es.slotID = ep.slotID
        INNER JOIN User wntu (nolock)
  ON t.userID = u.userID
        WHERE t.objectID IN (SELECT portID FROM EqpPort WHERE eqpID=@equipmentID)
        AND t.tableName IN('EqpPort','ATM','AppleTalk','BSTUN','DECNet','DLSW','Ethernet',
        'EthernetBridge','FrameRelay','HSRP','IP','IPX','ISDN','SDLC','SourceRouterBridge','VLAN','AppleTalkZone'))

Looks scary, isn't it ?

Сабина

[Dmitry67]

"Отложенное" в смысле "общепринятое", "как делается в большинстве случаев"?

Я попыталась представить такой расклад и, честно говоря, пока преимущества не вижу. Но может я просто не все учла...

Получается, что вместо заполнения двух общих таблиц aud-tracking и aud_tracking_details я буду заполнять A-Z_log таблицы. Сами триггеры в обоих случаях достаточно просты, ну по крайней мере одинаковой сложности.

(*)
Затем для составления query для веб сервиса, мне нужно будет создавать user functions, которые вместо опроса таблиц aud-tracking и aud_tracking_details будут содержать селекты для A-Z_log таблиц.

Отложенное в смысле 'delayed processing'
Я нечетко объяснил
Есть три этапа

1 Примитивные триггеры которые отрабатывают быстро. Результат попадает в A-Z_log таблицы
2 background process (scheduled job, or some sp trigerred by some event), который и делает всю сложную обработку и переводит информацию из A_Zlog таблиц в вашу настоящие таблицы аудита. Обработку можно вести 'от последней необработанной точки'. Причем что хорошо, все Join, которые были в тригере выносятся из OLTP раз, и два что хорошо, в процессы делаются не много раз для одной двух измененных записей, а сразу для пачки изменений
3 Ваши остальные процессы не изменяются

А ваша функция действительно looks scary Может добавить полей в аудит таблицы но не делать этих joins каждый раз ?


2

[Sabina]

Есть три этапа
1 Примитивные триггеры которые отрабатывают быстро. Результат попадает в A-Z_log таблицы
2 background process (scheduled job, or some sp trigerred by some event), который и делает всю сложную обработку и переводит информацию из A_Zlog таблиц в вашу настоящие таблицы аудита...

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

А ваша функция действительно looks scary Может добавить полей в аудит таблицы но не делать этих joins каждый раз ?

Получается дублей данных многовато. А вообще так и сделаю, если "жареным запахнет".

Сабина

[Dmitry67]

Я бы добавил поля и убрал joins еще вот по какой причине
Аудит показывает ИСТОРИЮ
Он не должен меняться
А в вашем случае... Что будет например при просмотре старой истории если например таблицу user кото изменял или хуже удалял записи ?

Кстати, теперь это все еще надо будет на DB2 перетаскивать ?
Merry Xmas

[Митяй]

В Informix есть встроенный аудит. А что, в SQL server нет ?

[tengiz]

В Informix есть встроенный аудит. А что, в SQL server нет ?

В MS SQL Server есть встроенный security audit, базирующийся в том числе на сервисах, предоставляемых операционной системой. Предназначен для подробного протоколирования всех событий в системе так или иначе связанных с безопасностью. Речь же в данной теме идёт об аудите на уровне приложения который настроенный для нужд конкретного приложения, что есть совершенно другое. Стандартное средство MS SQL которое позволяет это делать ценой эффективности - очень подробный журнал (степень подробности настраивается до мельчайших деталей) всех действий, которые были произведены на сервере, который можно сохранять либо в виде файла, либо в виде таблицы для последующего анализа. Если Informix позволяет делать толковый, эффективный и гибкий аудит для приложений, заметно отличающийся от того, что сделано в MS SQL - то это круто.

[Sabina]

Речь же в данной теме идёт об аудите на уровне приложения который настроенный для нужд конкретного приложения, что есть совершенно другое. ... Если Informix позволяет делать толковый, эффективный и гибкий аудит для приложений, заметно отличающийся от того, что сделано в MS SQL - то это круто.

Я не думаю, что для того аудита (на уровне приложения!), который я пишу подошли бы какие-то системные заранее настроенные логи. Тут вся сложность заключена в том какие таблицы/аттрибуты отобрать для данного коткретного audit trail. Трудно представить как это можно автоматизировать? Разьве что с самого начала ввести кучу служебных полей в каждой таблице для дальнейшей выборки.

Cабина

[Sabina]

А в вашем случае... Что будет, например, при просмотре старой истории если таблицу user кото изменял или хуже удалял записи ?

Cогласна. Подниму вопрос, захотят - переделаем. Таблицы аудита были нарисованы до меня и были переданы мне как факт, так что я и не вникала насколько они эффективны, пока не начала с ними работать.

Кстати, теперь это все еще надо будет на DB2 перетаскивать ?

За триггеры я не сильно переживаю. Данных пока мало, скорее всего попросят какую-нибудь тестовую базу перетащить, посмотреть как оно вообще со всем остальным будет работать. А если ИБМ это все понравится, значит будут деньги, то есть наймут full-time ДБА, "матерого" типа вас или Тенгиза , он все сделает по уму, а я буду у него учится

Cабина

[Sabina]

А в вашем случае... Что будет например при просмотре старой истории если например таблицу user кото изменял или хуже удалял записи ?

Вообще-то на самом деле ничего страшного не будет
Если loginID поменялся, то в report-е увидят новое имя. Ну а про delete - нечего удалять из юзеров .

Cабина