Скорее всего на чужой объект есть привилегия, выданная только через роль. Для использования объектов другого пользователя или системных привелегий в своих процедурах/триггерах и т.д., а также в job-ах необходимо дать на них привилегию напрямую, не через роль.
Так же это правило следует учесть для динамического SQL:
Используемая версия
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 в триггерах - необходимые привилегии должны быть даны явно, а не через роль.