Вопросы безопасности

То что в ОЕБС для ведения пользователей не используются встроенные возможности СУБД Oracle всегда казалось подозрительным.
Но что на столько!!!

Желающие могут убедиться сами.
Достаточно загуглить:
oracle.apps.fnd.security.WebSessionManagerProc.decrypt

Внешнее соединение

Человек старой закалки (кто видел СУБД Oracle версии меньше чем 9) на вопрос о внешнем соединении в SQL запросе уверенно ответит, что где то нужно поставить "плюсик".

И это правда.
Не смотря на появившийся в 9-й версии синтаксис ANSI для внешних соединений, "плюсик" привычнее и роднее. Вот, только, не всегда помнишь куда ж его нужно поставить. Собственно далее идет памятка о постановке "плюсика" во внешнем соединении.

Предположим есть у нас две таблицы: FND_USER и HR_EMPLOYEES. Вообще-то, HR_EMPLOYEES это view, но пусть для упрощения немного побудет таблицей. Соединяются они по столбцу EMPLOYEE_ID, который присутствует в обеих. Причем в HR_EMPLOYEES это еще и первичный ключ, а в FND_USER этот столбец не является обязательным.

Соединение этих таблиц в запросе выглядит так:

SELECT fu.*
      ,he.*
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id = fu.employee_id

А теперь вопрос.
Где же ставить "плюсик"?
Ответ оказывается не однозначным и всё зависит от того, что мы хотим получить в запросе.

Допустим, нам нужно для ряда пользователей (скажем, имя которых начинается с 'S') определить их Фамилию Имя Отчество. Информация о ФИО содержится в столбце HR_EMPLOYEES.full_name
В этом случае запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id(+) = fu.employee_id
   AND fu.user_name LIKE 'S%'

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

Ведь в таблице HR_EMPLOYEES столбец employee_id является первичным ключом, и значение в нем отсутствовать не может в принципе. Однако "плюсик" нужно поставить именно с этой стороны.
В чём же дело.
В данном случае, мы говорим о том, что в указанной постановке задачи (для пользователя найти ФИО) таблица FND_USER "главнее". Мы изначально имеем дело со списком пользователей, для части из которых, нужно найти дополнительную информацию (ФИО), если она существует.
Почему она может не существовать?
Да потому, что у пользователя в таблице FND_USER может отсутствовать значение в столбце EMPLOYEE_ID. Значит для ряда записей в FND_USER (где employee_id IS NULL) в HR_EMPLOYEES можно ничего и не искать. Именно поэтому "плюсик" ставится со стороны таблицы HR_EMPLOYEES

Кстати.
Аналогом этого запроса является:

SELECT fu.user_name
      ,(SELECT he.full_name
          FROM hr_employees he
         WHERE he.employee_id = fu.employee_id)
  FROM fnd_user fu
 WHERE fu.user_name LIKE 'S%'

В таком виде наиболее очевидно, что справочник пользователей "главнее". Ведь FND_USER единственная таблица во фразе FROM. А информация о ФИО вытаскивается подзапросом, который либо её найдет, либо нет.
Основным минусом такого запроса является то, что подзапрос позволяет вернуть только одно значение. А ведь нас могло бы интересовать не только full_name, но и employee_num, creation_date и т.д.

Другая ситуация.
Для ряда сотрудников, у которых фамилия начинается на 'А' нужно найти имена пользователей, с которыми они работают в системе. Имена пользователей содержатся в столбце FND_USER.user_name.
В этом случае запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id = fu.employee_id(+)
   AND he.full_name LIKE 'А%'

Запрос вроде похожий, однако "плюсик" переехал к таблице FND_USER.
В данном случае, мы говорим о том, что в указанной постановке задачи (для ряда сотрудников найти user_name) таблица HR_EMPLOYEES "главнее". Мы изначально имеем дело со списком сотрудников, для части из которых, нужно найти дополнительную информацию (user_name), если она существует.
Почему она может не существовать?
Да потому, что для сотрудника из таблицы HR_EMPLOYEES может отсутствовать запись в таблице FND_USER с соответствующим значением столбца EMPLOYEE_ID. Значит для ряда записей в HR_EMPLOYEES мы ничего не найдем в FND_USER. Именно поэтому "плюсик" ставится со стороны таблицы FND_USER.

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

Существуют разные способы переписать этот запрос по другому(без внешнего соединения), однако наша цель до конца разобраться именно во внешнем соединении.

Для реализации такого запроса необходимо полное внешнее соединение (FULL OUTER JOIN). И в этом случае придется воспользоваться синтаксисом ANSI.

А выглядеть это будет так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he FULL OUTER JOIN fnd_user fu
    ON he.employee_id = fu.employee_id

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

Ну и напоследок, чтобы окончательно запутаться.
А что если в запросе больше чем две таблицы? Допустим нам нужно для текущего пользователя (функция FND_GLOBAL.user_id) найти номер рабочего телефона сотрудника.

Информация о телефонах хранится в таблице PER_PHONES. Эта таблица связывается с HR_EMPLOYEES по условию:

   per_phones.parent_table = 'PER_ALL_PEOPLE_F'
   AND per_phones.parent_id = hr_employees.employee_id

Но при этом очевидно, что не у всех сотрудников есть записи о телефонах.

Итоговый запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
      ,pp.phone_number
  FROM fnd_user fu
      ,hr_employees he
      ,per_phones pp
 WHERE /* Соединяем fnd_user и hr_employees */
       he.employee_id(+) = fu.employee_id
       /* Соединяем hr_employees и per_phones */
   AND pp.parent_id(+) = he.employee_id
   AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
       /* Прочие ограничения */
   AND pp.phone_type(+) = 'W1' -- рабочий телефон
   AND SYSDATE BETWEEN pp.date_from(+) AND NVL(pp.date_to(+), SYSDATE) -- актуальная запись
   AND fu.user_id = FND_GLOBAL.user_id -- для текущего пользователя

Отметим следующее.
Таблицы соединяем попарно.
В каждой паре определяем главную и дополняющую таблицу.
В паре fnd_user и hr_employees главная - fnd_user, поэтому "плюсик" со стороны hr_employees.
В паре hr_employees и per_phones главная hr_employees, поэтому "плюсик" ставится со стороны per_phones и что особенно важно "плюсик" не ставится со стороны hr_employees.
В прочих условиях "плюсик" ставится для тех таблиц, которые хоть где-нибудь были дополнительными и не ставится у главной таблицы запроса.

Поиск полномочий для запуска параллельной программы

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

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

SELECT fr.responsibility_name
      ,(SELECT fa.application_name
          FROM fnd_application_vl fa
         WHERE fa.application_id = fr.application_id) AS application_name
      ,fr.responsibility_id
      ,fr.application_id
  FROM fnd_responsibility_vl fr
 WHERE SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date, SYSDATE)
   AND (fr.group_application_id, fr.request_group_id) IN (
           SELECT frgu.application_id
                 ,frgu.request_group_id
             FROM fnd_request_group_units frgu
            WHERE (frgu.request_unit_type = 'P' -- Program
                   AND 
                  (frgu.unit_application_id, frgu.request_unit_id) IN (
                      SELECT fcp.application_id
                            ,fcp.concurrent_program_id
                        FROM fnd_concurrent_programs_vl  fcp
                       WHERE  fcp.user_concurrent_program_name LIKE '%' 
                   ) 
                   )OR
                   (frgu.request_unit_type = 'A' -- Application
                    AND frgu.unit_application_id IN (
                       SELECT fcp.application_id
                         FROM fnd_concurrent_programs_vl  fcp
                        WHERE fcp.user_concurrent_program_name LIKE '%'
                    )
                    )
       )
 ORDER BY fr.responsibility_name

FND_CONCURRENT_REQUESTS

Параллельные программы в ОЕБС (concurrent programs) - замечательная особенность системы. Любые отчеты и процедуры обработки данных всегда оформляются, а затем и выполняются используя единый механизм. Так ведь мало того, всё это еще и протоколируется.

Поэтому, говоря о параллельных программах, мы можем ответить не только на вопросы популярной телепередачи, но и понять кто, сколько и во сколько :-)

Анализ запуска параллельных программ может быть полезен для ответов на многие вопросы.
Ну например:
1) Списки наиболее часто используемых отчетов
2) Списки наиболее медленно работающих отчетов
3) Какие программы работали в заданный промежуток времени.
4) Кто и что "забило" очередь конкарент менеджера
и т.д.

Специалисты поддержки разбирая жалобы пользователей на плохую работу того или иного отчета, могут предварительно проанализировать когда, с какими параметрами, из под каких полномочий этот пользователь запускал отчет (да и запускал ли?).

И всё это счастье доступно в одной таблице, имя которой FND_CONCURRENT_REQUESTS.

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

SELECT
       fcr.request_id 
      ,(SELECT fcp.user_concurrent_program_name
          FROM apps.fnd_concurrent_programs_vl fcp
         WHERE fcp.application_id = fcr.program_application_id
           AND fcp.concurrent_program_id = fcr.concurrent_program_id
       ) AS concurrent_name
      ,fcr.actual_start_date 
      ,fcr.actual_completion_date
      ,fcr.completion_text
      ,(SELECT fu.user_name FROM applsys.fnd_user fu 
          WHERE fu.user_id = fcr.requested_by
       ) AS user_name
      ,(SELECT fr.responsibility_name
          FROM apps.fnd_responsibility_vl fr
         WHERE fr.application_id = fcr.responsibility_application_id
           AND fr.responsibility_id = fcr.responsibility_id
       ) AS responsibility_name
      ,fcr.argument_text 
      ,fcr.*
  FROM applsys.fnd_concurrent_requests fcr
 WHERE 1=1
/* Start. Фильтр по пользователю */
--    AND fcr.requested_by IN (
--          SELECT fu.user_id 
--            FROM applsys.fnd_user fu 
--           WHERE fu.user_name LIKE '%'
--        )
/* End. Фильтр по пользователю */
/* Start. Фильтр по периоду */
--   AND fcr.actual_start_date >= TO_DATE('01.01.2008', 'DD.MM.YYYY')
--   AND fcr.actual_completion_date < TO_DATE('31.12.2008', 'DD.MM.YYYY')
/* End. Фильтр по периоду */
/* Start. Фильтр по concurrent_name */
--    AND (fcr.program_application_id, fcr.concurrent_program_id) IN (
--          SELECT fcp.application_id, fcp.concurrent_program_id 
--            FROM apps.fnd_concurrent_programs_vl fcp
--           WHERE fcp.user_concurrent_program_name LIKE '%'
--        )
/* End. Фильтр по concurrent_name */
/* Start. Фильтр по работающим сейчас конкарентам */
--   AND fcr.phase_code = 'R'
/* End. Фильтр по работающим сейчас конкарентам */
ORDER BY fcr.request_id DESC