Oracle Database 12c: функции с правами вызывающего в представлениях

До Oracle Database 12c, если мы вызываем функцию внутри представления (неважно каким способом: в предикате фразы WHERE, либо в вычисляемом столбце), этот вызов всегда происходит с правами создателя (DEFINER RIGHTS), независимо от того как была определена функция.
Рассмотрим следующий пример.
Предположим, что у нас в Oracle Database 11g есть функция определенная с правами вызывающего:

[oracle@rac1 ~]$ sqlplus rscott/rtiger

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 10 13:09:00 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
and Data Mining options

SQL> CREATE OR REPLACE FUNCTION getClosedAccounts(v_pBranchId IN NUMBER) RETURN VARCHAR2
AUTHID CURRENT_USER IS
  v_xRes NUMBER(9);
BEGIN
  SELECT 
    count(a.rowid)
  INTO
    v_xRes
  FROM
    accounts a
  WHERE
    a.branch_id = v_pBranchId and
    a.Status    = 'CLOSED';

  RETURN v_xRes;
END;
/

Function created.
SQL> 

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

SQL> grant execute on getClosedAccounts to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.

SQL> select rscott.getClosedAccounts(123) from dual;
select rscott.getClosedAccounts(123) from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RSCOTT.GETCLOSEDACCOUNTS", line 5

SQL> 

Все правильно: вызов произошел с правами вызывающего, а пользователь HR не имеет прав не чтение таблицы accounts. А теперь попробуем вызвать эту же функцию под пользователем HR, но уже внутри представления:

SQL> conn rscott/rtiger
Connected.

SQL>CREATE OR REPLACE VIEW v$Branches_Closed_Accounts AS
   SELECT
     b.Id,
     getClosedAccounts(b.Id) as Closed_Count
   FROM
     branches b;

View created.


SQL> GRANT SELECT ON v$Branches_Closed_Accounts TO hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> SELECT * FROM rscott.v$Branches_Closed_Accounts;

 ID  CLOSED_COUNT
---  ---------------------------------------------------------------------
  1  0

Функция внутри представления была вызвана без всякой ошибки! До Oracle Database 12c вызов функции внутри представления всегда происходит с правами создателя, даже если функция была определена как с правами вызывающего.

В Oracle Database 12c, в определении представления, указывается фраза BEQUEATH, которая определяет, как будут вызывать PL/SQL-функции используемые в запросе: DEFINER или CURRENT_USER.

Переключимся на Oracle Database 12c и перепишем наше представление по другому, указав что наша функция должна быть вызвана с правами вызывающего:

[oracle@localhost]$ sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 16 20:57:49 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 16 2014 20:56:22 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE OR REPLACE VIEW v$Branches_Closed_Accounts 
  BEQUEATH CURRENT_USER AS
   SELECT
     b.Id,
     getClosedAccounts(b.Id) as Closed_Count
   FROM
     branches b;

View created.

SQL> conn hr/hr
Connected.
SQL> SELECT * FROM rscott.v$Branches_Closed_Accounts;
SELECT * FROM rscott.v$Branches_Closed_Accounts
                       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RSCOTT.GETCLOSEDACCOUNTS", line 5
SQL> 

Теперь функция в представлении вызывается с правами вызывающего!

По умолчанию, в Oracle Database 12 представление будет создаваться с неявной фразой BEQUEATH DEFINER, то есть поведение будет как в предыдущих версиях - функции в представлениях будут вызываться с правами создателя.

Важно отметить, что если в определении функции был указан режим вызова с правами создателя, то фраза BEQUEATH CURRENT_USER в представлении игнорируется:

SQL> conn rscott/rtiger
Connected.
SQL> CREATE OR REPLACE FUNCTION getClosedAccounts(v_pBranchId IN NUMBER) RETURN VARCHAR2
AUTHID DEFINER IS
  v_xRes NUMBER(9);
BEGIN
  SELECT 
    count(a.rowid)
  INTO
    v_xRes
  FROM
    accounts a
  WHERE
    a.branch_id = v_pBranchId and
    a.Status    = 'CLOSED';

  RETURN v_xRes;
END;
/

Function created.

SQL> conn hr/hr
Connected.
SQL> SELECT * FROM rscott.v$Branches_Closed_Accounts;

 ID  CLOSED_COUNT
---  ---------------------------------------------------------------------
  1  0

Oracle JavaVM Component 12.1.0.2.1 Database PSU

Продолжается стабилизация релиза Oracle 12c R1 (12.1.0.2). В октябре, как и положено, вышел первый PatchSet Update (PSU 1).
Вместе с знакомыми нам патчами: для БД (DB PSU) и для Grid Infrastructure (GI PSU) появился новый вид PSU - Oracle JVM Component Database PSU. Этот пакет исправлений содержит исправления ошибок для виртуальной Java-машины работающей внутри БД.
Следует заметить, что перед установкой Oracle JVM Database PSU, на базе данных предварительно уже должен стоять соответствующий Database PSU, в данном случае - Database PSU1.

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

• обновляем утилиту OPatch до последней версии - здесь ничего необычного: всегда рекомендовалось скачать ее свежую версию (Patch 6880880);


• устанавливаем DB PSU - здесь тоже все по старому: в каталоге распакованного архива с патчем запускаем утилиту opatch c параметром apply (это знает любой школьник! :-));
  
  
  
  Рис.1 Установка патча Oracle Database 12.1.0.2 PSU 1

затем необходимо обновить словарь, - теперь это делается с помощью утилиты datapatch (никаких catcpu.sql!), эта утилита выполняет все SQL-скрипты, необходимые для корректной установки патча;

Рис.2 Обновление словаря для DB 12.1.0.2 PSU 1 с помощью команды $O_H/OPatch/datapatch -verbose

устанавливаем DB OJVM PSU, - точно также выполняем: opatch apply;

Рис.3 Установка патча Oracle Database 12.1.0.2 OJVM PSU 1

наконец, обновляем словарь для DB OJVM PSU, - точно также выполняем: datapatch -verbose;

Рис.4 Обновление словаря для DB 12.1.0.2 OJVM PSU 1

Процедура установки патчей на СУБД Oracle Database 12c претерпела изменения: теперь нам не нужно вручную запускать post-install SQL-скрипты, также следует не забывать об установке нового вида PSU - Database PSU JVM Component.

P.S. Выпущенный в сентябре большой MLR-патч для Oracle In-Memory (patch 19615705), к сожалению, конфликтует с DB PSU 12.1.0.2.1. В ближайшее время будет выпущен такой же MLR, но для 12.1.0.2.1 (on Top 12.1.0.2.1).