Oracle 12c: grant roles to PL/SQL units

В Oracle Database 12c появилась интересная новая возможность: назначать роли непосредственно PL/SQL-объектам.
Для чего это нужно? Это дает возможность давать доступ к таблицам (вообще к любым объектам БД) только через вызовы PL/SQL-объектов созданных с правами вызывающего.
Предположим, у нас есть очень секретная таблица с остатками по счетам:

[oracle@rac1 ~]$  sqlplus rscott/rtiger

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 18 05:57:08 2014

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

Last Successful login time: Thu Oct 16 2014 08:26:35 +04:00

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

SQL> CREATE TABLE acc_amounts
  (
    Acc_Id   NUMBER(9),
    Acc_Date DATE,
    Amount   NUMBER(22,2)
  );
Table created.

SQL> INSERT INTO acc_amounts VALUES(1,trunc(sysdate),100);

1 row created.

SQL> INSERT INTO acc_amounts VALUES(2,trunc(sysdate),200);

1 row created.

SQL> COMMIT;

И есть функция, которая возвращает остаток по счету на определенную дату. Для того, чтобы не давать делать вызов с потенциально более высокими правами создателя (definer right) - владельца объектов БД, функция создана с правами вызывающего (invoker rights):

SQL> CREATE OR REPLACE FUNCTION getAccAmount(v_pAccId IN NUMBER,
  2                                          v_pAccDate IN DATE) RETURN NUMBER
  3   AUTHID CURRENT_USER IS
  4        v_xRes acc_amounts.Amount%type;
  5      BEGIN
  6        SELECT
  7          Amount
  8        INTO
  9          v_xRes
 10        FROM
 11          rscott.acc_amounts
 12        WHERE
 13          Acc_Date = trunc(v_pAccDate);
 14
 15       RETURN v_xRes;
 16     END;
 17  /

Function created.

SQL>

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

 
SQL> GRANT EXECUTE ON getAccAmount TO user1;

Grant succeeded.

SQL> conn user1/oracle
Connected.

SQL> SELECT rscott.getAccAmount(1,sysdate) FROM dual;
select rscott.getAccAmount(1,sysdate) from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "RSCOTT.GETACCAMOUNT", line 6

SQL>

Казалось бы нет проблем: нужно дать права пользователю на чтение этой таблицы. После этого все будет работать:

 
SQL> conn rscott/rtiger
Connected.
SQL> GRANT SELECT ON acc_amounts TO user1;

Grant succeeded.

SQL> conn user1/oracle
Connected.
SQL> SELECT rscott.getAccAmount(1,sysdate) FROM dual;

RSCOTT.GETACCAMOUNT(1,SYSDATE)
------------------------------
                           100
SQL>

Но теперь пользователь имеет полный доступ к таблице, и может посмотреть остаток по любому счету!

 
SQL> SELECT * FROM rscott.acc_amounts;

    ACC_ID ACC_DATE      AMOUNT
---------- --------- ----------
         1 17-OCT-14        100
         2 17-OCT-14        200
SQL>

В Oracle Database 12c мы можем дать права на чтение таблицы непосредственно самой функции. Теперь пользователь не будет иметь непосредственного доступа к таблице:

 
SQL> conn rscott/rtiger
Connected.
SQL> REVOKE SELECT ON acc_amounts FROM user1;

Revoke succeeded.

SQL> GRANT SELECT ON acc_amounts TO FUNCTION getaccamount;
 GRANT SELECT ON acc_amounts TO FUNCTION getaccamount
*
ERROR at line 1:
ORA-28700: Only roles can be attached to or detached from program units.

REM Дать права PL/SQL-объекту можно только через роль!
SQL> CREATE ROLE getAccAmount_role;

Role created.

SQL> GRANT SELECT ON acc_amounts TO getAccAmount_role;

Grant succeeded.

SQL> GRANT getAccAmount_role TO FUNCTION getaccamount;

Grant succeeded.
SQL>

После этого пользователь успешно может вызывать функцию получения остатка, доступ к которому мы регламентируем через параметры (передавая в вызов функции на стороне приложения текущий идентификатор пользователя). При этом пользователь не имеет прямого доступа к таблице и вынужден использовать наш PL/SQL API:

 
SQL> conn user1/oracle
Connected.
SQL> SELECT * FROM rscott.acc_amounts;
SELECT * FROM rscott.acc_amounts
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SELECT rscott.getAccAmount(1,sysdate) FROM dual;

RSCOTT.GETACCAMOUNT(1,SYSDATE)
------------------------------
                           100
SQL>

Выдача прав на объекты непосредственно PL/SQL-объектам позволяет повысить защищенность вашего приложения от несанкционированного доступа к конфиденциальным данным. Прекрасно сочетаясь с другой возможностью Oracle 12c - ограничением места вызова PL/SQL-объектов, эта технология позволяет четко регламентировать доступ к данным через прикладной программный интерфейс вашего приложения!