Когда ваш интерфейс разработан или только разрабатывается с помощью Oracle Application Express, возникает проблема с реализацией аудита пользовательских действий. Конечно, если у вас система небольшая, то можно обойтись реализацией записи в таблицу аудита через Apex. Однако, если у вас в системе множество таблиц, а операции над записями осуществляются в десятках различных мест, фиксацию действий пользователя проще всего вести с помощью триггеров. Триггер — это проверенный механизм, который не будет зависеть от сети и не затормозит пользовательский интерфейс. Если реализовывать аудит с помощью механизма триггеров, то все становится достаточно просто — необходимо просто повесить на те таблицы, действия с данными которых вы хотите контролировать, триггер after insert, update, delete и прописать в нем insert в таблицу аудита с указанием названия таблицы, действия, колонки, старого и нового значений.
Например, таблица аудита выглядит так:
CREATE TABLE TEST.Test_audit
(
oper_login varchar2(200),
data date,
type_operation varchar2(200),
table varchaer2(1000),
row_id number,
fild_name varchar2(200)
value_new number,
value_old number
constraint PK_OPER_ID
PRIMARY KEY (table, row_id)
);
На каждую таблицу (для примера пусть будет TEST_TABLE), которую мы хотим инспектировать, необходимо повесить триггер after insert, update, detele. В этом триггере должна быть реализация добавления в строку аудита информации, которая будет специфичная для каждой операции.
CREATE OR REPLACE TRIGGER TEST.TEST_TABLE_AUDIT_TRIGG
AFTER INSERT OR DELETE OR UPDATE ON TEST_TABLE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO Test_audit
(
type_operation
table,
row_id,
value_new
)
VALUES (
'insert',
'Test_audit',
:NEW.ID,
:NEW.field1
);
ELSIF UPDATING THEN
INSERT INTO Test_audit
(
type_operation
table,
row_id,
field_name,
value_new,
value_old
)
VALUES (
'update',
'Test_audit',
:NEW.ID,
'field1',
:NEW.field1,
:OLD.field1
);
ELSIF DELETING THEN
INSERT INTO Test_audit
(
type_operation
table,
oper_type,
row_id,
field_name,
value_old
)
VALUES (
'delete',
'Test_audit',
:NEW.ID,
'field1',
:OLD.field1
);
END IF;
END;
В этом примере аудит будет идти только на одно поле. Для того, чтобы сделать аудит нескольких полей, надо либо объединять значения, либо делать проверки и формировать insert для выполнения через EXECUTE IMMEDIATE. Если у нас много таблиц, на которое надо повесить аудит, то логично реализовать процедуру добавления в таблицу аудита, которую мы можем вызвать из триггера.
После того как мы повесили триггеры на все нужные таблицы, наш аудит все ещё не готов, так как есть данные, которые мы ещё не записали. Мы не записали дату операции и логин пользователя, который выполнил эту операцию. Дату мы не стали записывать, так как для всех таблиц, на которые мы вешаем аудит, записывать дату мы будем абсолютно одинаково — sysdate, а повторяемый код — это зло. Логин пользователя, который выполнил те или иные действия так же мы получим для любой таблицы одним и тем же способом, по-этому выносить это на каждую таблицу нет смысла. Ну, с датой все понятно, а вот как нам получить пользователя нашего приложения и записать его логин в триггере?
Что-бы реализовать многопользовательскую работу через приложение, разработанное на apex, есть два пути:
- Использовать аутентификацию Apex. Для этого необходимо завести всех пользователей в схеме, под которой подключается apex и вести все управление через его средства.
- Использовать свою структуру для авторизации и работы с приложением. В этом случае необходимо реализовывать пакет для аутентификации и мониторинга действий пользователей.
В первом случае главное преимущество — это быстрота реализации, но тогда теряется гибкость системы. Управление передается в руки APEX. Со вторым вариантом происходит обратное. Мы получаем полное управление, однако возникает необходимость реализовывать все механизмы для проверки логина и пароля, доступа к данным, аудита и т.д. Так же возникает вопрос «Как узнать? под каким логином зашел пользователь в APEX?», ведь нам его надо будет сохранить в таблице аудита. Чтобы понять, как получить логин, под которым вошел в систему клиент, необходимо понять схему работы APEX. Для наглядной демонстрации ниже приведены схемы взаимодействия браузера и СУБД при использовании APEX, а так же схема работы с метаданными Oracle Application Express.
Из схемы взаимодействия видно, что к СУБД подключается не клиент на прямую, а HTTP-сервер Apache. А все клиенты подключаются под своими учетными записями к этому серверу. Под каким пользователем подключается приложение, настраивается в APEX. Если у вас несколько приложений, то для каждого приложения можно настроить своего пользователя. Настраивается это в разделе атрибутов безопасности приложения (Security Attribute).
Соответственно если в СУБД вы сделаете запрос системной переменной user, то получите именно этого пользователя. Для того, что-бы получить пользователя, под которым зашел клиент в приложение (то есть под каким логином началась сессия) необходимо:
- Создать Application Item, например OPER_LOGIN. Application Item — это что-то вроде глобальных переменных для приложения APEX.
- Создать свою Autentification Scheme;
- В этой схеме проинициализировать свой Application Item;
- Привязать свою схему аутентификации в настройках безопасности (Security Attribute).
После выполнения этих действий нужные нам данные мы сможем получить очень легко — вызвав функцию из пакета apex_util.get_session_state(‘OPER_LOGIN’). В качестве параметра ей необходимо передать имя переменной, значение которой нас интересует.
Для записи общей информации необходимо добавить триггер на нашу табличку аудита. Выглядеть он будет как-то так:
CREATE OR replace TRIGGER TEST.test_audit_ins_id
BEFORE INSERT ON test_audit
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
apex_user VARCHAR(200);
BEGIN
IF :new.id IS NULL THEN
SELECT twst_au_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
IF :new.date IS NULL THEN
:new.date := SYSDATE;
END IF;
--APEX_PUBLIC_USER - общий пользователь под которым подключается APACHE
IF :new.oper_login IS NULL
OR :new.oper_login = 'APEX_PUBLIC_USER' THEN
BEGIN
apex_user := apex_util.Get_session_state('OPER_LOGIN');
IF apex_user = ''
OR apex_user IS NULL THEN
apex_user := USER;
END IF;
:new.oper_login := apex_user;
END;
END IF;
END;