[ главная ]   [ рейтинг статей ]   [ справочник радиолюбителя ]   [ новости мира ИТ ]



Ответов: 0
25-02-12 07:01







   Web - программирование
PHP


ASP






XML



CSS

SSI





   Программирование под ОС











   Web - технологии








   Базы Данных









   Графика






Данные




Базы Данных / MySQL /

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

Небольшая заметка об использовании триггеров в СУБД MySQL. Несмотря на достаточно приличный возраст этой СУБД, поддержка триггеров появилась только в 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.

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

Бойко А. C. aka Alex_B, источник: http://joomlaportal.ru




Комментарии

 Ваш комментарий к данному материалу будет интересен нам и нашим читателям!



Последние статьи: Базы Данных / MySQL /

Близкие контакты третьего вида с Visual Foxpro (или как написать свой провайдер для FoxPro)
12-03-2010   

Многие наверное как и я в свое время задавались интересным вопросом – “А вот как бы задействовать всю силу применяемой в моем проекте СУБД? Не только стандартные SQL запросы, а и скрытые возможности.” Тогда ведь можно будет получать результат найэффективнешими методам... подробнее

Кол. просмотров: общее - 4458 сегодня - 0

Oracle: Изучаем метки доступа к строкам: задание свойств столбца доступа в таблице
07-03-2010   

Эта статья рассматривает некоторые особенности средства label security в oracle. Здесь показана возможность секретить служебный столбец с метками доступа к строкам, а также рассмотрены некоторые правила правки меток. В первую очередь статья затрагивает использование параметра table_options процедуры apply_table_policy из пакета sa_policy_admin... подробнее

Кол. просмотров: общее - 3250 сегодня - 0

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

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

Кол. просмотров: общее - 3153 сегодня - 0

Настройки mysql-server сразу после установки
07-03-2010   

Мой любимый вопрос, задаваемый DBA, которые хотят увеличить производительность MySQL: “какие параметры надо настраивать в первую очередь, сразу после установки сервера?”... подробнее

Кол. просмотров: общее - 2939 сегодня - 0

10+ способов обрушить mysql-сервер
07-03-2010   

Иногда у меня спрашивают о ошибках MySQL, (например таких), которые могут привести к обрушиванию mysql-сервера пользователем с обычными привелегиями. Потом звучит вопрос: “Что же делать в таких случаях? Как защититься от подобных ситуаций?”... подробнее

Кол. просмотров: общее - 8313 сегодня - 2



  WWW.COMPROG.RU - 2009-2012 | Designed and Powered by Zaipov Renat | Projects