Простой журнал аудита на триггерах MySQL

Небольшая заметка об использовании триггеров в СУБД MySQL.

🕛 20.04.2010, 22:47

Несмотря на достаточно приличный возраст этой СУБД, поддержка триггеров появилась только в 5-й версии и достаточно мало описана на русском языке.

Введение

Три́ггер (англ. trigger) - это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) - по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификации UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан.

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

Триггер активируется, когда происходит определенное событие для конкретной таблицы. Таких события все три:
1. INSERT 2. UPDATE 3. DELETE

Поддержка для триггеров включена начиная с MySQL 5.0.2.
Примеры данной статьи выполнялись на MySQL 5.0.18.
Постановка задачи

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

Например, у вас база данных магазина. Одному пользователю можно разрешить только просматривать наличие того или иного товара, другой может редактировать количество оставшихся копий, а третий может изменять стоимость. Более подробную информацию о пользователях вы можете получить на узле MySQL из статьи <MySQL User Account Management> или на русском.

Предположим, вы так и поступили и у нас имеется несколько пользователей. Само по себе разделение прав и обязанностей уже большой шаг в сторону повышения безопасности. Однако этого явно не достаточно - если товар стоящий 100 у.е. вдруг будет продан за 10, как найти виновного? Кто изменил стоимость в БД?

Или вдруг обнаружится, что закуплено было 10 экземпляров, по факту продано 5, а в остатке числится только 3. Куда делись еще 2? Кто списал товар?

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

MySQL предоставляет такую возможность - существует так называемый General Query Log. По всей видимости, там сохраняется даже слишком много информации. Мы напишем свой журнал аудита для одной конкретной таблицы. И, как вы уже наверно догадались, будем использовать триггеры.

Наш журнал аудита это новая таблица в БД. О существовании такой таблицы должно знать как можно меньше людей.

Назовем нашу таблицу mylog. Пусть в ней будет 4 столбца:
| id | user | query | timestamp |

Таблицу легко создать, используя phpMyAdmin** с помощью следующего запроса:

CREATE TABLE `mylog` (

`id` INT NOT NULL AUTO_INCREMENT ,
`user` VARCHAR( 20 ) NOT NULL ,

`query` VARCHAR( 15 ) NOT NULL ,
`timestamp` TIMESTAMP NOT NULL ,

PRIMARY KEY ( `id` )
);

Так же пусть у нас есть таблица table1, за изменениями в которой мы и будем следить.
Каждый раз, при добавлении, изменении или удалении записей таблицы table1 в таблице mylog будет добавляться строка, содержащая имя пользователя MySQL, сам запрос, а также время, в которое он был выполнен.
Создадим простой триггер

Каждое из трех поддерживаемых триггерами событий (INSERT, UPDATE и DELETE) как бы подразделяется еще на два:
1. начало события BEFORE 2. конец события AFTER

Иными словами у нас есть не 3 события:
1. INSERT 2. UPDATE и 3. DELETE

а целых 6:
1. BEFORE INSERT 2. AFTER INSERT 3. BEFORE UPDATE 4. AFTER UPDATE 5. BEFORE DELETE 6. AFTER DELETE

Например, событие BEFORE INSERT произойдет перед вставкой новой строки в таблицу, а событие AFTER INSERT после вставки.
Важно отметить, что событие AFTER INSERT может и не наступить, если во время выполнения запроса произошла ошибка.

На каждое из 6 событий для одной конкретной таблицы можно повесить только один триггер.

Таким образом, мы можем записать в наш журнал аудита (в таблицу mylog) все попытки выполнения (события BEFORE) запросов или только удачные (события AFTER)**.

Давайте записывать все попытки - будем использовать события BEFORE.

Следующим запросом к MySQL мы создадим триггер с именем my_trigger для таблицы table1:

CREATE TRIGGER `my_trigger` BEFORE INSERT ON `table1`
FOR EACH ROW /*ниже следует действие, выполняемое триггером*/ INSERT INTO `mylog` SET `user`= 'имя_пользователя', `query`='insert';

Как видите все просто: триггер вставляет строку в таблицу mylog. Однако такой триггер нужно еще немного подкорректировать - он должен вставлять не текст 'имя_пользователя' в качестве значения для поля user, а реальное имя пользователя.

Что бы узнать реальное имя пользователя воспользуемся одной из встроенных функций MySQL - USER(); Эта функция возвращает строку с именем пользователя MySQL. (Более подробно о функциях MySQL можно узнать на официальном сайте).

Вот так будет выглядеть полностью рабочий триггер:

CREATE TRIGGER `my_trigger` BEFORE INSERT ON `table1`

FOR EACH ROW INSERT INTO `mylog` SET `user`= USER(), `query`='insert';

По аналогии могут быть созданы триггеры для оставшихся событий UPDATE и DELETE.

Надо заметить, что в данном примере, в журнал событий попадает имя пользователя СУБД, однако, при желании туда можно записывать и более подробные данные - например имя пользователя CMS. Ведь в триггере есть информация об изменяемой строке таблицы, а из нее почти всегда можно получить ссылку на пользователя который ее создал/изменил.

Примечание

*При создании более сложных триггеров, если вы пользуетесь phpMyAdmin не старших версий, у вас, скорее всего, возникнут проблемы. Для корректной работы используйте версии старше 2.9.1.

**Вообще то, по-хорошему, журнал аудита должен содержать информацию, как о событии, так и о результате. Однако у нас простой триггер - а значит и простой журнал аудита :)