Execute Immediate within a stored procedure keeps giving insufficient priviliges error
Oracle's security model is such that when executing dynamic SQL using Execute Immediate (inside the context of a PL/SQL block or procedure), the user does not have privileges to objects or commands that are granted via role membership. Your user likely has "DBA" role or something similar. You must explicitly grant "drop table" permissions to this user. The same would apply if you were trying to select from tables in another schema (such as sys or system) - you would need to grant explicit SELECT privileges on that table to this user.
You should use this example with AUTHID CURRENT_USER :
CREATE OR REPLACE PROCEDURE Create_sequence_for_tab (VAR_TAB_NAME IN VARCHAR2) AUTHID CURRENT_USERIS 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;/
you could use "AUTHID CURRENT_USER" in body of your procedure definition for your requirements.