ACE Director
Источник: журнал Oracle Magazine #5 (сентябрь-октябрь) 2013
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53plsql-1999801.html
Oracle Database 12c предлагает большое количество улучшений, которые можно определить и использовать в программных PL/SQL-модулях.
В этой статье рассматриваются несколько новых функций Oracle Database 12c, которые позволят следующее:
В Oracle Database 11g был внедрен кэш результатов PL/SQL-функций, который представляет собой очень мощный, эффективный и простой в использовании механизм кэширования. Основная цель этого кэша заключается в том, что если строка данных не изменилась с момента последнего ее извлечения из базы данных, не нужно повторно выполнять SQL предложение для восстановления кэша.
Это справедливо во всем экземпляре базе данных. Другими словами, предположим, что пользователь, подключенный к схеме USER_ONE, выполняет функцию, получая строку из таблицы сотрудников для ID = 100, ее результат кэшируется. Когда другой пользователь, подключенный к схеме USER_TWO, выполняет ту же самую функцию для извлечения сотрудников с такими же идентификаторами, то информация извлекается непосредственно из кэша, а не в результате выполнения SELECT-предложения.
Если вы еще не используете эту функцию (или применяете Oracle Database 11g), я настоятельно рекомендую поинтересоваться и начать ее применение в тесном сотрудничестве с DBA, так как, что размер кэша результатов должен быть задан надлежащим образом.
Даже в Oracle Database 11g Release 2 нельзя было объединить права инициатора вызова (фраза AUTHID CURRENT_USER) с кэшем результата функции (ключевое слово RESULT_CACHE). При попытке компиляции следующей функции:
CREATE OR REPLACE FUNCTION last_name (
employee_id_in
IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
AUTHID CURRENT_USER
RESULT_CACHE
IS
l_return employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_return;
END;
/
Результатом была ошибка компиляции:
PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules [PLS-00999: ограничение выполнения (может быть временным) RESULT_CACHE отвергнуто в подпрограммах в модулях Invoker-Rights]
Причина этого ограничения и есть весь смысл прав инициатора вызова. Во время выполнения механизм PL/SQL использует привилегии текущего пользователя для разрешения ссылок на объекты базы данных, такие как таблицы и представления. Но если такая функция была скомпилирована с возможностью RESULT_CACHE, то (на примере выше) после выполнения функции USER_ONE, передавая значение 100, когда USER_TWO вызывает ту же функцию, тело функции выполняться не будет, и ссылка на таблицу EMPLOYEES не будет выполнена в соответствии с привилегией пользователя USER_TWO. Это может вызвать серьезные проблемы безопасности.
Хорошая новость в том, что это ограничение было временным. В Oracle Database 12c теперь можно без ошибок скомпилировать функции, например, last_name (см. выше), и Oracle Database 12c правильно делает свое дело.
За кулисами Oracle Database 12c передает имя текущего пользователя в качестве скрытого параметра, это значение кэшируется вместе с значениями всех аргументов, переданных функции. Таким образом, каждый раз, когда вызывается функция last_name, Oracle Database 12c проверяет, была ли эта функция ранее связана с теми же ID сотрудников и того же текущего пользователя.
Это означает, что результирующий кэш для вызова инициатора с правами функции (логически) секционируется по имени текущего пользователя. Следовательно, результирующий кэш для вызова инициатора с правами функции повышает производительность только в тех ситуациях, когда тот же самый пользователь неоднократно вызывает функцию с теми же значениями аргумента. Другой способ объяснить это, чтобы подчеркнуть, что в Oracle Database 11g Release 2 можно было бы добиться того же эффекта только в случае изменения реализации функции last_name, как показано в листинге 1.
CREATE OR REPLACE PACKAGE employee_api
AUTHID CURRENT_USER
IS
FUNCTION last_name (
employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY employee_api
IS
FUNCTION i_last_name (
employee_id_in IN employees.employee_id%TYPE,
user_in IN VARCHAR2 DEFAULT USER)
RETURN employees.last_name%TYPE
RESULT_CACHE
IS
l_return employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_return;
END;
FUNCTION last_name (
employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
IS
l_return employees.last_name%TYPE;
BEGIN
RETURN i_last_name (employee_id_in,
USER);
END;
END;
/
Обратите внимание, что функция last_name определена в спецификации пакета и не является результатом кэширования. Вместо этого, что общие (public) функции (объявленные в спецификации пакета) просто вызывает частную/внутреннюю "версию" функции, которая имеет второй параметр: user (пользователь).
Таким образом, каждый раз при вызове employee_api.last_name, Oracle Database 11g Release 2 добавляет имя пользователя в набор значений, используемый базой данных, чтобы определить, есть ли совпадение в результате кэша.
Это больше не необходимо в Oracle Database 12c вам просто нужно решить, стоит добавить права инициатора к вызову программ RESULT_CACHE.
Разработчики уже давно смогли вызывать свои собственные PL/SQL- функции в SQL-предложениях. Предположим, я создал функцию с именем BETWNSTR, которая возвращает подстроку между заданными начальным и конечным местами:
FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN PLS_INTEGER
, end_in IN PLS_INTEGER
)
RETURN VARCHAR2
IS
BEGIN
RETURN ( SUBSTR (
string_in, start_in,
end_in - start_in + 1 ));
END;
Затем я могу использовать ее в запросе следующим образом:
SELECT betwnstr (last_name, 3, 5) FROM employees
Этот подход предлагает способ, как "продолжить" язык SQL прикладной функциональностью и повторным использованием (а не копированием) алгоритмов. Недостаток пользовательски-определенной функции, выполняющей SQL, является то, что она включает в себя переключение контекста между SQL и PL/SQL механизмами выполнения.
Теперь в Oracle Database 12c можно определить PL/SQL функции и процедуры во фразе подзапроса WITH, а затем использовать их, как и любую другую встроенную или пользовательски-определенную функцию. Эта возможность позволяет мне консолидировать функцию BETWNSTR и показанный выше запрос в одном предложении:
WITH
FUNCTION betwnstr (
string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER)
RETURN VARCHAR2
IS
BEGIN
RETURN (SUBSTR (
string_in,
start_in,
end_in - start_in + 1));
END;
SELECT betwnstr (last_name)
FROM employees
Так почему бы разработчику не скопировать логику PL/SQL-функции в SQL-предложение? Для повышения производительности. Когда я вызываю свои собственные PL/SQL-функции в SQL-предложении, SQL-механизм должен выполнить переключение контекста на PL/SQL-механизм, что существенно влияет на производительность. Перемещение кода внутри SQL-предложения не вызывает переключения контекста.
Хотя пакетную функцию можно вызвать в SQL-предложении, нельзя ссылаться на константу, объявленную в пакете (если только это SQL-предложение не выполняется внутри PL/SQL-блока). Вот пример ссылочного ограничения на константу:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 year_number
CONSTANT INTEGER := 2013;
4 END;
5 /
Package created.
SQL> SELECT pkg.year_number
FROM employees
2 WHERE employee_id = 138
3 /
SELECT pkg.year_number FROM employees
ERROR at line 1:
ORA-06553: PLS-221: 'YEAR_NUMBER' is not
a procedure or is undefined
Классический обходной путь для этого ограничения состоит в погружении функции в пакет, а затем вызове пакетной функции:
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 FUNCTION year_number
4 RETURN INTEGER;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 c_year_number
CONSTANT INTEGER := 2013;
4
5 FUNCTION year_number
6 RETURN INTEGER
7 IS
8 BEGIN
9 RETURN c_year_number;
10 END;
11 END;
12 /
Package body created.
SQL> SELECT pkg.year_number
2 FROM employees
3 WHERE employee_id = 138
4 /
YEAR_NUMBER
———————————
2013
Понадобится много кода и усилий, чтобы просто иметь возможность ссылаться на значения констант в SQL-предложениях. И в Oracle Database 12c такой прием больше не нужен. Вместо этого, Я могу просто создать функцию с фразой WITH:
WITH FUNCTION year_number RETURN INTEGER IS BEGIN RETURN pkg.year_number; END; SELECT year_number FROM employees WHERE employee_id = 138
Вы также найдете в SQL-предложениях PL/SQL-функции, которые удобны в резервных только_для_чтения (standby read-only) базах данных. Хотя нельзя создать “helper” ("помощника") PL/SQL-функций в такой базе данных, можно определить эту функцию непосредственно в запросе.
Эта возможность WITH FUNCTION является очень полезным дополнением к языку SQL. Однако, каждый раз, когда вы собираетесь использовать ее, нужно задать себе такой вопрос: "Зачем нужна одна и та же функциональность в нескольких местах приложения?"
Да, если улучшение при использовании WITH FUNCTION превышает потенциальные избытки копирования и вставки этой логики в несколько SQL-предложений.
Большинство приложений, написанных на PL/SQL, состоят из большого количества пакетов, некоторые из которых являются “top level” API ("верхнего уровня" API), которые используются программистами для реализации требований пользователей и других субъектов, которые являются "помощниками" пакетов, которыми должны использоваться только определенные другие пакеты.
До Oracle Database 12c пакеты PL/SQL не могли помешать сессии использовать какие-либо или все ее подпрограммы в пакетах, для которых в схеме этой сессии были предоставлены права на выполнение EXECUTE. В Oracle Database 12c, напротив, все PL/SQL-программы имеют дополнительную фразу ACCESSIBLE BY, которая позволяет задать специальный whitelist ("белый список") других PL/SQL-блоков, которые могут получить доступ к PL/SQL-блоку, который вы создаете или изменяете.
Давайте рассмотрим пример. Сначала я создаю свою “public” ("общедоступную") спецификацию пакета, который предназначен для использования другими разработчиками для создания приложения.
CREATE OR REPLACE PACKAGE public_pkg IS PROCEDURE do_only_this; END; /
Затем я создаю спецификацию моего “private” («частного») пакета. Пакет является частным в том смысле, что я хочу убедиться, что он может быть вызван только как общедоступный пакет (public_pkg). Далее я добавляю фразу ACCESSIBLE_BY:
CREATE OR REPLACE PACKAGE private_pkg ACCESSIBLE BY (public_pkg) IS PROCEDURE do_this; PROCEDURE do_that; END; /
Теперь пришло время соорудить тело пакета. Процедура public_pkg.do_only_this вызывает подпрограммы private_pkg:
CREATE OR REPLACE PACKAGE BODY public_pkg
IS
PROCEDURE do_only_this
IS
BEGIN
private_pkg.do_this;
private_pkg.do_that;
END;
END;
/
CREATE OR REPLACE PACKAGE BODY
private_pkg
IS
PROCEDURE do_this
IS
BEGIN
DBMS_OUTPUT.put_line ('THIS');
END;
PROCEDURE do_that
IS
BEGIN
DBMS_OUTPUT.put_line ('THAT');
END;
END;
/
Теперь я могу запустить процедуру общедоступного пакета без каких-либо проблем:
BEGIN public_pkg.do_only_this; END; / THIS THAT
Но если я пытаюсь вызвать подпрограмму в частном пакете в анонимном блоке, то увижу такую ошибку:
BEGIN private_pkg.do_this; END; / ERROR at line 2: ORA-06550: line 2, column 1: PLS-00904: insufficient privilege to access object PRIVATE_PKG ORA-06550: line 2, column 1: PL/SQL: Statement ignored
И эта же ошибка произойдет, если я попытаюсь скомпилировать программный блок, который вызовет подпрограмму из частного пакета:
SQL> CREATE OR REPLACE PROCEDURE
use_private
2 IS
3 BEGIN
4 private_pkg.do_this;
5 END;
6 /
Warning: Procedure created with compilation errors.
[Процедура создана с ошибки компиляции.]
SQL> SHOW ERRORS
Errors for PROCEDURE USE_PRIVATE:
LINE/COL ERROR
———————— ——————————————————————————
4/4 PL/SQL: Statement ignored
4/4 PLS-00904: insufficient
privilege to access object
PRIVATE_PKG
Как известно ошибка "PLS" указывает, эта проблема возникла во время компиляции. Во время выполнения этой функции нет падения производительности.
До Oracle Database 12c права определяющего программу блока (определенного фразой AUTHID DEFINER или no AUTHID) всегда определяются привилегиями владельца этого блока. Активация прав на программный блок (определяемая фразой AUTHID CURRENT_USER) всегда выполняется с привилегиями запустившего этот блок.
Следствием этих двух разных установок параметров AUTHID является то, что программные блоки, которые должны быть выполнены для всех пользователей, теперь созданы с определяющими правами модулей. Программа модулей затем выполнит их со всеми привилегиями владельца, что может быть не оптимальным с точки зрения обеспечения безопасности.
В Oracle Database 12c можно предоставить роли PL/SQL-пакетам и процедурам и функциям уровня схемы. Ролевые привилегии, предоставляемые программным модулям, позволяют разработчикам осуществить тонкую настройку привилегий, доступных ранее только для вызывающего программного модуля.
Теперь можно определить программный модуль меткой, имеющей права, а затем дополнять привилегиями вызывающего с конкретными, ограниченными через роли привилегиями.
Давайте рассмотрим пример, который показывает, как предоставить роли программных модулей и их воздействие. Предположим, что схема HR содержит таблицы департаментов и сотрудников, определенные следующим образом и заполненные данными:
CREATE TABLE departments
(
department_id INTEGER,
department_name VARCHAR2 (100),
staff_freeze CHAR (1)
)
/
BEGIN
INSERT INTO departments
VALUES (10, 'IT', 'Y');
INSERT INTO departments
VALUES (20, 'HR', 'N');
COMMIT;
END;
/
CREATE TABLE employees
(
employee_id INTEGER,
department_id INTEGER,
last_name VARCHAR2 (100)
)
/
BEGIN
DELETE FROM employees;
INSERT INTO employees
VALUES (100, 10, 'Price');
INSERT INTO employees
VALUES (101, 20, 'Sam');
INSERT INTO employees
VALUES (102, 20, 'Joseph');
INSERT INTO employees
VALUES (103, 20, 'Smith');
COMMIT;
END;
/
И предположим, что схема SCOTT содержит только таблицу сотрудников, которая определена и заполняется данными следующим образом:
CREATE TABLE employees
(
employee_id INTEGER,
department_id INTEGER,
last_name VARCHAR2 (100)
)
/
BEGIN
DELETE FROM employees;
INSERT INTO employees
VALUES (100, 10, 'Price');
INSERT INTO employees
VALUES (104, 20, 'Lakshmi');
INSERT INTO employees
VALUES (105, 20, 'Silva');
INSERT INTO employees
VALUES (106, 20, 'Ling');
COMMIT;
END;
/
Схема HR также включает процедуру, которая удаляет всех сотрудников из указанного отдела, пока отдел "заморожен" и не имеет своего персонала. Для начала я создам эту процедуру как определитель привилегий подразделения по правам определяющего, как показано в листинге 2.
CREATE OR REPLACE PROCEDURE remove_emps_in_dept (
department_id_in IN employees.department_id%TYPE)
AUTHID DEFINER
IS
l_freeze departments.staff_freeze%TYPE;
BEGIN
SELECT staff_freeze
INTO l_freeze
FROM HR.departments
WHERE department_id = department_id_in;
IF l_freeze = ‘N’
THEN
DELETE FROM employees
WHERE department_id = department_id_in;
END IF;
END;
/
Пользователь SCOTT может выполнить эту процедуру:
GRANT EXECUTE ON remove_emps_in_dept TO SCOTT /
Когда SCOTT выполняет эту процедуру, как показано выше, он удаляет три строки из таблицы HR-служащих, поскольку процедура принадлежит определителю прав.
BEGIN HR.remove_emps_in_dept (20); END; /
Мне нужно изменить этот порядок так, чтобы удались строкисотрудников служащих SCOTT, а не HR. Это именно то, что может сделать определитель (invoker) права. Но если я изменю предложение AUTHID для этой процедуры
AUTHID CURRENT_USER
и запущу процедуру снова, то получу:
BEGIN * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "HR.REMOVE_EMPS_IN_DEPT", line 7 ORA-06512: at line 2
Проблема в том, что Oracle Database теперь использует привилегии пользователя SCOTT, распространяющиеся на две таблицы: HR.departments и SCOTT.employees. SCOTT не имеет никаких привилегий на таблицу HR департаментов, поэтому Oracle Database выдает ошибку ORA-00942.
До Oracle Database 12c администратору базы данных пришлось бы предоставить пользователю SCOTT необходимые привилегии на таблицы HR.departments и SCOTT.employees. Однако теперь администраторы баз данных могут предпринять следующие шаги:
CREATE ROLE hr_departments / GRANT hr_departments TO hr /
Подключение к HR, передача необходимых привилегий в роль, а затем предоставление роли процедуре:
GRANT SELECT ON departments TO hr_departments / GRANT hr_departments TO PROCEDURE remove_emps_in_dept /
И теперь, когда выполним следующие инструкции от имени SCOTT, строки будут удалены из таблицы SCOTT.employees:
SELECT COUNT (*)
FROM employees
WHERE department_id = 20
/
COUNT(*)
—————————————
3
BEGIN
hr.remove_emps_in_dept (20);
END;
/
SELECT COUNT (*)
FROM employees
WHERE department_id = 20
/
COUNT(*)
—————————————
0
Роли, предоставленные программному блоку, не влияют на компиляцию. Вместо этого, они влияют на проверку привилегий SQL-предложений во время выполнения. Таким образом, процедура или функция выполняется с привилегиями, как из собственной роли, так и любых других ролей, доступных в данный момент.
Эта функция чаще всего используется с инициатором прав программы. Вы, вероятно, захотите рассмотреть вопрос о грантировании ролей определителю прав модуля, когда этот модуль выполняет динамический SQL-запрос, поскольку привилегии для данной динамической заявление проверяются во время выполнения.
Вы, вероятно, рассмотреть вопрос о предоставлении роли подразделения по правам определяющего, что, когда блок выполняет динамический SQL, поскольку привилегии для данного динамического предложения проверяются во время выполнения.
Oracle Database 12c предлагает значительные улучшения в гибкости и функциональности, когда дело доходит до определения и выполнения программных модулей. Функции Oracle Database 12c позволяют PL/SQL-разработчикам использовать права инициатора вызова с функцией кэширования результатов, определения и выполнения подпрограмм PL/SQL в SQL-предложений, ограничивать доступ к программным модулям с использованием белых списков и грантирования ролей программным модулям. Oracle Database 12c также улучшает выполнение SQL-предложений в PL/SQL-программах в различных формах, о которых я расскажу в следующем выпуске журнала Oracle Magazine.
Принять Вызов
|
Стивен Ферштейн (Steven Feuerstein – (steven.feuerstein@quest.com) — служащий Quest Software, евангелист PL/SQL. Он опубликовал 10 книг по Oracle PL/SQL (O’Reilly Media), он член-директор коллегии Oracle ACE. На его сайте stevenfeuerstein.com можно найти много полезной информации.