Выполнение Immediate в хранимой процедуре продолжает давать недостаточную ошибку priviliges

Вот определение хранимой процедуры:

CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR) IS
BEGIN
  DECLARE v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) 
      INTO v_cnt 
      FROM all_tables 
     WHERE owner = schema
       AND table_name = tblToDrop;

     IF v_cnt > 0 THEN 
        EXECUTE IMMEDIATE('DROP TABLE someschema.some_table PURGE');
     END IF;
   END;
END;

Вот вызов:

CALL usp_dropTable('SOMESCHEMA', 'SOME_TABLE');

По какой-то причине я продолжаю получать недостаточную ошибку привилегий для команды EXECUTE IMMEDIATE. Я посмотрел онлайн и выяснил, что ошибка недостаточных привилегий обычно означает, что учетная запись пользователя oracle не имеет привилегий для команды, используемой в прохождении запроса, что в данном случае является DROP. Однако у меня есть привилегии на передачу. Я действительно смущен, и я не могу найти решение, которое работает для меня.

Спасибо вам заранее.

РЕШЕНИЕ:

Как уже упоминал Стив, модель безопасности Oracle странна в том, что ей нужно явно знать где-то в процедуре, какие привилегии использовать. Способ сообщить Oracle о том, чтобы использовать ключевое слово AUTHID в инструкции CREATE OR REPLACE. Если вам нужен тот же уровень привилегий, что и создатель процедуры, вы используете AUTHID DEFINER. Если вы хотите, чтобы Oracle использовала привилегии пользователя, выполняющего хранимую процедуру, вы хотите использовать AUTHID CURRENT_USER. Объявление процедуры выглядит следующим образом:

CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR) 
AUTHID CURRENT_USER IS
BEGIN
  DECLARE v_cnt NUMBER;
  BEGIN
    SELECT COUNT(*) 
      INTO v_cnt 
      FROM all_tables 
     WHERE owner = schema
       AND table_name = tblToDrop;

     IF v_cnt > 0 THEN 
        EXECUTE IMMEDIATE('DROP TABLE someschema.some_table PURGE');
     END IF;
   END;
END;

Спасибо всем за ответ. Это была определенно очень неприятная проблема, чтобы добраться до решения.

Ответ 1

Модель безопасности Oracle такова, что при выполнении динамического SQL с использованием Execute Immediate (внутри контекста блока или процедуры PL/SQL) пользователь не имеет привилегий для объектов или команд, предоставляемых через членство в ролях. У вашего пользователя, вероятно, есть роль "DBA" или что-то подобное. Вы должны явно предоставить разрешения "drop table" для этого пользователя. То же самое было бы применимо, если бы вы пытались выбрать из таблиц в другой схеме (например, sys или system) - вам нужно предоставить явным привилегии SELECT для этой таблицы этому пользователю.

Ответ 2

вы можете использовать "AUTHID CURRENT_USER" в своем определении процедуры для ваших требований.

Ответ 3

Вы должны использовать этот пример с помощью AUTHID CURRENT_USER:

CREATE OR REPLACE PROCEDURE Create_sequence_for_tab (VAR_TAB_NAME IN VARCHAR2)
   AUTHID CURRENT_USER
IS
   SEQ_NAME       VARCHAR2 (100);
   FINAL_QUERY    VARCHAR2 (100);
   COUNT_NUMBER   NUMBER := 0;
   cur_id         NUMBER;
BEGIN
   SEQ_NAME := 'SEQ_' || VAR_TAB_NAME;

   SELECT COUNT (*)
     INTO COUNT_NUMBER
     FROM USER_SEQUENCES
    WHERE SEQUENCE_NAME = SEQ_NAME;

   DBMS_OUTPUT.PUT_LINE (SEQ_NAME || '>' || COUNT_NUMBER);

   IF COUNT_NUMBER = 0
   THEN
      --DBMS_OUTPUT.PUT_LINE('DROP SEQUENCE ' || SEQ_NAME);
      -- EXECUTE IMMEDIATE 'DROP SEQUENCE ' || SEQ_NAME;
      -- ELSE
      SELECT 'CREATE SEQUENCE COMPTABILITE.' || SEQ_NAME || ' START WITH ' || ROUND (DBMS_RANDOM.VALUE (100000000000, 999999999999), 0) || ' INCREMENT BY 1'
        INTO FINAL_QUERY
        FROM DUAL;

      DBMS_OUTPUT.PUT_LINE (FINAL_QUERY);
      cur_id := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.parse (cur_id, FINAL_QUERY, DBMS_SQL.v7);
      DBMS_SQL.CLOSE_CURSOR (cur_id);
   -- EXECUTE IMMEDIATE FINAL_QUERY;

   END IF;

   COMMIT;
END;
/

Ответ 4

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