Детализированный аудит Oracle и программирование PL/SQL

Илья Дергунов

Илья Дергунов

Автор статьи. ИТ-специалист с 20 летним стажем, автор большого количества публикаций на профильную тематику (разработка ПО, администрирование, новостные заметки). Подробнее.

Программирование PL/SQL: аудит базы данных OracleДетализированный аудит (FGA, Fine-Grained Auditing) предоставляет механизм для регистрации факта выдачи пользователями определенных команд и выполнения не­которых условий. При этом регистрируется команда, введенная пользователем, а также другая информация: время, терминал и т. д.

Под традиционным аудитом в Oracle понимается механизм регистрации того, какая схема выполнила то или иное действие: пользователь Joe выполняет процедуру X, поль­зователь John выполнил выборку данных из таблицы Y и т. д. Протокол всех этих дей­ствий, называемый журналом аудита, хранится в таблице AUD$ в схеме SYS, доступной для всех пользователей через несколько представлений словаря данных — например, DBA_AUDIT_TRAIL. Журналы аудита также могут записываться в файлы операционной системы вместо таблиц базы данных.


Оглавление статьи[Показать]


Независимо от того, где хранится эта информация, главный недостаток традиционного аудита остается неизменным: он следит за тем, кто выполнил ту или иную команду, а не за тем, что при этом было сделано. Например, из журнала аудита можно узнать, что пользователь Joe получил данные из таблицы ACCOUNTS, но какие именно записи — остается неизвестным. Если вы хотите знать изме­ненные значения, приходится устанавливать для таблиц триггеры DML и сохранять значения в таблице, определенной вами. Но поскольку определить триггер для команды SELECT не удастся, для ситуации с Joe этот вариант не подойдет.

На помощь приходит механизм FGA. Функциональность FGA используется через встро­енный пакет DBMS_FGA. Механизм FGA, появившийся в Oracle9i Database, изначально применялся только к командам SELECT; начиная с Oracle Database 10g он применяется ко всем командам DML.

Не путайте FGA с FGAC (Fine Grained Access Control) — этот термин является синонимом RLS.

При помощи FGA можно сохранить описания операций SELECT, INSERT, UPDATE и DELETE в журнале аудита (хотя и в другом журнале, не в таблице AUD$). Из журнала вы получите информацию не только о том, кто выполнил ту или иную операцию, но и множество других полезных сведений — точную команду, введенную пользователем, код SCN (System Change Number), значения подставляемых параметров (если они использова­лись) и многое другое.

Одна из самых полезных особенностей FGA — возможность избирательного примене­ния для конкретных операций. Например, аудит может выполняться при выборке из столбца SAL, но не из других столбцов; или операция может регистрироваться в журнале аудита только при выборке из столбца SAL, и если значение этого столбца не менее 1500. Избирательная регистрация сокращает объем генерируемых данных аудита.

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

 

Зачем изучать FGA?

FGA — «близкий родственник» традиционного аудита. Безусловно, эта возможность предназначена в первую очередь для администраторов баз данных; зачем же разработ­чикам PL/SQL изучать ее? Есть несколько причин:

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

Производительность. Другая, более убедительная причина — практическая ценность информации, возвращаемой FGA. Кроме информации о том, кто выполнил ту или иную команду, FGA может точно описать выполненные команды SQL. Если включить FGA для таблицы, то все команды SQL, обращенные к этой таблице, будут сохраняться в журналах аудита FGA. Последующий анализ этой информации поможет выявить закономерности в выполнении команд и решить, не нужно ли добавить или изменить индексы или внести другие изменения, способствующие повышению производительности.

Подставляемые параметры. FGA также сохраняет значения параметров, а в любом нормально спроектированном приложении используется множество подставляемых параметров. Как узнать, какие значения передавались во время запуска приложения? На основании собранной информации вы решите, нужно ли определять дополнительный индекс. Журналы FGA предоставляют сведения, упрощающие принятие таких решений.

Модули-обработчики. FGA также может выполнить процедуру, называемую модулем- обработчиком, при выполнении некоторых условий аудита. Скажем, при включении FGA для команд SELECT модуль-обработчик будет выполняться для каждой команды SELECT, обращенной к таблице. Происходящее напоминает включение триггера для команды SELECTOracle не поддерживает эту возможность, но она может оказаться в высшей степени полезной. Предположим, каждый раз, когда пользователь запрашивает инфор­мацию об окладе руководства компании, сообщение должно ставиться в расширенную очередь для последующей передачи в другую базу данных. Для решения этой задачи можно реализовать модуль-обработчик, который будет делать то же самое, что делает триггер для SELECT.

Давайте поближе познакомимся с применением FGA в ваших приложениях.

Для правильной работы FGA база данных должна работать в режиме затратного оптимизатора (CBO), запросы должны использовать CBO (то есть они не долж­ны содержать рекомендации RULE), а таблицы (или представления) в запросе должны быть проанализированы (по крайней мере с оценками). Если эти условия не выполняются, при использовании FGA возможны ложноположительные сра­батывания: информация будет записываться в журнал аудита, хотя содержимое столбца на самом деле не выбирается.

 

Простой пример


Начнем с простого примера — таблица EMP из схемы HR уже знакома вам по обсуждению RLS в предыдущем разделе. Предположим, для контроля за использованием конфи­денциальной информации вы хотите регистрировать в журнале все операции выборки столбцов SAL и COMM. Чтобы сократить объем генерируемого журнала, регистрироваться будут только операции со строками, в которых оклад составляет $150 000 и более. На­конец, аудит также должен включаться при запросе оклада работника 100 (то есть вас). Разобравшись с требованиями, перейдем к построению архитектуры FGA. Как и в случае с RLS, все начинается с определения политики FGA для таблицы. Политика определяет все условия, в которых должен срабатывать механизм аудита. Для добавления политики используется процедура ADD_P0LICY из встроенного пакета DBMS_FGA:

1 BEGIN
2     DBMS_FGA.add_policy (object_schema        => 'HR',
3                          object_name          => 'EMP',
4                          policy_name          => 'EMP_SEL',
5                          audit_column         => 'SAL, COMM',
6                          audit_condition      => 'SAL >= 150000 OR EMPID = 100'
7                         );
8 END;

Я определяю политику FGA с именем EMP_SEL (строка 4), передавая ее в параметре policy_name. Политика определяется для таблицы EMP (строка 3), владельцем которой является HR (строка 2).

Политика требует сохранять данные в журнале аудита каждый раз, когда пользователь выбирает данные из двух столбцов, SAL и COMM (строка 5). Однако данные сохраняются только в том случае, если значение SAL в этой строке не менее $150 000 или если иден­тификатор работника равен 100 (условие аудита, строка 6).

Параметры audit_column и audit_condition не являются обязательными. Если они не указаны, то в аудите будет участвовать каждая команда SELECT к таблице EMP схемы HR. Начиная с Oracle Database 10g, поскольку FGA также может применяться и к обычным операциям DML, я могу определять конкретные команды, на которые должна распро­страняться политика аудита, при помощи нового параметра statement_types:

1    BEGIN
2       DBMS_FGA.add_policy (object_schema        => 'HR',
3                  object_name          => 'EMP',
4                  policy_name          => 'EMP_DML',
5                  audit_column         => 'SALARY, COMM',
6                  audit_condition      => 'SALARY >= 150000 OR EMPID = 100,
7                  statement_types      => 'SELECT, INSERT, DELETE, UPDATE'
8                           );
9    END;

Хотя политика используется и в FGA, и в RLS, она играет совершенно разные роли. Впрочем, имеется и сходство — политика FGA, как и ее «родственник» в RLS, не является «объектом схемы», то есть не принадлежит никакому пользо­вателю. Каждый пользователь, обладающей привилегией EXECUTE для пакета DBMS_FGA, может создавать политики и удалять политики, созданные другим пользователем. Ваш администратор базы данных должен быть очень осмотритель­ным при предоставлении привилегий EXECUTE для этого встроенного пакета; предоставление привилегии категории PUBLIC делает всю вашу деятельность по аудиту сомнительной — в лучшем случае.

В нашем примере журнал аудита записывается только в том случае, если:

Для сохранения информации в журнале аудита истинными должны быть оба усло­вия. Если одно условие истинно, а другое нет, то операция не регистрируется. Если пользователь в своем запросе не получает либо столбец SAL, либо столбец COMM (прямо или косвенно), то запись в журнале не генерируется, даже если у записи столбец SAL содержит значение 150 000 и более. Предположим, оклад Джейка равен 160 000, а его идентификатор EMPID равен 52. Пользователь, который просто хочет узнать имя руко­водителя, выдает следующий запрос:

SELECT mgr
  FROM emp
 WHERE empid = 52;

Так как в запросе ни столбец SAL, ни столбец COMM не задействован, операция не реги­стрируется. Однако запрос:

SELECT mgr
  FROM emp
 WHERE sal >= 160000;

будет зарегистрирован в журнале. Почему? Потому что столбец SAL задействован в секции WHERE, то есть пользователь неявно обращается к нему; следовательно, условие аудита выполнено. Кроме того, значение SAL прочитанных записей больше 150 000. Так как оба условия выполнены, выполняется аудит.

Условие аудита не обязано ссылаться на столбцы таблицы, для которой определена политика; оно также может ссылаться на другие значения (например, псевдостолбцы). Это может быть полезно при включении аудита для некоторого подмножества пользо­вателей. Допустим, вы хотите регистрировать обращения к таблице EMP от пользователя Scott. Политика определяется следующим образом:

BEGIN
   DBMS_FGA.add_policy (object_schema        => 'HR',
                        object_name          => 'EMP',
                        policy_name          => 'EMP_SEL',
                        audit_column         => 'SALARY, COMM',
                        audit_condition      => 'USER=''SCOTT'''
                       );
END;

 

Количество столбцов

В предыдущем примере список столбцов был задан следующим образом:

audit_column    => 'SAL, COMM'

Это означает, что регистрируются все обращения пользователя к столбцу SAL или COMM. Однако в некоторых ситуациях могут действовать более точные требования: обращение ко всем перечисленным столбцам, а не только к одному из них. Например, в базе данных employee механизм FGA должен регистрировать только обращения к SAL и EMPNAME одно­временно. Если в запросе задействован только один столбец, операция вряд ли раскроет конфиденциальную информацию. Выполняется следующий запрос:

SELECT salary FROM hr.emp;

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

SELECT empname
  FROM hr.emp;

вернет имена работников без окладов, то есть конфиденциальность останется под за­щитой. Однако при выдаче запроса 

SELECT empname, salary FROM hr.emp;

пользователь узнает, сколько получает каждый из работников — это та самая инфор­мация, которую следует защитить.

Из трех показанных случаев запись в журнал аудита должна происходить только для последнего (единственного, для которого запрос вернет содержательную информацию). В Oracle9i Database не было возможности задать комбинацию столбцов как условие аудита; в Oracle Database 10g и выше это стало возможно благодаря параметру audit_ colum_opts процедуры ADD_POLICY. По умолчанию значение параметра равно DBMS_FGA. ANY_COLUMNS, то есть запись в журнал аудита производится при обращении к любому из столбцов. Если же задать параметру значение DBMS_FGA.ALL_COLUMNS, то данные аудита генерируются только при обращении ко всем перечисленным столбцам. В моем приме­ре, чтобы политика FGA создавала запись аудита только при выборке столбцов SALARYи EMPNAME, она должна выглядеть так:

BEGIN
   DBMS_FGA.add_policy (object_schema          => 'HR',
         object_name            => 'EMP',
         policy_name            => 'EMP_DML',
         audit_column           => 'SALARY, EMPNAME',
         audit_condition        => 'USER=''SCOTT''',
         statement_types        => 'SELECT, INSERT, DELETE, UPDATE',
         audit_column_opts      => DBMS_FGA.all_columns
    );
END;

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

 

Просмотр журнала аудита

Данные аудитаFGA записываются в таблицуFGA_LOG$, принадлежащую схеме SYS. Для работы с данными аудита используется внешний интерфейс — представление словаря данных DBA_FGA_AUDIT_TRAIL:

SELECT db_user, sql_text
  FROM dba_fga_audit_trail
 WHERE object_schema = 'HR' AND object_name = 'EMP'

Запрос выдает следующий результат:

DB_USER SQL_TEXT
------- -----------------------------------------------
SCOTT   select salary from hr.emp where empid = 1

Кроме пользователя и команды SQL, в журнале FGA также сохраняются другие по­лезные сведения. Некоторые важные столбцы представления:

 

Основные принципы FGA

 


Подставляемые параметры

Описывая FGA, я упомянул о подставляемых параметрах. Давайте посмотрим, как исполь­зовать FGA с этими переменными. Предположим, вместо «обычной» команды в форме:

SELECT salary
  FROM emp
 WHERE empid = 100;

был использован блок следующего вида:

DECLARE
   l_empid PLS_INTEGER;
BEGIN
   SELECT salary
     FROM emp
    WHERE empid = l_empid;
END;

FGA сохраняет значения подставляемых параметров вместе с текстом команды SQL. Сохраненные значения находятся в столбце SQL_BIND представления DBA_FGA_AUDIT_TRAIL. В предыдущем примере используется код:

SQL> SELECT sql_text,sql_bind from dba_fga_audit_trail;

SQL_TEXT                                       SQL_BIND
---------------------------------------------- -----------------------
select * from hr.emp where empid = :empid      #1(3):100

Обратите внимание на формат вывода сохраненного параметра:

#1(3):100

Здесь #1 — номер параметра. Если запрос использует более одного подставляемого параметра, то другим будут присвоены номера #2, #3 и т. д. Фрагмент(3) обозначает фак­тическую длину переменной. В нашем примере пользователь Scott указал значение 100, поэтому длина равна 3. Фрагмент :100 обозначает фактическое значение параметра (100). Если в запросе используется несколько подставляемых параметров, столбец SQL_BIND содержит строку значений. Например, для запроса:

DECLARE
   l_empid    PLS_INTEGER := 100;
   l_salary   NUMBER := 150000;

   TYPE emps_t IS TABLE OF emp%ROWTYPE;
   l_emps     empts_t;
BEGIN
   SELECT * BULK COLLECT INTO l_emps
     FROM hr.emp
    WHERE empid = l_empid OR salary > l_salary;
END;

столбец SQL_BIND будет выглядеть так:

#1(3):100 #2(5):150000

Текст команды SQK и подставляемые параметры сохраняются только в том случае, если параметру audit_trail процедуры ADD_POLICY задано значение DB_EXTENDED (по умолчанию), но не значение DB.

 

Модули-обработчики

Как упоминалось ранее, механизм FGA также позволяет выполнять хранимые про­граммные блоки PL/SQL — например, хранимые процедуры. Если хранимая процедура, в свою очередь, содержит внешнюю программу, эта программа тоже будет выполнена. Хранимая программа называется модулем-обработчиком (handler module). В приве­денном ранее примере с построением механизма для аудита обращений к таблице EMP я также мог бы указать хранимую процедуру (отдельную или пакетную), которая должна выполняться при аудите. Если владельцем хранимой процедуры является пользователь FGA_ADMIN и она называется myproc, то процедура создания политики ADD_POLICY будет вызываться с двумя новыми параметрами — handler_schema и handler_module:

BEGIN
   DBMS_FGA.add_policy (object_schema        => 'HR',
              object_name          => 'EMP',
              policy_name          => 'EMP_SEL',
              audit_column         => 'SALARY, COMM',
              audit_condition      => 'SALARY >= 150000 OR EMPID = 100',
              handler_schema       => 'FGA_ADMIN',
              handler_module       => 'MYPROC'
              );
END;

При выполнении условий аудита и обращениях к соответствующим столбцам помимо регистрации операции в журнале также выполняется процедура fga_admin.myproc. Она выполняется автоматически при каждой записи данных аудита в виде автономной транзакции. Процедура получает ровно три параметра — имя схемы, имя таблицы и имя политики. Структура процедуры модуля-обработчика:

PROCEDURE myproc (
   p_table_owner   IN   VARCHAR2,
   p_table_name    IN   VARCHAR2,
   p_fga_policy    IN   VARCHAR2
)
IS
BEGIN
   -- здесь размещается код END;
END;

Как использовать эту возможность? Есть множество вариантов. Например, вы можете построить собственную программу, которая будет сохранять данные в ваших таблицах наряду с записью в стандартные таблицы журналов аудита. Можно записывать сообще­ния в очереди для размещения в других базах данных, запросить отправку электронной почты администраторам по безопасности, просто подсчитать количество выполнений условий аудита... Словом, возможности безграничны.

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

Вас заинтересует / Intresting for you:

Вызов Java-программ из PL/SQL:...
Вызов Java-программ из PL/SQL:... 9769 просмотров Antoni Mon, 06 Jan 2020, 15:02:03
Как вызвать код Java из програ...
Как вызвать код Java из програ... 4771 просмотров Максим Николенко Fri, 19 Jan 2018, 06:34:50
Java: подключение  к базам дан...
Java: подключение к базам дан... 18829 просмотров Александров Попков Fri, 27 Sep 2019, 07:43:26
Курс "Основы Adroid для начина...
Курс "Основы Adroid для начина... 1878 просмотров Андрей Волков Fri, 12 Jul 2019, 06:21:52
Печать
Войдите чтобы комментировать