Иван Сергеевич Задворьев
Язык PL/SQL
Защита исходного кода
При создании хранимых программ PL/SQL в словаре-справочнике базы данных Oracle сохраняются и байт-код программ и их исходный код, который доступен для построчного просмотра в представлениях словаря-справочника данных DBA_SOURCE, ALL_SOURCE и USER_SOURCE.
SQL> CREATE OR REPLACE PROCEDURE test AS
2 i INTEGER := 1;
3 BEGIN
4 SELECT i+1 INTO i FROM dual;
5 END;
6 /
Procedure created.
SQL> SELECT TEXT FROM USER_SOURCE
2 WHERE name='TEST' AND type='PROCEDURE'
3 ORDER BY line
4 /
TEXT
–
PROCEDURE test AS
i INTEGER := 1;
BEGIN
SELECT i+1 INTO i FROM dual;
END;
Именно из этих представлений словаря-справочника данных формируют исходный код хранимых программ GUI-клиенты (Quest SQL Navigator, TOAD, PL/SQL Developer) при открытии программ в Stored Program Editor и выполнении операций Extract DDL. Администратор базы данных в представлении DBA_SOURCE может посмотреть исходный код любой хранимой программы и фактически получается так, что все программное обеспечение, написанное на PL/SQL, является open-source software.
Разобраться в коде PL/SQL довольно легко и у кого-то может возникнуть желание внести изменения в логику чужой программы или в ее настройки. Довольно часто настройки программ PL/SQL «зашивают» в коде как локальные константы тел пакетов, и может возникнуть желание несанкционированно их поменять. Помимо этого на исходный код программ PL/SQL могут иметься права как на интеллектуальную собственность.
По этим причинам возникает необходимость защитить исходный код программ PL/SQL от чтения и изменения. Сделать так, чтобы исходный код не распространялся вместе с программами нельзя, но можно его специальным образом обработать – привести к нечитаемому виду. Мы будем использовать термин «нечитаемый код» («скрытый код»), а процесс приведения кода к нечитаемому виду называть сокрытием кода.
Для приведения кода PL/SQL к нечитаемому виду есть три средства:
утилита wrap;
встроенный пакет DBMS_DDL;
встроенный пакет DBMS_WRAP (с Oracle 10g Release 2).
Общая схема сокрытия исходного кода PL/SQL следующая:
программист разрабатывает программу PL/SQL как обычно;
выгружает исходный код программы в текстовый файл (операция Extract DDL средства разработки на PL/SQL);
обрабатывает исходный код в файле утилитой wrap;
полученный DDL-скрипт с нечитаемым исходным кодом программы PL/SQL доставляется администратору, который «прогоняет» его в базе данных.
Утилита wrap работает из командной строки операционной системы. Для Windows ее исполняемый файл wrap.exe нужно запустить из подкаталога BIN домашнего каталога инсталляции Oracle. Формат вызова утилиты:
wrap iname=исходный_файл [oname=обработанный файл]
Обработаем исходный код нашей процедуры test.
C:\Temp>wrap.exe iname=test.sql oname=test_wrapped.sql
PL/SQL Wrapper: Release 11.2.0.2.0-Production on Wed Jan 13 17:51:44 2015
Copyright (c) 1993, 2009, Oracle. All rights reserved.
Processing test.sql to test_wrapped.sql
Можно сразу посмотреть в текстовом редакторе получившийся файл test_wrapped.sql, но лучше мы сначала пересоздадим нашу процедуру, а потом посмотрим ее код в USER_SOURCES.
C:\Temp\sqlplus.exe u1/u1password @test_wrapped.sql
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 13 17:57:02 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Procedure created.
SQL> SELECT text FROM USER_SOURCE
2 WHERE name='TEST' AND type='PROCEDURE'
3 ORDER BY line
4 /
TEXT
–
PROCEDURE test wrapped
a000000
ab
abcd
…
abcd
7
51 8d
suQy2odKI6E8Sra9zNpbNb8IqrAwg5SXf8upynREacFBBrvc2NNbRvLsynXhgKU2wLXjHtmY
DTDLTIiGQltxbGfKvlKRlWFqVHtX8kGHrS0gyD3ka0e/J74vRQynV1uZUJv7WQW2hw==
Нечитаемый исходный код еще называют «wrap’ленный исходник». Отметим его следующие свойства:
исходный текст процедуры test стал совершенно нечитаемым;
после названия хранимой процедуры test есть слово wrapped, говорящее о проведенном сокрытии кода.
При сокрытии исходного кода PL/SQL следует учитывать следующие обстоятельства:
сокрытие кода никак не влияет на функциональность программы PL/SQL, просто становится нечитаемым ее исходный код;
общепринято оставлять читаемым спецификации пакетов (то есть интерфейсную часть) и «закрывать» их тела (в частности, так сделано практически для всех встроенных пакетов);
нельзя делать нечитаемым исходный код триггеров (для этого следует вынести логику из триггеров в хранимые процедуры и функции с нечитаемым кодом и в триггерах только вызывать их);
программы с нечитаемым кодом не могут быть созданы в базе данных версии ниже той, чем версия утилиты, которой они были обработаны;
на каждую версию wrap рано или поздно найдется unwrap или rewrap.
Известно несколько программ, выполняющих обратное преобразование нечитаемого кода, для которых надо только аккуратно выгрузить нечитаемый код из словаря-справочника данных (обычная операция Extract DDL здесь непригодна). Имеются и публикации на эту тему.
Встроенные пакеты
В базах данных Oracle имеется большое количество встроенных пакетов PL/SQL, предназначенных для облегчения и унификации процесса разработки на PL/SQL. Эти пакеты следует рассматривать как стандартные библиотеки, имеющиеся в других языках программирования, например, в C++ и Python. Встроенные пакеты PL/SQL также иногда называют поставляемыми пакетами (Oracle Supplied PL/SQL Packages).
Встроенные пакеты находятся в схеме пользователя SYS и имеют имена с префиксами DBMS и UTL. Число встроенных пакетов растет с выходом каждой новой версии сервера Oracle, вместе с Oracle 11g поставляется свыше 230 встроенных пакетов.
Некоторые встроенные пакеты, например, DBMS_SQL и DBMS_LOB используются при разработке программ PL/SQL очень часто, другие встроенные пакеты используются относительно редко. В литературе по PL/SQL обычно рассматриваются встроенные пакеты, предназначенные для организации ввода-вывода из программ PL/SQL и некоторые встроенные пакеты, предназначенные для автоматизации администрирования баз данных.
Далее приведены примеры использования следующих четырех встроенных пакетов:
DBMS_JOB (управление заданиями);
UTL_FILE (файловый ввод-вывод);
DBMS_LOB (работа с большими объектами);
DBMS_SQL (динамический SQL).
Управление заданиями
В большинстве вычислительных систем существует механизм, который позволяет автоматически запускать задания. Например, в операционной системе Microsoft Windows есть «Планировщик задач» (Task scheduler), который предоставляет возможность запланировать запуск программ в определенные моменты времени или через заданные временные интервалы. В UNIX-подобных операционных системах (Linux, Oracle Solaris) есть cron – классический демон-планировщик задач, использующийся для запуска заданий в определенное время.
Для сервера Oracle задания запускаются специальными фоновыми процессами (Job queue processes, Jnnn), а для управления заданиями предназначен встроенный пакет DBMS_JOB. Число фоновых процессов Jnnn настраивается параметром экземпляра job_queue_processes. Если задания не запускаются в назначенное им время, то следует проверить значение этого параметра, выставить при необходимости для него ненулевое значение и перезапустить экземпляр Oracle.
Автоматический запуск заданий в Oracle может использоваться для решения задач администрирования баз данных, например, автоматического сбора статистики или анализа журналов аудита. Также в заданные моменты времени могут автоматически запускаться и прикладные задачи, такие, например, как загрузка данных, поступивших из внешних источников, формирование месячных счетов к оплате или расчет клиентской задолженности. Ресурсоемкие операции, такие как подготовка аналитических отчетов, следует запускать в то время, когда серверы баз данных загружены меньше – ночью или в выходные дни. Понятно, что такие задания также должны запускаться автоматически, без участия человека.
При использовании пакета DBMS_JOB можно поставить в очередь заданий новое задание, которое будет автоматически выполнено в указанные моменты времени. Заданием в Oracle является анонимный блок PL/SQL, в котором, как правило, вызываются хранимые программы.
Распространено мнение, что среди всех встроенных пакетов DBMS_JOB – наиболее часто используемый администраторами баз данных Oracle.
Таблица 8. Программы пакета DBMS_JOB.
Программа
Описание программы
SUBMIT (процедура)
отправляет новое задание в очередь заданий
CHANGE (процедура)
изменяет параметры задания
WHAT (процедура)
изменяет описание задания
NEXT_DATE (процедура)
изменяет следующее время выполнения задания
REMOVE (процедура)
удаляет задание из очереди
RUN (процедура)
указывает немедленно выполнить задание
INTERVAL (процедура)
изменяет интервал между запусками задания
Будем рассматривать в качестве задания анонимный блок PL/SQL, запускающий процедуру p1.
CREATE TABLE job_test (insert_date DATE);
CREATE PROCEDURE p1 AS
BEGIN
INSERT INTO job_test VALUES(SYSDATE);
COMMIT;
END;
Отправим задание в очередь со следующими параметрами:
SQL> DECLARE
2 l_job_num INTEGER;
3 BEGIN
4 DBMS_JOB.submit(job =>l_job_num,
5 what=>'BEGIN p1; END;',
6 next_date=>sysdate,
7 interval=>'sysdate+10/24/60/60');
8 COMMIT;
9 DBMS_OUTPUT.PUT_LINE(l_job_num);
10 END;
11 /
23
PL/SQL procedure successfully completed.
В очередь будет помещено новое задание BEGIN p1; END; с немедленным выполнением после помещения в очередь и последующим выполнением каждые десять секунд, что задается выражением sysdate+10/24/60/60. На экран выводится уникальный номер задания, назначенный ему при постановке в очередь (23). Важно отметить, что после вызова процедуры DBMS_JOB.submit необходимо явно зафиксировать транзакцию, в противном случае задание в очередь поставлено не будет.
Посмотрим на результаты работы процедуры p1 и убедимся, что задание запускается каждые десять секунд:
SQL> SELECT TO_CHAR(insert_date,'DD.MM.YYYY HH24:MI:SS') AS insert_date
2 FROM job_test ORDER BY insert_date;
INSERT_DATE
–
25.01.2015 11:26:18
25.01.2015 11:26:28
25.01.2015 11:26:38
Данные о заданиях пользователя, которые в данный момент находятся в очереди, можно просмотреть в представлении словаря-справочника данных USER_JOBS:
SQL> SELECT job,log_user,last_sec,next_sec,broken,interval,what
2 FROM USER_JOBS;
JOB LAST_SEC NEXT_SEC BROKEN INTERVAL WHAT
– – – – – –
23 11:26:51 11:27:01 N sysdate+10/24/60/60 BEGIN p1; END;
В первом столбце отображаются номера заданий, назначаемые им при постановке в очередь. В следующих столбцах отображаются время последнего и следующего выполнения задания, интервал выполнения и анонимный блок PL/SQL для выполнения задания. Меткой BROKEN (заблокировано) помечаются те задания, при шестнадцати попытках выполнения которых произошли ошибки. Заблокированные задания перестают выполняться, поэтому администратору баз данных следует контролировать содержание представления DBA_JOBS, в котором отображаются сведения обо всех заданиях. Если в задании временно отпала необходимость, то его можно пометить как заблокированное специально, используя для этого процедуру DBMS_JOB.broken.
Удаляется из очереди задание следующим образом:
SQL> BEGIN
2 DBMS_JOB.remove(23);
3 COMMIT;
4 END;
5 /
PL/SQL procedure successfully completed.
Файловый ввод-вывод
Встроенный пакет UTL_FILE предоставляет программам PL/SQL возможность работать с файлами на сервере. С помощью этого пакета можно решать следующие задачи:
формирование на сервере небольших текстовых отчетов;
загрузка в базу данных строк небольших текстовых файлов;
чтение log-файлов сервера для мониторинга ошибок;
формирование log-файлов программ PL/SQL.
Для загрузки/выгрузки данных значительных (мегабайты, гигабайты) объемов следует использовать не пакет UTL_FILE, а использовать клиентские приложения – SQL*Loader для загрузки данных и SQL*Plus для их выгрузки. Если запускать эти утилиты на сервере баз данных, то их использование – самый быстрый способ загрузить и выгрузить данные из базы в файлы. В то же время при разработке серверной бизнес-логики встречаются ситуации, когда читать и писать в файлы хотелось бы именно в хранимых программах на PL/SQL без усложнений, вызванных обращениями к другим средствам. Чтобы для каждого поступившего платежа выгружать на сервере XML-файл размером меньше килобайта пакет UTL_FILE вполне подходит.
Настройка ограничений доступа к каталогам
Считается, что UTL_FILE потенциально может представлять большую угрозу безопасности баз данных Oracle. Этот пакет дает возможность доступа к файлам от имени такого пользователя операционной системы, который в ней имеет доступ ко всем файлам данных, журнала базы данных, управляющим файлам, файлам паролей и параметров. Получив неправомерный доступ к пакету UTL_FILE, при определенных навыках можно довольно быстро грамотно вывести базу данных из строя или реализовать угрозу нарушения конфиденциальности информации (например, прочитать файлы данных).
По этой причине перед использованием пакета UTL_FILE требуется настроить ограничения доступа к каталогам, с файлами из которых сможет осуществлять операции пакет. Настроить ограничения доступа к каталогам можно сделать двумя способами:
использовать параметр базы данных utl_file_dir;
создать специальные объекты баз данных – директории (DIRECTORY) и предоставить пользователям привилегии доступа к ним.
В параметре utl_file_dir задается список каталогов, с файлами из которых может осуществлять операции UTL_FILE. Для просмотра текущего значения параметра utl_file_dir можно использовать представление словаря данных V$PARAMETER или команду SHOW PARAMETER утилиты SQL*Plus:
SQL> SELECT value dir FROM V$PARAMETER
2 WHERE name='utl_file_dir';
DIR
–
C:\Dir1
SQL> SHOW PARAMETER utl_file_dir
NAME TYPE VALUE
– – –
utl_file_dir string C:\Dir1
Значением параметра utl_file_dir может быть и символ *, что означает, что с помощью пакета UTL_FILE можно получить доступ к файлам в любых каталогах, к которым есть доступ у пользователя oracle операционной системы сервера. Выставление этого параметра таким способом обычно практикуется в тестовых инсталляциях и должно быть совершенно исключено в production по требованиям безопасности.
Директория (directory) – объект баз данных Oracle, являющийся псевдонимом каталога в файловой системе сервера. Директории создаются DDL-командами следующего вида:
SQL> CREATE OR REPLACE DIRECTORY dir1 AS 'C:\Dir1';
Directory created.
По сравнению с использованием параметра utl_file_dir, директории дают более гибкие возможности по управлению доступом к файлам. Так, после того как директория создана, администратор базы данных может предоставить конкретным пользователям привилегии только на чтение из нее:
SQL> GRANT READ ON DIRECTORY dir1 TO user1;
Grant succeeded.
Параметр utl_file_dir задает список каталогов сразу для всех пользователей, которые имеют привилегии на выполнение программ пакета UTL_FILE, причем настроить конкретные действия (чтение или запись) с файлами из этих каталогов с его помощью нельзя.
Использование UTL_FILE
В пакете UTL_FILE используется следующая последовательность действий с файлами – сначала файл открывается в заданном режиме, затем производятся действия с его содержимым, по окончании которых файл закрывается. После открытия файла во все процедуры и функции пакета UTL_FILE передается его идентификатор, который представляет собой переменную-запись PL/SQL объявленного в пакете UTL_FILE типа.
Таблица 9. Программы пакета UTL_FILE.
Программа
Описание программы
FOPEN (функция)
открывает файл для чтения/записи
IS_OPEN (функция)
проверяет, открыт ли файл
FCLOSE (процедура)
закрывает открытый файл
FCLOSE_A (процедура)
закрывает все открытые файлы (a – all)
GET_LINE (процедура)
считывает строку из файла
PUT (процедура)
записывает в файл строку без символа конца строки
PUT_LINE (процедура)
записывает в файл строку с символом конца строки
PUTF (процедура)
записывает в файл форматированный текст
FFLUSH (процедура)
вызывает физическую запись буферизированных данных
Файл может быть открыт процедурой UTL_FILE.FOPEN в одном из трех режимов:
для чтения (read) – содержимое файла не будет изменяться;
для записи (write) – содержимое файла будет перезаписано;
для добавления данных (append) – запись будет осуществляться в конец файла без перезаписи имевшегося содержимого.
В качестве примера работы с пакетом UTL_FILE приведем код процедуры table_copy, которая построчно сохраняет выборку SQL-запроса к таблице tab1 в файл. У table_copy имеется параметр p_mode, определяющий режим открытия файла.
SQL> CREATE OR REPLACE PROCEDURE table_copy(p_mode IN VARCHAR2) IS
2 fid UTL_FILE.FILE_TYPE;
3 BEGIN
4 fid := UTL_FILE.FOPEN (location=> 'C:\Dir1',
filename => 'f-name.txt',
open_mode=> p_mode);
5 FOR rec IN (SELECT at1,at2 FROM tab1) LOOP
6 UTL_FILE.PUT_LINE (fid, rec.at1||' '||rec.at2);
7 END LOOP;
8 UTL_FILE.FCLOSE (fid);
9 EXCEPTION
10 WHEN UTL_FILE.INVALID_PATH
11 THEN DBMS_OUTPUT.PUT_LINE('Неверный каталог');
12 WHEN UTL_FILE.INVALID_MODE
13 THEN DBMS_OUTPUT.PUT_LINE('Неверный режим работы с файлом');
14 WHEN UTL_FILE.INVALID_FILEHANDLE
15 THEN DBMS_OUTPUT.PUT_LINE('Ошибочный дескриптор файла');
16 WHEN UTL_FILE.READ_ERROR
17 THEN DBMS_OUTPUT.PUT_LINE('Ошибка при чтении файла');
18 WHEN UTL_FILE.WRITE_ERROR
19 THEN DBMS_OUTPUT.PUT_LINE('Ошибка при записи в файл');
20 WHEN UTL_FILE.INTERNAL_ERROR
21 THEN DBMS_OUTPUT.PUT_LINE('Произошла внутренняя ошибка');
22 WHEN OTHERS
25 THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
26 END;
27 /
Procedure created.
SQL> BEGIN
2 table_copy('A');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM tab1;
AT1 A
– -
1 A
2 B
3 C
– команда HOST утилиты SQL*Plus позволяет выполнять команды ОС
– выполняем прямо из SQL*Plus команду type ОС Windows
SQL> HOST type C:\Dir1\f-name.txt
1 A
2 B
3 C
В ходе выполнения процедуры table_copy с параметром 'A' (append) в конец файла fname.txt, находящийся в каталоге C:\Dir1, будут записаны все строки таблицы tab1. Обратите внимание – если вызвать процедуру table_copy с параметром 'W' (write), то существующее содержимое файла будет перезаписано на содержимое таблицы.
В старших версиях сервера Oracle с помощью пакета UTL_FILE можно копировать и удалять файлы. Также для работы с файлами на сервере можно использовать хранимые программы на Java.