Аудит данных в Oracle на основе пакетных переменных и триггеров

В пакеты Oracle можно добавлять переменные. Эти переменные имеют свои значения для каждой сессии. Такое свойство позволяет организовать аудит — лог записей о том кто и когда менял данные в таблицах. Общий алгоритм такой:

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

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

Пакет может выглядеть примерно так

CREATE OR REPLACE PACKAGE AUD.MY_AUDIT IS
  curr_user       core.users%rowtype;
  curr_module     core.modules%rowtype;

  -- Установка текущего пользователя
  procedure SetCurrentUser
    (currentUserID in core.users.rid%type);

  -- Установка текущего модуля
  procedure SetCurrentModule
    (currentModuleID in core.modules.rid%type);

  -- Одна процедура для установки пользователя и модуля
  procedure SetCurrentAuditParams
    (currentUserID in core.users.rid%type,
     currentModuleID in core.modules.rid%type);

  -- Получение информации о текущем пользователе
  function GetCurrentUser
    return core.users%rowtype;

  -- Получение информации о текущем модуле
  function GetCurrentModule
    return core.modules%rowtype;

  -- Функции для отдельных полей могут использоваться в запросах или представлениях
  function current_user_name return core.users.name%type;

end MY_AUDIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Недостаток такого подхода — исключение ORA-04068: существующее состояние пакетов было сброшено. Оно происходит если в сессии используется какой-либо пакет и во время работы сессии этот пакет перекомпилируется. Такая ситуация возникакет, например, при обновлении БД, без отключения клиентов. После исключения все переменные всех пакетов, в том числе в пакете аудита, сбрасываются и регистрацию сессии нужно производить заново.

# Аудит данных в Oracle на основе пакетных переменных и триггеров

Аудит данных в Oracle на основе пакетных переменных и триггеров