Избирательность строк и значений с учетом столбцов

«Старое» решение

Сначала воспроизведем пример из упомянутой выше по тексту первой статьи с тою разницей, что теперь функция политики доступа к таблице SCOTT.EMP и вспомогательная таблица, регулирующая доступ, создаются в отдельной схеме VPD_ADMIN «администратора политик» (что в методологически более правильно):

CONNECT vpd_admin/vpd_admin

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

CREATE TABLE permissions_table ( username VARCHAR2(14) , deptno NUMBER (2) );

INSERT INTO permissions_table VALUES ('SCOTT', 10);

INSERT INTO permissions_table VALUES ('SCOTT', 30);

INSERT INTO permissions_table VALUES ('ADAM', 10);

(Полагаем, что пользователь SCOTT будет работать с сотрудниками 10-го и 30-го отделов, а пользователь ADAM – с сотрудниками только 10-го).

Функция, служащая предикатом доступа, задающая фильтр для строк при обращении к таблице SCOTT.EMP:

CREATE OR REPLACE FUNCTION permissions_function ( obj_schema IN VARCHAR2 ,obj_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN RETURN 'deptno IN (SELECT deptno FROM permissions_table ' 'WHERE username = USER)'; END; /

Политику доступа к таблице SCOTT.EMP по-прежнему назовем EPOLICY:

BEGIN DBMS_RLS.ADD_POLICY ( POLICY_NAME => 'epolicy' ,OBJECT_SCHEMA => 'scott' ,OBJECT_NAME => 'emp' ,FUNCTION_SCHEMA => 'vpd_admin' ,POLICY_FUNCTION => 'permissions_function' ); END; /

GRANT EXECUTE ON permissions_function TO scott;

Проверка:

SQL> CONNECT scott/tiger Connected. SQL> SELECT ename, sal, deptno FROM emp;

ENAME SAL DEPTNO

---------- ---------- ---------- MILLER 1300 10

KING 5000 10

CLARK 2450 10

JAMES 950 30

TURNER 1500 30

BLAKE 2850 30

MARTIN 1250 30

WARD 1250 30

ALLEN 1600 30

9 rows selected.

Новые параметры политики доступа в версии 10

В версии 10.1 у процедуры DBMS_RLS.ADD_POLICY появились новые необязательные (умолчательное значение – NULL) параметры:

Параметр SEC_RELEVANT_COLS

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

CONNECT vpd_admin/vpd_admin

HOST echo CONNECT vpd_admin/vpd_admin > drop_policy.sql

BEGIN DBMS_RLS.DROP_POLICY ( POLICY_NAME => 'epolicy' ,OBJECT_SCHEMA => 'scott' ,OBJECT_NAME => 'emp' ); END; /

SAVE drop_policy APPEND

(Файл drop_policy.sql я создал ради удобства, чтобы более экономно удалить политику во второй раз, ниже по тексту).

BEGIN DBMS_RLS.ADD_POLICY ( POLICY_NAME => 'epolicy' ,OBJECT_SCHEMA => 'scott' ,OBJECT_NAME => 'emp' ,FUNCTION_SCHEMA => 'vpd_admin' ,POLICY_FUNCTION => 'permissions_function' ,SEC_RELEVANT_COLS => 'sal, comm'

); END; /

Проверка:

SQL> CONNECT scott/tiger Connected. SQL> SELECT ename, sal, deptno FROM emp;

ENAME SAL DEPTNO

---------- ---------- ---------- MILLER 1300 10

KING 5000 10

CLARK 2450 10

JAMES 950 30

TURNER 1500 30

BLAKE 2850 30

MARTIN 1250 30

WARD 1250 30

ALLEN 1600 30

9 rows selected.

А вот что получим, если не обратимся к «секретному» столбцу с зарплатой:

SQL> SELECT ename, deptno FROM emp;

ENAME DEPTNO

---------- ---------- SMITH 20

ALLEN 30

WARD 30

JONES 20

MARTIN 30

BLAKE 30

CLARK 10

SCOTT 20

KING 10

TURNER 30

ADAMS 20

JAMES 30

FORD 20

MILLER 10

14 rows selected.

В любом случае защищенных данных мы не увидим, но способ достижения этого своеобразен: как только мы обращаемся к «секретным» столбцам, политика препятствует показу некоторых строк (как и раньше), но если мы к «секретным» столбцам не обращаемся, то пожалуйста – нам даются все строки, словно бы никакой политики и не было.

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

Параметр SEC_RELEVANT_COLS_OPT

Если для него указать константу DBMS_RLS.ALL_ROWS, то при операции SELECT будут выдаваться все строки, но значения в «секретных» столбцах некоторых строк (защищенных политикой) мы не увидим:

@drop_policy

BEGIN DBMS_RLS.ADD_POLICY ( POLICY_NAME => 'epolicy' ,OBJECT_SCHEMA => 'scott' ,OBJECT_NAME => 'emp' ,FUNCTION_SCHEMA => 'vpd_admin' ,POLICY_FUNCTION => 'permissions_function' ,SEC_RELEVANT_COLS => 'sal, comm'

,SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS

); END; /

Проверка:

SQL> CONNECT scott/tiger Connected. SQL> SELECT ename, sal, deptno FROM emp;

ENAME SAL DEPTNO

---------- ---------- ---------- SMITH 20

ALLEN 1600 30 WARD 1250 30 JONES 20

MARTIN 1250 30 BLAKE 2850 30 CLARK 2450 10 SCOTT 20

KING 5000 10 TURNER 1500 30 ADAMS 20

JAMES 950 30 FORD 20

MILLER 1300 10

14 rows selected.

SQL> SELECT ename, deptno FROM emp;

ENAME DEPTNO ---------- ---------- SMITH 20 ALLEN 30 WARD 30 JONES 20 MARTIN 30 BLAKE 30 CLARK 10 SCOTT 20 KING 10 TURNER 30 ADAMS 20 JAMES 30 FORD 20 MILLER 10

14 rows selected.

Обратите внимание, что различить в столбцах пропуски значений, обязанные применению политики, от пропусков в исходных данных в Oracle нельзя. Это контрастирует с возможностью аналогичного различения «исходных» и «благоприобретенных» пропусков, существующей, например, в SELECT с использованием GROUP BY ROLLUP/CUBE.

Некоторые замечания по употреблению

Не совсем очевидными могут представиться правила видимости строк. Так обратите внимание, что политика EPOLICY ограничивает пользователю SCOTT доступ к строкам даже собственной таблицы ! Возможно это не самый реалистичный пример, и чаще разработчик будет помещать таблицу с данными в отдельную схему, но таковы свойства VPD. С другой стороны, пользователь SYS имеет доступ ко всем данным любой таблицы вне зависимости от того, связана с ней какая-нибудь политика или нет. Точнее, не замечать политики будет любой пользователь, обладающей привилегией EXEMPT ACCESS POLICY (проверьте это !).

Обращает на себя внимание содержательная упрощенность примера выше. Отчасти она намеренная, а отчасти вынужденная. Например, прямолинейная попытка реализовать более интересный вариант, при котором каждый сотрудник будет «видеть» строки только о себе и о своих подчиненных, обречена на неудачу, в чем легко убедиться. Действительно, формулирование в функции-предикате условия отбора, содержащее упоминание самой таблицы EMP (что потребуется, если мы захотим отбирать из EMP строки с подчиненными) приведет к рекурсивному обращению к политике EPOLICY, то есть к ошибке. Как поступить в этом случае ?

Во-первых, создать отдельную таблицу, наподобие того, как это сделано выше, и перенести в нее нужные данные из EMP.

Во-вторых, создать не новую таблицу, а materialized view на основе EMP, и в функции-предикате обращаться именно к materialized view. Это будет технологичнее, так как снимет проблему синхронизации данных во вспомогательной таблице с изменениями в EMP. Materialized view в этом случае удобно создать со свойством REFRESH ON COMMIT.

К сожалению, в любом случае придется дублировать часть данных из основной таблицы EMP.

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

1. Формально не эквивалент, так как документация по Oracle считает, что VPD = FGAC + контекст приложения. По существу же мне разницы не видится.

Подготовка примера

Хотя формально этого не требуется, при использовании VPD методологически правильно завести специального пользователя-администратора. Ниже ему предоставляется минимум полномочий, достаточных для примеров из этой статьи. Выдаем в SQL*Plus:

CONNECT / AS SYSDBA

CREATE USER vpd_admin IDENTIFIED BY vpd_admin DEFAULT TABLESPACE sysaux TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON sysaux;

GRANT CREATE SESSION TO vpd_admin;

GRANT CREATE PROCEDURE TO vpd_admin;

GRANT CREATE TABLE TO vpd_admin;

GRANT EXECUTE ON dbms_rls TO vpd_admin;

В версии Oracle10 «виртуальные частные базы данных» данных стали избирательнее

Владимир Пржиялковский

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

Если на клетке буйвола прочтешь "Слон" – не верь глазам своим.
Козьма Прутков

что понятие виртуальная частная база

Напомню, что понятие виртуальная частная база данных (virtual private database, VPD), появилось в версии Oracle 8.1 для обозначения возможности ограничить конкретным сеансам доступное множество строк в таблице (в том числе выводимой, view), чтобы каждый сеанс, обращаясь формально к одной и той же таблице, имел доступ в ней («видел») только положенные строки. Сам термин VPD рекламный, его технический эквивалент, фигурирующий наравне в документации – детальный контроль доступа [к строкам] (fine grained access control, FGAC)1. Пользоваться VPD можно напрямую, через пакет SYS.DBMS_RLS, а так же неявно и не догадываясь об этом, поскольку это средство положено в основу другого, label security, реализующего известную модель мандатного доступа применительно к строкам таблицы из приложения. Пример работы непосредственно с VPD приводился в статье , а пример работы с label security приводился в статье .
Фирма Oracle не считает для себя направление VPD случайным, о чем свидетельствует непрекрывное развитие этого средства от одной версии СУБД к другой. Так, в числе новшеств VPD в версии 9 – собственная GUI-программа администрирования Policy Manager и поддержка синонимов, а в версии 10.1 – отбор строк (и даже значений в строках) с учетом указанных столбцов и возможность выбора между статическим и динамическим вычислением заданного предикатом отбора критерия видимости.
Именно первая из указанных двух новых возможностей VPD версии 10.1 и будет продемонстрирована в этой статье.

Аннотация

Рассматривается использование электронного бумажника Oracle Wallet для защиты от прочтения данных на внешнем носителе путем прозрачного шифрования (TDE) содержимого столбцов таблиц и табличных пространств, а также путем шифрования резервных копий.

Бумажник Oracle Wallet: использование для шифрования данных на внешнем носителе

Преподаватель технологий Oracle
http://open.oracle.tu2.ru/.index

Ноябрь 2008 г.

Держи карман шире!

Крылатое выражение

Электронный бумажник Oracle

Бумажник Oracle Wallet представляет собой двоичный файл, составленный по промышленному стандарту PKCS #12 «синтаксиса обмена персональной информацией» (), что обеспечивает ему совместимость с ПО третьих фирм. Файл имеет имя ewallet.p12, и он должен располагаться в любом доступном СУБД каталоге файловой системы. Тем не менее, когда бумажник используется для хранения главного ключа шифрования (о чем речь в этой статье), имеется умолчательное местонахождение (не требующее явного указания): по системе обозначений Windows это %ORACLE_BASE %\admin\%ORACLE_SID%\wallet. (Оно разумно привязано к месту хранения рабочих файлов СУБД, так как несколько СУБД на одном компьютере не имеют права пользоваться общим бумажником).

Сам бумажник Oracle появился в версии 8.1, однако приводимое далее его применение оказалось возможным только в версии 10.2.

Для работы со своим электронным бумажником фирма Oracle дает следующие средства:

команды SQL; программу Oracle Wallet Manager (она же owm);

программу orapki;

программу mkstore.

Три последние суть обращения к методам main классов Java, соответственно:

oracle.security.admin.wltmgr.owma.OwmaApp, oracle.security.pki.textui.OraclePKITextUI, oracle.security.pki.OracleSecretStoreTextUI.

(Все они, вместе с адресованными по цепочке, располагаются в каталогах ПО Oracle в разных архивах ORACLEHOME).

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

Постоянно открытый бумажник

Бумажник не обязательно открывать и закрывать всякий раз по мере надобности вручную. На компьютере, где он располагается, можно сообщить ему режим «автооткрытия» - при запуске ОС. Сделать это можно опять-таки через Oracle Wallet Manager:

Обратите внимание, что после этого в каталоге бумажника на пару файлу ewallet.p12 появился файл cwallet.sso. Расширение sso есть сокращение от single sign-on, обозначающего «технику единого входа». Новый файл есть по сути копия прежнего бумажника (но не копия файла).

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

Чтобы снять режим автооткрытия, в том же меню Wallet следует снять пометку Auto Logon. Файл cwallet.sso после этого автоматически пропадет. Это же (но не обратное !) действие можно выполнить командой, например: >mkstore -wrl C:\oracle\admin\orcl\wallet -deleteSSO

Внешнее хранение ключа

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

Размещение бумажника в реестре Windows

В Windows есть возможность поместить бумажник не в файл, а в реестр. Местонахождением в этом случае будет раздел \\HKEY_CURRENT_USER\SOFTWARE\ORACLE\WALLETS, волей-неволей скрывающий бумажник в профиле пользователя; то есть сохранение его в реестре выгоднее и по части управляемости, и по части защищенности.

Перенести бумажник в реестр можно средствами Oracle Wallet Manager (пометка Use Windows Registry в меню Wallet):

Создание, открытие и закрытие бумажника с главным ключом

Создать бумажник с главным ключом шифрования можно неявно командой ALTER SYSTEM SET ENCRYPTION WALLET ... или явно программами Oracle Wallet Manager и mkstore. Обратите внимание, что главный ключ (симметричный) будет применяться СУБД для шифрования данных при размещении на диске и расшифровке при взятии с диска, поэтому дальнейшая работа предполагается на сервере, с серверным ПО.

К сожалению, для электронного бумажника в Oracle ПО написано недостаточно тщательно, так что несоблюдение описанного далее порядка действий способно привести вплоть до ошибки ORA-00600 и необходимости перезагрузки СУБД.

Проще завести бумажник неявно, командой SQL. Для этого нужно создать необходимый умолчательный каталог и выдать команду SQL от имени SYS, например в SQL*Plus: SQL> HOST mkdir C:\oracle\admin\orcl\wallet SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "amicus123";

Приведенный пароль будет служить для последующего открытия бумажника и вообще доступа к его содержимому. Двойные кавычки неслучайны, так как в пароле различаются большие и малые буквы. Кроме того пароль должен содержать более 8 символов и складываться одновременно из букв и цифр. Для смены пароля в будущем надо будет использовать программу Oracle Wallet Manager, обратиться к которой в Windows можно через меню запуска программ или из командной строки ОС, а в Unix - набором owm в командной строке ОС.

По указанной команде SQL в (умолчательном) каталоге появится файл бумажника с главным ключом доступа. К фактическому использованию главного ключа из бумажника мы еще не готовы, так как бумажник нужно открыть. Команды открытия и закрытия бумажника: ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "amicus123"; ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

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

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

Данные БД могут находиться на внешнем носителе и в виде запасных (резервных) копий. Версия 10 позволила их шифровать. Это делается программой rman, и распространяется на резервные наборы (но не на копии образов файлов).

Шифрование резервного набора программой rman может осуществляться (1) с явным указанием ключа шифрования; (2) «прозрачно», то есть без явного указания, когда ключ берется из бумажника; (3) в «двойном режиме», когда допускается указывать ключ явно или пользоваться бумажником - по выбору. Администратору проще использовать для шифрования резервных наборов бумажник, и этот способ рассмотрен ниже.

Выдадим: >rman TARGET / ... [ответ] ... RMAN> SET ENCRYPTION OFF; ... [ответ] ... RMAN> BACKUP AS BACKUPSET TABLESPACE users TAG "unencrypted"; ... [ответ] ... RMAN> SET ENCRYPTION ON; ... [ответ] ... RMAN> BACKUP AS BACKUPSET TABLESPACE users TAG "the last one"; ... [ответ] ...

Для проверки можно последовательно выдать команды (далее в этом разделе - все для rman):

RESTORE TABLESPACE users VALIDATE; SQL "ALTER SYSTEM SET ENCRYPTION WALLET CLOSE"; RESTORE TABLESPACE users VALIDATE; RESTORE TABLESPACE users FROM TAG "unencrypted" VALIDATE; SQL 'ALTER SYSTEM SET ENCRYPTION WALLET open AUTHENTICATED BY "amicus12 3"'; RESTORE TABLESPACE users VALIDATE;

Алгоритмы, которые можно использовать для шифрования, перечисленны в таблице V $RMAN_ENCRYPTION_ALGORITHMS. Задать нужный алгоритм можно явочным путем, например: SET ENCRYPTION ALGORITHM 'AES192';

На практике в rman удобно установить умолчания, например: CONFIGURE ENCRYPTION ALGORITHM 'AES256'; CONFIGURE ENCRYPTION FOR DATABASE ON;

Создание столбцов с зашифрованными данными

Создадим таблицу для работы, для определенности в схеме SCOTT: CREATE TABLEclosed ( a VARCHAR2 ( 10 ) , b VARCHAR2 ( 10 ) , c VARCHAR2 ( 10 ) ); INSERT INTO closed VALUES ( 'normal1', 'secret', 'normal1' ); INSERT INTO closed VALUES ( 'normal2', 'secret', 'normal2' ); COMMIT;

Для начала рассмотрим обычное хранение строк. Выдадим в SQL*Plus: SQL> SELECT 2 DBMS_ROWID.ROWID_RELATIVE_FNO ( ROWID ) file# 3 , DBMS_ROWID.ROWID_BLOCK_NUMBER ( ROWID ) block# 4 FROM closed SQL> ;

FILE# BLOCK# ---------- ---------- 4 4

597 597

Обе короткие строки попали в один блок на диске. В моем случае это блок № 597 в файле № 4. Подставим полученные значения в команду, которую выдадим от имени SYS: ALTER SYSTEM DUMP DATAFILE 4 BLOCK 597;

В трассировочном файле серверного процесса, обслуживающего сеанс, появилась примерно следующая информация: ... EEF7DB0 32656662 5F353633 72756F53 6F546563 [bfe2365_SourceTo] EEF7DC0 4C4D5448 766E6F43 0703012C 6D726F6E [HTMLConv,...norm] EEF7DD0 06326C61 72636573 6E077465 616D726F [al2.secret.norma] EEF7DE0 012C326C 6F6E0703 6C616D72 65730631 [l2,...normal1.se] EEF7DF0 74657263 726F6E07 316C616D A1810604 [cret.normal1....] Block header dump: 0x01000255 ...

Пока еще не засекреченные значения выделены серым фоном. Зашифруем их в блоке: ALTER TABLE closed MODIFY ( b ENCRYPT );

Это можно сделать только при открытом бумажнике. Вот что получим в файле трассировки серверного процесса: ... EEF7D30 0B6A7807 12380B13 0703022C 6D726F6E [.xj...8.,...norm] EEF7D40 34326C61 0E8464A4 1304CAAE E1116635 [al24.d......5f..] EEF7D50 5B06C8CD C3AABA4A 8B4EA144 CE2B4987 [...[J...D.N..I+.] EEF7D60 EB2DEBB1 E97EB0C5 CDCA2CDB 99AC18E9 [..-...~..,......] EEF7D70 52E1F415 CA85201C 726F6E07 326C616D [...R. ...normal2] EEF7D80 0703022C 6D726F6E 34316C61 1F21C866 [,...normal14f.!.] EEF7D90 D765D462 EC2D8A33 8DF8F328 EC42E142 [b.e.3.-.(...B.B.] EEF7DA0 CB3EEB72 A36909CE A28C845E 0F04567C [r.>...i.^...|V..] EEF7DB0 3C74BEC5 8F3EAEA8 B3612F6E 6009C40C [..t.n/a....`] EEF7DC0 726F6E07 316C616D 0703002C 6D726F6E [.normal1,...norm] EEF7DD0 06326C61 72636573 6E077465 616D726F [al2.secret.norma] EEF7DE0 002C326C 6F6E0703 6C616D72 65730631 [l2,...normal1.se] EEF7DF0 74657263 726F6E07 316C616D A9BB0601 [cret.normal1....] Block header dump: 0x01000255 ...

в блоке действительно оказались зашифрованы

Значения двух слов 'secret' в блоке действительно оказались зашифрованы (и стали занимать больше места). Обратите внимание, что шифрование ранее занесенных данных Oracle отрабатывает алгоритмом команды UPDATE, который допускает временное сохранение в блоке старых данных, на радость злоумышленникам (в SQL Server то же самое, если верить интернету).

Выдача значений не раскроет того обстоятельства, что в блоке они хранятся зашифровано: SQL> SELECT * FROM closed; A B C ---------- ---------- ---------- normal1 secret normal1 normal2 secret normal2

Упражнение. Закройте бумажник командой, приведенной выше, и убедитесь, что в этом случае Oracle не сможет показать значение зашифрованного поля B таблицы CLOSED. Откройте бумажник снова для продолжения работы.

Бросается в глаза, что одно и то же значение ('secret') дало разный результат шифрования. Так произошло потому, что для дополнительной защиты Oracle перед шифрованием приписывает к значению случайную последовательность байтов, так называемую «привязку» (salt). Это препятствует расшифровке, и даже лишает возможности злоумышленника понять, что в разных полях исходно были одинаковые значения. С

другой стороны (а) «привязка» замедляет обработку данных и (б) препятствует созданию (древовидного) индекса на шифруемый столбец. Последнее вызвано тем, что значения полей индексируемого столбца помещаются в структуру индекса, и поскольку равные исходные значения будут давать разные шифрованные, индекс не сможет отрабатывать поиск по диапазону, то есть обесценится. Вот Oracle это и запрещает: SQL> CREATE INDEX closed_b ON closed ( b ); CREATE INDEX closed_b ON closed ( b ) * ERROR at line 1: ORA-28338: cannot encrypt indexed column(s) with salt

От применения привязки можно отказаться: ALTER TABLE closed MODIFY ( b ENCRYPT NO SALT );

Вот пример последовавшего результата из трассировочного файла: ... EEF7CC0 02C10265 06C102FF 0703012C 6D726F6E [e.......,...norm] EEF7CD0 24326C61 C565F542 45F6CA9D C4516D27 [al2$B.e....E'mQ.] EEF7CE0 902DEF69 C655685B 00844987 08803082 [i.-.[hU..I...0..] EEF7CF0 A6106E22 45F862E5 726F6E07 326C616D ["n...b.E.normal2] EEF7D00 0703012C 6D726F6E 24316C61 C565F542 [,...normal1$B.e.] EEF7D10 45F6CA9D C4516D27 902DEF69 C655685B [...E'mQ.i.-.[hU.] EEF7D20 00844987 08803082 A6106E22 45F862E5 [.I...0.."n...b.E] EEF7D30 726F6E07 316C616D 0703002C 6D726F6E [.normal1,...norm] EEF7D40 34326C61 0E8464A4 1304CAAE E1116635 [al24.d......5f..] EEF7D50 5B06C8CD C3AABA4A 8B4EA144 CE2B4987 [...[J...D.N..I+.] EEF7D60 EB2DEBB1 E97EB0C5 CDCA2CDB 99AC18E9 [..-...~..,......] EEF7D70 52E1F415 CA85201C 726F6E07 326C616D [...R. ...normal2] EEF7D80 0703022C 6D726F6E 34316C61 1F21C866 [,...normal14f.!.] EEF7D90 D765D462 EC2D8A33 8DF8F328 EC42E142 [b.e.3.-.(...B.B.] EEF7DA0 CB3EEB72 A36909CE A28C845E 0F04567C [r.>...i.^...|V..] EEF7DB0 3C74BEC5 8F3EAEA8 B3612F6E 6009C40C [..t.n/a....`] EEF7DC0 726F6E07 316C616D 0703002C 6D726F6E [.normal1,...norm] EEF7DD0 06326C61 72636573 6E077465 616D726F [al2.secret.norma] EEF7DE0 002C326C 6F6E0703 6C616D72 65730631 [l2,...normal1.se] EEF7DF0 74657263 726F6E07 316C616D A9BB0601 [cret.normal1....] Block header dump: 0x01000255 ...

Теперь два верхних отмеченных серым фоном значения в блоке стали одинаковыми.

Упражнение. Проверить открывшийся шанс завести индекс по полю B таблицы CLOSED.

Создание табличных пространств с зашифроваными данными

Когда возникает надобность шифровать данные многих столбцов в многих таблицах, приходится иметь дело с большим количеством справочных сведений, что технологически хлопотно. Версия 11 дает для таких случаев обобщенное решение: шифрование всех данных, помещаемых в конкретное табличное пространство. Такое свойство табличного пространства неизменно и указывается один раз при его создании, например: CREATE TABLESPACE encrypted_data DATAFILE 'G:\oracle\oradata\orcl\encrypt_df.dat' SIZE 1M ENCRYPTION USING '3DES168' DEFAULT STORAGE ( ENCRYPT ) ;

Оно отображено в поле ENCRYPTED таблицы DBATABLESPACES.

Справочная информация и некоторые подробности

Посмотреть состояние и местонахождение бумажника с шифроключами можно только с версии 11 в таблице V$ENCRYPTION_WALLET.

Перечень шифруемых столбцов в таблицах БД можно получить из отдельных системных таблиц DBA/ALL/USER_ENCRYPTED_COLUMNS. В нашем случае мы получим: SQL> SELECT * FROM user_encrypted_columns;

TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL ------------------- ------------------- -------------------------- --- CLOSED B AES 192 bits key NO

Полное имя последнего столбца SALT. Поле ENCRYPTIONALG сообщает алгоритм шифрования. Кроме умолчательного AES192 возможны AES128, AES256 и 3DES168. Эти алгоритмы следует указывать явно: ALTER TABLE closed REKEY USING 'aes256';

Обратите внимание, что алгоритм указывается не для столбца, а для всей таблицы. Это потому, что Oracle не позволяет разным столбцам таблицы использовать разные алгоритмы шифрования: технически «шифрование выполняется на уровне блока» (цитата). Фактически последняя команда сменяет не только алгоритм, но и ключ шифрования, тоже один на всю таблицу. Этот собственный ключ шифрования таблицы (не «главный») тут же сам шифруется главным ключом из бумажника и запоминается для нее в БД, в системной таблице ENC$. Оттуда он будет браться при всякой шифровке/расшифровке значений полей строк таблицы. Так, при обращении к зашифрованному полю таблицы СУБД возьмет из ENC$ зашифрованый ключ этой таблицы, расшифрует его главным ключом из бумажника, и уже восстановленым ключом таблицы расшифрует значение поля.

Если нужно сменить хранимые шифрозначения, можно выдать просто: ALTER TABLE closed REKEY;

На «законную» возможность читать данные это никак не скажется (равно как и предыдущее действие), но хранимые значения будут подменены на вычисленные заново.

Упражнение. Проверить, изменится ли хранение в БД зашифрованных данных при смене шифроключа таблицы.

Посмотреть ключи шифрования в бумажнике можно программой mkstore, например: >mkstore -wrl C:\oracle\admin\orcl\wallet -list

Enter password: amicus123

Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AcYP07kvGk +av9Jnw2C48y0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

То же, но чуть иначе, покажет программа orapki, если выдать: >orapki wallet display -wallet C:\oracle\admin\orcl\wallet -pwd amicus123

Эти же ключи покажет и Oracle Wallet Manager:

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

А вот значение главного ключа покажет только программа mkstore (следует одна строка): >mkstore -wrl C:\oracle\admin\orcl\wallet -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Сменить значение главного ключа можно командой типа: ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "amicus123";

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

БД Oracle не является замкнутой

БД Oracle не является замкнутой системой. СУБД вступает в контакт с участниками компьютерной сети, а данные базы, равно как и резервные копии, технически хранятся на внешних носителях. Хотя СУБД Oracle имеет собственную систему защиты данных, внешнее окружение, с которым она взаимодействует, вовсе не подконтрольно ей. Например, канал связи прикладной программы с СУБД может испытать постороннее вмешательство; существует риск постороннего же обращения к содержимому файлов с данными в обход Oracle.
Проблема не связана исключительно с Oracle и носит общий характер для информационных систем как таковых. Стандартное решение состоит в использовании шифрования передаваемых данных и подсчете контрольной свертки (суммы). Более того, передача шифра (необходимого для расшифровки принимающей стороной) часто вдобавок вынуждено сопровождается ссылкой на «свидетельство подлинности», так называемый сертификат. Когда взаимодействие субъектов и объектов доступа в информационной системе идет активно, предъявлять в программе шифр или его свидетельство подлинности многократно и «вручную» и неудобно, и небезопасно. Гораздо легче запомнить эти сведения где-то один раз и «попросить» систему делать проверку по мере надобности самостоятельно.
Популярным приемом является использовать для такого локализованного расположения данных параметров защищенного доступа «электронный бумажник» (ewallet). По сути это файл на компьютере (сервере или клиенте), где информация о параметрах доступа сама в свою очередь защищена. Английским прообразом для «удостоверяющих параметров доступа» является слово credentials, заимствованное из юридического языка и обозначающее там «верительные грамоты». (Слово credentials должно быть знакомо администраторам работе с Oracle Enterprise Manager). В «информационном», электронном бумажнике могут храниться такие персональные «верительные грамоты», как ключи шифрования данных и сертификаты подлинности, а кроме того еще и «прочие секретные сведения» (цитата) об участнике доступа.
Фирма Oracle предполагает использовать в качестве электронного бумажника собственное решение, Oracle Wallet, существующее в рамках расширения Advanced Security для Oracle Enterprise Edition или для клиента. Oracle Wallet позволяет хранить и обеспечивать использование системой следующие основные категории сведений:
«главный ключ» (masterkey) - шифр для автоматического шифрования данных на диске: в требуемых столбцах таблиц или целиком в табличных пространствах (transparent data encryption, TDE), а также для шифрования результатов сохранного (резервного) копирования; сертификаты подлинности ключей шифрования, используемых при передаче данных по защищенным каналам и сами ключи; имена пользователя и пароли ради упрощенного («беспарольного») соединения программы с сервером по данным из бумажника клиента.
В этой статье будет рассказано о применении электронного бумажника Oracle в первом качестве, а в последующей - во втором. Использование бумажника для упроцения процедуры соединения клиентом рассматриваться не будет.
В примерах ниже подразумевается файловая система ОС Windows, что никак не сказывается на существе дела.

Выбор расположения файла бумажника

Файл бумажника ewallet.p12 имеет право располагаться в любом месте файловой системы сервера, лишь бы только в области доступности СУБД. Нестандартное его местонахождение следует указать в конфигурационном файле серверного сетевого ПО sqlnet.ora. Включим в него параметр: WALLET_LOCATION =

( SOURCE = ( METHOD = FILE ) ( METHOD_DATA = ( DIRECTORY = C:\oracle\product\10.2.0\db_1\encryptionwallet ) ) )

Создадим указанный каталог и перенесем в него ранее созданый файл ewallet.p12. Закроем бумажник и откроем снова командами ALTER SYSTEM, как выше. Файл бумажника самодостаточен, и поэтому сделанного довольно, чтобы продолжать с ним работу на новом месте.

Вместо названия WALLETLOCATION файл sqlnet.ora понимает название ENCRYPTIONWALLETLOCATION.

Защита данных на внешнем носителе средствами TDE

Термин «прозрачное шифрование данных» (TDE) используется для обозначения процессов автоматической шифровки данных при размещении на носитель и дешифровки при чтении с носителя. «Прозрачное» здесь означает «прозрачное» для прикладной программы, в которой ни коим образом нет нужды учитывать фактическое выполнение указанных действий. В противовес этому программа имеет право самостоятельно шифровать нужные значения прежде чем передавать их БД и самостоятельно расшифровывать по извлечению, пользуясь, например, пакетом DBMSCRYPTO. Оба подхода имеют свои достоинства; при этом TDE проще для программиста, но и требует бумажника.

Техника TDE не присуща исключительно СУБД Oracle (пример: SQL Server), или даже базам данных вообще (пример: патент IBM).

Аннотация

Рассматривается использование электронного бумажника Oracle Wallet для установления защищенных соединений СУБД с узлами интернета по протоколу TCPS и клиентских программ с СУБД по протоколу HTTPS.

Бумажник Oracle Wallet: использование для связи по защищенным каналам

Преподаватель технологий Oracle
http://open.oracle.tu2.ru/.index

Декабрь 2008 г.
Небольшая правка: январь 2009 г.

И начал меня спрашивать: «Что за человек? Откудова?»
И я сказал, что с Москвы, да и подал ему господарский лист воложской.

Московского священника Ивана Лукьянова хождение в Святую землю

Файлы клиента

Правка файла tnsnames.ora может выглядеть примерно так: ... ORCL_SSL = ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCPS )( HOST = oraserver )( PORT = 2484 ) ) ( CONNECT_DATA = ( SERVICE_NAME = orcl ) ) ) ...

(Предполагается, что имя службы БД - orcl).

Правка файла sqlnet.ora может выглядеть примерно так: ... NAMES.DIRECTORY_PATH = ( TNSNAMES )

SSL_VERSION = 3.0

WALLET_LOCATION = ( SOURCE = ( METHOD = FILE ) ( METHOD_DATA = ( DIRECTORY = C:\oracle\product\10.2.0\client_1\NETWORK\wallet ) ) ) ...

Файлы сервера

Примерный фрагмент из listener.ora может выглядеть так: ... LISTENER = ( DESCRIPTION_LIST = ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP )( HOST = oraserver )( PORT = 1521 ) ) ( ADDRESS = ( PROTOCOL = TCPS )( HOST = oraserver )( PORT = 2484 ) ) ) )

SSL_CLIENT_AUTHENTICATION = FALSE

SQLNET.AUTHENTICATION_SERVICES = ( TCPS, NTS )

WALLET_LOCATION = ( SOURCE = ( METHOD = FILE ) ( METHOD DATA = ( DIRECTORY = C:\oracle\product\10.2.0\db_1\NETWORK\wallet ) ) ) ...

После подобной правки файла listener.ora программу listener стоит перезапустить. Правка файла sqlnet.ora может выглядеть примерно так: ... SQLNET.AUTHENTICATION_SERVICES = ( TCPS, NTS )

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 3.0

WALLET_LOCATION = ( SOURCE = ( METHOD = FILE ) ( METHOD_DATA = ( DIRECTORY = C:\oracle\product\10.2.0\db_1\NETWORK\wallet ) ) ) ...

Импорт сертификатов в бумажник

Нагляднее импортировать сертификаты опять-таки в Oracle Wallet Manager, хотя делать это можно и программами из командной строки.

При желании можно с пощью меню запомнить текст заявки во внешнем файле.

Вначале импортируется корневой сертификат. Используется меню Operations —»Import Trusted Certificate.

Во вторую очередь импортируется сертификат пользователя посредством меню Operations — Import User Certificate. Оба действия допускают указание как ссылки на файл, так и живого текста.

В результате ветка Certificate (Requested) объектов бумажника получит вид Certificate (Ready):

Сертификаты импортируются отдельно в бумажники сервера и клиента.

Конфигурирование Oracle Net

Процесс установки соединеий клиентов с СУБД listener должен быть настроен на прием заявок подключений по протоколу TCPS. Для этого в файле listener.ora следует пополнить описание списка портов в параметре LISTENER. По умолчанию Oracle Net использует для TCPS порт 2484; ниже эта традиция сохранена. Указание SSLVERSION в файлах ниже приводится для определенности и не имеет принципиального значения.

Конфигурирование программой Net Manager

Выставить требуемые значения параметрам можно программой Net Manager, например:

Может случиться, что ПО Advanced Security доустанавливалось впоследствии. Из-за внутренних ошибок программа Net Manager не всегда в таких случаях способна показать позицию Oracle Advanced Security ниспадающего меню. Выйти из положения можно, подправив строку из файла netproperties в каталоге %ORACLE_HOME%\network\tools; например заменить: INSTALLEDCOMPONENTS=CLIENT,ORACLENET

на: INSTALLEDCOMPONENTS=CLIENT,ORACLENET,ANO.

(ANO - сокращение старого названия нынешнего ПО Advanced Security Option).

Обращение в интернет по протоколу https

Наличие бумажника с необходимым сертификатом позволяет устанавливать соединения СУБД с узлами сети по защищенному протоколу HTTPS с помощью пакета UTLHTTP. Для примера рассмотрим адрес . Чтобы обратиться к нему из программы потребуется занести в бумажник сертификат. Сертификат можно извлечь из полученного ранее файла thawte-roots.zip, из каталога Thawte Server Roots. С равным успехом для импорта в бумажник годятся и двоичный файл ThawtePremiumServerCA.cer и содержимое текстового ThawtePremiumServerCA_b64.txt. Импортируем сертификат как и выше, с помощью Wallet Manager и меню Operations — Import Trusted Certificate.

Подготовим текст программы для SQL*Plus: SET SERVEROUTPUT ON

DECLARE req UTL_; resp UTL_; walletdir VARCHAR2 ( 100 ) := 'file:C:\oracle\product\10.2.0\db_1\NETWORK\wallet'; walletpass VARCHAR2 ( 100 ) := 'amicus123';

targeturl VARCHAR2 ( 100 ) := 'https://www.thawte.com';

BEGIN UTL_HTTP.SET_WALLET ( walletdir, walletpass ); req := UTL_HTTP.BEGIN_REQUEST ( targeturl ); resp := UTL_HTTP.GET_RESPONSE ( req ); UTL_HTTP.END_RESPONSE ( resp );

DBMS_OUTPUT.PUT_LINE ( 'HTTP response status code: ' resp.status_code ); DBMS_OUTPUT.PUT_LINE ( 'HTTP response reason: ' resp.reason phrase ); END; /

При наличии приближенного сервера (proxy) потребуется связаться через него (UTL_HTTP.SET_PROXY).

Прогон текста должен дать результат: HTTP response status code: 200

HTTP response reason: OK

Упражнение. Убрать из текста программы обращение к бумажнику (SETWALLET) и проверить доступ к адресам: (а) , (б) .

В созданом средствами Oracle бумажнике имеется несколько предустановленных сертификатов. Один из них готов для установления прямого защищенного соединения по протоколу HTTPS с узлом по адресу .

Подготовка заявки на сертификат пользователя

Получение сертификата пользователя производится по подготовленной заявке (request). Подготовить заявку можно с помощью Wallet Manager, отдельно для бумажников на сервере и на клиенте. Обратиться к форме составления заявки можно через меню Operations ® Add Certificate Request. В форме Create Certificate Request следует выбрать Key Size = 1024 и нажать кнопку Advanced. В появившуюся форму нужно внести различительное имя и нажать OK:

Если теперь «встать» на ветку Certificate (Requested) дерева объектов бумажника, справа увидим текст заявки на сертификат:

Получение цифрового сертификата

Для установления защищенного соединения по SSL потребуется получить цифровые сертификаты подлинности: (а) «пользователя» и (б) «корневой». Сертификат пользователя будем получать на обращение к ресурсу, обозначенному следующим «различительным именем» (distinguished name, dn): cn=orcl, cn=OracleContext, dc=us, dc=oracle, dc=com

(Предполагается, что имя экземпляра СУБД - orcl). Правила построения различительных имен многократно описаны в литературе и в интернете.

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

Получение сертификатов

Получить сертификат можно разными способами:

от уполномоченных центров сертификации (certificate authority, CA), выдающих сертификаты за плату; с помощью бесплатных программ получения сертификатов, например openSSL;

от собственного уполномоченного центра в рамках Identity Management Infrastructure в составе Oracle Application Server.

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

На месте в web нужно найти страницу «21-Day Free Trial SSL Certificate from thawte» и заполнить поля своими данными. Далее на странице «21-Day Free SSL Trial Certificate)) нужно проставить отметку SSL123 Certificate (All servers) и в поле для заявки внести текст, полученный от Wallet Manager. Нажав next, получим в ответ текст сертификата. Пока его нужно запомнить в каком-нибудь файле, например userCert.txt. Такой файл получаем отдельно для бумажника на сервере и на клиенте.

Корневой сертификат можно получить по адресу . Скачав файл thawte-roots.zip, нужно извлечь из него текстовое или двоичное представление корневого сертификата в каталоге Thawte Test Roots.

Создание бумажника с сертификатами

ПО Oracle позволяет создать пустой бумажник, и потом включить в него необходимые сертификаты подлинности. Однако программы owm (Wallet Manager), orapki, mkstore и mkwallet позволяют создать бумажник с некоторым начальным набором сертификатов. Первая из этих программ сумеет создать желаемый каталог для хранения файла бумажника сама, а остальные предполагают каталог уже имеющимся. Пример, для сервера: >set WALLETLOC=C:\oracle\product\10.2.0\db_1\NETWORK\wallet >mkdir %WALLETLOC% >mkstore -wrl %WALLETLOC% -create

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

Еще пример создания: >orapki wallet create -wallet %WALLETLOC% -pwd amicus123

Появился один файл ewallet.p12, но зато этой же программой можно ознакомиться с его содержимым: >orapki wallet display -wallet %WALLETLOC% -pwd amicus123

Наконец, создать бумажник и работать с его содержимым можно через Oracle Wallet Manager (меню Wallet ® New). При этом от следующего предложения придется отказаться:

В противном случае программа предложит текст заявки на получение сертификата, что пока не нужно. Свойство бумажника Auto Login в Wallet Manager нужно будет задать самостоятельно.

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

Установление защищенного соединения

На клиенте набираем: >sqlplus scott/tiger@orcl_ssl

Убедиться, что действительно произошло соединение по SSL, можно по файлам протокола (журнала) соединений listener.log. Если же включить трассировку сеанса (параметр TRACE_LEVEL_CLIENT в файле sqlnet.ora), увидим в файле трассировки примерно следующий фрагмент: [10-DEC-2008 20:13:11:828] nzos_Trace_Negotiated_Cipher: The Final Negotiated SSL Cipher Suite is: SSL_RSA_WITH_3DES_EDE_CBC_SHA

[10-DEC-2008 20:13:11:828] ntzdosecneg: SSL handshake done

Защищенные соединения и сертификаты подлинности

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

Соединения участников сети могут быть двух типов: СУБД с другими участниками, когда она выступает в качестве клиента, и других участников (клиентских программ) с СУБД. Ниже приводятся примеры организации того и другого.

Защищенные соединения с СУБД по протоколу SSL

ПО Oracle Advanced Security позволяет проводить авторизацию соединений клиента с сервером средствами сторонних продуктов, таких как Kerberos или RADIUS. Здесь же будет показано, как это можно сделать с использованием бумажника и техники защищенного соединения SSL (более современное название - TLS, ). С SSL связано и иное решение: соединяться в Oracle без Advanced Security, но по каналу связи («туннелю») SSL в ОС. Оно здесь не рассматривается по меньшей мере потому, что лишает смысла тему настоящей статьи.

Проверка средствами командной строки ОС:

>ftp

ftp> open localhost 2100

Connected to FLEXIT-V3TNTGIS. 220- FLEXIT-V3TNTGIS Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution. 220 FLEXIT-V3TNTGIS FTP Server (Oracle XML DB/Oracle Database) ready. User (FLEXIT-V3TNTGIS:(none)): xdb

331 pass required for XDB Password: 230 XDB logged in ftp> ls

200 PORT Command successful 150 ASCII Data Connection home public sys xdbconfig.xml 226 ASCII Transfer Complete ftp: 34 bytes received in 0,39Seconds 0,09Kbytes/sec. ftp> get xdbconfig.xml

200 PORT Command successful 150 ASCII Data Connection 226 ASCII Transfer Complete ftp: 11612 bytes received in 0,61Seconds 19,07Kbytes/sec. ftp> bye

221 QUIT Goodbye.

Жирным шрифтом выделены команды, набранные пользователем (набор пароля для пользователя XDB на экране не отразился). По результату этих действий из репозитария XML DB в текущий каталог ОС скопировался файл xdbconfig.xml. Подключиться по FTP к репозитарию XML DB можно было и под любым другим именем пользователя Oracle, однако не все пользователи смогут совершать все операции с имеющимися файлами.

Упражнение:

Проверить появление в текущем каталоге ОС файла xdbconfig.xml. Подключиться по FTP под именем SCOTT и удостовериться в невозможности извлечь файл из XML DB.

Аннотация

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

Проверка средствами браузера MS IE:

Войти в браузер MS IE, набрать в поле адреса следующие варианты и проверить работу:

Действия по открытию доступа

Во-первых, требуется удостовериться в нужных значениях параметров СУБД DISPATCHERS (обязательно) и LOCAL_LISTENER (при наличии нескольких процессов listener). Установка нужных значений может быть выполнена так:

ALTER SYSTEM SET dispatchers = '(PROTOCOL=TCP)(SERVICE=ORCLXDB)' SCOPE = SPFILE ;

ALTER SYSTEM SET local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' ;

При этом указываемое значение SERVICE формируется как имя_СУБДXDB (здесь считается, что INSTANCE_NAME = ORCL), а значения HOST и PORT соответствуют местонахождению и месту приписки процесса listener (когда этих процессов несколько - того, что будет обслуживать доступ через HTTP). В работе, получив заявку на соединение с XML DB, процесс listener передаст ее дистпетчеру, а тот - одному из общественных (shared) серверных процессов.

Во-вторых, требуется назначить или переназначить (при необходимости) порты для публичного доступа по HTTP и FTP. Порты по умолчанию соответственно 8080 и 2100, однако, начиная с версии 10.2, они изначально выставлены в 0. Для переустановки (а с версии 10.2 - для установки) требуется выполнить от имени SYS:

EXECUTE DBMS_XDB.SETFTPPORT ( 2100 ) EXECUTE DBMS_XDB.SETHTTPPORT ( 8080 ) ALTER SYSTEM REGISTER;

Выдача последней команды некритична. После этого по некоторым рекомендациям нужно перезапустить СУБД:

SQL> SHUTDOWN IMMEDIATE SQL> STARTUP

В ОС следует выдать:

>lsnrctl status

Ожидаемый результат должен выглядеть примерно так:

... Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FLEXIT-V3TNTGIS)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FLEXIT-V3TNTGIS)(PORT=8080))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FLEXIT-V3TNTGIS)(PORT=2100))(Presentation=FTP)(Session=RAW)) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... ...

В версии 10.2 появится также информация о службе имя_СУБД_XPT. При желании ее можно отключить скрытым параметром СУБД, однако ж и ее наличие не мешает обычной работе.

Как открыть доступ к репозитарию по протоколам HTTP и FTP

Результатом заведения XML DB в базе явилось появление в ее составе так называемого репозитария XML DB, фактически - файловой системы. Для работы с репозитарием извне возможно и удобно открыть доступ по протоколам HTTP и FTP.

Организация доступа по WebDAV в Windows

При установленном доступе по HTTP, Oracle поддерживает также основанное на этом протоколе средство WebDAV (Web-based Distributed Authoring and Versioning, http://techinfo.net.ru/webdav.html). ОС Windows обеспечивает клиентский доступ к серверу WebDAV, позволяя обращаться к репозитарию XML DB как к каталогу собственной файловой системы.

Проверка доступа

Подключиться в Windows Explorer к сетевому ресурсу с именем 'XML DB Repository' и копировать файлы между репозитарием XML DB и файловой системой ОС методом Drag and Drop.

Доступ по WebDAV обеспечивает не обязательно только Windows Explorer, но и целый ряд продуктов разных фирм.

Проверка доступа по FTP

Проверка доступа к репозитарию- возможно наиболее эффектный шаг в освоении XML DB. Для FTP может выполняться двояко.

Проверка доступа по HTTP

Войти в браузер Web, набрать в поле адреса следующие варианты и проверить работу:

http://localhost:8080/

http://localhost:8080/sys/schemas/OE/localhost:8080/source/schemas/poSource/xsd/

http://localhost:8080/sys/schemas/OE/localhost:8080
/source/schemas/poSource/xsd/purchaseOrder.xsd

Обратите внимание на доступность текстового вида страниц.

Установка и убирание XML DB

Мура туфельку снимала,
В огороде закопала:
- Расти, туфелька моя,
Расти, маленькая !

Корней Чуковский,
Что сделала Мура, когда ей прочли сказку
"Чудо-дерево"

Установка XML DB может выполняться средствами Database Configuration Assistant (DBCA), либо вручную. Типовой вариант БД, начиная с версии 9.2, включает предустановленную XML DB и не требует дополнительной установки.

Узнать о наличии установленной XML DB и готовности к работе можно так:

SQL> CONNECT / AS SYSDBA Connected. SQL> SELECT count ( * ) 2 FROM dba_objects 3 WHERE owner = 'XDB' AND status = 'INVALID';

COUNT(*) ---------- 0

SQL> COLUMN comp_name FORMAT A30 SQL> SELECT comp_name, status, version 2 FROM dba_registry 3 WHERE comp_name = 'Oracle XML Database';

COMP_NAME STATUS VERSION ------------------------------ ----------- ------------ Oracle XML Database VALID 10.2.0.1.0

При полученных выше ответах может потребоваться, однако, разблокировать подключение к СУБД (уже имеющемуся) пользователю XDB, например:

ALTER USER xdb ACCOUNT UNLOCK IDENTIFIED BY xdb;

Если же в существующей БД XML DB не установлена, вручную это можно сделать следующей последовательностью действий от имени SYS:

Завести табличное пространство для нужд пользователя XDB (желательно). Прогнать сценарий catqm.sql заведения необходимой инфраструктуры в БД (реестр XML DB, служебные пакеты PL/SQL и пр.):

SQL> @?/rdbms/admin/catqm пароль_для_XDB табличное_пространство_для_XDB табличное_пространство_для_временных данных_XDB

Прогнать сценарий catqm.sql заведения необходимых библиотек на Java и типов (они могут иметься и без этого):

SQL> @?/rdbms/admin/catxdbj

Прогнать сценарий создания пакетов из состава XDK (по желанию):

SQL> @?/rdbms/admin/initxml

Выполнить SHUTDOWN и STARTUP.

Для убирания из БД XML DB достаточно выполнить от имени SYS:

SQL> @?/rdbms/admin/catnoqm

SHUTDOWN и STARTUP

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

в Oracle появилась XML DB

В версии 9.2 в Oracle появилась XML DB - набор средств и возможностей СУБД и БД в Oracle, ориентированный на работу с форматом XML как по части преобразования, так и хранения данных. По сути, XML DB дополнила традиционную для Oracle табличную организацию хранения и доступа к данным новыми видами организации и доступа. Это не может не интересовать разработчика, поскольку XML все увереннее завоевывает себе место в информационных системах.
Возможности XML DB были развиты в версии 10, а судя по просачивающимся сведениям, получат дальнейшее развитие и в версии Oracle 11, существующей сегодня в бета-варианте и возможно планируемой к выпуску в 2007 году.
В этой статье рассказано, как можно установить XML DB в своей БД и как наладить внешнее взаимодействие с ней. В следующих статьях будет рассказано, как при установленной XML DB работать с репозитарием, регистрировать и использовать схему XML и создавать сервлеты в СУБД для досупа к данным.
Большинство возможностей Oracle XML DB, обозначенных ниже, обеспечены уже в версии Oracle 9.2.0.2, однако в некоторых технических подробностях могут иметься отличия по отношению к более поздним версиям.

XML DB - новое измерение в организации данных в Oracle

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

... К несчастию, никак не мог прибрать рифмы для
лядунок и киверов; итак, пусть будет это в прозе.

А. С. Грибоедов, Письмо из Бреста Литовского к издателю «Вестника Европы»

Заведение каталога в Windows на основе доступа по WebDAV

Действия в Windows:

Открываем форму My Network Places. Это пожно сделать через Internet Explorer или через форму Network Connections в установках компьютера. Открываем форму Add a network place. Входим в помошник установки (wizard) Выбираем Choose another network connect. Вводим адрес http://localhost:8080/. В появившемся окошке вводим имя и пароль пользователя Oracle, например xdb/xdb. Указываем имя сетевого соединения, например: XML DB Repository. Снимаем «галочку» с позиции выбора Open this network place … (необязательно) и нажимаем Finish.

Соединение заведено. Войти в репозитарий можно, нажав дважны на имя «каталога» XML DB Repository.

Аннотация

Установленная в БД XML DB позволяет регистрировать в базе схемы XML, задающие типы документов XML. Зарегистрированная в репозитарии схема может употребляться для данных типа XMLTYPE: как хранимых в обычных таблицах, так и доступных из производных (синтезированных) таблиц типа XMLTYPE. В статье показано на примерах, как это делать.

Использование для дополнительной типизации XMLTYPE в базовых таблицах

Просто указанный XMLTYPE требует лишь, чтобы помещаемый в БД документ был правильно оформлен. Зарегистрированная в XML DB схема XML позволяет указать для размещаемых в конкретные поля документов XML-образные ограничения целостности, позволяя хранить только «годные» (valid) из них.

Создадим таблицу объектов XMLTYPE, уточненную ссылкой на зарегистрированную схему:

CREATE TABLE xtbooks OF XMLTYPE XMLSCHEMA "http://localhost:8080/public/bookCover.xsd" ELEMENT "cover" ;

Проверка занесения данных:

INSERT INTO xtbooks VALUES ( XMLTYPE ( '<cover><author>Einstein</author></cover>' ) );

INSERT INTO xtbooks VALUES ( XMLTYPE ( ' <cover> <title>Java Programming with Oracle JDBC</title> <author>Donald Bales</author> <publisher>OReilly and Associates</publisher> <pubdate>December 2001</pubdate> <isbn>0-596-00088-x</isbn> <pages>496</pages> </cover> ' ) );

COMMIT;

Упражнение. Проверьте реакцию СУБД на попытку вставить в таблицу XTBOOKS правильно оформленный документ XML, не соответствующий описанию схемы.

Аналогично накладываются дополнительные ограничения на данные столбца XMLTYPE в обычной таблице:

CREATE TABLE tbooks ( id NUMBER ( 9 ) , description XMLTYPE ) XMLTYPE description STORE AS OBJECT RELATIONAL XMLSCHEMA "http://localhost:8080/public/bookCover.xsd" ELEMENT "cover" ;

В приведенных примерах данные в БД фактически будут храниться «объектно-реляционно», то есть будучи распределены по разным структурам БД, в том числе по спрятанным столбцам таблиц. Однако типизируя таблицу или столбец типа XMLTYPE схемой, можно потребовать фактического хранения документа в виде объекта CLOB, подобно тому, как это происходит при отсутствии типизации схемой.

Пример:

CREATE TABLE xtbooksclob OF XMLTYPE XMLTYPE STORE AS CLOB

XMLSCHEMA "http://localhost:8080/public/bookCover.xsd" ELEMENT "cover" ;

Упражнение. Проверить возникновение новых объектов БД и их структуры по результатам заведения таблиц XTBOOKS, TBOOKS и XTBOOKSCLOB. Воспользоваться для этого таблицами USER_OBJECTS, USER_TAB_COLS, USER_TYPES, USER_TRIGGERS, USER_LOBS.

При отсутствии XML DB не заперщено создавать производную таблицу (view), выдающую данные в виде таблицы документов XML (типа XMLTYPE) на основе данных из обычных таблиц. Рассмотрим, как при наличии XML DB можно дополнительно уточнить такую производную таблицу схемой XML.

В этом примере регистрируется схема, не взятая из ресурса репозитария, как ранее, а явно выписанная в виде текста XML:

BEGIN DBMS_XMLSCHEMA.REGISTERSCHEMA ( schemaurl => 'http://localhost:8080/public/employee.xsd'

, schemadoc => '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="employee"> <xs:complexType> <xs:sequence> <xs:element name="ename" type="xs:string"/> <xs:element name="job" type="xs:string"/> <xs:element name="sal" type="xs:integer"/> </xs:sequence> <xs:attribute name="empno" type="xs:integer"/> </xs:complexType> </xs:element> </xs:schema> ' ); END; /

Пример создания производной таблицы:

CREATE OR REPLACE VIEW empxml_schema_view OF XMLTYPE XMLSCHEMA "http://localhost:8080/public/employee.xsd" ELEMENT "employee" WITH OBJECT ID ( EXTRACT ( SYS_NC_ROWINFO$, '/employee/@empno' ).GETNUMBERVAL ( ) ) AS SELECT XMLELEMENT ( "employee" , XMLATTRIBUTES ( e.empno AS "empno" ) , XMLFOREST ( e.ename AS "ename", e.job AS "job", e.sal AS "sal" ) ) FROM emp e ;

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

SELECT VALUE ( v ).ISSCHEMAVALID ( ) FROM empxml_schema_view v;

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

Создадим таблицу объектов XMLTYPE, уточненную ссылкой на зарегистрированную схему:

CREATE TABLE xtbooks OF XMLTYPE XMLSCHEMA "http://localhost:8080/public/bookCover.xsd" ELEMENT "cover" ;

Проверка занесения данных:

INSERT INTO xtbooks VALUES ( XMLTYPE ( '<cover><author>Einstein</author></cover>' ) );

COMMIT;

Аналогично накладываются дополнительные ограничения на данные столбца XMLTYPE в обычной таблице:

CREATE TABLE tbooks ( id NUMBER ( 9 ) , description XMLTYPE ) XMLTYPE description STORE AS OBJECT RELATIONAL XMLSCHEMA "http://localhost:8080/public/bookCover.xsd" ELEMENT "cover" ;

Пример:

CREATE TABLE xtbooksclob OF XMLTYPE XMLTYPE STORE AS CLOB

XMLSCHEMA "http://localhost:8080/public/bookCover.xsd" ELEMENT "cover" ;

BEGIN DBMS_XMLSCHEMA.REGISTERSCHEMA ( schemaurl => 'http://localhost:8080/public/employee.xsd'

Пример создания производной таблицы:

SELECT VALUE ( v ).ISSCHEMAVALID ( ) FROM empxml_schema_view v;

SELECT VALUE ( v ).ISSCHEMAVALID ( ) FROM empxml_view v;

SELECT VALUE ( v ) .ISSCHEMAVALID ( 'http://localhost:8080/public/employee.xsd' ) FROM empxml_view v ;

Упражнение. Внести в схему http://localhost:8080/public/employee.xsd пользователя SCOTT изменение (снять с регистрации и зарегистрировать исправленный текст). Заменить в схеме существующее описание зарплаты:

<xs:element name="sal" type="xs:integer"/>

на следующее:

<xs:element name="sal"> <xs:simpleType> <xs:restriction base="xs:integer"> <xs:maxInclusive value="1500"/> <xs:minInclusive value="1000"/> </xs:restriction> </xs:simpleType> </xs:element>

Проверить поведение последних трех упоминавшихся выше запросов.

Последний пример показывает использование чуть более сложных ограничений целостности на структуру и данные документов XML (сформулированы минимальное и максимальное значения зарплат).

SELECT VALUE ( v ).ISSCHEMAVALID ( ) FROM empxml_view v;

SELECT VALUE ( v ) .ISSCHEMAVALID ( 'http://localhost:8080/public/employee.xsd' ) FROM empxml_view v ;

Упражнение. Внести в схему http://localhost:8080/public/employee.xsd пользователя SCOTT изменение (снять с регистрации и зарегистрировать исправленный текст). Заменить в схеме существующее описание зарплаты:

<xs:element name="sal" type="xs:integer"/>

на следующее:

<xs:element name="sal"> <xs:simpleType> <xs:restriction base="xs:integer"> <xs:maxInclusive value="1500"/> <xs:minInclusive value="1000"/> </xs:restriction> </xs:simpleType> </xs:element>

Проверить поведение последних трех упоминавшихся выше запросов.

Последний пример показывает использование чуть более сложных ограничений целостности на структуру и данные документов XML (сформулированы минимальное и максимальное значения зарплат).

Как зарегистрировать схему XML

Рассмотрим несложную схему XML, определение которой создадим в файле bookCover.xsd:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="cover" type="coverType"/> <xsd:complexType name="coverType"> <xsd:sequence> <xsd:element name="title" type="xsd:string"/> <xsd:element name="author" type="xsd:string" maxOccurs="unbounded"/> <xsd:element name="publisher" type="xsd:string"/> <xsd:element name="pubdate" type="xsd:string"/> <xsd:element name="isbn" type="xsd:string"/> <xsd:element name="pages" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:schema>

Поместим файл bookCover.xsd в репозитарий XML DB в папку /public (любым методом: командной строкой, программно, графическим способом, по WebDAV или же по FTP). Получим ресурс /public/bookCover.xsd репозитария. Зарегистрируем схему, являющуюся содержанием этого ресурса, с помощью пользователя SCOTT.

Для возможности регистрации схемы пользователь БД должен обладать привилегиями

ALTER SESSION CREATE TYPE CREATE TABLE CREATE PROCEDURE CREATE TRIGGER

В версии 10 содержание роли CONNECT было сведено только к привилегии CREATE SESSION, поэтому после версии 9 пользователю SCOTT потребуется так или иначе выдать привилегию ALTER SESSION, например:

CONNECT / AS SYSDBA

GRANT ALTER SESSION TO scott;

CONNECT scott/tiger

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

ALTER SESSION SET EVENTS='31098 trace name context forever';

Регистрация:

BEGIN DBMS_XMLSCHEMA.REGISTERSCHEMA ( schemaurl => 'http://localhost:8080/public/bookCover.xsd' , schemadoc => XDBURITYPE ( '/public/bookCover.xsd' )

); END; /

Описание схемы не обязательно заводить в репозитарии в качестве ресурса. Для регистрации можно воспользоваться и внешним файлом, явно указанным документом XML или же документом, указанным через внешний URL (подтипы URITYPE).

Проверка:

SQL> COLUMN schema_url FORMAT A50 WORD SQL> COLUMN local FORMAT A5 SQL> SELECT schema_url, local FROM user_xml_schemas;

SCHEMA_URL LOCAL -------------------------------------------------- ----- http://localhost:8080/public/bookCover.xsd YES

Интерес представляют также другие поля таблицы USER_XML_SCHEMAS, не указанные в этом запросе за экономией места. Например, поле QUAL_SCHEMA_URL указывает на полное (реальное), а не сокращенное имя ресурса, созданного в результате регистрации схемы: в нашем случае /sys/schemas/SCOTT/localhost:8080/public/bookCover.xsd. Если бы регистрируя схему мы объявили ее глобальной (что требует явного указания и наличия прав), полный адрес ресурса был бы иной: /sys/schemas/PUBLIC/localhost:8080/public/bookCover.xsd.

Выдача команды ALTER SESSION выше привела к фиксации в трассировочном файле сеанса некоторых скрытых действий СУБД, например:

CREATE OR REPLACE TYPE "SCOTT"."author342_COLL" AS VARRAY(2147483647) OF VARCHAR2(4000 CHAR) / CREATE OR REPLACE TYPE "SCOTT"."coverType341_T" AS OBJECT ("SYS_XDBPD$"
"XDB"."XDB$RAW_LIST_T","title" VARCHAR2(4000 CHAR),"author"
"author342_COLL","publisher" VARCHAR2(4000 CHAR),"pubdate" VARCHAR2(4000 CHAR),"isbn"
VARCHAR2(4000 CHAR),"pages" VARCHAR2(4000 CHAR))NOT FINAL INSTANTIABLE / CREATE TABLE "SCOTT"."cover343_TAB" OF SYS.XMLTYPE XMLSCHEMA
"http://localhost:8080/public/bookCover.xsd" ID '6927F846BBDA487F84B9BCCEC191A059'
ELEMENT "cover" ID 2664 TYPE "SCOTT"."coverType341_T" /

Результат этих действий, а также созданную заодно триггерную процедуру "cover343_TAB$xd" (в нашем случае), можно наблюдать в таблице USER_OBJECTS. (Упражнение. Узнать из таблицы USER_OBJECTS перечень появившихся после регистрации схемы XML объектов). Обратите внимание, что сведения о таблице "cover343_TAB" не отражены в USER_TABLES, хотя представлены в прочих системных таблицах, например, в USER_TAB_COLUMNS и USER_TAB_COLS.

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

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

BEGIN DBMS_XMLSCHEMA.DELETESCHEMA ( schemaurl => 'http://localhost:8080/public/bookCover.xsd' , delete_option => DBMS_XMLSCHEMA.DELETE_CASCADE

); END; /

Для краткости (но в ущерб ясности кода) можно сразу указать цифровое значение, например в SQL*Plus:

EXECUTE DBMS_XMLSCHEMA - .DELETESCHEMA ( 'http://localhost:8080/public/bookCover.xsd', 3 )

Как зарегистрировать схему XML в XML DB и как этим воспользоваться

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

Чиновник по особым порученьям,

Который их до места проводил,

С заботливым Попова попеченьем

Сдал на руки дежурному.

А. К. Толстой. Сон Попова

Проверка действия схемы

Метод CREATESCHEMABASEDXML типа XMLTYPE позволяет устроить проверку соответствия документа XML схеме.

Ради краткости записи следующих примеров имя зарегистрированной схемы занесем в переменную SQL*Plus:

VARIABLE bookschema VARCHAR2 ( 100 ) EXECUTE :bookschema := 'http://localhost:8080/public/bookCover.xsd'

Следующие запросы проработают без ошибок:

SELECT XMLTYPE ( '<cover></cover>' ).CREATESCHEMABASEDXML ( :bookschema )

FROM dual ;

SELECT XMLTYPE ( '<cover><author>Einstein</author></cover>' ) .CREATESCHEMABASEDXML ( :bookschema ) FROM dual ;

SELECT XMLTYPE ( ' <cover> <title>Java Programming with Oracle JDBC</title> <author>Donald Bales</author> <publisher>OReilly and Associates</publisher> <pubdate>December 2001</pubdate> <isbn>0-596-00088-x</isbn> <pages>496</pages> </cover> ' ).CREATESCHEMABASEDXML ( :bookschema ) FROM dual ;

Следующие запросы проработают с ошибками ввиду противоречий документа схеме:

SELECT XMLTYPE ( '<c/>' ).CREATESCHEMABASEDXML ( :bookschema ) FROM dual ;

SELECT XMLTYPE ( '<cover><a>Einstein</a></cover>' ) .CREATESCHEMABASEDXML ( :bookschema ) FROM dual ;

SELECT XMLTYPE ( '<cover><title>A</title><title>B</title></cover>' ) .CREATESCHEMABASEDXML ( :bookschema ) FROM dual ;

Эта статья является продолжением статей

Эта статья является продолжением статей «XML DB - новое измерение в организации данных в Oracle» и «». Здесь говорится о регистрации в БД пользователя схем XML, что возможно после установки XML DB (об этом рассказывалось ранее). Показано, как выполняется регистрация, и как зарегистрированную схему XML можно использовать при работе с данными типа XMLTYPE.

Преобразование из табличной формы в XMLTYPE

Для обратного преобразования удобно воспользоваться функциями, объединенными в стандарте SQL:2003 названием SQL/XML (другое название – SQLX). В версии Oracle 9.2 реализованы следующие (не все) функции из этого стандартного набора:

- XMLElement

- XMLAttributes

- XMLAgg

- XMLConcat

- XMLForest

Вот некоторые примеры использования в схеме SCOTT:

SELECT XMLELEMENT("Employee", ename) FROM emp;

SELECT XMLELEMENT("Employee",

XMLATTRIBUTES(ename AS "Name", empno AS "Number"))

FROM emp;

Обратите внимание, что в результатах выдаются поля типа XMLTYPE:

CREATE TABLE xtable (n) AS SELECT XMLELEMENT("Name", ename) FROM emp;

DESCRIBE xtable

Следующий пример – агрегирующей функции XMLAGG, допускающей использование в запросах с группировкой GROUP BY, подобно тому, как агрегирующие функции MIN, AVG и другие применяются для обычных данных, а не XMLTYPE:

SET LONG 2000

SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),

XMLAGG(XMLELEMENT("employee", ename))

FROM emp

GROUP BY deptno;

Интересно, что последний запрос допускает создания на своей основе выводимой таблицы, но не базовой:

CREATE VIEW xview (a, b) AS

SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),

XMLAGG(XMLELEMENT("employee", ename))

FROM emp

GROUP BY deptno;

(срабатывает)

CREATE TABLE xtable (a, b) AS

SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),

XMLAGG(XMLELEMENT("employee", ename))

FROM emp

GROUP BY deptno;

(ошибка !)

Это объясняется тем, что столбцы A и B в обоих случаях Oracle пытается создавать как XMLTYPE, а наши данные таковы, что в столбце B содержатся строго говоря некорректные строки XML, например

<employee>CLARK</employee><employee>KING</employee> ....

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

Преобразование из XMLTYPE в табличную форму

Для преобразования данных типа XMLTYPE в обычный табличный вид можно использовать функции SQL и методы XMLTYPE, в первую очередь упоминавшуюся метод-функцию EXTRACT:

COLUMN xdoc FORMAT A80

SELECT ROWNUM, id, b.description.EXTRACT('/cover/author') xdoc

FROM books b;

Обратите внимание на возможность и способ обработки нескольких авторов в XML элементах <author>.

Использование функции SQL EXTRACTVALUE, в свою очередь, оставляет возможность отбора не более одного элемента XML для формирования каждой строки результата SELECT, но зато безболезнено убирает обрамляющие значение элемента XML метки:

SELECT id, EXTRACTVALUE(b.description.EXTRACT('/cover/title'), '/title') xdoc

FROM books b;

То же самое можно записать проще, что уже демонстрировалось в начале статьи.

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

CREATE TABLE books

(id NUMBER PRIMARY KEY

, description XMLTYPE);

INSERT INTO books VALUES

(100

, XMLTYPE('<cover>

<title>Oracle SQL*Loader</title>

<author>Jonathan Gennick</author>

<author>Sanjay Mishra</author>

</cover>'));

SET long 1000

SELECT id, description FROM books;

SELECT id, b.description.XMLDATA FROM books b;

XMLDATA – специально созданный для XMLTYPE «псевдостолбец».

До этого наиболее подходящим для

Тип XMLTYPE появился в Oracle в версии 9.0. До этого наиболее подходящим для хранения документов в формате XML был тип CLOB (и менее подходящим – тип VARCHAR2, ограниченный максимумом 4000 знаков). Сам по себе объектный, новый тип XMLTYPE технически может храниться либо по-прежнему в виде CLOB, либо в виде объекта (начиная с версии 9.2). И еще одно замечание: несмотря на то, что технологии XML и Java идут «рука об руку», рамки приводимых ниже примеров не требуют от вашей БД установленных возможностей Java.
В этой заметке рассмотрены только логические стороны использования XML в Oracle безотносительно к техническим свойствам хранения и доступа.

Взаимные преобразования табличного вида и XMLTYPE

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

XMLTYPE – объектный тип Oracle

Доказательством утверждения в заголовке служит создание следующей таблицы объектов типа XMLTYPE, «таблицы документов XML»:

CREATE TABLE xbooks OF XMLTYPE;

Работать с ними можно, как и с XML-атрибутом в обычной таблице:

INSERT INTO xbooks VALUES

(XMLTYPE('<cover>

<title>Oracle SQL*Loader</title>

<author>Jonathan Gennick</author>

<author>Sanjay Mishra</author>

</cover>'));

INSERT INTO xbooks VALUES

(NEW XMLTYPE('<?xml version="1.0"?>

<cover>

<title>SQL*Plus Pocket Reference</title>

<author>Jonathan Gennick</author>

</cover>'));

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

SELECT * FROM xbooks;

SELECT VALUE(x) FROM xbooks x;

SELECT XMLDATA FROM xbooks;

Так же как для таблиц объектов прочих типов, элементы таблицы объектов XML имеют ссылки, то есть позволяют ссылаться на себя через REF в других типах и таблицах:

SELECT REF(x) FROM xbooks x;

SELECT DEREF(REF(x)) FROM xbooks x;

У этого типа нет свойств, но есть методы. В этом можно убедиться, сделав запрос от имени SYS:

COLUMN text FORMAT A80

SELECT text

FROM user_source

WHERE name ='XMLTYPE' AND type='TYPE'

ORDER BY line;

Исследование каталога rdbms/admin позволяет обнаружить и исходное описание этого типа (но не его тела !) в файле dbmsxmlt.sql. К сожалению в документации описания этих методов разбросаны по разным местам, не всегда последовательны и ясны. Так например, EXTRACT и EXISTSNODE (о последней речь шла выше), возведены в ранг функций SQL, то есть описаны в книжке документации по SQL в разделе «Функции», в то время как из предыдущего запроса к словарю-справочнику следует, что это методы. О том же говорит синтаксис употребления. Для EXISTSNODE пример уже приводился, а для EXTRACT он может выглядеть так:

SELECT b.description.EXTRACT('/cover/title') FROM books b;

(Сравните с примером использования функции EXTRACTVALUE выше).

Вот некоторые другие примеры методов XMLTYPE:

SELECT b.description.GETCLOBVAL() FROM books b;

SELECT b.description.GETSTRINGVAL() FROM books b;

SELECT b.description.GETROOTELEMENT() FROM books b;

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

Правда, объектность типа XMLTYPE реализована не в полной степени. Так, попытка создать в таблице столбец из коллекции документов XML (вложенной таблицы или массива VARRAY) в версии 9.2 терпит неудачу. Это относится только к БД; в PL/SQL этих проблем не возникает:

SQL> declare type xml_nt is table of xmltype index by varchar2(10);

2 begin null; end;

3 /

PL/SQL procedure successfully completed.

XMLTYPE – тип XML

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

INSERT INTO books VALUES (101, XMLTYPE('<cover><title></title>'));

С дугой стороны, Oracle поймет правильно составленные директивы XML и встроенное в текст описание DTD:

INSERT INTO books VALUES

(101

, XMLTYPE('<?xml version="1.0"?>

<!DOCTYPE cover [

<!ELEMENT cover (title, author*, pages)>

<!ELEMENT title (#PCDATA)>

<!ELEMENT author (#PCDATA)>

<!ELEMENT pages (#PCDATA)>

<cover>

<title>SQL*Plus Pocket Reference</title>

<author>Jonathan Gennick</author>

</cover>'));

Убедитесь в этом сами, что Oracle действительно соотносит описание DTD самому тексту документа !

Для выборки можно использовать специально придуманные для XMLTYPE функции. Так, функция EXTRACTVALUE извлекает значения элемента из документа XML:

SELECT id, EXTRACTVALUE(description, '/cover/title')

FROM books;

Функция EXISTSNODE дает возможность использовать в SQL условие отбора XPath (язык отбора, принятый в технологиях XML):

SELECT id, b.description.XMLDATA

FROM books b

WHERE b.description.EXISTSNODE('/cover[author="Sanjay Mishra"]')=1;