Лучшее из практического опыта использования прав инициатора вызова и функций.
Вопрос: Ответ:
Обычно всё, что требуется, это написать основную программу, которая возвращает управление подпрограмме, название которой ещё неизвестно при написании программы. Таким образом, программа должна во время вызова понимать, какая программа ее вызвала.
Один из способов использования механизма возврата, это применение динамического полиморфизма и объектных типов Oracle. Это наиболее удобный способ, который необходимо будет рассмотреть в другой раз. А в этот раз я покажу, как ещё можно использовать возвраты и права инициатора вызова, чтобы получить желаемый результат.
Давайте начнём с некоторых принципов прав инициатора вызова, которые применяются ко всем SQL-предложениям (как статическим, так и динамическим), выполняемым в PL/SQL-блоке. При применении прав инициатора вызова, область имён и вычислений соответствующих привилегий возникает в процессе вызова (позднее рассмотрим роли, распознаваемые и используемые для вычисления этих привилегий). Права инициатора вызова применяются к следующим предложениям:
- Предложения для манипуляций с данными SELECT, INSERT, UPDATE и DELETE
- Предложение для управления транзакцией LOCK TABLE
- Предложения для управления курсором OPEN и OPEN-FOR
- Все динамические SQL-предложения (EXECUTE IMMEDIATE, OPEN FOR и DBMS_SQL)
Предположим, я включу оператор AUTHID CURRENT_USER в заголовок моей программы. Когда я компилирую эту программу, текущей схеме всё ещё требуются непосредственно выданные привилегии на все объекты, к которым обращается программа.
Затем я передаю авторизацию другим схемам для выполнения моей программы. Когда пользователь, присоединившийся к одной из этих схем, вызывает мою программу, зависимости от объектов базы данных в каждом из перечисленных выше предложений будут разрешены в соответствии с привилегиями (прямыми или предоставленными через роли) текущей схемы.
Один из очевидных выводов, которые можно сделать из этого, это то, что оператор AUTHID CURRENT_USER не будет применим к статическим вызовам других подпрограмм, вызываемых из программ с правами инициатора вызова.
Статический вызов программы, это когда программа определена на момент компиляции. Например, в следующем фрагменте кода, proc1 статически вызывает proc2.
PROCEDURE proc1 IS BEGIN proc2 (); END proc1;
Во время компиляции proc1 в схеме ABC, Oracle Database разрешает ссылку на proc2 применительно к привилегиям этой схемы. Теперь предположим, что база данных ищет процедуру proc2 на уровне схемы ABC. Выдаём привилегию EXECUTE для ABC.proc1 другой схеме, такой как DEF, в которой есть своя собственная процедура proc2. Когда пользователь соединяется как DEF и выполняет ABC.proc1, этот пользователь всегда будет вызывать ABC.proc2, а не DEF.proc2.
Означает ли это, что невозможно создать работающую программу "запись в файл" с правами инициатора вызова? Не совсем. Можно на самом деле заставить Oracle динамически выбирать, какую программу он должен вызвать, но для этого необходимо применить возможности динамического PL/SQL.
Рассмотрим два простых демонстрационных примера о влиянии прав инициатора вызова, а затем я продемонстрирую динамический PL/SQL-"обход"
Сначала рассмотрим AUTHID CURRENT USER по отношению к объектам для хранения данных. В Листинге 1 в схеме HR создаётся процедура с правами инициатора вызова show_my_data, которая показывает количество строк в таблице my_data table (10). Выдаём привилегию EXECUTE пользователю SCOTT для вызова этой программы. В схеме SCOTT тоже есть таблица my_data, в которой 1 строка. Затем SCOTT вызывает HR.show_my_data, и программа отображает 1, а не 10 строк.
Листинг 1: Создание и вызов show_my_data
SQL> CONNECT hr/hr
Connected.
SQL> CREATE TABLE my_data (VALUE NUMBER)
2 /
Table created.
SQL> BEGIN
2 FOR indx IN 1.. 10
3 LOOP
4 INSERT INTO my_data
5 VALUES (indx);
6 END LOOP;
7
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE show_my_data
2 AUTHID CURRENT_USER
3 IS
4 num PLS_INTEGER;
5 BEGIN
6 SELECT COUNT (*)
7 INTO num
8 FROM my_data;
9
10 DBMS_OUTPUT.put_line ('Count of my_data = ' // num);
11 END show_my_data;
12 /
Procedure created.
SQL> GRANT EXECUTE ON show_my_data TO scott
2 /
Grant succeeded.
SQL> CONNECT scott/tiger
Connected.
SQL> CREATE TABLE my_data (VALUE NUMBER)
2 /
Table created.
SQL> BEGIN
2 INSERT INTO my_data
3 VALUES (1);
4
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> CALL hr.show_my_data();
Count of my_data = 1
Как видите, даже несмотря на то, что я запустил процедуру show_my_data пользователя HR, эта программа показывает мне количество из таблицы my_data пользователя SCOTT.
Однако теперь давайте попробуем создать программу "запись в файл". Сначала создадим директорию базы данных у пользователя HR и функцию форматирования "по умолчанию" (текст переводится в верхний регистр):
CONNECT hr/hr CREATE DIRECTORY temp AS 'c:\temp' / GRANT WRITE ON DIRECTORY temp TO scott; / CREATE OR REPLACE FUNCTION format_line (line_in IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN UPPER (line_in); END format_line; /
Затем создадим очень простую программу, показанную в Листинге 2, которая выполняет "запись в файл", и предоставим пользователю SCOTT возможность вызвать её.
Листинг 2: Создание write_to_file
CREATE OR REPLACE PROCEDURE write_to_file ( dir_in IN VARCHAR2 , file_name_in IN VARCHAR2 , lines_in IN DBMS_SQL.varchar2s ) AUTHID CURRENT_USER IS l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen (LOCATION => dir_in , filename => file_name_in , open_mode => 'W' , max_linesize => 32767 ); FOR indx IN 1.. lines_in.COUNT LOOP UTL_FILE.put_line (l_file, format_line (lines_in (indx))); END LOOP; UTL_FILE.fclose (l_file); END write_to_file; / GRANT EXECUTE ON write_to_file TO scott /
Затем присоединимся как SCOTT, создадим форматирующую функцию, которая переводит текст в нижний регистр, и вызовем write_to_file как показано в Листинге 3.
Листинг 3: Создание format_line (и вызов write_to_file)
CREATE OR REPLACE FUNCTION format_line (line_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN LOWER (line_in);
END format_line;
/
DECLARE
l_lines DBMS_SQL.varchar2s;
BEGIN
l_lines (1) := 'steven feuerstein';
l_lines (2) := 'is obsessed with PL/SQL.';
hr.write_to_file ('TEMP', 'myfile.txt', l_lines);
END;
/
К сожалению, я получил следующий текст в выходном файле myfile.txt:
STEVEN FEUERSTEIN IS OBSESSED WITH PL/SQL.
Итак, не очень похоже, что мы смогли использовать права инициатора при вызове программы? Хорошо, может быть, попробуем другой способ: использование динамического SQL.
Я переписал исходную программу write_to_file в схеме HR, как показано в Листинге 4.
Листинг 4: Создание изменённой write_to_file
CREATE OR REPLACE PROCEDURE write_to_file ( dir_in IN VARCHAR2 , file_name_in IN VARCHAR2 , lines_in IN DBMS_SQL.varchar2s ) AUTHID CURRENT_USER IS l_file UTL_FILE.file_type; l_newline VARCHAR2 (32767); BEGIN l_file := UTL_FILE.fopen (LOCATION => dir_in , filename => file_name_in , open_mode => 'W' , max_linesize => 32767 ); FOR indx IN 1.. lines_in.COUNT LOOP EXECUTE IMMEDIATE 'BEGIN :new_line := format_line (:old_line); END;' USING OUT l_newline, IN lines_in (indx); UTL_FILE.put_line (l_file, l_newline); END LOOP; UTL_FILE.fclose (l_file); END write_to_file; /
Заметьте, что я переместил вызов функции format_line в предложение EXECUTE IMMEDIATE. Таким образом, я вызвал функцию из динамического PL/SQL-блока. А теперь я присоединюсь как SCOTT и вызову write_to_file:
DECLARE
l_lines DBMS_SQL.varchar2s;
BEGIN
l_lines (1) := 'steven feuerstein';
l_lines (2) := 'is obsessed
with PL/SQL.';
hr.write_to_file ('TEMP',
'myfile.txt',
l_lines);
END;
/
И вот видим следующее содержимое в выходном файле myfile.txt:
steven feuerstein is obsessed with pl/sql.
О, работает! Неужели это возможно?
Переместив вызов программы в динамически выполняемый PL/SQL-блок, вызов стал выполняться из предложения, к которому могут быть применены права инициатора вызова: EXECUTE IMMEDIATE. Таким образом, блок теперь выполняется в правах пользователя SCOTT', потому что вызывается версия format_line пользователя SCOTT.
Поэтому именно его права как инициатора вызова могут применяться для выполнения стека вызовов при использовании динамического PL/SQL.
Обратите внимание, пожалуйста, что динамическое выполнение PL/SQL-подпрограммы медленнее, чем статическое. Для долго выполняющихся программ эти издержки будут несущественны, однако имейте это в виду при создании программ, которые завершаются очень быстро.
Я сравнил производительность статического и динамического запуска программы, которая вызывает функцию USER и сохраняет это значение в локальной переменной. Запуск программы 100,000 раз с использованием статического вызова выполняется за 2.83 секунд. Динамический запуск выполняется за 6.66 секунд.
Я перенял эту технологию у Алекса Ньюжтена (Alex Nuijten) из AMIS ( technology.amis.nl/blog ), который недавно участвовал в прошедшей конференции по Oracle PL/SQL Programming 2007 в Сан Матео, Калифорния ( www.odtugopp07.com ) и был награжден Oracle Development Tools User Group PL/SQL Innovation и Community Award от имени группы AMIS Oracle.
И последнее замечание: альтернативный способ достичь такого же эффекта (выбор программы для выполнения во время работы), это использование динамического полиморфизма. Эта техника встроена в объектные типы в PL/SQL-а именно с помощью установки иерархии объектных типов с ключевым словом UNDER. Более подробно об объектных типах и динамическом полиморфизме в PL/SQL, изучайте Главу 12, "Using PL/SQL With Object Types" в Oracle Database PL/SQL User's Guide and Reference .
Никакого выхода
Вопрос: Ответ:
- Не используйте OUT или IN OUT аргументы в списке параметров функции.
- Возвращайте все данные в операторе RETURN.
- Кодируйте только одно предложение RETURN в выполняемой части функции и делайте RETURN последней строкой в этой секции.
- Если необходимо вернуть несколько значений, можно использовать как композитные типы данных (запись, объект, коллекция и т.п.), так и изменить функцию на процедуру, и затем использовать OUT или IN OUT аргументы.
Почему Oracle позволяет нам применять OUT-аргументы в списке параметров функции? Вероятно, потому что он распознаёт этот мир не как чёрно-белый, а создаёт множество оттенков серого. Программисты имеют большую свободу выбора, с множеством различных стилей написания кода.
Я очень рад, что Oracle не отказывается от практики использования OUT-параметров функций (которая не является, между прочим, универсально принятой или распространённой практикой в мире программирования) при его реализации PL/SQL.
Мой опыт знакомства с миром программирования на C, например, показывает, что разработчики обычно пишут функции, которые возвращают код возврата, обозначающий, успешно ли завершена функция. Затем они используют OUT-аргументы в списке параметров для возврата информации из функции.
Мне не нравится этот подход, особенно в PL/SQL, однако я чувствую, что нужно больше гибкости языка, а не меньше. И необходимо разрабатывать собственные индивидуальные технологии, чтобы выбирать, какие возможности PL/SQL использовать, а какие нет.
Конечно, могут быть ситуации, в которых функция с OUT-параметром полезна. Рассмотрим следующий блок. Функция source_code.more_data возвращает значение Boolean, которое используется для выхода из цикла, и она же возвращает как OUT-аргумент структуру данных, которая затем обрабатывается в цикле.
BEGIN source_code.init (...); WHILE source_code.more_data (output_structure) LOOP process (output_structure); END LOOP; source_code.TERMINATE (); END;
Да, я, конечно, могу переписать этот код, так чтобы он использовал функцию без OUT-параметра или процедуру, однако это сделает код более нескладным и сложным для чтения.
Поэтому мы явно не хотим, чтобы Oracle делал функции с OUT или IN OUT параметрами недопустимыми, однако я думаю, что такой список параметров становится полезным только в специфичных ситуациях.