Oracle
PL/SQL

ORA-01031: insufficient privileges при выполнении динамического SQL в процедурах

Опубликовано: 22 сен 03
Рейтинг:

Автор: Violina
Прислал: Violina

Используемая версия
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production

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

Пример

1) Создаем пользователя

SQL> create user u1 identified by u1
  2  quota 100M on user_ts;

User created.

SQL> create role r1;

Role created.

SQL> grant connect, create table, create procedure to r1;

Grant succeeded.

SQL> grant r1 to u1; -- пользователь получает привилегю create table через роль


Grant succeeded.

Заходим под этим пользователем

SQL> connect u1/u1
Connected.

-- убеждаемся что пользователь имеет право создавать таблицу


SQL> create table u1.test_table (x int);

Table created.

SQL> drop table test_table;

Table dropped.

-- создаем процедуру


SQL> create or replace procedure test as
  2  begin -- используем native dynamic SQL

  3    execute immediate 'create table u1.test_table (x int)';
  4  end;
  5  /

Procedure created.

-- получаем insufficient privileges при выполнении 


SQL> exec test;
BEGIN test; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "U1.TEST", line 3
ORA-06512: at line 1

-- даем пользователю привилегию create table явно


SQL> grant create table to u1;

Grant succeeded.

-- выполняем процедуру и видим что теперь все ОК


SQL> exec test;

PL/SQL procedure successfully completed.

SQL> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------

TEST                           PROCEDURE
TEST_TABLE                     TABLE

Очень часто разработчики ломают голову, почему, если я создаю таблицу в sqlplus
или динамически в анонимном блоке, то все в порядке, однако если я это делаю в
процедуре, то получаю ошибку ORA-01031: insufficient privileges. Вот ответ на
этот вопрос. При использовании анонимного блока ситуация иная. Анонимный блок
выполняется на правах исполняющего, поэтому привилегии, данные через роль,
действуют!

SQL> revoke create table from u1;

Revoke succeeded.

SQL> begin -- используем native dynamic SQL

  2    execute immediate 'create table u1.test_table (x int)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

Использование динамического SQL в триггерах

Триггер выполняется на правах его создателя, а НЕ на правах пользователя, вызвавшего его срабатывание. Таким образом вышесказанное распостраняется и на динамический SQL в триггерах - необходимые привилегии должны быть даны явно, а не через роль.

Комментарии


  • спасибо.

  • вот такие вот грабли..

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

  • вот такие вот грабли..

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

  • вот такие вот грабли..

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

  • Лаконично и понятно. Дякую.

  • Спасибо помогло ;)

  • Я бы еще добавил, что и при ссылках в процедуре вида:
    [src Oracle]
    CREATE OR REPLACE PROCEDURE PPP AS
    Q SCHEMA_NAME.TABLE%ROWTYPE;
    begin
    [/src] необходим явный грант создателю процедуры от владельца SCHEMA_NAME, а не через роль.

  • полезно

  • 11 октября 2006, 17:46 Николай aka Motodor

    Спасибо!

  • Spasibo!

  • Спасибо =)

  • spasibo

  • Спасибо!

  • Spasibo



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Oracle / PL/SQL / ORA-01031: insufficient privileges при выполнении динамического SQL в процедурах