Права на создание job oracle

Обновлено: 06.07.2024

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

Введение

Первый механизм планового запуска появился в версии 7 для поддержки автоматических обновлений снимков (snapshots), как поначалу именовались нынешние материализованные виртуальные таблицы (materialized views). В версии 8 этот механизм был открыт для обычных пользователей через посредство некоторых параметров СУБД, таблиц словаря-справочника, а также пакета DBMS_JOB. Пакет DBMS_JOB позволял (и позволяет) запускать хранимую процедуру, или же неименованный блок PL/SQL в моменты времени, вычисляемые по указанной пользователем формуле.

Schedule (расписание)
Program (программа)
Job (плановое задание = расписание + программа)

таблицы словаря-справочника LIKE '%SCHEDULER_%' (DBA_SCHEDULER_JOBS, DBA_SCHEDULER_JOB_LOG и прочие);

несколько типов объектов хранения, как то:

JOB
SCHEDULE
PROGRAM
JOB CLASS,

CREATE SESSION
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
CREATE EXTERNAL JOB,

EXECUTE
ALTER
ALL,

Некоторые ключевые моменты использования планировщика в Oracle 10 рассматриваются ниже на примерах.

Простой запуск задания

Простой запуск задания очень напоминает запуск с помощью процедуры SUBMIT из пакета DBMS_JOB. Однако, в отличие от SUBMIT, он возможен только при наличии привилегии CREATE JOB. В последующих примерах созданием заданий и управлением ими для простоты будет заниматься пользователь SCOTT, хотя в жизни разумно подумать об отдельном администраторе для этой цели. Выдадим пользователю SCOTT нужную привилегию:

Кроме системных привилегий, использование планировщика регулируется объектными привилегиями EXECUTE, ALTER и ALL, выдача которых применительно (GRANT . ON) к заданию, программе, расписанию или классу заданий позволяет работать с объектами БД типов JOB, PROGRAM, SCHEDULE и JOB CLASS соответственно, введенных в Oracle 10 вместе с новым планировщиком.

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

Внутреннее задание для СУБД

Пример внутреннего задания в виде неименованного блока PL/SQL:

Обрамлять блок словами BEGIN и END не обязательно, так как код пакета DBMS_SCHEDULER это сделает самостоятельно (ради особой программной логики, добавляемой им к тексту пользователя).
Задание запускается в этом же сеансе и сопровождается неявной выдачей COMMIT. В этом легко удостовериться: Зарплата SAL увеличится на 2. Проверить это в качестве упражнения.

Для хранимой процедуры задание формируется аналогично:

Обратите внимание, что нам не потребовалось удалять старое задание SIMPLE_JOB, так как при выбранных нами параметрах процедуры CREATE_JOB задания (и первое, и второе) прогонялись однократно, моментально и сразу же удалялись автоматически. Последнее как раз можно и отменить посредством не использованного в примере выше параметра AUTO_DROP.

Внешнее задание (для ОС)

Совсем новым в планировщике Oracle 10 является возможность запускать плановые задания в ОС. Однако чтобы это было возможно, в ОС должна быть запущена программа extjob из ПО СУБД. На Windows она запускается службой OracleJobScheduler . Для того чтобы следующий пример проработал, службу необходимо запустить. Вдобавок потребуется выдать пользователю SCOTT еще одну привилегию.

Обратите внимание, что в Windows выдача команды ОС или же запуск командного файла напрямую (без вызова cmd.exe), не проходит.

В Unix аналогичное действие можно записать как 'ls > /tmp/out. txt'.

Возможности запуска, наблюдения, вмешательства

Так же, как для пакета DBMS_JOB, в новом планировщике предусмотрено именно плановое, а не одноразовое исполнение задания. Добавим к последнему вызову параметр:

В результете корневой файл out.txt получим через 10 секунд после создания задания. Добавим еще параметр: В результате задание будет исполняться ежемесячно по воскресениям и последним субботам месяца. В отличие от DBMS_JOB, DBMS_SCHEDULER, в дополнение к возможности употребить выражение на PL/SQL, имеет для формулирования графика запуска еще и специальный язык. Он позволяет указывать частоту, интервал и уточнитель запуска задания. Примеры:

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

Полное описание языка приводится в документации по Oracle.

Если указать план запуска, задание появится в системе уже надолго. Удалить его при необходимости можно будет так:

Информацию об имеющихся заданиях пользователь SCOTT может посмотреть запросом:

Скомпонованное задание

Более развитая возможность DBMS_SCHEDULER позволяет скомпоновать задание из независимых элементов: программы и расписания. Характерная особенность в том, что оба эти элемента самостоятельны; их можно комбинировать в разных заданиях и изменять, не внося изменений в определения заданий.

Создание программы

Простой пример создания программы:

Список сведений об имеющихся программах для планировщика имеется в таблицах DBA/ALL/USER_SCHEDULER_PROGRAMS.

Другими значениями параметра PROGRAM_TYPE могут быть 'PLSQL_BLOCK' и 'EXECUTABLE' (см. выше).

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

Создание расписания

Пример создания расписания:

В общем случае язык указания графика для расписания (параметр REPEAT_INTERVAL) допускает ссылаться на ранее созданные таким же образом расписания.

Список сведений об имеющихся расписаниях для планировщика имеется в таблицах DBA/ALL/USER_SCHEDULER_SCHEDULES.

Простой пример скомпонованного задания

Из самостоятельно существующих программы и расписания можно составить задание:

При наличии параметра пример может выглядеть так:

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

Создание и использование ресурсного окошка СУБД для задания

Ресурсный план построить несложно, но, чтобы не отвлекаться, воспользуемся встроенным в любую БД планом SYSTEM_PLAN (см. таблицу DBA_RSRC_PLANS). Тогда создание окошка может выглядеть так:

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

Если подгадать момент, когда значение OPERATION для окошка MY_JOB_WINDOW станет OPEN, от имени SYS можно будет удостовериться, что план включен:

Чтобы связать с этим периодически открывающимся ресурсным окошком задание, пользователю SCOTT достаточно указать его вместо расписания:

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

Изменение свойств объектов планировщика

Хотя упомянутые JOB, SCHEDULE, PROGRAM, WINDOW и проч., причисляются к объектам хранения БД (и видны в таблицах DBA/ALL/USEROBJECTS), не только их создание и удаление, но и изменение свойств выполняются так, как было удобно разработчику: через API. Для всех перечисленных видов объектов существует довольно много поведенческих свойств, указанию которых нет места в процедурах LIKE 'CREATE_%'. Устанавливать их следует явно единой для всех процедурой SET_ATTRIBUTE. Вот пример, как для задания MY_WINDOW_JOB (а) задать приоритет выполнения (по отношению к другим заданиям своего класса), если на одно время пришлось выполнение нескольких заданий одновременно, и (б) потребовать прекращения (процедурой STOP_JOB), если оно еще не выполнилось, а ресурсное окошко уже закрылось:

Полный список атрибутов и объектов, к которым они применимы, имеется в документации по Oracle.

Заключение

Помимо использованного выше общения с планировщиком Oracle 10 средствами PL/SQL и SQL, общаться с ним можно через графический интерфейс Oracle Enterprise Manager. По сути, OEM ничего нового не дает, так как в конечном итоге отсылает к СУБД те же команды на PL/SQL и SQL, но выполнение разовых действий через OEM часто администратору быстрее и понятнее. Для автоматизации работ, однако, лучше может подойти работа со сценариями запросов.

После проведенных опытов с планировщиком не забудьте освободить БД от ненужных объектов. Например:

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

Задания в Oracle9i

В Oracle существует возможность запланировать выполнение определенного набора действий в виде заданий. Задание может, представляет собой хранимую процедуру, анонимный блок PL /SQL, внешнюю процедуру на языке C или Java. Время выполнения может иметь значение любого времени суток и подчинятся заданному интервалу. Это хорошо подходит для переноса тяжёлых в обработке расчётов на менее загруженное ночное время. По умолчанию выполнение заданий выключено. Поэтому надо провести небольшую дополнительную настройку сервера.

Настройка сервера

Для того чтобы задания начались выполняться необходимо, установить параметр инициализации JOB_QUEUE_PROCESSES. Изначально он имеет значение 0 и задаёт максимальное количество фоновых процессов для выполнения заданий. В версии Oracle 9.2 максимальное значение этого параметра может составлять 1000. На практике же обычно можно ограничиться не более 5 процессами. В любом случае вы всегда можете изменить это значение с помощью команды ALTER SYSTEM SET без перезагрузки сервера. Итак, для начала внесем новую строчку в файл инициализации и перезагрузим сервер:

В файле alert.log мы увидим, что в момент, когда стартуют фоновые процессы, у нас появилось новая запись:

Это стартовал новый фоновый процесс CJQ0, так называемый координатор заданий. Что он делает и для чего он нужен, мы рассмотрим чуть ниже, а на этом предварительную настройку сервера можно считать законченной.

Процессы

Итак, процесс координатора заданий запущен, и как можно догадаться из его названия именно этот процесс осуществляет общее управление всеми заданиями. Для начала он выбирает таблицу SYS.JOB$, в которой хранятся параметры заданий. Если среди заданий имеются те, которые будут выполняться в ближайший интервал времени указанный в скрытом параметре _JOB_QUEUE_INTERVAL (по умолчанию его значение составляет 5 секунд), то для них порождаются фоновые процессы очереди заданий Jnnn, которые в свою очередь создают сеансы для непосредственного выполнения запланированных действий. Именно максимальное количество процессов Jnnn, которые могут быть одновременно запущены и отражает настраиваемый параметр JOB_QUEUE_PROCESSES.

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

Выставляйте значение параметра JOB_QUEUE_PROCESSES чуть больше максимального количества одновременно запускаемых заданий. Маленькое значение может привести к сдвигу времени выполнения из-за конкуренции за процессы Jnnn. Большое значение к неоправданному запуску этих же процессов в исключительных ситуациях.

Создание заданий

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

Опишем параметры этой процедуры:

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

WHAT - тело задания. Представляет собой анонимный PL/SQL блок. Всё что здесь указано, будет выполнено в процессе работы задания. Если вы запускаете только одну процедуру, то можно не заключать её в блок достаточно поставить в конце названия процедуры точку с запятой. Значение WHAT в этом случае автоматически будет помещено в PL/SQL блок. Если процедура имеет строковые параметры, то они обязательно должны заключаться в две одинарные кавычки с каждой стороны. В PL/SQL блоке можно также писать DML и DDL команды, но нельзя производить создание и запуск заданий. Это только приведёт к ошибке ORA-32317. Если же используются ссылки на удалённую базу данных, то они должны явно включать имя и пароль. Анонимные ссылки здесь не поддерживаются. И, наконец, владельцу задания требуется явно предоставить привилегии, на объекты, используемые в теле задания.

NEXT_DATE - дата следующего выполнения задания. Время непосредственно задаётся владельцем или автоматически вычисляется Oracle.

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

INTERVAL - формула интервала времени. Представляет собой DATE функцию. Именно от её правильного значения будет зависеть дата следующего выполнения задания казанного в столбце NEXT_DATE. Приведём некоторые примеры формулы интервала задания:

Задание будет выполняться ровно в n часов m минут каждого дня.

Задание будет выполняться ровно в n часов m минут последнего дня каждого месяца.

Задание будет выполняться ровно в n часов m минут первого дня каждого месяца.

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

NO_PARSE - флаг разбора PL/SQL. Если его значение равно FALSE разбор происходит в момент установки задания. Иначе, в момент выполнения задания.

INSTANCE - какой экземпляр производит выполнение задания.

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

При создании задания или изменения его параметров ORACLE записывает текущие параметры NLS владельца. Эти параметры каждый раз восстанавливаются при выполнении задания. Это может приводить к некоторым ошибкам в случае ожидания других значений. Поэтому если необходимо лучше производить установку нужных NLS значений с помощью команды ALTER SESSION в параметре WHAT задания.

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

Изменение задания

И так задание создано. Теперь попробуем изменить некоторые его параметры. Для изменения доступны следующие параметры задания: WHAT, NEXT_DATE, INTERVAL и INSTANCE. Их можно менять все одновременно или по отдельности. К примеру, следующая процедура пакета меняет все три параметра, при этом следует учитывать, что если какой либо из них равен NULL, то значение параметра не изменится.

А вот уже эта процедура меняет значение только параметра WHAT:

Также с параметрами NEXT_DATE,INTERVAL,INSTANCE:

В качестве примера, увеличим паузу, которое делает задание, созданное ранее, до 30 секунд и изменим, время повторного запуска задания на 3 часа 15 минут:

Изменить параметры или совершать другие действия над заданием можно только его владельцу. В противном случае возникнет ошибка ORA-23421: job number 24 is not a job in the job queue.

Удаление задания

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

Выключение задания

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

Если флаг BROKEN имеет значение истинно, то такое задание считается разрушенным и выполняться не будет. Параметр NEXT_DATE определяет здесь дату следующего выполнения задания и действует только при его включении.

В момент выключения задания параметр NEXT_DATE устанавливается в максимальное значение даты. Если не указать параметр NEXT_DATE в момент включения, то задание начнёт выполняться немедленно.

Для примера выключим задание:

Вынужденное выполнение задания

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

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

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

Экспорт задания

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

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

Контроль задания

Как было указано выше, координатор заданий обращается в своей работе к системной таблице SYS.JOB$, хранящей описания всех заданий. На эту таблицу существуют три представления: DBA_JOBS, ALL_JOBS и USER_JOBS. Они наиболее часто используются для контроля над заданиями. Рассмотрим их некоторые столбцы:

JOB, NEXT_DATE, INTERVAL, WHAT, INSTANCE - эти столбцы знакомы нам по процедуре SUBMIT.
LOG_USER - пользователь под которым была создано задание. Фактически это и есть владелец задания.
PRIV_USER - пользователь привилегии которого используются для выполнения задания.
SCHEMA_USER - схема по умолчанию для разбора задания.
LAST_DATE (LAST_SEC) - дата (время) последнего успешного выполнения задания.
THIS_DATE (THIS_SEC) - дата (время) начала выполнения задания.
TOTAL_TIME - общее время выполнения задания в секундах. Содержит суммарное время длительности всех выполнений задания.
BROKEN - этот столбец показывает состояние флага разрушенного задания. Если значение равно Y задание выполняться не будет.
FAILURES - количество неудачных попыток выполнить задание. Максимальное значение может достигать 16.
NLS_ENV - NLS параметры сеанса. Соответствуют параметрам сеанса, в котором задание создавалось.

Кроме вышеперечисленных представлений существует и ещё одно - DBA_JOBS_RUNNING. Оно показывает задания, которые выполняются в текущий момент времени. С его помощью можно легко определить SID сеанса выполняемого задания.

Блокировки

Если подробнее разобрать представление DBA_JOBS_RUNNING, то можно увидеть что в его основе лежит соединение таблицы SYS.JOB$ и представления V$LOCK. Кажется, какая тут есть связь? Оказывается, есть и самая прямая. Для того чтобы гарантировать, что данное задание выполняется одновременно только в одном сеансе, Oracle выставляет блокировку JQ. Это можно хорошо видеть, сделав запрос к представлению V$LOCK во время выполнения задания:

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

Ошибки

Прерывание задания

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

Grant Privileges для таблиц

Вы можете предоставить пользователям различные привилегии к таблицам. Эти привилегии могут быть любой комбинацией SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, или другие.

Синтаксис

Синтаксис для предоставления привилегий для таблицы в Oracle/PLSQL:

privileges
Привилегии для назначения. Это может быть любое из следующих значений:

Привилегии	Описание
SELECT	Возможность выполнения SELECT на таблице
INSERT	Возможность выполнения INSERT на таблице
UPDATE	Возможность выполнения UPDATE на таблице
DELETE	Возможность выполнения DELETE на таблице
REFERENCES	Возможность создавать CONSTRAINT, который ссылается на таблицу.
ALTER	Возможность выполнять оператор ALTER TABLE, чтобы изменить описание таблицы.
INDEX	Возможность создавать INDEX таблице с помощью оператора CREATE INDEX.
ALL	Все привилегии для таблицы

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

Пример

Рассмотрим некоторые примеры, предоставления привилегий для таблиц в Oracle.
Например, если вы хотите предоставить SELECT, INSERT, UPDATE и DELETE привилегии на таблицу с наименованием suppliers для user с именем trizor , то нужно выполнить следующие GRANT предложение:

В случае если у нас есть привилегия CREATE ANY JOB, которая по умолчанию есть у пользователя WKSYS, то мы можем создать задание, назначающее роль DBA пользователю WKSYS и сохранить его в схеме пользователя SYSTEM. В результате чего это задание выполнится с привилегиями пользователя SYSTEM, и пользователь WKSYS станет обладателем DBA. Ниже приведен код, реализующий данную технику:

DBMS_SCHEDULER.CREATE_JOB

, job_action => 'EXECUTE IMMEDIATE ''GRANT DBA TO WKSYS'';'

Если у нас есть привилегия CREATE EXTERNAL JOB, то мы можем не только повысить привилегии до роли DBA но и выполнять произвольные команды на сервере при помощи создания внешних заданий (External Jobs). Этот метод был описан в разделе 8.1.3.

Таблица 9.1.16. Пользователи, имеющие роль JAVASYSPRIV по умолчанию

Oracle 9g R2

Oracle 10g R1

Oracle 10g R2

Oracle 11g R1

Роль JЛVЛSYSPRIV позволяет пользователю писать процедуры на JAVA которые могут быть использованы для получения доступа к командной строке сервера и доступа к файлам на сервере. Подробно об этом было рассказано в разделе 8.1.2. Таким образом, обладая ролью JAVЛSYSPRIV, мы получаем доступ к командной строке сервера.

Для того, чтобы поднять свои права до пользователя СК8У5, можно воспользоваться уязвимой процедурой из схемы VKSYS (см. раздел 6.1.8).

Таблица 9.1.17. Пользователи, имеющие роль SELECT CATALOG_ROLE по умолчанию

Oracle 9g R2

Oracle 10g R1

Oracle 10g R2

Oracle 11g R1

Данная роль позволяет нам выполнять некоторый расширенный набор процедур, не доступный стандартным пользователям с ролью CONNECT или RESOURCE. В этих процедурах тоже периодически находят уязвимости. Для примера можно назвать одну из последних (на момент написания главы) уязвимостей в процедуре SYS.DBMS_CDC_UTILITY.LOCK_CHANGE_SET, информация о которой появилась в апреле 2008 года.

Уязвимости подвержены версии СУБД lOgRl, 10g R2n llg Rl, и реализовать ее может любой пользователь СУБД, у которого есть права EXECUTE на пакет процедур SYS.DBMS_CDC_UTILITY. По умолчанию права на выполнение этой процедуры есть у пользователя, обладающего привилегией SELECTCATALOGROLE.

В случае если мы тем или иным образом получили аутентификационные данные пользователя с ролью SELECT_CATALOG_ROLE (при помощи перебора или обнаружив уязвимость), нам достаточно реализовать уязвимость в процедуре SYS.DBMS_CDC_UTILITY.LOCK_CHANGE_SET, и мы получим права DBA в системе. Ниже приведен код эксплоита, реализующий данную уязвимость:

CREATE OR REPLACE FUNCTION EVILPROC return varchar2

authid current_user as

EXECUTE IMMEDIATE 'grant dba to SCOTT';

Успешный результат выполнения эксплоита можно наблюдать на рис 9.1.17-1.

Рис 9.17.1-1. Повышение привилегий при помощи уязвимости в процедуре 0ВМЗ_С0С_иииТУ

Также необходимо с осторожностью смотреть на такие роли, как EXECUTE_ CATALOGROLE и любые другие нестандартные роли, выдаваемые пользователям, так как они тоже разрешают доступ к ряду процедур, которые могут быть уязвимы к PL/SQL-инъекции или переполнению буфера.

К примеру, одна из последних (на момент написания главы) уязвимостей из набора обновлений за апрель 2008 года присутствует в процедуре SYS.KUPF$FILE_ INT.GET_FULL_FILENAME и позволяет выполнить произвольный код на сервере! Уязвимости подвержены следующие версии СУБД Oracle: 10g Rl, 10g R2, llg R1 и реализовать ее могут пользователи, обладающие ролью EXECUTE_ CATALOGROLE.

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

Читайте также: