Execute Immediate within a stored procedure keeps giving insufficient priviliges error

Question

Here is the definition of the stored procedure   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  gt  0 THEN          EXECUTE IMMEDIATE  DROP TABLE someschema some table PURGE         END IF     END  END    Here is the call   CALL usp dropTable  SOMESCHEMA    SOME TABLE      For some reason  I keep getting insufficient privileges error for the EXECUTE IMMEDIATE command  I looked online and found out that the insufficient privileges error usually means the oracle user account does not have privileges for the command used in the query that is passes  which in this case is DROP  However  I have drop privileges  I am really confused and I can t seem to find a solution that works for me   Thanks to you in advance   SOLUTION   As Steve mentioned below  Oracle security model is weird in that it needs to know explicitly somewhere in the procedure what kind of privileges to use  The way to let Oracle know that is to use AUTHID keyword in the CREATE OR REPLACE statement  If you want the same level of privileges as the creator of the procedure  you use AUTHID DEFINER  If you want Oracle to use the privileges of the user currently running the stored procedure  you want to use AUTHID CURRENT USER  The procedure declaration looks as follows   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  gt  0 THEN          EXECUTE IMMEDIATE  DROP TABLE someschema some table PURGE         END IF     END  END    Thank you everyone for responding  This was definitely very annoying problem to get to the solution

User · Answer

you could use  AUTHID CURRENT USER  in body of your procedure definition for your requirements

User · Answer

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

User · Answer

Alternatively you can grant the user DROP ANY TABLE privilege if need be and the procedure will run as is without the need for any alteration  Dangerous maybe but depends what you re doing

User · Answer

You should use this example with 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      gt      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

[sql] Execute Immediate within a stored procedure keeps giving insufficient priviliges error

Examples related to sql

Examples related to stored-procedures

Examples related to oracle10g

Examples related to privileges