Аудит триггером составной транзакции

← →
Гость (2010-02-03 10:40) [0]

Доброго дня!
Есть программа, которая выполняет в одной транзакции delete, insert, update. Есть триггер:

create or replace trigger SAMPLE_AUD
before insert or delete or update on SAMPLE_TABLE
for each row

, задача которого отловить все изменения и записать в таблицу аудита. Проблема в том, что отлавливается только insert. Можно ли сделать так, чтобы триггер отрабатывал на каждую команду в транзакции?

← →
Sergey13 © (2010-02-03 10:49) [1]

А все действия на одной таблице производятся?

← →
Гость (2010-02-03 10:50) [2]

да

← →
12 © (2010-02-03 10:51) [3]

должно работать..
А что за версия?

на моей, покоцанной/бесплатной работает и инсерт и апдейт

> create or replace trigger SAMPLE_AUD
> before insert or delete or update on SAMPLE_TABLE
> for each row

BEGIN
INSERT INTO audit VALUES (:old.name, :new.name)
END

← →
Sergey13 © (2010-02-03 10:51) [4]

> [2] Гость (03.02.10 10:50)

А можно сам тригер увидеть?

← →
Гость (2010-02-03 10:56) [5]

create or replace trigger INASTRBUD_AUD before insert or delete or update on INASTRBUD for each row declare New_Idaud xx_audit.INASTRBUD_AUD.IDAUD%TYPE; begin SELECT SEQ_INASTRBUD_AUD.NEXTVAL INTO New_Idaud FROM Dual; IF Updating THEN IF :old.IDNTYPBUDG <> :new.IDNTYPBUDG OR :old.IDNBACC <> :new.IDNBACC OR :old.IDNBUDGET <> :new.IDNBUDGET OR :old.IDINASTRBUD <> :new.IDINASTRBUD THEN INSERT INTO xx_audit.INASTRBUD_AUD (IDNTYPBUDG, IDNBACC, IDNBUDGET, IDINASTRBUD, IDAUD, DML_TYPE, DML_TYPEU, TERMINAL, OS_USER, IP_ADRES, SESSION_USER) VALUES (:old.IDNTYPBUDG, :old.IDNBACC, :old.IDNBUDGET, :old.IDINASTRBUD, New_Idaud, "U", "0", TRIM(Sys_Context("userenv", "TERMINAL")), TRIM(Sys_Context("userenv", "OS_USER")), TRIM(Sys_Context("userenv", "IP_ADDRESS")), TRIM(Sys_Context("userenv", "SESSION_USER"))); INSERT INTO xx_audit.INASTRBUD_AUD (IDNTYPBUDG, IDNBACC, IDNBUDGET, IDINASTRBUD, IDAUD, DML_TYPE, DML_TYPEU, TERMINAL, OS_USER, IP_ADRES, SESSION_USER) VALUES (:new.IDNTYPBUDG, :new.IDNBACC, :new.IDNBUDGET, :new.IDINASTRBUD, New_Idaud, "U", "1", TRIM(Sys_Context("userenv", "TERMINAL")), TRIM(Sys_Context("userenv", "OS_USER")), TRIM(Sys_Context("userenv", "IP_ADDRESS")), TRIM(Sys_Context("userenv", "SESSION_USER"))); END IF; ELSIF Inserting THEN INSERT INTO xx_audit.INASTRBUD_AUD (IDNTYPBUDG, IDNBACC, IDNBUDGET, IDINASTRBUD, IDAUD, DML_TYPE, DML_TYPEU, TERMINAL, OS_USER, IP_ADRES, SESSION_USER) VALUES (:new.IDNTYPBUDG, :new.IDNBACC, :new.IDNBUDGET, :new.IDINASTRBUD, New_Idaud, "I", "", TRIM(Sys_Context("userenv", "TERMINAL")), TRIM(Sys_Context("userenv", "OS_USER")), TRIM(Sys_Context("userenv", "IP_ADDRESS")), TRIM(Sys_Context("userenv", "SESSION_USER"))); ELSE INSERT INTO xx_audit.INASTRBUD_AUD (IDNTYPBUDG, IDNBACC, IDNBUDGET, IDINASTRBUD, IDAUD, DML_TYPE, DML_TYPEU, TERMINAL, OS_USER, IP_ADRES, SESSION_USER) VALUES (:old.IDNTYPBUDG, :old.IDNBACC, :old.IDNBUDGET, :old.IDINASTRBUD, New_Idaud, "D", "", TRIM(Sys_Context("userenv", "TERMINAL")), TRIM(Sys_Context("userenv", "OS_USER")), TRIM(Sys_Context("userenv", "IP_ADDRESS")), TRIM(Sys_Context("userenv", "SESSION_USER"))); END IF; end INASTRBUD_AUD;

← →
Sergey13 © (2010-02-03 11:10) [6]

> [5] Гость (03.02.10 10:56)
на первый взгляд вроде все логично.
А если сделать три отдельных тригера на каждое действие?

← →
Гость (2010-02-03 11:12) [7]

пробовал, отрабатывает один, по последней команде

← →
Кщд (2010-02-03 13:33) [8]

триггеры аудита обычно вешают на after
а конкретно по теме: тест-кейс из sqlplus в студию, пожалуйста

← →
Гость (2010-02-03 15:24) [9]

Спасибо, все решилось!

Дело было в ошибке сравнения с NULL. Надо так:

IF Updating THEN IF nvl(:old.IDNTYPBUDG, 0) <> nvl(:new.IDNTYPBUDG, 0) OR nvl(:old.IDNBACC, 0) <> nvl(:new.IDNBACC, 0) OR nvl(:old.IDNBUDGET, 0) <> nvl(:new.IDNBUDGET, 0) OR nvl(:old.IDINASTRBUD, 0) <> nvl(:new.IDINASTRBUD, 0) THEN

← →
Sergey13 © (2010-02-03 15:42) [10]

> [9] Гость (03.02.10 15:24)

А как это влияло на НЕсрабатывании на удаление? Или ты не удалял?

← →
Ega23 © (2010-02-03 15:55) [11]

А как в Oracle Update работает?
У меня складывается глубокое убеждение, что в MSSQL он "в кишках" как delete-insert работает.

← →
Anatoly Podgoretsky © (2010-02-03 16:01) [12]

> Ega23 (03.02.2010 15:55:11) [11]

Update по сути такой и есть, а внутреннея реализация не интересна.

← →
Кщд (2010-02-03 18:33) [13]

Гость (03.02.10 15:24) [9]
null = 0
с такой логикой далеко не уедешь
также, не ясно, зачем при update плодить две записи и почему в логе нет timestamp?
допустим, двойной вставкой при update страхуетесь от того, что два пользователя одновременно редактируют запись(а Вам, видимо, хочется видеть что менял пользователь и на что, на случай lost update), но как Вы собираетесь определять очередность изменений при таком подходе - совершенно непонятно
trim, опять же, где нужно и где не нужно

и принято сообщать, СУБД и версию
удачи)

← →
ANB (2010-02-04 12:30) [14]

> null = 0
> с такой логикой далеко не уедешь

Да ничего страшного вообще то

> также, не ясно, зачем при update плодить две записи

Вполне нормально. Старое и новое значение.

> но как Вы собираетесь определять очередность изменений при
> таком подходе

SELECT SEQ_INASTRBUD_AUD.NEXTVAL INTO New_Idaud FROM Dual;

Короче, вполне себе так нормальный триггер. Я бы еще поле для sysdate добавил - не помешает.

И стандартная ошибка при сравнении (забыли про null). Я так тоже люблю косячить. Правда, в последнее время уже очень редко.

← →
Sergey13 © (2010-02-04 13:14) [15]

Насчет аудита я скажу так. Я бы трижды подумал на тему - а нужен ли он такой. Пробовал несколько раз делать такое - очень скоро аудитные таблицы начинают превосходить исходные по объему на порядки. При этом используется он ОЧЕНЬ (обычно) редко и не дает особо ничего. Ну нашли виновника, и что? "Ну ошибся я, с кем не бывает". И ВСЕ. Я бы просто добавил логин юзера и дату последней модификации записи в основную таблицу. В 99% случаев этогшо достаточно.

Разумеется в некоторых системах аудит необходим даже по закону, но обычно люди пишущие его не задают вопросов на форумах.

ИМХО

ЗЫ: Я так и не понял как влияло некорректное сравнение с нулом на секцию тригера про удаление.

← →
Sergey13 © (2010-02-04 13:20) [16]

Плюсом про аудит.
В Оракле (насколько я понял это он) вроде как есть штатное средство по работе со штатными логами. Log Miner вроде называется. Я с ним не разбирался, но вроде как по отзывам вполне работоспособен. А логах оракла есть все.

← →
Кщд (2010-02-04 17:25) [17]

>ANB (04.02.10 12:30) [14]
>Да ничего страшного вообще то
0!=null - логическая ошибка
т.о. при изменении 0 на null и обратно, изменение не будет учтено

>Вполне нормально. Старое и новое значение.
вопрос был "зачем"
если бездумно плодить данные - нормально, то остается только умыть руки

>SELECT SEQ_INASTRBUD_AUD.NEXTVAL INTO New_Idaud FROM Dual;
т.е. если ID2 > ID1, то изменения в ID2 - более поздние, так?

>Короче, вполне себе так нормальный триггер. Я бы еще поле для sysdate добавил - не помешает.
timestamp
и, желательно, с local time zone
впрочем, это уже излишество, но весьма полезное время от времени

← →
Кщд (2010-02-04 17:29) [18]

>Sergey13 © (04.02.10 13:20) [16]
logminer пишет IP пользователя?)

а если серьезно, то, например, на простенькой, некритичной базке с 3-5 таблиц никто не будет поднимать архивлоги - это просто ни к чему

да и с помощью sql работать куда как приятнее - опять же можно в софтину клиентскую привязать показ изменений

← →
Кщд (2010-02-04 20:49) [19]

>Sergey13 © (04.02.10 13:14) [15]
перечитал Ваш пост внимательно)

>Разумеется в некоторых системах аудит необходим даже по закону, но >обычно люди пишущие его не задают вопросов на форумах.
ещё как задают))
здесь ещё немного кода и специфичных названий полей/таблиц - есть примеры, когда выкладывают код в несколько десятков килобайт...
только когда(и если) работодатель видит потроха базы и кода в форуме - ни к чему хорошему для вопрошавшего это не приводит

← →
Игорь Шевченко © (2010-02-05 00:55) [20]

RTFM Кулибиным:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/audit.htm
http://www.oracle-base.com/articles/8i/Auditing.php
http://www.citforum.ru/database/oracle/audit/

← →
Кщд (2010-02-05 05:55) [21]

>Игорь Шевченко © (05.02.10 00:55) [20]
особенно хорош и гибок - FGA
никто не спорит, да и пользовать его в той же 10-ке - очень удобно
вот только в обычной такой системе, где авторизация/аутентификация самописные(т.е. коннект происходит под одним пользователем Oracle), система разграничение прав своя и все изменения(DML) должны быть увязаны с OPERATION_LOG.ID(т.е. выполненной пользователем операции (или, например, идентификатором web-сессии), - все эти внутренние и, безусловно, эффективные инструменты несколько не к месту
другими словами, это, скорее, инструментарий dba, а не разработчика

← →
Sergey13 © (2010-02-05 09:51) [22]

> [18] Кщд (04.02.10 17:29)
> некритичной базке с 3-5 таблиц никто не будет поднимать
> архивлоги - это просто ни к чему

Для "некритичной базки" ни к чему юзать Оракл (хотя конечно не возбраняется 8-), и уж тем более прикручивать аудит. ИМХО.

← →
ANB (2010-02-05 10:05) [23]

> Для "некритичной базки" ни к чему юзать Оракл

А что юзать то ?
Оракл :
1) халявый
2) ставится за 5 минут задавая 3 вопроса
3) удобный

← →
Sergey13 © (2010-02-05 10:30) [24]

> [23] ANB (05.02.10 10:05)

Он просто тупо большой и достаточно сложный если в него смотреть.
Дистрибутив ФБ весит 2.5 метра и сервер и клиент. Сколько весит Оракл?

← →
ANB (2010-02-05 11:16) [25]

> Сколько весит Оракл?

212 метров.

← →
Sergey13 © (2010-02-05 11:23) [26]

> [25] ANB (05.02.10 11:16)

Ну вот и сравнивай почему

> [22] Sergey13 © (05.02.10 09:51)
> Для "некритичной базки" ни к чему юзать Оракл

← →
Кщд (2010-02-05 12:58) [27]

>Он просто тупо большой и достаточно сложный если в него смотреть.
>Дистрибутив ФБ весит 2.5 метра и сервер и клиент. Сколько весит Оракл?
зачем FB, когда нажав несколько раз на кнопку next получу нормальную поддержку xml, аналитические ф-ции, хранимый код на java, пользовательские типы, поддержку пользовательских типов, возможность работать с гео-объектами и др. и пр.?)

← →
Кщд (2010-02-05 13:08) [28]

>Sergey13 © (05.02.10 09:51) [22]
>Для "некритичной базки" ни к чему юзать Оракл (хотя конечно не >возбраняется 8-), и уж тем более прикручивать аудит. ИМХО.
уже писал, что с результатом работы logminer работать гораздо неудобнее, чем с sql-запросами, не говоря уже об объемах архивлогов...
да и разные это вещи - принципиально: пользователь хочет видеть, кто и когда выполнял операцию "перевод средств на счета доходов со счетов будущих доходов", а вовсе не изменения в 10-ти таблицах, которые были вызваны выполнением этой операции
также, администратор системы желает знать, какой пользователь системы выполнил эту операцию, а совсем не под каким пользователем Oracle она была выполнена

> [27] Кщд (05.02.10 12:58)

А это нужно для

> [18] Кщд (04.02.10 17:29)
> некритичной базке с 3-5 таблиц

?

ЗЫ: Не надо убеждать меня в "классности" Оракла. 8-)

Кщд (05.02.10 13:08) [28]

> пользователь хочет видеть, кто и когда выполнял операцию
> "перевод средств на счета доходов со счетов будущих доходов",
> а вовсе не изменения в 10-ти таблицах, которые были вызваны
> выполнением этой операции
> также, администратор системы желает знать, какой пользователь
> системы выполнил эту операцию, а совсем не под каким пользователем
> Oracle она была выполнена

Сдается мне, что это автоматизация бардака

> [28] Кщд (05.02.10 13:08)

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

У тебя то "некритичной базке с 3-5 таблиц" то "изменения в 10-ти таблицах, которые были вызваны выполнением этой операции".
Ты уж как нибудь определись в своих предположениях. 8-)

← →
Кщд (2010-02-05 13:44) [32]

>Sergey13 © (05.02.10 13:09) [29]
>А это нужно для?
да, простейшая система онлайн-мониторинга автотранспорта
для хранения пользуем georaster, для обмена с внешними клиентскими местами - xml
в случае падения базы, в течение 20мин поднимается из вчерашнего бэкапа и прокатывается оперативный лог за текущие сутки
т.о. 20-25мин и база снова в бою

← →
Кщд (2010-02-05 13:49) [33]

>Игорь Шевченко © (05.02.10 13:22) [30]
как угодно называйте
типовая банковская система
операция движения по счету может породить десятки других операций, соответственно, изменив не один десяток таблиц
теперь представьте, какую кашу выдаст FGA и уж тем более logminer в этом случае
а что хочет увидеть администратор системы? конечно, кто и когда запустил бизнес-операцию, а вовсе не кашу из изменений в массе таблиц

← →
Кщд (2010-02-05 13:54) [34]

>Sergey13 © (05.02.10 13:35) [31]
"некритичной базке с 3-5 таблиц" - было сказано к тому, что нецелесообразно поднимать архивлоги в некоторых случаях

"изменения в 10-ти таблицах, которые были вызваны выполнением этой операции" - этот тезис относился к необходимости в целях аудита логировать бизнес-операцию и связывать с ней конкретные изменения в таблицах, а не логировать бессвязные dml-изменения

> [32] Кщд (05.02.10 13:44)
> да, простейшая система онлайн-мониторинга автотранспорта
> [33] Кщд (05.02.10 13:49)
> типовая банковская система

И тут Остапа понесло. (с)
8-)

Кщд (05.02.10 13:49) [33]

> а что хочет увидеть администратор системы? конечно, кто
> и когда запустил бизнес-операцию, а вовсе не кашу из изменений
> в массе таблиц

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

← →
Кщд (2010-02-05 14:32) [37]

>Игорь Шевченко © (05.02.10 14:09) [36]
в случае, если реализована система "запрещено всё то, что не разрешено", пользователь физически не сможет вызвать операцию, которой у него нет)
речь сейчас о самописной системе разграничения прав, а не о средствах Oracle
создание такой системы оправдано, например, тем, что никто не будет создавать пользователя Oracle для каждого посетителя/зарегистрированного пользователя сайта

и ещё: полезность и эффективность внутреннего аудита средствами Oracle не ставил под сомнение

← →
Кщд (2010-02-05 14:33) [38]

> речь сейчас о самописной системе разграничения прав

Том Кайт - рулез фарева

← →
ANB (2010-02-12 15:13) [40]

> Том Кайт - рулез фарева

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

Практика - вот рулез.

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

Архив логи же - штука мощная, но не всегда удобная.
1) Они толстые, у нас их чистят каждый день, причем держат максимум 2 дня.
2) Разбираться в них повесишься.
3) Не всегда в них содержиться ВСЯ нужная информация хотя бы для того же логгирования.

И по поводу прав. Типичная ситуация. Есть 5 юзеров, которые отвечают за конкретный интерфейс. Никто другой вломиться туда не может. В какой то момент выясняется, что в системе сидят кривые данные, из-за того, что кто то что не так настроил или удалил. Первым делом начальство спрашивает - кого драть ? Тут простенькая таблица нам этого кого то вычисляет в течение минуты. Причем со всеми координатами - имя юзера ОС, IP, с какой машины в сети, под каким логином.

Аудит триггером составной транзакции Найти похожие ветки