К настоящему времени очень много

К настоящему времени очень много написано о новых функциональных возможностях сервера Oracle Database 10g Release 2 (например см. oracle.com/ru/oramag/feb2006/dev_asktom55.html), таких, как прозрачное шифрование данных, новое DML-предложение LOG ERRORS, условная компиляция в языке PL/SQL и так далее. Теперь я хотел бы рассмотреть некоторые другие новые функциональные возможности, обсуждения которых прошли на сайте asktom.oracle.com.

Следующие шаги:

Том Кайт, вице-президент корпорации Oracle, отвечает на наиболее трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие материалы этого форума публикуются в данной колонке.
ЧИТАЙТЕ более подробно об

удалении дубликатов

порядке срабатывания триггеров

операторе LOCK TABLE

поддержке больших объектов

шифровании резервных копий

сжатии неиспользуемых блоков

уменьшении набора привилегий роли CONNECT

удалении пустых файлов данных

Новые возможности сервера Oracle Database 10g Release 2

oracle.com/ru/oramag/feb2006/dev_asktom55.html

oracle.com/pls/db102/portal.portal_db?selected=1

ЧИТАЙТЕ еще Тома

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

ЗАГРУЖАЙТЕ

Oracle Database 10g Express Edition (Oracle Database XE)

Начнем с улучшенной поддержки больших объектов (LOB) в распределенных системах. В среде PL/SQL максимальный размер больших объектов, к которым возможен доступ через связи баз данных, вырос до 32 КБ (максимальный размер строки в языке PL/SQL) – в интерфейсе уровня вызовов СУБД Oracle (OCI) и среде Java/JDBC отсутствуют такие ограничения, поэтому в них можно иметь доступ через связи баз данных к большим объектам любого размера.

В диспетчере восстановления Recovery Manager (RMAN) появилась новая функциональная возможность – шифрование резервных копий. Оно может выполняться прозрачно с использованием электронных бумажников (Oracle Wallet), что очень похоже на прозрачное шифрование данных, или же во время резервирования вы можете указать пароль, который вам хочется. Вы также можете использовать комбинацию обоих этих способов, например, это будет полезно, когда электронный бумажник не доступен во время восстановления. Конечно, вы должны заботиться о гарантиях доступности бумажника или пароля, иначе зашифрованные данные нельзя будет восстановить.

Другая новая функциональная возможность утилиты RMAN – возможность пропускать во время резервирования базы данных любые неиспользованные блоки. В прошлом утилита RMAN "сжимала" наборы резервных копий, пропуская (не резервируя) любые блоки базы данных, которые никогда не использовалась, но после того, как в блок хотя бы один раз вставлялись данные, он всегда будет резервироваться. То есть, если бы вы создали пустое табличное пространство размером 100 ГБ и не помещали в него ничего, то во время резервирования утилита RMAN пропустила бы в этом табличном пространстве все блоки (поскольку они никогда не содержали никаких данных). Если бы вы впоследствии заполнили все это табличное пространство данными (использовали бы все блоки), а затем удалили бы таблицу с этими данными, то в некоторой момент времени утилита RMAN создала бы резервную копию всех этих блоков, поскольку они содержали данные (были сформатированы), даже при том, что они в настоящее время пусты. Начиная с сервера Oracle Database 10g Release 2, утилита RMAN имеет возможность не резервировать такие блоки, не содержащие никакой информации, которую действительно нужно резервировать.

По соображениям безопасности, серьезно уменьшен набор привилегий роли CONNECT. В прошлом роль CONNECT позволяла не только создавать сеансы, но также и кластерные сегменты базы данных, связи базы данных, последовательности, синонимы, таблицы, представления и изменять параметры сеансов. (Она была больше похожа на роль разработчика, а не на роль для простого подключения к базе данных.) Ей были предоставлены очень серьезные права. Начиная с сервера Oracle Database 10g Release 2, роль CONNECT имеет только привилегию создания сеансов CREATE SESSION.

И наконец, долгожданная функциональная возможность сервера Oracle Database 10g Release 2 – возможность удаления из табличного пространства файла данных, который не содержит никаких данных (в нем не выделено никаких экстентов). Это позволяет администратору базы данных удалить файл, который был случайно добавлен в неправильные место или табличное пространство. В прошлом такой файл навсегда закреплялся за табличным пространством.

Это – только короткий список из многих новых функциональных возможностей сервера Oracle Database 10g Release 2. Если вам интересно узнать больше об этом новом выпуске, на сайте oracle.com/pls/db102/portal.portal_db?selected=1 ознакомьтесь с руководством New Features Guide (новые возможности). Кроме того, не забывайте просматривать начальные разделы почти каждой книги комплекта документации Oracle, посвященные описанию новых возможностей рассмотренных в конкретном документе.

Ведущий данной колонки Том Кайт () работает в корпорации Oracle с 1993 года. Кайт – вице-президент Oracle, возглавляющий группу Oracle Public Sector, он автор книг "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003), а также ряда других.

Объектная привилегия SELECT разрешает блокирование

Вопрос. Нашим разработчикам (по крайней мере, нашим разработчикам группы технической поддержки) необходим доступ только для чтения (READ ONLY) к нашим промышленным базам данных, поэтому в каждой промышленной базе данных у нас есть роль с объектными привилегиями SELECT на каждую таблицу приложения и эта роль предоставляется разработчикам.

Несколько недель назад один из наших новых разработчиков заблокировал главную таблицу промышленной базы данных и приостановил всю пакетную обработку. Все администраторы базы данных клялись, что нельзя установить блокировку, имея только объектную привилегию SELECT, и мы потеряли много времени выясняя, как разработчик заблокировал таблицу. Мы открыли в Oracle запрос на техническую поддержку (TAR) и получили ответ: "Да, вы можете заблокировать строки, имея только объектную привилегию SELECT. Фактически, вы можете заблокировать всю таблицу оператором LOCK TABLE". Я хочу знать, сколько администраторов баз данных не знают об этом, и понять причину разрешения блокирования, когда нельзя модифицировать данные.

Ответ. Такое блокирование "известно" и допускается, я согласен, что немногие знают об этой особенности (в этом заключается основная причина моего решения о публикации этого вопроса – повысить уровень знаний). В описании оператора LOCK TABLE (Сервер Oracle Database. Справочное руководство по языку SQL), например, сказано: "Необходимые условия: таблица или представление должны принадлежать вашей собственной схеме либо вы должны иметь системную привилегию LOCK ANY TABLE или любую объектную привилегию на таблицу или представление. (выделено мною)

Заметим, эти необходимые условия не обязательно означают возможность модификации данных, но и возможность чтения данных указывает на возможность блокирования этих данных (обеспечивать их устойчивое состояние, "замораживать", запрещать модификацию). Так работает оператор SELECT ... FOR UPDATE. Итак, почему это правильно?

Следующий сценарий показывает, почему это правильно. Предположим, я имею доступ по чтению к таблице конференц-залов и доступ по чтению-записи к таблице расписания заседаний. Мне нужно зарезервировать конференц-зал X. Для того чтобы сделать это надежно (чтобы избежать наложений) и никто другой не мог зарезервировать конференц-зал X, я должен заблокировать этот конференц-зал (в таблице конференц-залов). Транзакция, в которой резервируются залы, – хранимая процедура, как это должно и быть – все попытки модификации таблицы расписания заседаний выполняются с помощью этой процедуры. Хранимая процедура блокирует этот конференц-зал (в таблице конференц-залов) оператором SELECT ... FOR UPDATE, чтобы никто другой не мог зарезервировать его.

Другой сценарий, который показывает, почему чтение может заблокировать таблицу: я хочу удалить строку из таблицы P, которая является родительской таблицей таблицы C. У меня есть доступ по чтению-записи к таблице P и доступ только по чтению к таблице С. Я пытаюсь удалить строку из таблицы P, но перед этим я должен заблокировать часть таблицы C (если существуют индексированные внешние ключи) или всю эту таблицу (если нет индекса по внешнему ключу).

Порядок срабатывания триггеров

Вопрос. У меня есть таблица и два или больше триггеров AFTER INSERT/AFTER DELETE. У меня есть два-три триггера AFTER INSERT для одной и той же таблицы. Можете ли вы рассказать о порядке срабатывания триггеров AFTER INSERT? (Я пытался определить это, используя отметки времени (time stamps), но все они указывают одинаковое время).

Ответ. О порядке срабатывания триггеров я могу сказать только следующее:

будут срабатывать все триггеры BEFORE в каком-то порядке; будут срабатывать все триггеры BEFORE FOR EACH ROW в каком-то порядке; будут срабатывать все триггеры AFTER FOR EACH ROW в каком-то порядке; а затем будут срабатывать все триггеры AFTER в каком-то порядке.

В пределах одного типа триггеров вы не должны рассчитывать на порядок срабатывания этих триггеров. Даже если вы обнаружили, что три триггера BEFORE запускаются в таком порядке: TRIGGER_A, TRIGGER_B, TRIGGER_C; вы не можете полагаться на это. В документации Oracle (Сервер Oracle Database. Руководство разработчика приложений. Основы) ясно написано: "Если для таблицы существует несколько триггеров одного типа, СУБД Oracle выполняет их в произвольном порядке".

Если ваши триггеры зависят от порядка срабатывания, вы должны объединить их в один триггер. На самом деле я рекомендую, чтобы во всех триггерах вызывались хранимые процедуры, так что большинство ваших триггеров будут состоять из одной строки – вызова процедуры. Таким образом вы сможете взять два-три триггера AFTER INSERT, разместить их код в хороших модульных процедурах пакета, а затем вызывать их в правильном порядке в одном триггере.

Побочное примечание: во время интерактивного обсуждения возник вопрос: "Почему в СУБД Oracle вообще разрешается использование множественных триггеров одного и того же типа"? Причина восходит к первоначальной реализации в сервере Oracle Database механизма тиражирования. В журналах материализованных представлений (materialized view logs) – раньше в версии 7.0 они назывались просто журналами моментальных копий (snapshot logs) – для регистрации операций языка манипулирования данными (DML), которые выполнялись на данной таблице, использовались триггеры базы данных. До поддержки в сервере базы данных множественных триггеров одного и того же типа это накладывало на разработчиков ограничение: либо не использовать сам этот тип триггеров (который уже использовался сервером Oracle), либо не использовать журналы материализованных представлений. Ни один из тех вариантов не был привлекательным, поэтому в сервер базы данных была добавлена возможность использования множественных триггеров одного и того же типа.

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

С чего начинать

Вопрос. В течение прошедших четырех лет я был администратором базы данных SQL Server 2000, теперь я очень хочу изучить СУБД Oracle. С чего мне начинать?

Ответ. Я часто получаю этот вопрос, поэтому я придумал план начального изучения документации Oracle.

Я рекомендую всем начинать с Основных концепций. Затем я ввожу две ветви: для разработчиков и администраторов баз данных, как это показано на рис.1.

Рис. 1. План изучения документации сервера Oracle Database 10g

Я также предлагаю загрузить и инсталлировать бесплатную СУБД Oracle Database 10g Express Edition. Объем загружаемой информации относительно небольшой (приблизительно 150 МБ), а для последующей инсталляции и запуска в ОС Windows или Linux нужно около пяти минут – великолепный способ начать изучать СУБД Oracle.

И наконец, участвуйте в форумах, начинайте задавать вопросы. Некоторые предлагаемые форумы:

Usenet newsgroups (система телеконференций Интернета). С этого я начинал в октябре 1994 г., когда отправил свое первое сообщение. Что удивительно, масса людей, которые участвовали в этих телеконференциях тогда, все еще там и сегодня. Сейчас я не отправляю столько же сообщений, сколько раньше, но время от времени по-прежнему просматриваю телеконференции, в которых активно участвует много людей.

Oracle-l. (Модератор – Стив Адамс. – Прим. пер.) Здесь участвуют такие известные специалисты, как Волфганг Брайтлинг (Wolfgang Breitling), Лекс де Хаан (Lex de Haan), Пол Дрэйк (Paul Drake), Младен Гогала (Mladen Gogala), Тим Горман (Tim Gorman), Джонатан Льюис (Jonathan Lewis,), Найалл Личфилд (Niall Litchfield), Коннор Макдоналд (Connor McDonald), Кэри Миллсап (Cary Millsap), Танэль Подер (Tanel Poder), Марк Пауэлл (Mark Powell), Пит Шарман (Pete Sharman) и Джаред Стилл (Jared Still). (И этот список далеко не исчерпывающий.) Кроме того, здесь очень высокий дух содружества. В каждой конференции или событии любого масштаба обычно участвуют практически все. У многих участников сложились хорошие дружеские отношения.

The Oracle Users' Co-Operative FAQ (совместные "ЧАВО" пользователей продуктов Oracle). Спонсируется и поддерживается Джонатаном Льюисом. Не забывайте, здесь надо делиться. Если вы обнаружили часто задаваемый вопрос там, где вы работаете, то, вероятно, он будет таким же во всем FAQ-мире, так что вы тоже можете внести и свой вклад.

OTN discussion forums (дискуссионные форумы на сайте OTN). Здесь вы можете даже получить ответ от человека, который написал программное обеспечение, о котором вы спрашиваете.

OracleMetaLink. Я должен упомянуть этот сайт. На нем также проводятся интерактивные дискуссионные форумы.

( Из русскоязычных дискуссионных форумов стоит отметить форум на сайте sql.ru, как имеющий самый большой трафик. Однако культура общения многих его участников заметно уступает зарубежной. Вместе с тем, участвует также и много высококлассных специалистов. – Прим. пер.)

Independent Oracle Users Group. (IOUG, независимая группа пользователей Oracle) Я рекомендую этот сайт не столько из-за дискуссионных форумов (у них небольшой трафик), а как организацию в целом. Я – большой сторонник IOUG и пытаюсь сделать все, что могу, для ее поддержки. Я настоятельно призываю всех вас, кто связал свою карьеру с Oracle, вступить в нее. Само по себе сетевое общение - это одно. Встречайтесь с людьми, которые работают в разных компаниях в вашей предметной области, отдельно от специалистов в других областях. Послушайте об их опыте работы; поделитесь своим. Проводятся большие конференции – я участвовал в американской IOUG-A, британской UKOUG (и планирую в этом году быть там снова), ирландской Irish OUG, не считая других событий OUG, которых слишком много, чтобы даже начать их перечислять. Это – большая организация с большим техническим содержанием и сильным лидерством.

Сложные ограничения целостности

Вопрос. Я написал на языке PL/SQL хранимую процедуру, но думаю, она не будет работать правильно с одновременным доступом к ней нескольких пользователей. Эта процедура проверяет, что строка с определенными значениями не существует и инициирует ошибку, если она существует. Если строка не существует, процедура выполняет некоторые вычисления, а затем вставляет строку. Я думаю, если другой пользователь вставил строку в эту же таблицу и зафиксировал эту вставку после того, как моя хранимая процедура выполнила проверку, но до того, как она зафиксировала вставку, то в таблицу могут быть вставлены неверные данные. Эта проблема не может быть устранена при помощи уникального ключа, поскольку поля не всегда должны быть различными – только иногда. Единственное решение, которое я смог придумать, состоит в том, чтобы проверять условие в начале хранимой процедуры, а затем снова проверять в конце, и в случае любой неудачной проверки выполнять откат. Есть ли лучший способ сделать это?

Таблица имеет три столбца: A, B и С; комбинация значений столбцов A и B всегда должна быть уникальной, если только значение столбца C не равно 'W'. Если в столбце C содержится значение 'W', то значения столбцов A и B могут совпадать со значениями в других строках. Кроме того, столбец B может иметь неопределенное значение (null). Можно ли создать ограничение для этого?

Ответ. Вы правы, при работе в многопользовательской среде могут быть проблемы. Фактически невозможно определять кросс-стороковые (это ваш случай) или кросс-табличные (типа внешних ключей) ограничения целостности. Почти всегда я вижу попытки реализовать это в кодах приложений или с помощью триггеров, что неправильно для многопользовательской среды. Как вы заметили, если два пользователя одновременно делают одно и тоже, они не видят работу друг друга. Чтобы достичь цели, нужно фактически сериализовать модификации этой таблицы, используя оператор LOCK TABLE.

К счастью, есть действительно хороший способ сделать это, используя индексы по ключу-функции. Здесь вы можете использовать два факта:

ключи, которые содержат только значения NULL, не включаются в индексы типа "B-дерево". Если вы создаете индекс на T (C1, C2) и существует строка, в которой оба столбца C1 и C2 имеют значения NULL, то такая строка не помещается в индекс. Итак, вы можете в сервере Oracle селективно индексировать строки, если вы будете возвращать NULL-ключ (полностью NULL-ключ); вы может индексировать сложные функции, включая пользовательские и встроенные.

Итак, я создаю вашу таблицу:

SQL> create table t 2 ( a int, 3 b int, 4 c varchar2(1) not null 5 ); Table created.

И уникальный индекс по столбцам A и B, когда столбец C <> 'W':

SQL> create unique index t_idx 2 on t 3 ( case when c <> 'W' 4 then a 5 end, 6 case when c <> 'W' 7 then b 8 end 9 ); Index created.

Я получил именно то, что вам нужно: SQL> set feedback off SQL> insert into t values (1,1,'W'); SQL> insert into t values (1,1,'W'); SQL> insert into t values (1,1,'x'); SQL> insert into t values (1,1,'y'); insert into t values (1,1,'y') * ERROR at line 1: ORA-00001: unique constraint (T_IDX) violated

Значения NULL, NULL получаются тогда, когда столбец C = 'W', а значения столбцов A, B, когда столбец C <> 'W', то есть я уникально индексирую столбцы A,B только тогда, когда столбец C <> 'W' – в точности то, что вам нужно. Это как бы похоже на индексирование предложения WHERE.

Том Кайт: об удалении дубликатов

Перевод:

Источник: журнал Oracle Magazine, March-April 2006

(http://www.oracle.com/technology/oramag/oracle/06-mar/o26asktom.html).

Том Кайт удаляет дубликаты, задает порядок срабатывания триггеров и объясняет, почему объектная привилегия SELECT разрешает блокирование.

Вопрос. Пожалуйста, помогите мне написать SQL-запрос для избавления от дубликатов, которые ниже помечены как XXX. Дублирующие записи для меня – записи, которые имеют одинаковые значения в столбцах SA и SB, независимо от их позиции. (Первичный ключ – объединенные столбцы SA и SB).

SA SB --- --- A B A C A D B A XXX (DUPLICATE ) B C C A XXX (DUPLICATE) C B XXX (DUPLICATE) C D

Ответ. Есть несколько способов сделать это, я продемонстрирую свой подход, но на будущее я предлагаю вам добавить ограничение целостности, которое в дальнейшем будет запрещать ввод дубликатов.

А пока для удаления из таблицы дубликатов я могу использовать некоторые встроенные функции и аналитику.

Используя аналитику, я буду разделять, разбивать, данные с помощью функций LEAST(SA,SB) и GREATEST(SA,SB). То есть, сначала я найду меньшее значение столбцов SA и SB, а затем большее значение, чтобы строки со значениями A, B находились в одном и том же разделе, что и строки со значениями B, A. После разбиения данных с помощью этих функций я сортирую строки по значениям столбцов SA и SB и применяю к каждой строке функцию row_number (). Все, что я должен сделать затем, – найти любые строки, у которых номер строки row_number () <> 1 (не первая строка в разделе – в каждом разделе мне нужна только одна строка), и удалить их.

Этот SQL-код похож на показанный в листинге 1.

SQL> delete from t 2 where rowid in 3 ( select rid 4 from ( 5 select rowid rid, sa, sb, 6 row_number() over 7 (partition by least(sa,sb), 8 greatest(sa,sb) order by sa, sb) rn 9 from t 10 ) 11 where rn <> 1 12 ) 13 / 3 rows deleted.

Листинг 1. Удаление дубликатов из таблицы t.

На будущее я рекомендую вам изначально предотвращать в системе ввод дубликатов. Один из способов добиться этого – определить на уровне таблицы проверочное ограничение целостности: SQL> alter table t 2 add constraint sa_less_than_equal_sb 3 check (sa <= sb); Table altered.

Затем создайте составной первичный ключ по столбцам SA и SB. Таким образом вы никогда не будете иметь A, B и B, A; поскольку B, A изначально не будет вставлено. Если вы все же хотите иметь в первичном ключе значение B, A; вы можете использовать индекс по ключу-функции:

SQL> create unique index t_idx 2 on t(least(sa,sb), 3 greatest(sa,sb)); Index created.

Это – уникальный индекс с отсортированными значениями столбцов таблицы – в первом столбце индекса сначала помещается наименьшее значение столбцов SA и SB, а во втором столбце индекса – наибольшее значение столбцов SA и SB.

Эта тема вылилась в довольно интересное интерактивное обсуждение, когда кто-то спросил: "А что насчет трех столбцов или N столбцов"? Вы можете прочитать исходное обсуждение на сайте asktom.oracle.com, там же рассматривались альтернативные операторы DELETE, которые также позволяют удалять дубликаты.

Аннотация

Automatic Storage Management (ASM) &#x2013; средство автоматического управления дисковым пространством БД, появившееся в версии 10 и развитое в версии 11. Два главных свойства ASM &#x2013; контролируемая избыточность данных и автоматическая балансировка загрузки дисков. В статье рассматривается доступный пример построения ASM.

Использование дисковых групп

Вот пример того, как созданную дисковую группу можно употребить для размещения в ней файла табличного пространства:

CONNECT /@prima.class AS SYSDBA CREATE TABLESPACE users2 DATAFILE '+prima_data1' SIZE 5M;

Проверка:

SQL> COLUMN name FORMAT A60 SQL> SELECT file#, name FROM v$datafile;

FILE# NAME ---------- ----------------------------------------------------- 1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMA\SYSTEM01.DBF 2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMA\UNDOTBS01.DBF 3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMA\SYSAUX01.DBF 4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIMA\USERS01.DBF 5 +PRIMA_DATA1/prima/datafile/users2.256.639862169

К новому &#xab;файлу&#xbb; можно обращаться как к обычному, например:

ALTER DATABASE DATAFILE '+PRIMA_DATA1/prima/datafile/users2.256.639862169' RESIZE 10M ;

Но поскольку это может показаться неудобным, для таких названий можно заводить псевдоним (alias). Подробности имеются в документации по Oracle.

&#xab;Файл&#xbb; вполне работоспособен:

CONNECT scott/tiger@prima.class CREATE TABLE emp2 TABLESPACE users2 AS SELECT * FROM emp;

Подготовка и запуск экземпляра СУБД

Поскольку здесь речь идет о Windows, требуется создать службу ОС:

>oradim -new -asmsid +ASM -startmode manual >oradim -edit -asmsid +ASM -startmode auto

Перевод службы в режим автозапуска обсуждаем; главное &#x2013; так, или иначе обеспечивать запуск +ASM ранее экземпляров СУБД, использующих +ASM на компьютере (но после запуска демона cssd).

Далее как обычно:

>set ORACLE_SID=+ASM

>sqlplus / AS SYSDBA

Версия 11 отнеслась более серьезно к разграничению доступа и ввела специальную привилегию SYSASM, так что подключаться начиная с нее следует так:

>sqlplus / AS SYSASM

В SQL*Plus набираем, как обычно:

SQL> STARTUP

Необычным будет результат: легко удостовериться, что СУБД перейдет в состояние STARTED, а не OPEN.

Кроме того, в ответ мы, скорее всего, увидим ошибку. Она вызвана тем, что +ASM не воспринимает наши файлы, как &#xab;дисковые группы&#xbb;; не знает, как их использовать.

Подготовка и запуск службы синхронизации для кластера (CSS)

CSS (Cluster Sychronization Service), как следует из названия, рассчитана на употребление кластерной разновидности БД Oracle (RAC), но эта служба используется и экземпляром ASM на обычном компьютере. Хотя установка CSS на конфигурации RAC выполняется и сложнее, в нашем случае единственного компьютера она проста. В версии 10.1 служба устанавливалась автоматически при установке ПО СУБД (независимо, нужна она пользователю, или не нужна), а с версии 10.2 ее требуется устанавливать самостоятельно.

Выдаем в ОС:

>localconfig reset

На Windows CSS оформлена как служба ОС, но запускать и останавливать ее можно и из командной строки, например:

>ocssd start

О других действиях можно узнать, выдав:

>ocssd --help

Обратите внимание, чтобы имя пользователя ОС не содержало русских букв.

На Unix CSS оформлена как демон cssd. При ближайшем рассмотрении [ORACLE_HOME]/bin, localconfig оказывается сценарием для командной оболочки ОС (.bat или .sh).

Подготовка инфраструктуры

Как и для всякого экземпляра СУБД, для работы +ASM потребуется обеспечить место рабочим файлам. Выдадим в ОС:

>mkdir c:\oracle\product\10.2.0\admin\+asm\bdump >mkdir c:\oracle\product\10.2.0\admin\+asm\cdump >mkdir c:\oracle\product\10.2.0\admin\+asm\udump

Все же +ASM &#x2013; вырожденый экземпляр СУБД, создаваемый по техническим причинам, а не для обслуживания доступа к какой-либо БД, а потому файл с параметрами для него может выглядеть необычно просто, например:

background_dump_dest = c:\oracle\product\10.2.0\admin\+ASM\bdump core_dump_dest = c:\oracle\product\10.2.0\admin\+ASM\cdump user_dump_dest = c:\oracle\product\10.2.0\admin\+ASM\udump

instance_type = ASM compatible = 10.2.0.3.0 # для определенности

large_pool_size = 12M # можно и больше, например, 64M, если потребуется

remote_login_passwordfile = exclusive

_asm_allow_only_raw_disks = FALSE # этот пример без "сырых" устройств asm_diskstring = 'c:\asmdisks\_file*', 'd:\asmdisks\_file*' # возможно групповое указание файлов

Файл размещается по правилам INIT.ORA. Назовем его init+ASM.ora и разместим в %ORACLE_HOME%\database (в Unix &#x2013; в $ORACLE_HOME/dbs).

Неочевидны специальная забота о LARGE_POOL_SIZE и обратное, необязательность SHARED_POOL_SIZE (в нашем случае легко проверить, что SGA_TARGET окажется = 0).

Параметр _ASM_ALLOW_ONLY_RAW_DISKS является недокументированным и неофициальным.

Параметр ASM_DISKSTRING ссылается на созданные предварительно файлы, имитирующие неформатированные устройства для размещения дисковых групп.

Другие возможные параметры, здесь не указанные:

ASM_DISKGROUPS: явно задает дисковые группы, что будут автоматически монтироваться при запуске +ASM или монтировании командой ALTER DISKGROUP ALL MOUNT; ASM_POWER_LIMIT: регулирует автоматическую балансировку загрузки дисковых групп; ASM_PREFERRED_READ_FAILURE_GROUPS (версия 11.1): указывает предпочтителные группы отказа в дисковых группах; DB_UNIQUE_NAME: задает, если необходимо, имя, отличное от умолчательного +ASM.

Подготовка экземпляра +ASM

Для использования обычной СУБД средства ASM требуется дополнительное наличие на компьютере (в случае RAC &#x2013; на каждом узле кластера) хотя бы одного специального экземпляра СУБД. Такие экземпляры могут носить разные имена. Ниже предполагается использование имени +ASM, которое считается умолчательным для этой техники. Вообще, экземпляр можно назвать как угодно, но принято начинать его имя со знака '+'.

Подготовить экземпляр +ASM можно, во-первых, средствами DBCA (для этого в DBCA версии 10 были введены несколько специальных форм), а во-вторых, вручную. Здесь рассматривается именно второй вариант, который в большей степени способен дать понимание происходящему.

Подготовка места для дисковых групп

Если на дисках рабочего компьютера нет неформатированных файловой системой разделов, на которых положено размещать файловые группы для ASM, такие разделы можно промоделировать обычными файлами. Создавать их можно как угодно, но в статьях из интернета с поразительным постоянством приводится метод с использованием Perl. Воспользоваться им нетрудно, так как с версии 10 Perl включен в ORACLE_HOME &#xab;на законных основаниях&#xbb; в подкаталог perl (а до этого он тоже присутствовал в ORACLE_HOME, но более завуалировано). Можно составить примерно следующую программу в файле Createtextfiles.pl:

my $s='0' x 2**20;

open (DF1, ">C:/asmdisks/_file_disk1") die "Cannot create file - $!\n"; open (DF2, ">C:/asmdisks/_file_disk2") die "Cannot create file - $!\n"; open (DF3, ">D:/asmdisks/_file_disk3") die "Cannot create file - $!\n"; open (DF4, ">D:/asmdisks/_file_disk4") die "Cannot create file - $!\n";

for ( my $i = 1; $i < 100; $i++ ) { print DF1 $s; print DF2 $s; print DF3 $s; print DF4 $s; }

exit

Желающие могут отнестись к такому файлу утилитарно, но нетрудно понять, что здесь создаются четыре файла, содержащие 100 раз по 1024*1024 знаков '0', то есть по 100 мегабайтов. Заставить программу проработать можно следующими действиями:

>mkdir c:\asmdisks >mkdir d:\asmdisks >set ORACLE_HOME=c:\oracle\product\10.2.0\db_1 >set PERL_HOME=%ORACLE_HOME%\perl\5.8.3\bin\MSWin32-x86-multi-thread >%PERL_HOME%\perl Createtextfiles.pl

Если на тренировочной машине одно дисковое устройство, все файлы придется разместить на нем.

Пример построения автоматизированного управления дисками (ASM)

,
преподаватель технологий Oracle

	Есть вещи, о которых солдатам надлежит ведать, и есть вещи которых им лучше не знать.
	Отон у Тацита в «Истории»

Проверяем отказоустойчивость

Приведем пример последовательности действий, доказывающей устойчивость такого &#xab;файла&#xbb; к потерям в файловой системе:

CONNECT /@prima.class AS SYSDBA SHUTDOWN IMMEDIATE CONNECT / AS SYSDBA SHUTDOWN HOST del d:\asmdisks\_file_disk3

STARTUP @asm_diskgroup @asm_disks ALTER DISKGROUP prima_data1 MOUNT; @asm_diskgroup @asm_disks CONNECT /@prima.class AS SYSDBA STARTUP SELECT COUNT ( * ) FROM scott.emp2;

Сценарии asm_diskgroup.sql и asm_disks.sql были выданы для диагностики.

Восстановить утерянный файл помогут следующие действия. Воссоздадим файл d:\asmdisks\_file_disk3 программой на Perl указанным выше образом. Затем выполним следующую последовательность действий:

CONNECT /@prima.class AS SYSDBA ALTER TABLESPACE users2 OFFLINE; CONNECT / AS SYSDBA ALTER DISKGROUP prima_data1 DISMOUNT; SHUTDOWN STARTUP ALTER DISKGROUP prima_data1 MOUNT; ALTER DISKGROUP prima_data1 ADD FAILGROUP controller2 DISK 'd:\asmdisks\_file_disk3' ; CONNECT /@prima.class AS SYSDBA ALTER TABLESPACE users2 ONLINE; SELECT COUNT ( * ) FROM scott.emp2;

При необходимости файл можно восстанавливать в любом другом надежном месте. Уточнить происходящее помогает своевременная выдача запросов в asm_diskgroup.sql и asm_disks.sql.

Упражнение. Повторить пример с потерей файлов, удалив, а затем восстановив, сразу два из них: d:\asmdisks\_file_disk3 и d:\asmdisks\_file_disk4.

1Отзывы первопроходцев не все были благоприятными, но уже в версии 10.2 картина улучшилась.

2В интернете есть и другие статьи на эту тему, все вместе удивительно перекликающиеся общими моментами. В то же время [1] содержит ряд обозначений их документации по Oracle. Однако статья [1] является наиболее ясным и полным описанием, встреченным мною.

Создание дисковых групп

Работающий зкземпляр +ASM по наводке параметра СУБД ASM_DISKSTRING распознает разделы, выделенные для дисковых групп:

COLUMN path FORMAT A30 SELECT group_number , disk_number , mount_status , header_status , state , path FROM v$asm_disk . SAVE asm_disks @asm_disks

Чтобы превратить эти разделы в дисковые группы, можно выдать:

CREATE DISKGROUP prima_data1 NORMAL REDUNDANCY FAILGROUP controller1 DISK 'c:\asmdisks\_file_disk1', 'c:\asmdisks\_file_disk2' FAILGROUP controller2 DISK 'd:\asmdisks\_file_disk3', 'd:\asmdisks\_file_disk4' ;

Создана дисковая группа со свойством &#xab;нормальной избыточности&#xbb; (NORMAL REDUNDANCY), запрещающей наличие менее двух групп отказа. Можно было указать HIGH REDUNDANCY (не менее трех групп отказа) или EXTERNAL REDUNDANCY (достаточно одной). Названия групп отказа можно было явно не задавать. Результаты выполненной команды можно наблюдать такими запросами:

COLUMN name FORMAT A15 SELECT group_number , name , total_mb , free_mb, state , type FROM v$asm_diskgroup . SAVE asm_diskgroup @asm_diskgroup @asm_disks

Другие сведения о конфигурации ASM можно посмотреть в нескольких прочих таблицах с именами LIKE 'V$ASM%'.

в Oracle имелось два способа

До версии 10 в Oracle имелось два способа отображения данных БД (табличных пространств, журнальных файлов, контрольного файла) на диск: в файлы ОС и на неформатированные файловой системой разделы. В версии 10 появился третий способ, в определенном смысле промежуточный между первыми двумя, называемый автоматическим управлением дисковой памятью, Automatic Storage Management (ASM). Он предполагает, что из неформатированных разделов диска формируются дисковые группы, внутри которых формируется своего рода облегченный специализированый вариант файловой системы для нужд БД. Управление &#xab;файлами&#xbb; внутри дисковых групп берет на себя облегченный специализированый вариант экземпляра СУБД (экземпляр ASM). Отличительными свойствами такого управления дисковым пространством являются, помимо прочего:
контролируемая избыточность воимя отказоустойчивости; автоматическое перераспределение нагрузки на дисковые устройства.
Файлы, размещением которых можно управлять таким образом, могут быть:
файлами БД, файлами резервирования и восстановления, SPFILE.ORA файлы программ expdp/impdp.
Может показаться, что ASM развивает собой, или даже заменяет введенную в версии 9 возможность OMF. Это не так: оба средства самостоятельны в отношениях друг с другом, и ASM может при желании использоваться совместно с OMF. В то же время ASM дает администратору БД намного больше.
Фирма Oracle прочит ASM в перспективу, и в версии 11, помимо усовершенствований (хотя без радикальных новшеств), добавила в документацию по СУБД отдельную книгу, Storage Administrator's Guide. Основную ценность ASM фирма, судя по заявлениям, видит в применении этого механизма к большим установкам, в первую очередь кластерным (RAC), использующим &#xab;сотни&#xbb; дисковых устройств. Не исключено, что в будущем пользователям Oracle придется иметь дело с ASM как с основным механизмом. Поэтому, несмотря на то, что (а) в сегодняшней практике ASM редкость, и что (б) будучи новой, эта техника, как водится, требует выдержки временем1, разумно присматриваться к ней уже сейчас.
Ниже рассматривается пример организации ASM, доступный для воспроизведения на обычной, не кластерной платформе. Не потребуется даже иметь дело с неформатированными разделами диска, так как в данном случае допускается их имитация обычными файлами ОС. Для определенности, пример относится к Windows. Многие необходимые подправки для Unix очевидны. Более полно материал для Unix превосходно изложен в статье Manually Creating an ASM Instance (Jeff Hunter) (далее [1]), из которой я много позаимствовал2.
Пример приводится для версии 10.2.0.3.0 СУБД.

Аудит доступа к базе данных

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

Аудит изменений в структуре базы данных

В производственной базе данных никому из пользователей никогда не следует изменять структуру схемы. Администраторам баз данных следует вносить изменения в специально отведенное для этого время. Какие-либо другие изменения следует рассматривать как подозрительные. Наблюдение за структурными изменениями может включить индикаторы некорректного использования базы данных.

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

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

Аудит в Oracle разделен на три части:

аудит таких выражений как CREATE TABLE или CREATE SESSION, аудит привилегий ALTER USER и аудит на объект на объектном уровне SELECT TABLE.

Аудит Oracle

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

Детализированный (Fine-grained) аудит

Детализированный аудит решает проблему отслеживания доступа на чтение. Данная возможность основана на внутренних триггерах, срабатывающих, при разборе какой-нибудь части SQL-предложени я. Это очень эффективно, так как SQL-предложени е разбирается единожды для аудита и выполнения. Эта возможность использует предикаты, которые определены и проверяются каждый раз, когда происходит доступ к соответствующим объектам. Fine-grained аудит управляется PL/SQL пакетом который называется DBMS_FGA. Созданная PL/SQL процедура выполняется каждый раз, когда выполняется, соответствующее ей, действие с предикатом. Этот метод позволяет контролировать не только DML-операции на уровне строк и столбцов, но и предложения чтения. Следует предостеречь читателей, в том, что для использования этой возможности необходим некоторый опыт программирования.

Как могут быть проконтролированы пользователи Oracle?

Стандартные команды аудита позволяют контролировать все системные, и объектные привилегии доступа к любым таблицам или представлениям базы данных на select, delete, insert or update. Аудит может быть запущен как для успешных, так и для неуспешных попыток или для тех и других сразу. Как индивидуально для каждого пользователя, так и для всех пользователей сразу, он может выполняться на сессионном уровне или на уровне действия (доступа). На уровне действия - одна запись создается для одного действия, а на сессионном - одна запись для всех контролируемых операций одной сессии.

Какие проблемы возникают с производительностью и сложностью?

Часто аудит воспринимается как сложный и медленный. Причина этому обычное невежество. Если большинство из всех опций включены, тогда получающийся в результате журнал аудита может быть большим и трудным для интерпретации и управления. Кроме того, если аудит задействован на всех таблицах и представлениях базы, то это может повлиять и на производительность. Всякий раз, когда выполняемое действие контролируется аудитом в журнал вносится запись; очевидно, что чем интенсивнее используется аудит, тем больше записей будет записано в системное табличное пространство исключительно для аудита. В некоторых случаях это может привести к удвоению количества записей в базу данных: оригинальная запись и выполняемая для нее запись аудита.

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

Стандартные команды аудита не разрешают контролировать операции на уровне строк. Так же невозможно отслеживать действия привилегированных пользователей, таких как SYS или "as sysdba" до версии Oracle 9iR2.

Когда пользователям Oracle следует подвергаться аудиту?

Простой набор основных действий аудита должен быть активен все время. Необходимый минимум включает в себя отслеживание доступа пользователей, использование системных привилегий и изменение в структуре базы данных. Этот основной набор не покажет неудавшихся попыток доступа к специфическим данным, которые не должны быть доступны; тем не менее, он даст a достаточно простой обзор "некорректного" доступа или использования привилегий. Если служащий подозревается в недозволенных действиях или ожидается атака, тогда может быть применен более детализованный аудит для специфических таблиц. С точки зрения управления БД, аудит изменения данных для всех таблиц не так уж практичен и может повлиять на производительность системы в целом. Аудит доступа для изменения данных следует использовать для таблиц лишь имеющих особо важное значение (например, заработанная плата сотрудников в базе данных HR).

Множественные попытки доступа под различными учетными записями с одного и того же терминала

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

SQL> select count(distinct(username)),terminal 2 from dba_audit_session 3 having count(distinct(username))>1 4 group by terminal SQL> /

COUNT(DISTINCT(USERNAME)) TERMIN ------------------------- ------ 3 pts/1 2 pts/2 3 pts/3 3 pts/5

SQL>

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

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

В следующем примере, настройки аудита были установлены для определения изменений, выполняемых в схеме базы данных. Сюда можно отнести создание новых объектов или попытки изменения уже существующих.

Простой SQL, приведенный ниже, покажет любые сведения из журнала аудита, имеющие отношения к созданным или измененным объектам: col username for a8 col priv_used for a16 col obj_name for a22 col timestamp for a17 col returncode for 9999 select username, priv_used, obj_name, to_char(timestamp,'DD-MON-YYYY HH24:MI')
timestamp, returncode from dba_audit_trail where priv_used is not null and priv_used<>'CREATE SESSION' / SQL> @check_obj.sql

ZULIA CREATE TABLE STEAL_SALARY 09-APR-2003 20:07 0 PETE CREATE PROCEDURE HACK 09-APR-2003 20:42 0

Этот пример показывает, что пользователь ZULIA создал таблицу, а пользователь PETE писал PL/SQL процедуру. Любые изменения такого рода, в производственной базе данных, должны быть исследованы. Намного более специфичные злодеяния могут быть обнаружены в отношении изменений объектов и схемы, но в целом, пользователи не должны иметь возможности менять что-либо в производственной базе данных. И как результат, проверка может остаться чисто символической.

Некоторые примеры

Из-за большого количества возможностей, настройка аудита базы данных Oracle может показаться довольно обескураживающим мероприятием. В порядке упрощения и конкретизации цели, обозначим две задачи, которые нам предстоит исследовать и проработать.

Неудачные попытки входа

Они могут означать попытки атакующего получить неавторизованный доступ в базу данных. Нижеследующий SQL ярко демонстрирует это: SQL> select count(*),username,terminal,to_char
(timestamp,'DD-MON-YYYY') 2 from dba_audit_session 3 where returncode<>0 4 group by username,terminal,to_char
(timestamp,'DD-MON-YYYY');

COUNT(*) USERNAME TERMIN TO_CHAR(TIM ---------- --------------- ------ ----------- 1 BILL pts/3 09-APR-2003 3 FRED pts/3 09-APR-2003 4 ZULIA pts/1 09-APR-2003

SQL>

Здесь можно заметить два возможных злоупотребления, первое – это то, что пользователь Zulia пытается войти в систему и получает отказ четыре раза в один и тот же день. Возможно, пользователь забыл свой пароль или может быть кто-то пытается его угадать. Если изменить SQL, как показано ниже, то это даст более детальную информацию: SQL> select count(*),username,terminal,to_char
(timestamp,'DD-MON-YYYY'),returncode 2 from dba_audit_session 3 group by username,terminal,to_char
(timestamp,'DD-MON-YYYY')
,returncode;

COUNT(*) USERNAME TERMIN TO_CHAR(TIM RETURNCODE ---------- ------------ ------ ----------- ---------- 1 BILL pts/3 09-APR-2003 1017 1 EMIL pts/1 09-APR-2003 0 1 EMIL pts/2 09-APR-2003 0 1 EMIL pts/3 09-APR-2003 0 1 EMIL pts/4 09-APR-2003 0 3 FRED pts/3 09-APR-2003 1017 3 SYS pts/1 09-APR-2003 0 1 SYS pts/2 09-APR-2003 0 1 SYSTEM pts/5 09-APR-2003 0 4 ZULIA pts/1 09-APR-2003 1017 1 ZULIA pts/1 09-APR-2003 0

11 rows selected.

SQL>

Отчет показывает, что пользователь успешно вошел в систему с этого же терминала, в этот же день. Проверяйте число неудачных попыток входа в систему каждый день. Те пользователи, для которых, число неудачных попыток входа превышает пороговое значение, должны быть изучены.

Основная конфигурация

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

Аудит включается для записи в базу данных добавлением следующей строки в файле init.ora. Символьная связь к нему обычно может быть найдена в $ORACLE_HOME/dbs

audit_trail = db

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

SQL> select name,value from v$parameter 2 where name like 'audit%';

NAME VALUE ------------------------------ ---------- audit_trail DB audit_file_dest ?/rdbms/audit

SQL>

Но контролируемые действия не отслеживаются до тех пор, пока эти действия не заданы явно; это верно, кроме случаев привилегированного доступа к базе данных, запуска и останова базы данных, структурных изменений, таких как добавление файла данных. Эти действия отслеживаются в файле операционной системы в $ORACLE_HOME/rdbms/audit до тех пор пока audit_file_dest не переопределено в файле init.ora. В Windows эти события появляются в Event Viewer.

Для того, что бы проверить наличие того, что какие-нибудь привилегии или выражения уже используются для аудита, сделайте следующее:

SQL> select * from dba_stmt_audit_opts 2 union 3 select * from dba_priv_audit_opts;

no rows selected

SQL>

Что бы найти какие объекты уже контролируются аудитом, запросите представление dba_obj_audit_opts.

Попытки доступа несуществующих пользователей в базу данных

Одно интересное дополнение к приведенному выше SQL позволяет отыскать попытки входа в систему под несуществующим пользователем. В этом случае тоже будут созданы записи аудита. Следующий SQL иллюстрирует это:

SQL> select username,terminal,to_char
(timestamp,'DD-MON-YYYY HH24:MI:SS') 2 from dba_audit_session 3 where returncode<>0 4 and not exists (select 'x' 5 from dba_users 6 where dba_users.username=
dba_audit_session.username) SQL> /

USERNAME TERMIN TO_CHAR(TIMESTAMP,'D --------------- ------ -------------------- FRED pts/3 09-APR-2003 17:31:47 FRED pts/3 09-APR-2003 17:32:02 FRED pts/3 09-APR-2003 17:32:15 BILL pts/3 09-APR-2003 17:33:01

SQL>

Возможно это тоже злонамеренные действия. Все попытки войти в систему под несуществующим пользователем следует проверять и расследовать каждый день.

Попытки доступа в базу данных в необычное время

Следует выполнять проверки попыток доступа в базу данных во внерабочие часы. Им может оказаться обычная сверхурочная работа, но также легко - неавторизованный доступ. Его можно проверить следующим выражением: SQL> select username, 2 terminal, 3 action_name, 4 returncode, 5 to_char(timestamp,'DD-MON-YYYY
HH24:MI:SS'), 6 to_char(logoff_time,'DD-MON-YYYY
HH24:MI:SS') 7 from dba_audit_session 8 where to_date(to_char(timestamp,
'HH24:MI:SS'),'HH24:MI:SS') < to_date('08:00:00','HH24:MI:SS') 9 or to_date(to_char(timestamp,
'HH24:MI:SS'),'HH24:MI:SS') > to_date('19:30:00','HH24:MI:SS') SQL> /

USERNAME TERMIN ACTION_N RETURNCODE TO_CHAR(TIMESTAMP,'D TO_CHAR(LOGOFF_TIME, -------- ------ -------- ---------- -------------------- -------------------- SYS pts/1 LOGOFF 0 09-APR-2003 20:10:46 09-APR-2003 20:16:41 SYSTEM pts/5 LOGOFF 0 09-APR-2003 21:49:20 09-APR-2003 21:49:50 ZULIA pts/5 LOGON 0 09-APR-2003 21:49:50 EMIL APOLLO LOGON 0 09-APR-2003 22:49:12

SQL>

Приведенные выше SQL показывает любые соединения до 8:00 утра и после 7:30 вечера. Любые соединения, особенно те, которые выполнены привилегированными пользователями, такими как SYS или SYSTEM, должны быть исследованы. Особенное внимание следует обратить на то, откуда был произведен доступ. Например, если привилегированный доступ выполнен с машины, которая не находится в отделе администратора, администратор должен выяснить зачем он производился.

Проверка пользователей, которые используют общую учетную запись в базе данных

Следующее выражение SQL ищет пользователей, которые потенциально могут использовать общую учетную запись в базе данных:

SQL> select count(distinct(terminal)),username 2 from dba_audit_session 3 having count(distinct(terminal))>1 4 group by username SQL> /

COUNT(DISTINCT(TERMINAL)) USERNAME ------------------------- ---------- 4 EMIL 3 SYS 3 ZULIA SQL>

Здесь показано, что три пользователя входили в систему более чем с одного места. Дальнейшая проверка может показать время, что бы увидеть работали ли они одновременно. Установите временной интервал для данной проверки один день. Приведенный выше SQL показывает лишь направление исследования, без лишних сложностей. И вновь, обнаруженные учетные записи должны быть изучены дополнительно.

Рабочие примеры

Давайте сейчас проработаем два примера и посмотрим, что можно изучить. Для начала включите аудит для попыток доступа к базе данных: SQL> audit create session; Audit succeeded. SQL>

Приведенная команда будет отслеживать доступ всех пользователей, независимо от того успешен он или нет. [by access] это действительное умолчание для данной команды.

Заметка: Формат всех команд аудита по документации Oracle выглядит следующим образом:

audit {statement_option|privilege_option}
[by user] [by {session|access}] [ whenever
{successful|unsuccessful}]

Обязательными являются только лишь statement_option и privilege_option части выражения. Другие части являются опционными и их использование позволяет сделать аудит более специфичным.

Что бы пользователь мог задать команду аудита, необходимым условием для него является наличие привилегии "AUDIT SYSTEM". Найти пользователей, которые имеют эту привилегию, можно выполнив следующее: SQL> select * 2 from dba_sys_privs 3 where privilege like '%AUDIT%';

GRANTEE PRIVILEGE ADM ------------------------- ----------------------- CTXSYS AUDIT ANY NO CTXSYS AUDIT SYSTEM NO DBA AUDIT ANY YES DB AUDIT SYSTEM YES IMP_FULL_DATABASE AUDIT ANY NO MDSYS AUDIT ANY YES MDSYS AUDIT SYSTEM YES WKSYS AUDIT ANY NO WKSYS AUDIT SYSTEM NO

9 rows selected.

SQL>

Выше приведенные результаты принадлежат базе данных Oracle 9i. Пользователи по умолчанию MDSYS, CTXSYS и WKSYS были бы неплохой мишенью для атакующего, так как любые действия аудита могут быть выключены любым из этих пользователей, что бы скрыть любые предпринятые действия.

Теперь аудит будет отслеживать все попытки доступа, и нам необходимо подождать, когда какие-нибудь пользователи войдут в систему что бы выполнить свою работу. И пока они будут делать это, давайте установим аудит, для контроля изменений в схеме. Для краткости, не все изменения объектов схемы будем отслеживать, в этом примере. Хотя в принципе, можно отслеживать изменения любых объектов БД: таблиц, индексов, кластеров, представлений, последовательностей, процедур, триггеров, библиотек и т.д. В этом примере аудит будет включен на выборочной группе объектов. Настройка аудита может быть выполнена за два этапа, создание команд аудита и запуск на исполнение, как показано ниже: set head off set feed off set pages 0 spool aud.lis select 'audit 'name';' from system_privilege_map where (name like 'CREATE%TABLE%' or name like 'CREATE%INDEX%' or name like 'CREATE%CLUSTER%' or name like 'CREATE%SEQUENCE%' or name like 'CREATE%PROCEDURE%' or name like 'CREATE%TRIGGER%' or name like 'CREATE%LIBRARY%') union select 'audit 'name';' from system_privilege_map where (name like 'ALTER%TABLE%' or name like 'ALTER%INDEX%' or name like 'ALTER%CLUSTER%' or name like 'ALTER%SEQUENCE%' or name like 'ALTER%PROCEDURE%' or name like 'ALTER%TRIGGER%' or name like 'ALTER%LIBRARY%') union select 'audit 'name';' from system_privilege_map where (name like 'DROP%TABLE%' or name like 'DROP%INDEX%' or name like 'DROP%CLUSTER%' or name like 'DROP%SEQUENCE%' or name like 'DROP%PROCEDURE%' or name like 'DROP%TRIGGER%' or name like 'DROP%LIBRARY%') union select 'audit 'name';' from system_privilege_map where (name like 'EXECUTE%INDEX%' or name like 'EXECUTE%PROCEDURE%' or name like 'EXECUTE%LIBRARY%') / spool off @@aud.lis

Данный скрипт выведет набор команд аудита в спул файл, который затем запустится для выполнения команд аудита.

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

Сейчас, когда все примеры аудита только что включены, установки могут быть просмотрены при помощи этого SQL: 1 select audit_option,success,failure 2 from dba_stmt_audit_opts 3 union 4 select privilege,success,failure 5* from dba_priv_audit_opts SQL> /

AUDIT_OPTION SUCCESS FAILURE --------------------------- ---------- ---------- ALTER ANY CLUSTER BY ACCESS BY ACCESS ALTER ANY INDEX BY ACCESS BY ACCESS ALTER ANY INDEXTYPE BY ACCESS BY ACCESS ALTER ANY LIBRARY BY ACCESS BY ACCESS ? EXECUTE ANY LIBRARY BY SESSION BY SESSION EXECUTE ANY PROCEDURE BY SESSION BY SESSION

38 rows selected.

SQL>

Каждый раз, когда пользователь пытается что-нибудь затронуть в базе данных, на что включен аудит, ядро Oracle проверяет действие и создает или обновляет (в случае одной записи для сессии) запись в таблице AUD$, владельцем которой является пользователь SYS. Эта таблица по умолчанию находится в табличном пространстве SYSTEM. Кстати говоря, это само по себе может стать причиной проблемы при атаке – отказ в обслуживании. Если табличное пространство SYSTEM заполнится, то база данных зависнет.

AUD$ - особенная таблица [словаря данных], так как только из нее пользователь SYS имеет право удалять из нее записи. Если журнал аудита включен и пишется в базу данных, то число записей в этой таблице необходимо внимательно контролировать что бы удостовериться что она не растет слишком быстро, и не заполнила все системное табличное пространство. Стратегия очистки нуждается в адаптации, что бы сохранять размер таблицы и если необходимо архивировать записи журнала аудита для будущего использования. Одна из тактик может состоять в том, что бы копировать записи в итоговую таблицу, созданную для выполнения спецпроверки с целью выявления злоупотреблений. Эти итоговые таблицы могут располагаться в отдельной базе данных для увеличения защищенности. После копирования таблица sys.aud$ может быть усечена.

Таблицу SYS.AUD$ можно передвинуть в табличное пространство, отличное от SYSTEM, но сначала, уточните это в технической поддержке Oracle, так как это действие может более не поддерживаться.

Только пользователи, которым предоставлен специальный доступ к таблице SYS.AUD$ могут читать, изменять и удалять данные из нее. По умолчанию этими правами владеет SYS, но эти действия может выполнять любой другой пользователь, наделенный необходимыми правами. Существуют две специальные роли, которым разрешен доступ к таблице SYS.AUD$ на select и delete, это роли DELETE_CATALOG_ROLE и SELECT_CATALOG_ROLE. Эти роли не следует предоставлять обычным пользователям.

Возвращаясь к примерам, наши пользователи уже вошли в систему и проработали целый день и создали некоторые записи аудита. Эти записи могут быть просмотрены различными способами:



Путем выборки записей из SYS.AUD$ - Это исходный журнал аудита Путем выборки записей из dba_audit_trail – Это представление DBA показывающее исходный журнал аудита. Путем выборки записей из dba_audit_session – Это представление показывает только лишь события входа и выхода.

Простое SQL-предложение может показать попытки соединения в деталях: SQL> get check_create_session 1 -- 2 -- check_create_session.sql 3 -- 4 col username for a15 5 col terminal for a6 6 col timestamp for a15 7 col logoff_time for a15 8 col action_name for a8 9 col returncode for 9999 10 select username, 11 terminal, 12 action_name, 13 to_char(timestamp,'DDMMYYYY:HHMISS') timestamp, 14 to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time, 15 returncode 16* from dba_audit_session SQL> / USERNAME TERMIN ACTION_N TIMESTAMP LOGOFF_TIME RETURNCODE ----------- ------ -------- --------------- --------------- -------- SYS pts/1 LOGOFF 09042003:051046 09042003:051641 0 ZULIA pts/1 LOGON 09042003:051641 1017 SYS pts/1 LOGOFF 09042003:051649 09042003:053032 0 SYS pts/2 LOGOFF 09042003:052622 09042003:053408 0 ZULIA pts/1 LOGON 09042003:053032 1017

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

Системные триггеры

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

Системные журналы

СУБД Oracle генерирует много журнальных файлов, и многие из них могут содержать полезную информацию для проведения аудита. Например, alert log используется для записи информации о запуске и останове базы, а также о вносимых структурных изменениях, таких как добавление файла данных в базу.

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

Сложные вопросы

Существует несколько основных вопросов, на которые необходимо ответить, при принятии решения об использовании средств аудита Oracle. Такие как:

Ссылки

Oracle security step-by-step -A survival guide for Oracle security, Pete Finnigan 2003, published by SANS Institute Oracle security handbook - Aaron Newman and Marlene Theriault, published by Oracle Press.

Pete Finnigan - автор ранее изданной книги "Oracle security step-by-step - A survival guide to Oracle security" опубликованную в 2003 институтом SANS (см. http://store.sans.org). Pete Finnigan - основатель и CTO PeteFinnigan.com Limited (

http://www.petefinnigan.com), компании в UK, которая специализируется по вопросам безопасности Oracle.

Update, delete и insert триггеры

Это “вторая линия обороны”, которая позволяет понять действия пользователей на более детальном уровне. Для того, что бы отслеживать изменения в базе на уровне столбца и строки, можно написать триггеры, которые позволят полностью сохранять данные, до или после выполненного действия. Использование этого типа контроля очень ресурсоемко, так как создается и хранится много дополнительных записей. Кроме того, что существует еще один недостаток, связанный с этим методом - доступ на чтение нельзя отследить с помощью обычных триггеров базы данных.

Возможности аудита Oracle

Задачу аудита базы данных Oracle не следует ограничивать только лишь использованием команд аудита; так же успешно могут быть применены и другие технологии. Приведем некоторые основные методы, которые могут быть использованы для аудита базы данных Oracle:

Вступление

В данной статье читателю дается возможность получить представление об основах аудита баз данных Oracle. СУБД Oracle - функционально развитый продукт, и в нем существует несколько возможностей проведения аудита (доступных читателю). Но так как аудит Oracle это довольно широкая тема, и ее описание по праву заняло бы целую книгу, то мы затронем лишь основы того, как, зачем и когда использовать аудит. Далее будут показаны два примера, демонстрирующие, насколько может быть полезен аудит Oracle в вашей организации.

Все примеры SQL приведенные в этом документе могут быть скачены с авторского веб сайта по адресу http://www.petefinnigan.com/papers/audit.sql

Зачем аудит нужен в Oracle?

Странный вопрос? Тем не менее, многие компании, в действительности, не используют средства внутреннего аудита Oracle. А когда и пытаются использовать, то заваливаются предложенным выбором. Они включают все подряд для полноты контроля, затем, видя, что в отчете слишком много информации для прочтения и изучения - быстро снова его выключают. Это типично для использования фаерволов, систем обнаружения вторжения (IDS) или других инструментов информационной безопасности, созданных для обнаружения нападений на сеть или операционную систему. Так почему бы не отслеживать то, что пользователи делают с "сокровищами короны" организации - данными. Аудит Oracle может помочь в определении неавторизованного доступа или внутреннего злоупотребления по отношению к информации содержащейся в базе данных.

Аудит Oracle очень мощное средство

Аудит Oracle очень мощное средство и иногда кажется довольно сложным. Как мы увидели во вступлении, существует более чем одна опция доступная для аудита базы данных Oracle. В СУБД Oracle существует возможность контролировать почти все с помощью стандартных команд, но не на строковом уровне. Если вам необходим высоко-уровневый аудит используйте стандартные функции что бы посмотреть общую активность, и затем рассмотреть исследуйте нужное в более мелких деталях.
Так как возможно контролировать почти все типы действий в базе данных Oracle, используя стандартные функции аудита, читателю следует поэкспериментировать, что бы выбрать наиболее полезные настройки аудита для их организации. Сохраняйте его простым и не старайтесь использовать все подряд. Кроме всего прочего, предопределите какие данные будут созданы в журнале аудита и какие злонамеренные действия с их помощью можно выявить. Напишите отчеты, что бы проверить журнал аудита и очищайте его регулярно. Анализируйте данные этих отчетов каждый день и предпринимайте соответствующие действия.
Для более детального аудита, используйте триггеры базы данных и мелко-уровневый(fine grained) аудит. Помните, что для использования и реализации этих методов необходим опыт программирования, так что они должны быть внимательно рассмотрены. Много полезной информации может быть собрано без аудита, на строковом уровне. Кроме всего прочего, внедрите принцип наименьших привилегий, что бы избегнуть изменений и чтений данных пользователями, для которых они не предназначены.

Защита базы данных от рассмотренных злонамеренных действий

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

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

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

Важно, чтобы настройки аудита планировались с точки зрения производительности и удобства использования. Журнал аудита также должен был управляем.

Не менее важно то, что данные журнала аудита можно описывать в категориях защиты информации.

Последняя книга автора выпущенная SANS Institute “Безопасность Oracle шаг за шагом- руководство выживания для службы безопасности Oracle ”("Oracle security step- by-step - A survival guide for Oracle security" ) дает отличное руководство как сконфигурировать Oracle безопасным.

Аудит в XML-формате

Источник:

Оригинал: Auditing in XML, By Arup Nanda. Oracle Magazine, January-February 2006

Дополнительная защита

Естественно, для повышения контролируемости, вы хотите "уплотнить" защиту инфраструктуры аудита. Вышеизложенная процедура начальной установки имеет одну потенциальную проблему – любой, имеющий системную привилегию выполнения поставляемого пакета UTL_FILE, может удалить файл журнала аудита из файловой системы ОС, используя процедуру FREMOVE. Чтобы снизить этот риск, вы можете ограничить эти возможности:

аннулировав эту привилегию у группы пользователей PUBLIC; аннулировав системную привилегию CREATE DIRECTORY у группы пользователей PUBLIC.

Первый вариант – несколько радикальный, но он представляет собой надежный способ снижения риска. Второй вариант является, вероятно, более практичным. Для удаления этого файла пользователи должны иметь доступ к данному каталогу ОС или возможность создания объектов базы данных типа DIRECTORY (каталог). Если вы у группы пользователей PUBLIC отзовете системную привилегию CREATE DIRECTORY, то только пользователи с ролью DBA смогут создавать каталоги в том каталоге ОС, в котором находится журнал аудита, но не обычные пользователи. Если они не могут создать каталог, они не смогут и удалить файл, используя пакет UTL_FILE. В любом случае, в методах передовой практики считается, что системную привилегию CREATE DIRECTORY следует отозвать у группы пользователей PUBLIC.

Пользователь ОС, который владеет программным обеспечением Oracle, владеет и файлами XML-аудита, поэтому любой, кто имеет права доступа к серверу и к этой учетной записи пользователя ОС, может удалить эти файлы. Тем не менее, ограничивая привилегии, вы можете добиться приемлемого уровня безопасности.

В сервере Oracle предлагается и другой тип аудита. При этом аудите журнал аудита пишется в системные журналы ОС (system logs, syslogs), которые принадлежат привилегированному пользователю (такому, как "root" в ОС UNIX) и не могут удаляться другими пользователями, включая владельца программного обеспечения Oracle.

Начальная настройка

По умолчанию аудит в сервере Oracle Database 10g Release 2 не включен. Для его включения и записи данных аудита в XML-формате нужно только вставить в файл параметров инициализации следующую строку:

AUDIT_TRAIL = XML

Это – статический параметр, поэтому, чтобы он начал действовать, необходимо перезапустить экземпляр сервера базы данных.

Подготовим для этой статьи демонстрационные данные, выполнив с привилегиями SYSDBA следующие операторы:

SQL> CREATE USER bank IDENTIFIED BY bank; SQL> GRANT CONNECT, RESOURCE TO bank; SQL> CONNECT bank/bank SQL> CREATE TABLE accounts (accno NUMBER); SQL> GRANT SELECT ON accounts TO SCOTT; SQL> INSERT INTO accounts VALUES (104);

Затем включим аудит созданной таблицы. Здесь мы хотим выполнять аудит всех, кто выбирает данные из таблицы ACCOUNTS (банковские счета), находящейся в схеме BANK (банк). Для этого выполним:

AUDIT SELECT ON bank.accounts;

Этот оператор может выполнить (и включить аудит этой таблицы) пользователь BANK (владелец таблицы) или любой другой пользователь с системной привилегией AUDIT ANY. После этого шага, когда любой пользователь, имеющий объектную привилегию SELECT на эту таблицу, выбирает из нее что-нибудь, этот факт регистрируется в журнале аудита. Например, если пользователь SCOTT подключается к системе базы данных и выбирает что-то из этой таблицы, выполняя:

CONNECT scott/tiger ... SELECT * FROM bank.accounts WHERE accno = 104;

Этот оператор SELECT генерирует запись аудита. Параметр AUDIT_TRAIL имеет значение "XML", поэтому запись генерируется в XML-формате.

Файлы журнала аудита записываются в каталог, указанный в параметре инициализации AUDIT_FILE_DEST, значение по умолчанию которого –$ORACLE_BASE/admin/$ORACLE_SID/adump. Вы можете динамически изменить это местоположение, не перезапуская экземпляр сервера базы данных. Если вы хотите создавать эти файлы в другом каталоге, таком, например, как /audit_trail, выполните следующий оператор (как SYSDBA):

ALTER SYSTEM SET AUDIT_FILE_DEST = '/audit_trail' DEFERRED;

После выполнения этого оператора записи аудита для вновь создаваемых сеансов будут поступать в указанный каталог.

Просмотр файлов аудита в реляционном представлении

XML-файлы, записанные средствами аудита, – обычные файлы ОС, которые можно рассматривать любой программой просмотра XML-документов, но вы можете пожалеть о старом знакомом журнале аудита в таблице базы данных, с которым можно было работать, используя SQL-операторы. Не беспокойтесь, вы по-прежнему можете использовать обычный SQL для выполнения запросов к данным, записанным в XML-файлы журнала аудита. Содержимое этих файлов показывает новое представление словаря данных V$XML_AUDIT_TRAIL. Для того чтобы увидеть информацию аудита, вы можете выбрать все столбцы этого представления:

SELECT * FROM

V$XML_AUDIT_TRAIL;

Результат этого запроса для облегчения просмотра показан на листинге 3 в вертикальном формате. Обратите внимание, если бы были множественные записи XML-аудита, вы видели бы в этом представлении одну запись для каждой записи XML-аудита. Имена всех столбцов представления совпадают с именами тегов в XML-файле журнала аудита; например, тег <DB_User> в файле показывается в представлении как столбец DB_USER. Отметка времени показывается в столбце EXTENDED_TIMESTAMP, но время показывается в часовом поясе местного времени, а не в поясе UTC, как в файле XML-аудита. Столбцы, которые не заполнены в XML-файле, имеют в представлении значение NULL.

SELECT * FROM V$XML_AUDIT_TRAIL;

AUDIT_TYPE : 1 SESSION_ID : 108844 PROXY_SESSIONID : 0 STATEMENTID : 10 ENTRYID : 1 EXTENDED_TIMESTAMP : 10-OCT-05 06.26.18.720548 PM -04:00 GLOBAL_UID : DB_USER : SCOTT CLIENTIDENTIFIER : EXT_NAME : OS_USER : oracle OS_HOST : prolin1 OS_PROCESS : 22584 TERMINAL : pts/3 INSTANCE_NUMBER : 0 OBJECT_SCHEMA : BANK OBJECT_NAME : ACCOUNTS POLICY_NAME : NEW_OWNER : NEW_NAME : ACTION : 103 STATEMENT_TYPE : 0 TRANSACTIONID : RETURNCODE : 0 SCN : 6447496045 COMMENT_TEXT : AUTH_PRIVILEGES : GRANTEE : PRIV_USED : 0 SES_ACTIONS : ---------S------ OS_PRIVILEGE : ECONTEXT_ID : SQL_BIND : #1(3):107 SQL_TEXT : select * from bank.accounts where accno = :i

Листинг 3.Содержимое V$XML_AUDIT_TRAIL

Проверка журнала аудита

Теперь, когда вы знаете, где генерируются записи аудита, вы можете проверить журнал аудита. Это будет XML-файл в каталоге, который указан в параметре инициализации AUDIT_FILE_DEST. Файл, сгенерированный выполненным действием (оператор SELECT), показан на листинге 1. Давайте рассмотрим, как интерпретировать его.

<?xml version="1.0" encoding="UTF-8" ?> <Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd" xmlns: xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ oracleas/schema/dbserver_audittrail-10_2.xsd"> <Version>10.2</Version> <AuditRecord> <Audit_Type>1</Audit_Type> <Session_Id>108802</Session_Id> <StatementId>9</StatementId> <EntryId>1</EntryId> <Extended_Timestamp>2005-10-09T00:20:02.284327</Extended_Timestamp> <DB_User>SCOTT</DB_User> <OS_User>oracle</OS_User> <Userhost>prolin1</Userhost> <OS_Process>22158</OS_Process> <Terminal>pts/3</Terminal> <Instance_Number>0</Instance_Number> <Object_Schema>BANK</Object_Schema> <Object_Name>ACCOUNTS</Object_Name> <Action>103</Action> <Returncode>0</Returncode> <Scn>6447392335</Scn> <SesActions>---------S------</SesActions> </AuditRecord>

</Audit>

Листинг 1.Журнал аудита в XML-формате.

Запись в журнал аудита осуществляется в обычном XML-стиле:

<Audit> <Audit_Record> <Audit_Type>... <Session_Id>... <StatementId>... <EntryId>... <Extended_Timestamp>... <DB_User>... <OS_User>... <Userhost>... <OS_Process>... <Terminal>... <Instance_Number>... <Object_Schema>... <Object_Name>... <Action>... <Returncode>... <Scn>... <SesActions>... </Audit_Record>

</Audit>

Запись аудита содержится внутри тегов <Audit_Record> и </Audit_Record> вместе со специальными тегами, показывающим детальную информацию, которая находится в записи. Например, тег <DB_User> показывает пользователя базы данных, который инициировал действие, сгенерировавшее эту запись. Если сеанс инициировал более одного действия, то в файле аудита будут показаны наборы деталей каждого действия, заключенные в теги <Audit_Record>.

Первый тег каждого набора – <Audit_Type>, который указывает тип записи аудита. На значение <Audit_Type> – 1, которое указывает на обычный аудит в XML-формате. Вы можете также использовать XML-формат для детального аудита, в этом случае тег покажет значение 2. Если вы включите аудит SYS-операций (по умолчанию аудит этих операций не выполняется), установив в параметре инициализации AUDIT_SYS_OPERATIONS значение TRUE, то этот тег покажет значение 4. Наконец, обязательные (mandatory) записи аудита в XML-формате показываются значением 8. Примеры обязательных записей аудита: записи о запуске и остановке экземпляра сервера базы данных, генерируемые независимо от значения параметра инициализации AUDIT_TRAIL. Все записи журнала аудита в XML-формате содержат этот тег; он помогает дифференцировать типы этих записей.

Следующий тег, <Session_Id>, показывает идентификатор сеанса (не системный идентификатор экземпляра сервера базы данных SID), который сгенерировал эту запись аудита. Обратите внимание, вы можете увидеть этот идентификатор в столбце AUDSID представления V$SESSION: SELECT AUDSID FROM V$SESSION WHERE SID = <SID>;

В одном и том же сеансе пользователь мог выполнять множественные операторы, идентификатор каждого из которых показывается отдельным тегом <StatementId>. Время регистрации записи аудита показывается в теге <Extended_Timestamp>. Обратите внимание, на время регистрации: 2005-10-09T00:20:02.284327. Время показывается не в часовом поясе местного времени, а в поясе UTC (Universal Time Coordinated, универсальное синхронизированное время, также называемое среднем временем по Гринвичу (GMT)); поэтому формат отметки времени кажется странным.

Остальная часть тегов показывает пользователя, который выполнял действия, и другие существенные детали этих действий. Теги <DB_User>, <OS_User>, <Userhost>, <OS_Process>, <Terminal>, <Instance_Number>, <Object_Schema>, <Object_Name> и <Action> показывают соответственно имя пользователя базы данных, имя пользователя ОС, имя хост-машины, к которой подключен пользователь, идентификатор процесса ОС, идентификатор терминала пользователя, номер экземпляра, к которому подключен пользователь (в среде Oracle Real Application Clusters), владелец таблицы, с которой работает пользователь, имя этой таблицы и числовой код типа действия.



Действие, аудит которого показан на , выполнилось успешно, поэтому тег кода возврата <Returncode> показывает значение 0. Обратите внимание, если бы это действие выполнилось неудачно, был бы показан номер ошибки сервера Oracle. Например, если бы вы попытались удалить несуществующую таблицу, вы получили бы ошибку ORA-00955 и тег <Returncode> показывал бы число 955.

Это успешное действие было выполнено, когда системный номер изменения SCN (system change number) был равен 6447392335, как показывает тег <Scn>. Это очень полезно в ретроспективных запросах для выяснения значений столбцов в определенный момент времени. Например, предположим, значение столбца BALANCE (остаток на счете) за прошедший период времени значительно изменилась. Как вы можете узнать точное значение, которое видел пользователь? Вы могли бы использовать ретроспективный запрос и увидеть значение столбца BALANCE , каким оно было во время этого SCN:

SELECT balance FROM accounts AS OF SCN 6447392335 WHERE accno = 104;

Тег <SesActions> показывает действия, выполненные в сеансе. В теге содержится строка длиной 16 символов, из которых важными являются первые 12. Он показывает результат действий, выполненных пользователем; в каждой позиции показывается результат выполнения определенного действия: Alter (изменение), Audit (аудит), Comment (примечание), Delete (удаление), Grant (предоставление), Index (индексирование), Insert (вставка), Lock (блокирование), Rename (переименование), Select (выборка), Update (обновление) и Flashback (ретроспективная операция).

Например, на значение тега <SesActions> – "----------S------", где S (Success) в 10-й позиции указывает на успешное выполнение действия SELECT. Это означает, что пользователь SCOTT выполнил в сеансе одно или более успешных действий SELECT и больше никаких других действий, подлежащих аудиту.

Если пользователь SCOTT также выполнил бы в этом сеансе успешное действие ALTER, в первой позиции вместо "-" появилась бы буква S. Если бы при выполнении показанного выше оператора SELECT произошел сбой, то в 10-й позиции вместо буквы S появилась бы буква F (Failure). Если бы пользователь SCOTT выполнил более одного оператора SELECT, и при выполнении некоторых из них происходили сбои, тогда как другие были выполнены успешно, то 10-й позиции появилась бы буква B (Both), указывая как на успешное, так и на неуспешное выполнение действий.

Расширенный аудит

Запись аудита на показывает действие (оператор SELECT) и объект, над которым было выполнено это действие (таблица ACCOUNTS). Однако она не показывает сам SQL-оператор, который выполнил пользователь SCOTT. В механизме аудита есть также средства расширения его функциональных возможностей, позволяющие записывать текст этих SQL-операторов. Для их включения установите в файле параметров инициализации следующий параметр и перезапустите экземпляр сервера базы данных.

AUDIT_TRAIL = XML, EXTENDED

После включения расширенного аудита пользователь SCOTT выполнил другой оператор SELECT:

SELECT * FROM accounts WHERE accno = :i;

Сгенерированный XML-файл показан на . Он содержит два дополнительных элемента, которые не входили в файл обычного аудита, показанный на :

<Sql_Bind>#1(3):107</Sql_Bind> и

<Sql_Text>select * from bank.accounts where accno = :i</Sql_Text>.

<?xml version="1.0" encoding="UTF-8" ?> <Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd" xmlns: xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ oracleas/schema/dbserver_audittrail-10_2.xsd"> <Version>10.2</Version> <AuditRecord>

<Audit_Type>1</Audit_Type> <Session_Id>108844</Session_Id> <StatementId>10</StatementId> <EntryId>1</EntryId> <Extended_Timestamp>2005-10-10T18:26:18.720548</Extended_Timestamp> <DB_User>SCOTT</DB_User> <OS_User>oracle</OS_User> <Userhost>prolin1</Userhost> <OS_Process>22584</OS_Process> <Terminal>pts/3</Terminal> <Instance_Number>0</Instance_Number> <Object_Schema>BANK</Object_Schema> <Object_Name>ACCOUNTS</Object_Name> <Action>103</Action> <Returncode>0</Returncode> <Scn>6447496045</Scn> <SesActions>---------S------</SesActions> <Sql_Bind>#1(3):107</Sql_Bind> <Sql_Text>select * from bank.accounts where accno = :i</Sql_Text> </AuditRecord>

</Audit>

<Sql_Bind>#1(3):107</Sql_Bind> <Sql_Text>select * from bank.accounts where accno = :i</Sql_Text>

Листинг 2. Расширенный XML-формат.

Тег <Sql_Text> показывает текст фактического SQL-оператора, выполненного пользователем SCOTT. В этом конкретном SQL-операторе есть переменная связывания (:i). Значение этой переменной показывается в теге <Sql_Bind> в формате #ПозицияПеременной(ДлинаЗначенияПеременной): ЗначениеПеременнойСвязывания. показывает, что есть только одна переменная связывания (#1), длина ее значения равна трем символам (3), а значение – 107. Использование расширенного XML-аудита позволяет регистрировать SQL-операторы и использованные значения переменных связывания.

Обратите внимание, расширенный XML-аудит зафиксировал только тот SQL-оператор, который пользователь SCOTT выполнил после установки параметра AUDIT_TRAIL=XML, EXTENDED и перезапуска экземпляра сервера базы данных. Результаты XML-аудита, показанные на листингах и , получены в разных сеансах.

Системные журналы

Вышеприведенные варианты не будут работать, если владелец программного обеспечения сервера базы данных, обычно пользователь "oracle", решит удалить все записи в этих файлах XML-аудита. Чтобы дополнительно защитить их, вы можете использовать утилиту (систему) syslog. Эта система записывает сообщения в специальный файл, принадлежащий привилегированному пользователю ОС (пользователь "root"), поэтому никакой другой пользователь не сможет удалить его. Вы можете установить параметры инициализации так, чтобы журнал аудита записывался системой syslog:

AUDIT_TRAIL=OS AUDIT_SYSLOG_LEVEL=USER.ALERT

Теперь после перезапуска экземпляра сервера базы данных все записи аудита будут записываться системой syslog в соответствии с установленным средством (источником сообщений) – facility – (kernel (ядро ОС), user (пользовательские процессы) и так далее) и определенным уровнем серьезности сообщений – level – (таким, как notice (необычные состояния), warning (предупреждения), err (состояния ошибок) и так далее). Вторая строка показанного выше кода указывает, что записи аудита записываются с установленными средством user и уровнем alert (срочные ситуации). Если вы не укажете ничего больше, то эти записи аудита будут поступать в файл сообщений сервера по умолчанию – обычно в сервере Linux это файл /var/log/messages. Однако в этот файл поступают все сообщения, включая и сообщения самой ОС, поэтому вы можете создать другой файл только для целей аудита, скажем, audit.log. Укажите местоположение этого файла для данного средства в конфигурационном файле системы syslog, обычно находящимся в/etc/syslog.conf:

user.alert /var/log/audit.log

Эта строка указывает, что сообщения средства user на уровне alert должны поступать в файл /var/log/audit.log. Теперь перезапустите процесс системы syslog. Фактическая команда зависит от системы, ваш системный администратор должен применять правильную команду. В ОС UNIX как пользователь "root" выполните команду:

/etc/init.d/syslog restart

Она перезапустит процесс системы syslog, который будет писать сообщения средства user на уровне alert в файл /var/log/audit.log file. После этого, когда пользователь выполнит запрос к таблице ACCOUNTS, в этом файле появится следующая строка:

Oct 13 01:26:55 oradba Oracle Audit[28955]: SESSIONID: "25386" ENTRYID: "1" STATEMENT: "8" USERID: "SCOTT" USERHOST: "prolin1" TERMINAL: "pts/2" ACTION: "103" RETURNCODE: "0" OBJ$CREATOR: "ARUP" OBJ$NAME: "ACCOUNTS" SES$ACTIONS: "---------S------" SES$TID: "76564" OS$USERID: "oracle"

К сожалению, она не в XML-формате, но действия вполне понятны. Этот файл принадлежит пользователю root, поэтому пользователь oracle не сможет удалить или изменить его, что обеспечивает очень хорошую защиту.

Создание журнала аудита в XML-формате

Один из краеугольных камней инфраструктуры системы безопасности в сервере Oracle Database – контролируемость (accountability): возможность регистрации действий пользователей в системе базы данных. Когда действия происходят (например, пользователь обновляет определенную таблицу), сервер базы данных регистрирует эти события в журнале аудита (audit trails), который может находиться либо в базе данных в специальной таблице AUD$ схемы SYS, либо в специальных файлах операционной системы (ОС). Когда эти данные хранятся в базе данных, они защищаются резервным копированием этой базы, и администратору базы данных легко запрашивать их, используя обычные операторы языка SQL. Однако в этом случае после совершения злонамеренного обновления любой, кто имеет доступ к схеме SYS, потенциально может стереть из журнала аудита соответствующие данные.

Журнал аудита в среде ОС принадлежит владельцу программного обеспечения сервера Oracle, поэтому его хранение в специальных файлах ОС – один из способов защиты от доступа пользователей с привилегиями SYS. Вы можете иметь отдельные учетные записи ОС для администраторов базы данных, которые позволяют им администрировать базу данных и даже они могут иметь привилегии SYSDBA, но эти привилегии не разрешают администраторам удалять или изменять файлы журнала аудита. Использование файлов журнала аудита в файловой системе (с аккуратным разграничением доступа на уровне ОС и базы данных) может удовлетворить требования по безопасности многих организаций.

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

В сервере Oracle Database 10g Release 2 функциональные возможности аудита на уровне ОС были расширены – теперь в среде ОС можно создавать файлы журнала аудита в стандартном XML-формате. XML-документы легко распознаваемы, существует много инструментов (работающих во многих ОС) для чтения и форматирования этих документов, поэтому такие журналы аудита легко анализировать. Для облегчения запросов к содержимому этих журналов аудита есть также соответствующий SQL-интерфейс. В этой статье, я покажу, как настраивать журнал аудита в XML-формате и эффективно использовать его.

в сервере Oracle Database 10g

Журнал аудита в XML-формате в сервере Oracle Database 10g Release 2 позволяет вам без необходимости выбора иметь и то и другое – журнал аудита, отделенный от базы данных для усиления защиты от несанкционированного доступа, и тот же самый знакомый SQL-интерфейс для выполнения запросов к данным, который повышает продуктивность работы. Это очень полезно для обеспечения соблюдения многих законов и требований по безопасности. Вы можете использовать общедоступные программы просмотра XML-документов, разработанные сторонними производителями, и вы (или, возможно, специалисты отдела, который обязан контролировать вашу команду, но не имеющий возможности использовать SQL) можете использовать XML-анализаторы с таблицами стилей, позволяющие создавать заказные отчеты по файлам журнала аудита.
Дополнительно ИЗУЧАЙТЕ средства аудита:

Oracle Database Security Guide

Oracle Database Concepts (глава 20)

Oracle Database Reference

Oracle Database SQL Reference

Арап Нанда(Arup Nanda) () – администратор баз данных Oracle с 1993 г. Он занимается всеми аспектами администрирования – от оптимизации производительности до информационной безопасности и аварийного восстановления. Арап – соавтор книги "PL/SQL for DBAs" (O'Reilly Media). В 2003 г. он был удостоен награды журнала Oracle Magazine "Oracle's DBA of the Year" (администратор года баз данных Oracle).

---

---