Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Execute Immediate within a stored procedure keeps giving insufficient priviliges error

People also ask

How do I grant execute immediate privileges to user?

0 - Production SQL> create user u identified by u; Utilisateur créé. SQL> grant create session to u; Autorisation de privilèges (GRANT) acceptée. SQL> connect u/u Connecté. SQL> declare n number; 2 begin 3 execute immediate 'select count(*) from dual' into n; 4 end; 5 / Procédure PL/SQL terminée avec succès.

How do I resolve insufficient privileges in SQL?

ORA-01031: insufficient privileges Solution: Go to Your System User. then Write This Code: SQL> grant dba to UserName; //Put This username which user show this error message. Grant succeeded.

Why do I have insufficient privileges?

You see the Insufficient Privileges error if you don't have the right access on different levels. For example, your profile prevents you from accessing the account object, or your role prevents you from accessing a case record.

What is mean by insufficient privileges in Oracle?

ORA-01031 Error Message “Insufficient Privileges” Error ORA-01031 means you are attempting to execute an operation for which you do not have appropriate privileges.


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_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;
/

you could use "AUTHID CURRENT_USER" in body of your procedure definition for your requirements.